How do I dump the data of some SQLite3 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.

You're not saying what you wish to do with the dumped file.

To get a CSV file (which can be imported into almost everything)

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

To get an SQL file (which can be reinserted into a different SQLite database)

.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 -vx -f schema.sql dump.sql > 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.

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

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.

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.