How do I dump the data of some SQLite3 tables

Question

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

User · Answer

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

User · Answer

According to the SQLite documentation for the Command Line Shell For SQLite 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   sqlite gt   header on sqlite gt   mode csv sqlite gt   once c  work dataout csv sqlite gt  SELECT   FROM tab1  sqlite gt   exit   Then use the   import  command to import CSV  comma separated value  data into an SQLite table   sqlite gt   mode csv sqlite gt   import C  work dataout csv tab1 sqlite gt   exit   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

User · Answer

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

User · Answer

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  gt  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

User · Answer

You re not saying 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 csv  select   from MyTable    If you want to reinsert into a different SQLite database then    mode insert  lt target table name gt   out file sql  select   from MyTable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can do this getting difference of  schema and  dump commands  for example with grep   sqlite3 some db  schema  gt  schema sql sqlite3 some db  dump  gt  dump sql grep -vx -f schema sql dump sql  gt  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

User · Answer

According to the SQLite documentation for the Command Line Shell For SQLite 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   sqlite gt   header on sqlite gt   mode csv sqlite gt   once c  work dataout csv sqlite gt  SELECT   FROM tab1  sqlite gt   exit   Then use the   import  command to import CSV  comma separated value  data into an SQLite table   sqlite gt   mode csv sqlite gt   import C  work dataout csv tab1 sqlite gt   exit   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

User · Answer

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

User · Answer

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

User · Answer

You can do this getting difference of  schema and  dump commands  for example with grep   sqlite3 some db  schema  gt  schema sql sqlite3 some db  dump  gt  dump sql grep -vx -f schema sql dump sql  gt  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

User · Answer

You re not saying 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 csv  select   from MyTable    If you want to reinsert into a different SQLite database then    mode insert  lt target table name gt   out file sql  select   from MyTable

User · Answer

Review of other possible solutions  Include only INSERTs  sqlite3 database db3  dump   grep   INSERT INTO  tablename     Easy to implement but it will fail if any of your columns include new lines  SQLite insert mode  for t in   sqlite3  DB  tables   do     echo -e   mode insert  t nselect   from  t   done   sqlite3  DB  gt  backup sql   This is a nice and customizable solution  but it doesn t work if your columns have blob objects like  Geometry  type in spatialite  Diff the dump with the schema  sqlite3 some db  schema  gt  schema sql sqlite3 some db  dump  gt  dump sql grep -v -f schema sql dump  gt  data sql   Not sure why  but is not working for me  Another  new  possible solution  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 expression like this  grep -Pzo    s  INSERT        t       To select the tables do be dumped  dump admits a LIKE argument to match the table names  but if this is not enough probably a simple script is better option  TABLES  table1 table2 table3   echo     gt   tmp backup sql for t in  TABLES   do     echo -e   dump   t     sqlite3 database db3   grep -Pzo    s  INSERT          gt  gt   tmp backup sql done   or  something more elaborated to respect foreign keys and encapsulate all the dump in only one transaction  TABLES  table1 table2 table3   echo  BEGIN TRANSACTION    gt   tmp backup sql echo     gt  gt   tmp backup sql for t in  TABLES   do     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 done  echo     gt  gt   tmp backup sql echo  COMMIT    gt  gt   tmp backup sql   Take into account that the grep expression will fail if    is a string present in any of the columns  To restore it  in a database with the tables already created   sqlite3 -bail database db3  lt   tmp backup sql

User · Answer

You re not saying 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 csv  select   from MyTable    If you want to reinsert into a different SQLite database then    mode insert  lt target table name gt   out file sql  select   from MyTable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Review of other possible solutions  Include only INSERTs  sqlite3 database db3  dump   grep   INSERT INTO  tablename     Easy to implement but it will fail if any of your columns include new lines  SQLite insert mode  for t in   sqlite3  DB  tables   do     echo -e   mode insert  t nselect   from  t   done   sqlite3  DB  gt  backup sql   This is a nice and customizable solution  but it doesn t work if your columns have blob objects like  Geometry  type in spatialite  Diff the dump with the schema  sqlite3 some db  schema  gt  schema sql sqlite3 some db  dump  gt  dump sql grep -v -f schema sql dump  gt  data sql   Not sure why  but is not working for me  Another  new  possible solution  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 expression like this  grep -Pzo    s  INSERT        t       To select the tables do be dumped  dump admits a LIKE argument to match the table names  but if this is not enough probably a simple script is better option  TABLES  table1 table2 table3   echo     gt   tmp backup sql for t in  TABLES   do     echo -e   dump   t     sqlite3 database db3   grep -Pzo    s  INSERT          gt  gt   tmp backup sql done   or  something more elaborated to respect foreign keys and encapsulate all the dump in only one transaction  TABLES  table1 table2 table3   echo  BEGIN TRANSACTION    gt   tmp backup sql echo     gt  gt   tmp backup sql for t in  TABLES   do     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 done  echo     gt  gt   tmp backup sql echo  COMMIT    gt  gt   tmp backup sql   Take into account that the grep expression will fail if    is a string present in any of the columns  To restore it  in a database with the tables already created   sqlite3 -bail database db3  lt   tmp backup sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You re not saying 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 csv  select   from MyTable    If you want to reinsert into a different SQLite database then    mode insert  lt target table name gt   out file sql  select   from MyTable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

[sql] How do I dump the data of some SQLite3 tables?

Examples related to sql

Examples related to sqlite