Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- INSERT INTO "MyTable" VALUES ('A', 'B');
- INSERT INTO "MyTable" (Column1, Column2) VALUES ('A', 'B');
- 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;
- /|/ {
- 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("%sn", values_part);
- }
- $ 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');
- 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;
- /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("%sn", values_part);
- }
- 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');
- #!/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;
- }
- }
- 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
- 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]
- }
- CREATE TABLE "strom" (
- "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
- "otec" integer NOT NULL,
- "nazev" text NOT NULL,
- "ikona" text NULL,
- "barva" text NULL
- );
- 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, /(".*")/, b))
- columnlist[tablename] = columnlist[tablename] b[1]
- if (match($0, /`(.*)`/, 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
- }
- sqlite> .headers on
- sqlite> .mode insert MyTable
- sqlite> .output MyTable_data.sql
- sqlite> select * from MyTable;
- sqlite> .quit
- sudo apt install libsqlite3-mod-impexp
- .load libsqlite3_mod_impexp
- select export_sql('dump.sql','1');
- 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],");"
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement