Is there any SQLite command or third-party tool that allows database dumps to include column names in the INSERT INTO statements?
Instead of
INSERT INTO "MyTable" VALUES ('A', 'B');
I'd like to see
INSERT INTO "MyTable" (Column1, Column2) VALUES ('A', 'B');
The .dump command in SQLite only offers the first version.
This does not answer the question. I'm writing this here because it is how I am tackling a similar issue. One way is to dump the structure and data separately. For inserts like you describe outside of the data file:
sqlite> .headers on
sqlite> .mode insert MyTable
sqlite> .output MyTable_data.sql
sqlite> select * from MyTable;
sqlite> .quit
Let me take another crack at this.
Dump column names and INSERT statements to a file.
sqlite> .output test.data
sqlite> pragma table_info(test);
sqlite> .dump test
sqlite> .quit
$ cat test.data
0|test_id|int|0||1
1|test_name|varchar(35)|0||0
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (test_id int primary key, test_name varchar(35));
INSERT INTO "test" VALUES(1,'Wibble');
INSERT INTO "test" VALUES(2,'Wobble');
INSERT INTO "test" VALUES(3,'Pernicious');
COMMIT;
Now run this awk script
/\|/ {
split($0, col_name, "|");
column_names[++n] = col_name[2];
}
/INSERT INTO \"[A-Za-z].*\"/ {
insert_part = match($0, /INSERT INTO \"[A-Za-z].*\"/);
printf("%s ", substr($0, RSTART, RLENGTH));
printf("(");
for (i = 1; i <= n; i++) {
if (i == 1) {
printf("%s", column_names[i]);
}
else {
printf(", %s", column_names[i]);
}
}
printf(") ");
values_part = substr($0, RLENGTH+1, length($0) - RSTART);
printf("%s\n", values_part);
}
And we get
$ awk -f dump_with_col_names.awk test.data
INSERT INTO "test" (test_id, test_name) VALUES(1,'Wibble');
INSERT INTO "test" (test_id, test_name) VALUES(2,'Wobble');
INSERT INTO "test" (test_id, test_name) VALUES(3,'Pernicious');
There is an SQLite extension module for importing/exporting database information from/to SQL source text and export to CSV text. http://www.ch-werner.de/sqliteodbc/html/impexp_8c.html
For example in Ubuntu your steps are:
Install module from ubuntu repository
sudo apt install libsqlite3-mod-impexp
Load module in sqlite command line prompt run
.load libsqlite3_mod_impexp
Export database to dump.sql file
select export_sql('dump.sql','1');
Result example for my database is
INSERT OR REPLACE INTO "camera" ("name","reviews") VALUES('BenQ GH700', NULL);
INSERT OR REPLACE INTO "camera" ("name","reviews") VALUES('Canon EOS 40D', NULL);
I created this shell script:
#!/bin/sh
SQLITE=sqlite3
if [ -z "$1" ] ; then
echo usage: $0 sqlite3.db
exit
fi
DB="$1"
TABLES=`"$SQLITE" "$DB" .tables`
echo "-- $TABLES"
echo 'BEGIN TRANSACTION;'
for TABLE in $TABLES ; do
echo
echo "-- $TABLE:";
COLS=`"$SQLITE" "$DB" "pragma table_info($TABLE)" |
cut -d'|' -f2 `
COLS_CS=`echo $COLS | sed 's/ /,/g'`
echo -e ".mode insert\nselect $COLS_CS from $TABLE;\n" |
"$SQLITE" "$DB" |
sed "s/^INSERT INTO table/INSERT INTO $TABLE ($COLS_CS)/"
done
echo 'COMMIT;';
Two problems:
I took a quick look at the source code. I didn't see any obvious way to do that. But I whipped up a quick and dirty awk script to insert the column names.
Starting with this dump:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (test_id int primary key, test_name varchar(35));
INSERT INTO "test" VALUES(1,'Wibble');
INSERT INTO "test" VALUES(2,'Wobble');
INSERT INTO "test" VALUES(3,'Pernicious');
COMMIT;
I ran this awk script
/CREATE TABLE/ {
# Extract the part between parens. This part contains the
# column definitions.
first_col = match($0, /\(.*\)/ );
if (first_col) {
num_columns = split(substr($0, RSTART + 1, RLENGTH), a, ",");
for (i = 1; i <= num_columns; i++) {
sub(/^ /, "", a[i]);
split(a[i], names, " ");
column_names[i] = names[1];
}
}
}
/INSERT INTO \"[A-Za-z].*\"/ {
insert_part = match($0, /INSERT INTO \"[A-Za-z].*\"/);
printf("%s ", substr($0, RSTART, RLENGTH));
printf("(");
for (j = 1; j <= num_columns; j++) {
if (j == 1) {
printf("%s", column_names[j]);
}
else {
printf(", %s", column_names[j]);
}
}
printf(") ");
values_part = substr($0, RLENGTH+1, length($0) - RSTART);
printf("%s\n", values_part);
}
which gave me this output.
INSERT INTO "test" (test_id, test_name) VALUES(1,'Wibble');
INSERT INTO "test" (test_id, test_name) VALUES(2,'Wobble');
INSERT INTO "test" (test_id, test_name) VALUES(3,'Pernicious');
Here is a Perl version that works with any number of tables:
#!/usr/bin/perl
use strict;
use warnings;
my @column_names;
my $col_reset = 1;
while(<>)
{
if (/^\d+\|/) {
if ($col_reset)
{
@column_names = ();
$col_reset = 0;
}
my @col_info = split(/\|/);
push @column_names, $col_info[1];
}
if(/INSERT INTO/) {
m/(INSERT INTO \"?[A-Za-z_]+\"?) (.*)/ or die $_;
my $insert_part = $1;
my $values_part = $2;
print $insert_part." (".join(",", @column_names).") ".$values_part."\n";
$col_reset = 1;
}
}
And this is how I generated the dump of every table in the database:
grep 'CREATE TABLE' /tmp/school.sql.final \
| awk '{ print $3 }' \
| while read table; do
echo -e "pragma table_info($table);\n.dump $table"
done | sqlite3 school.db \
> /tmp/school.sql.final-with-table-info
Pure SQL solution combined with shell pipes:
Create file dump_sqlite.sql:
.headers off
.mode list
select
"select " || """" || "insert into " || tab || " (" ||
"" || group_concat(col_name) || ") VALUES (" || """ || " ||
group_concat(col_val, " || "","" || ") ||
" || "")" || """" || " FROM " || tab || ";" as stmt
from (
select
m.name as tab,
ti.name as col_name,
case
when ti.type like "text" or ti.type like "varchar%"
then """"" || " || "coalesce(quote(`" || ti.name || "`), 'NULL')" || " || """""
else "coalesce(`" || ti.name || "`, 'NULL')"
end as col_val,
ti.type as coltype
from sqlite_master as m,
-- https://stackoverflow.com/a/54962853
PRAGMA_TABLE_INFO(m.name) as ti
where m.type = 'table' and m.name not like 'sqlite_%'
) group by tab
;
Then execute shell comand:
sqlite3 your.db < ./dump_sqlite.sql | sqlite3 your.db
If you want to save output to the file (instead of print it to console), add final redirection to a file.
sqlite3 your.db < ./dump_sqlite.sql | sqlite3 your.db > dump.sql
Another AWK script which works directly from the output of "sqlite3 data.db .dump" for any number of tables
It uses the fact that CREATE statements are now printed with each column on its own line
BEGIN {
state = "default" # Used to know if we are in the middle of a table declaration
print_definitions = 1 # Wether to print CREATE statements or not
}
state == "default" && match($0, /^CREATE TABLE ([A-Za-z0-9_]+)/, a) {
tablename = a[1]
state = "definition"
if (print_definitions)
print
next
}
state == "definition" && /^);$/ {
state = "default"
if (print_definitions)
print
next
}
state == "definition" && ! ( /PRIMARY/ || /UNIQUE/ || /CHECK/ || /FOREIGN/) {
if (length(columnlist [tablename]))
columnlist[tablename] = columnlist[tablename] ", "
columnlist[tablename] = columnlist[tablename] $1
if (print_definitions)
print
next
}
state == "default" && match($0, /^(INSERT INTO ")([A-Za-z0-9_]+)"(.*)$/, a) {
print a[1] a[2] "\" (" columnlist[a[2]] ")" a[3]
}
Louis L. solution was not working for me, so I made this gawk solution tested with dumps from sqlite3 version 3.8.7.1
the table CREATE statements are like e.g.
CREATE TABLE "strom" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"otec" integer NOT NULL,
"nazev" text NOT NULL,
"ikona" text NULL,
"barva" text NULL
);
but also may look like this one
CREATE TABLE "changes" (
"version" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"last_change" text NOT NULL DEFAULT (datetime('now','localtime')),
`ref` text NOT NULL,
"ref_id" text NULL,
"action" text NOT NULL
, "data" text NOT NULL DEFAULT '');
#!/usr/bin/gawk -f
# input is sqlite3 dump, tested with sqlite3 version 3.8.7.1
# output are INSERT statements including column names
# i.e. not e.g.
# INSERT INTO "changes" VALUES(1,'2016-07-19 17:46:12','cenik','10','UPDATE','');
# like in standard dump
# but
# INSERT INTO "changes" ("version", "last_change", "ref", "ref_id", "action", "data") VALUES(1,'2016-07-19 17:46:12','cenik','10','UPDATE','');
# BEGIN TRANSACTION and COMMIT are included in output
BEGIN {
state = "default" # default/definition/insert let us know wether we are in CREATE or INSERT statement
print_definitions = 0 # wether to print CREATE statements or not
}
state == "default" && match($0, /^CREATE TABLE \"([A-Za-z0-9_]+)\" *\($/, a) {
tablename = a[1]
state = "definition"
if (print_definitions)
print
next
}
state == "definition" && /^ *); *$/ {
state = "default"
if (print_definitions)
print
next
}
state == "definition" && ! ( /^[\ ]{1,2}PRIMARY/ || /UNIQUE/ || /CHECK/ || /^[\ ]{1,2}FOREIGN KEY.*REFERENCES/) {
if (length(columnlist [tablename]))
columnlist[tablename] = columnlist[tablename] ", "
if (match($0, /(\".*\")\s/, b))
columnlist[tablename] = columnlist[tablename] b[1]
if (match($0, /`(.*)`\s/, c))
columnlist[tablename] = columnlist[tablename] "\""c[1]"\""
if (print_definitions)
print
}
state == "definition" && /^.*); *$/ {
state = "default"
next
}
state == "default" && match($0, /^(INSERT INTO ")([A-Za-z0-9_]+)"(.*)/, a) {
print a[1] a[2] "\" (" columnlist[a[2]] ")" a[3]
state = "insert"
if (/^.*); *$/)
state = "default"
}
state == "insert" && ! /^INSERT INTO/{
print
}
state == "insert" && /^.*); *$/ {
state = "default"
next
}
state == "default" && (/^ *BEGIN TRANSACTION;/ || /^ *COMMIT;/) {
print
}
If you don't mind GUI. You can use DB Browser for SQLite. Check option Keep column names in INSERT INTO
straightforward python script do the trick
import sqlite3
infile="your_file.sqlite3"
table="your_table"
conn = sqlite3.connect(infile)
conn.row_factory = sqlite3.Row
c = conn.cursor()
res = c.execute("SELECT * FROM " + table)
curr_row = -1
for row in res:
curr_row += 1
if curr_row == 0:
col_names = sorted(row.keys())
s = "INSERT INTO " + table + " ("
for col_name in col_names:
s+=col_name + ","
prefix = s[:-1] + ") VALUES ("
s = ""
for col_name in col_names:
col_val = row[col_name]
if isinstance(col_val,int) or isinstance(col_val,float):
s+= str(row[col_name]) +","
else:
s+= "'" + str(row[col_name]) +"',"
print prefix,s[:-1],");"
