Export part of sq3 database

Question

Hi,
I'm using python2 and sqlite3 and want to export a sql query. This query should be saved in the form that I can insert it again in my original database.

Do I have to do this manual or does anybody know a neat trick?

I found this functionality in the pycharm sql plugin (export as sql...) but I need it in python.
Any hints or ideas are appreciated.

Answer 1

Within Python (no jumping out to shell automation) this answer tries to emulate the shell ".dump" command:
https://stackoverflow.com/a/6677833

Surrounding answers show other dump methods (one goes by table and may work with a finer query).

Answer 2

If you were automating, you could send the ".mode" command directly to the sqlite3 binary. Here's a StackOverflow thread with mostly shell automation:
https://stackoverflow.com/questions/75675/how-do-i-dump-the-data-of-some-sqlite3-tables
I'll look for something within Python.

sqlite> .mode insert
sqlite> select * from Meta;
INSERT INTO table VALUES(1,'[e-mail]','/destination/dir','/source/dir','foo');
sqlite>

The .mode command is visible when looking at .help:
.mode MODE ?TABLE? Set output mode where MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
* THIS ONE* insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator strings
tabs Tab-separated values
tcl TCL list elements

Schema for insert above:
sqlite> .sch
CREATE TABLE Meta (_id integer primary key autoincrement, accountId text, remoteRoot text, localRoot text, folderId text);

Answer 3

thank you for your answers