Create dump file only from some SQLite tables

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

sqlite3 db .dump 

but without dumping the schema and selecting which tables to dump.

Replay

You don't say what you wish to do with the dumped file.

I would use the following to get a CSV file, which I can import into almost everything

.mode csv
-- use '.separator SOME_STRING' for something other than a comma.
.headers on
.out file.dmp
select * from MyTable;

If you want to reinsert into a different SQLite database then:

.mode insert <target_table_name>
.out file.sql
select * from MyTable;

You can do this getting difference of .schema and .dump commands. for example with grep:

sqlite3 some.db .schema > schema.sql
sqlite3 some.db .dump > dump.sql
grep -v -f schema.sql dump > data.sql

data.sql file will contain only data without schema, something like this:

BEGIN TRANSACTION;
INSERT INTO "table1" VALUES ...;
...
INSERT INTO "table2" VALUES ...;
...
COMMIT;

I hope this helps you.

Not the best way, but at lease does not need external tools (except grep, which is standard on *nix boxes anyway)

sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'

but you do need to do this command for each table you are looking for though.

Note that this does not include schema.

You can specify one or more table arguments to the .dump special command, e.g.sqlite3 db ".dump 'table1' 'table2'".

As an improvement to Paul Egan's answer, this can be accomplished as follows:

sqlite3 database.db3 '.dump "table1" "table2"' | grep '^INSERT'

--or--

sqlite3 database.db3 '.dump "table1" "table2"' | grep -v '^CREATE'

The caveat, of course, is that you have to have grep installed.

Any answer which suggests using grep to exclude the CREATE lines or just grab the INSERT lines from the sqlite3 $DB .dump output will fail badly. The CREATE TABLE commands list one column per line (so excluding CREATE won't get all of it), and values on the INSERT lines can have embedded newlines (so you can't grab just the INSERT lines).

for t in $(sqlite3 $DB .tables); do
    echo -e ".mode insert $t\nselect * from $t;"
done | sqlite3 $DB > backup.sql

Tested on sqlite3 version 3.6.20.

If you want to exclude certain tables you can filter them with $(sqlite $DB .tables | grep -v -e one -e two -e three), or if you want to get a specific subset replace that with one two three.

You could use a tool like SQLite Administrator.

There's a long list of such tools here.

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:

from os import path
import csv 

def convert_to_csv(directory, db_name):
    conn = sqlite3.connect(path.join(directory, db_name + '.db'))
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    for table in tables:
        table = table[0]
        cursor.execute('SELECT * FROM ' + table)
        column_names = [column_name[0] for column_name in cursor.description]
        with open(path.join(directory, table + '.csv'), 'w') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(column_names)
            while True:
                try:
                    csv_writer.writerow(cursor.fetchone())
                except csv.Error:
                    break

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:

        if 'id' in column_names:
            with open(path.join(directory, table + '_aggregate.csv'), 'w') as csv_file:
                csv_writer = csv.writer(csv_file)
                column_names.remove('id')
                column_names.remove('round')
                sum_string = ','.join('sum(%s)' % item for item in column_names)
                cursor.execute('SELECT round, ' + sum_string +' FROM ' + table + ' GROUP BY round;')
                csv_writer.writerow(['round'] + column_names)
                while True:
                    try:
                        csv_writer.writerow(cursor.fetchone())
                    except csv.Error:
                        break

The best method would be to take the code the sqlite3 db dump would do, excluding schema parts.

Example pseudo code:

SELECT 'INSERT INTO ' || tableName || ' VALUES( ' ||
  {for each value} ' quote(' || value || ')'     (+ commas until final)
|| ')' FROM 'tableName' ORDER BY rowid DESC

See: src/shell.c:838 (for sqlite-3.5.9) for actual code

You might even just take that shell and comment out the schema parts and use that.

This version works well with newlines inside inserts:

sqlite3 database.sqlite3 .dump | grep -v '^CREATE'

In practice excludes all the lines starting with CREATE which is less likely to contain newlines

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 .dump.

Finally I wrote a tool for the split up the SQL generated from .dump:

https://github.com/motherapp/sqlite_sql_parser/

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.

Category: sql Time: 2008-09-16 Views: 14
Tags: sql sqlite

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.152 (s). 12 q(s)