<p>How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)?
The dump should be in SQL format, as it should be easily re-entered into the database later and should be done from the command line. Something like</p>

<pre><code>sqlite3 db .dump
</code></pre>

<p>but without dumping the schema and selecting which tables to dump.</p>

[{'answer_id': 75694, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': -1, 'selected': False, 'text': '<p>You could do a select on the tables inserting commas after each field to produce a csv, or use a GUI tool to return all the data and save it to a csv.</p>\n'}, {'answer_id': 79168, 'author': 'harningt', 'author_id': 12713, 'author_profile': 'https://Stackoverflow.com/users/12713', 'pm_score': 2, 'selected': False, 'text': "<p>The best method would be to take the code the sqlite3 db dump would do, excluding schema parts.</p>\n\n<p>Example pseudo code:</p>\n\n<pre><code>SELECT 'INSERT INTO ' || tableName || ' VALUES( ' || \n  {for each value} ' quote(' || value || ')'     (+ commas until final)\n|| ')' FROM 'tableName' ORDER BY rowid DESC\n</code></pre>\n\n<p>See: <code>src/shell.c:838</code>  (for sqlite-3.5.9) for actual code</p>\n\n<p>You might even just take that shell and comment out the schema parts and use that.</p>\n"}, {'answer_id': 199221, 'author': 'CyberFonic', 'author_id': 23999, 'author_profile': 'https://Stackoverflow.com/users/23999', 'pm_score': 8, 'selected': False, 'text': "<p>You're not saying what you wish to do with the dumped file.</p>\n<p>To get a CSV file (which can be imported into almost everything)</p>\n<pre><code>.mode csv \n-- use '.separator SOME_STRING' for something other than a comma.\n.headers on \n.out file.csv \nselect * from MyTable;\n</code></pre>\n<p>To get an SQL file (which can be reinserted into a different SQLite database)</p>\n<pre><code>.mode insert &lt;target_table_name&gt;\n.out file.sql \nselect * from MyTable;\n</code></pre>\n"}, {'answer_id': 422842, 'author': 'polyglot', 'author_id': 45383, 'author_profile': 'https://Stackoverflow.com/users/45383', 'pm_score': 5, 'selected': False, 'text': '<p>Not the best way, but at lease does not need external tools (except grep, which is standard on *nix boxes anyway)</p>\n\n<pre><code>sqlite3 database.db3 .dump | grep \'^INSERT INTO "tablename"\'\n</code></pre>\n\n<p>but you do need to do this command for each table you are looking for though.</p>\n\n<p>Note that this does not include schema.</p>\n'}, {'answer_id': 1938480, 'author': 'Paul Egan', 'author_id': 2211429, 'author_profile': 'https://Stackoverflow.com/users/2211429', 'pm_score': 5, 'selected': False, 'text': '<p>You can specify one or more table arguments to the .dump special command, e.g.<code>sqlite3 db ".dump \'table1\' \'table2\'"</code>.</p>\n'}, {'answer_id': 7526055, 'author': 'jellyfish', 'author_id': 534951, 'author_profile': 'https://Stackoverflow.com/users/534951', 'pm_score': 8, 'selected': False, 'text': '<p>You can do this getting difference of .schema and .dump commands. for example with grep:</p>\n\n<pre><code>sqlite3 some.db .schema &gt; schema.sql\nsqlite3 some.db .dump &gt; dump.sql\ngrep -vx -f schema.sql dump.sql &gt; data.sql\n</code></pre>\n\n<p><code>data.sql</code> file will contain only data without schema, something like this:</p>\n\n<pre><code>BEGIN TRANSACTION;\nINSERT INTO "table1" VALUES ...;\n...\nINSERT INTO "table2" VALUES ...;\n...\nCOMMIT;\n</code></pre>\n\n<p>I hope this helps you.</p>\n'}, {'answer_id': 7974100, 'author': 'Drew', 'author_id': 295290, 'author_profile': 'https://Stackoverflow.com/users/295290', 'pm_score': 3, 'selected': False, 'text': '<p>As an improvement to Paul Egan\'s answer, this can be accomplished as follows:</p>\n\n<pre><code>sqlite3 database.db3 \'.dump "table1" "table2"\' | grep \'^INSERT\'\n</code></pre>\n\n<p>--or--</p>\n\n<pre><code>sqlite3 database.db3 \'.dump "table1" "table2"\' | grep -v \'^CREATE\'\n</code></pre>\n\n<p>The caveat, of course, is that you have to have grep installed. </p>\n'}, {'answer_id': 10619827, 'author': 'Elia Schito', 'author_id': 601782, 'author_profile': 'https://Stackoverflow.com/users/601782', 'pm_score': 2, 'selected': False, 'text': "<p>This version works well with newlines inside inserts:</p>\n\n<p><code>sqlite3 database.sqlite3 .dump | grep -v '^CREATE'</code></p>\n\n<p>In practice excludes all the lines starting with <code>CREATE</code> which is less likely to contain newlines</p>\n"}, {'answer_id': 20014210, 'author': 'retracile', 'author_id': 100073, 'author_profile': 'https://Stackoverflow.com/users/100073', 'pm_score': 4, 'selected': False, 'text': '<p>Any answer which suggests using grep to exclude the <code>CREATE</code> lines or just grab the <code>INSERT</code> lines from the <code>sqlite3 $DB .dump</code> output will fail badly.  The <code>CREATE TABLE</code> commands list one column per line (so excluding <code>CREATE</code> won\'t get all of it), and values on the <code>INSERT</code> lines can have embedded newlines (so you can\'t grab just the <code>INSERT</code> lines).</p>\n\n<pre><code>for t in $(sqlite3 $DB .tables); do\n    echo -e ".mode insert $t\\nselect * from $t;"\ndone | sqlite3 $DB &gt; backup.sql\n</code></pre>\n\n<p>Tested on sqlite3 version 3.6.20.</p>\n\n<p>If you want to exclude certain tables you can filter them with <code>$(sqlite $DB .tables | grep -v -e one -e two -e three)</code>, or if you want to get a specific subset replace that with <code>one two three</code>.</p>\n'}, {'answer_id': 23658679, 'author': 'Davoud Taghawi-Nejad', 'author_id': 236830, 'author_profile': 'https://Stackoverflow.com/users/236830', 'pm_score': 3, 'selected': False, 'text': '<p>In Python or Java or any high level language the .dump does not work. We need to code the conversion to CSV by hand. I give an Python example. Others, examples would be appreciated:</p>\n\n<pre><code>from os import path   \nimport csv \n\ndef convert_to_csv(directory, db_name):\n    conn = sqlite3.connect(path.join(directory, db_name + \'.db\'))\n    cursor = conn.cursor()\n    cursor.execute("SELECT name FROM sqlite_master WHERE type=\'table\';")\n    tables = cursor.fetchall()\n    for table in tables:\n        table = table[0]\n        cursor.execute(\'SELECT * FROM \' + table)\n        column_names = [column_name[0] for column_name in cursor.description]\n        with open(path.join(directory, table + \'.csv\'), \'w\') as csv_file:\n            csv_writer = csv.writer(csv_file)\n            csv_writer.writerow(column_names)\n            while True:\n                try:\n                    csv_writer.writerow(cursor.fetchone())\n                except csv.Error:\n                    break\n</code></pre>\n\n<p>If you have \'panel data, in other words many individual entries with id\'s add this to the with look and it also dumps summary statistics:</p>\n\n<pre><code>        if \'id\' in column_names:\n            with open(path.join(directory, table + \'_aggregate.csv\'), \'w\') as csv_file:\n                csv_writer = csv.writer(csv_file)\n                column_names.remove(\'id\')\n                column_names.remove(\'round\')\n                sum_string = \',\'.join(\'sum(%s)\' % item for item in column_names)\n                cursor.execute(\'SELECT round, \' + sum_string +\' FROM \' + table + \' GROUP BY round;\')\n                csv_writer.writerow([\'round\'] + column_names)\n                while True:\n                    try:\n                        csv_writer.writerow(cursor.fetchone())\n                    except csv.Error:\n                        break \n</code></pre>\n'}, {'answer_id': 28554255, 'author': 'Walty Yeung', 'author_id': 176423, 'author_profile': 'https://Stackoverflow.com/users/176423', 'pm_score': 0, 'selected': False, 'text': '<p>The answer by retracile should be the closest one, yet it does not work for my case. One insert query just broke in the middle and the export just stopped. Not sure what is the reason. However It works fine during <code>.dump</code>.</p>\n\n<p>Finally I wrote a tool for the split up the SQL generated from <code>.dump</code>:</p>\n\n<p><a href="https://github.com/motherapp/sqlite_sql_parser/" rel="nofollow">https://github.com/motherapp/sqlite_sql_parser/</a> </p>\n'}, {'answer_id': 37296788, 'author': 'Francisco Puga', 'author_id': 930271, 'author_profile': 'https://Stackoverflow.com/users/930271', 'pm_score': 3, 'selected': False, 'text': '<h1>Review of other possible solutions</h1>\n\n<p><strong>Include only INSERTs</strong></p>\n\n<pre><code>sqlite3 database.db3 .dump | grep \'^INSERT INTO "tablename"\'\n</code></pre>\n\n<p>Easy to implement but it will fail if any of your columns include new lines</p>\n\n<p><strong>SQLite insert mode</strong></p>\n\n<pre><code>for t in $(sqlite3 $DB .tables); do\n    echo -e ".mode insert $t\\nselect * from $t;"\ndone | sqlite3 $DB &gt; backup.sql\n</code></pre>\n\n<p>This is a nice and customizable solution, but it doesn\'t work if your columns have blob objects like \'Geometry\' type in spatialite</p>\n\n<p><strong>Diff the dump with the schema</strong></p>\n\n<pre><code>sqlite3 some.db .schema &gt; schema.sql\nsqlite3 some.db .dump &gt; dump.sql\ngrep -v -f schema.sql dump &gt; data.sql\n</code></pre>\n\n<p>Not sure why, but is not working for me</p>\n\n<h1>Another (new) possible solution</h1>\n\n<p>Probably there is not a best answer to this question, but one that is working for me is grep the inserts taking into account that be new lines in the column values with an <a href="https://stackoverflow.com/a/7167115/930271">expression like this</a></p>\n\n<pre><code>grep -Pzo "(?s)^INSERT.*\\);[ \\t]*$"\n</code></pre>\n\n<p>To select the tables do be dumped <code>.dump</code> admits a LIKE argument to match the table names, but if this is not enough probably a simple script is better option</p>\n\n<pre><code>TABLES=\'table1 table2 table3\'\n\necho \'\' &gt; /tmp/backup.sql\nfor t in $TABLES ; do\n    echo -e ".dump ${t}" | sqlite3 database.db3 | grep -Pzo "(?s)^INSERT.*?\\);$" &gt;&gt; /tmp/backup.sql\ndone\n</code></pre>\n\n<p>or, something more elaborated to respect foreign keys and encapsulate all the dump in only one transaction</p>\n\n<pre><code>TABLES=\'table1 table2 table3\'\n\necho \'BEGIN TRANSACTION;\' &gt; /tmp/backup.sql\necho \'\' &gt;&gt; /tmp/backup.sql\nfor t in $TABLES ; do\n    echo -e ".dump ${t}" | sqlite3 $1 | grep -Pzo "(?s)^INSERT.*?\\);$" | grep -v -e \'PRAGMA foreign_keys=OFF;\' -e \'BEGIN TRANSACTION;\' -e \'COMMIT;\' &gt;&gt; /tmp/backup.sql\ndone\n\necho \'\' &gt;&gt; /tmp/backup.sql\necho \'COMMIT;\' &gt;&gt; /tmp/backup.sql\n</code></pre>\n\n<p>Take into account that the grep expression will fail if <code>);</code> is a string present in any of the columns</p>\n\n<p>To restore it (in a database with the tables already created)</p>\n\n<pre><code>sqlite3 -bail database.db3 &lt; /tmp/backup.sql\n</code></pre>\n'}, {'answer_id': 41738349, 'author': 'PeterCo', 'author_id': 2613621, 'author_profile': 'https://Stackoverflow.com/users/2613621', 'pm_score': 2, 'selected': False, 'text': '<p>According to the SQLite documentation for the <a href="https://www.sqlite.org/cli.html" rel="nofollow noreferrer">Command Line Shell For SQLite</a> you can export an SQLite table (or part of a table) as CSV, simply by setting the "mode" to "csv" and then run a query to extract the desired rows of the table:</p>\n\n<pre><code>sqlite&gt; .header on\nsqlite&gt; .mode csv\nsqlite&gt; .once c:/work/dataout.csv\nsqlite&gt; SELECT * FROM tab1;\nsqlite&gt; .exit\n</code></pre>\n\n<p>Then use the ".import" command to import CSV (comma separated value) data into an SQLite table:</p>\n\n<pre><code>sqlite&gt; .mode csv\nsqlite&gt; .import C:/work/dataout.csv tab1\nsqlite&gt; .exit\n</code></pre>\n\n<p>Please read the further documentation about the two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist. </p>\n'}]
['https://Stackoverflow.com/questions/75675', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6068/']