List of tables db schema dump etc using the Python sqlite3 API

Question

For some reason I can t find a way to get the equivalents of sqlite s interactive shell commands    tables  dump   using the Python sqlite3 API    Is there anything like that

User · Answer

The FASTEST way of doing this in python is using Pandas (version 0.16 and up).

Dump one table:

db = sqlite3.connect('database.db')
table = pd.read_sql_query("SELECT * from table_name", db)
table.to_csv(table_name + '.csv', index_label='index')

Dump all tables:

import sqlite3
import pandas as pd


def to_csv():
    db = sqlite3.connect('database.db')
    cursor = db.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    for table_name in tables:
        table_name = table_name[0]
        table = pd.read_sql_query("SELECT * from %s" % table_name, db)
        table.to_csv(table_name + '.csv', index_label='index')
    cursor.close()
    db.close()

User · Answer

Check out here for dump  It seems there is a dump function in the library sqlite3

User · Answer

In Python   con   sqlite3 connect  database db   cursor   con cursor   cursor execute  SELECT name FROM sqlite master WHERE type  table     print cursor fetchall      Watch out for my other answer  There is a much faster way using pandas

User · Answer

Apparently the version of sqlite3 included in Python 2 6 has this ability  http   docs python org dev library sqlite3 html    Convert file existing db db to SQL dump file dump sql import sqlite3  os  con   sqlite3 connect  existing db db   with open  dump sql    w   as f      for line in con iterdump            f write   s n    line

User · Answer

usr bin env python   - - coding  utf-8 - -  if   name         main         import sqlite3     dbname      db database db     try        print  INITILIZATION           con   sqlite3 connect dbname        cursor   con cursor         cursor execute  SELECT name FROM sqlite master WHERE type  table           tables   cursor fetchall         for tbl in tables           print   n            tbl 0                        cursor execute  SELECT   FROM   tbl 0                rows   cursor fetchall            for row in rows              print row       print cursor fetchall       except KeyboardInterrupt        print   nClean Exit By user     finally        print   nFinally

User · Answer

Here s a short and simple python program to print out the table names and the column names for those tables  python 2   python 3 follows    import sqlite3  db filename    database sqlite  newline indent     n      db sqlite3 connect db filename  db text factory   str cur   db cursor    result   cur execute  SELECT name FROM sqlite master WHERE type  table     fetchall   table names   sorted zip  result  0   print   ntables are   newline indent newline indent join table names   for table name in table names      result   cur execute  PRAGMA table info   s      table name  fetchall       column names   zip  result  1      print    ncolumn names for  s     table name  newline indent  newline indent join column names    db close   print   nexiting      EDIT  I have been getting periodic vote-ups on this  so here is the python3 version for people who are finding this answer   import sqlite3  db filename    database sqlite  newline indent     n      db sqlite3 connect db filename  db text factory   str cur   db cursor    result   cur execute  SELECT name FROM sqlite master WHERE type  table     fetchall   table names   sorted list zip  result   0   print    ntables are   newline indent newline indent join table names    for table name in table names      result   cur execute  PRAGMA table info   s      table name  fetchall       column names   list zip  result   1      print     ncolumn names for  s     table name              newline indent              newline indent join column names     db close   print    nexiting

User · Answer

After a lot of fiddling I found a better answer at sqlite docs for listing the metadata for the table  even attached databases    meta   cursor execute  PRAGMA table info  Job     for r in meta      print r   The key information is to prefix table info  not my table with the attachment handle name

User · Answer

After a lot of fiddling I found a better answer at sqlite docs for listing the metadata for the table  even attached databases    meta   cursor execute  PRAGMA table info  Job     for r in meta      print r   The key information is to prefix table info  not my table with the attachment handle name

User · Answer

You can fetch the list of tables and schemata by querying the SQLITE MASTER table   sqlite gt   tab job         snmptarget  t1          t2          t3         sqlite gt  select name from sqlite master where type    table   job t1 t2 snmptarget t3  sqlite gt   schema job CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR    sqlite gt  select sql from sqlite master where type    table  and name    job   CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR

User · Answer

You can fetch the list of tables and schemata by querying the SQLITE MASTER table   sqlite gt   tab job         snmptarget  t1          t2          t3         sqlite gt  select name from sqlite master where type    table   job t1 t2 snmptarget t3  sqlite gt   schema job CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR    sqlite gt  select sql from sqlite master where type    table  and name    job   CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR

User · Answer

The FASTEST way of doing this in python is using Pandas (version 0.16 and up).

Dump one table:

db = sqlite3.connect('database.db')
table = pd.read_sql_query("SELECT * from table_name", db)
table.to_csv(table_name + '.csv', index_label='index')

Dump all tables:

import sqlite3
import pandas as pd


def to_csv():
    db = sqlite3.connect('database.db')
    cursor = db.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    for table_name in tables:
        table_name = table_name[0]
        table = pd.read_sql_query("SELECT * from %s" % table_name, db)
        table.to_csv(table_name + '.csv', index_label='index')
    cursor.close()
    db.close()

User · Answer

If someone wants to do the same thing with Pandas   import pandas as pd import sqlite3 conn   sqlite3 connect  db sqlite3   table   pd read sql query  SELECT name FROM sqlite master WHERE type  table    conn  print table

User · Answer

You can fetch the list of tables and schemata by querying the SQLITE MASTER table   sqlite gt   tab job         snmptarget  t1          t2          t3         sqlite gt  select name from sqlite master where type    table   job t1 t2 snmptarget t3  sqlite gt   schema job CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR    sqlite gt  select sql from sqlite master where type    table  and name    job   CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR

User · Answer

Some might find my function useful if you just want to print out all of the tables and columns in your db  In the loop  I query each TABLE with LIMIT 0 so it just returns the header info without all the data   You make an empty df out of it  and use the iterable df columns to print each column name out  conn   sqlite3 connect  example db   c   conn cursor    def table info c  conn               prints out all of the columns of every table in db     c   cursor object     conn   database connection object             tables   c execute  quot SELECT name FROM sqlite master WHERE type  table   quot   fetchall       for table name in tables          table name   table name 0    tables is a list of single item tuples         table   pd read sql query  quot SELECT   from    LIMIT 0 quot  format table name   conn          print table name          for col in table columns              print   t-    col          print    table info c  conn   Results will be   table1     -column1     -column2  table2     -column1     -column2     -column3   etc

User · Answer

Check out here for dump  It seems there is a dump function in the library sqlite3

User · Answer

I m not familiar with the Python API but you can always use  SELECT   FROM sqlite master

User · Answer

In Python   con   sqlite3 connect  database db   cursor   con cursor   cursor execute  SELECT name FROM sqlite master WHERE type  table     print cursor fetchall      Watch out for my other answer  There is a much faster way using pandas

User · Answer

usr bin env python   - - coding  utf-8 - -  if   name         main         import sqlite3     dbname      db database db     try        print  INITILIZATION           con   sqlite3 connect dbname        cursor   con cursor         cursor execute  SELECT name FROM sqlite master WHERE type  table           tables   cursor fetchall         for tbl in tables           print   n            tbl 0                        cursor execute  SELECT   FROM   tbl 0                rows   cursor fetchall            for row in rows              print row       print cursor fetchall       except KeyboardInterrupt        print   nClean Exit By user     finally        print   nFinally

User · Answer

You can fetch the list of tables and schemata by querying the SQLITE MASTER table   sqlite gt   tab job         snmptarget  t1          t2          t3         sqlite gt  select name from sqlite master where type    table   job t1 t2 snmptarget t3  sqlite gt   schema job CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR    sqlite gt  select sql from sqlite master where type    table  and name    job   CREATE TABLE job       id INTEGER PRIMARY KEY      data VARCHAR

User · Answer

Check out here for dump  It seems there is a dump function in the library sqlite3

User · Answer

Check out here for dump  It seems there is a dump function in the library sqlite3

User · Answer

I m not familiar with the Python API but you can always use  SELECT   FROM sqlite master

User · Answer

If someone wants to do the same thing with Pandas   import pandas as pd import sqlite3 conn   sqlite3 connect  db sqlite3   table   pd read sql query  SELECT name FROM sqlite master WHERE type  table    conn  print table

User · Answer

I m not familiar with the Python API but you can always use  SELECT   FROM sqlite master

User · Answer

Some might find my function useful if you just want to print out all of the tables and columns in your db  In the loop  I query each TABLE with LIMIT 0 so it just returns the header info without all the data   You make an empty df out of it  and use the iterable df columns to print each column name out  conn   sqlite3 connect  example db   c   conn cursor    def table info c  conn               prints out all of the columns of every table in db     c   cursor object     conn   database connection object             tables   c execute  quot SELECT name FROM sqlite master WHERE type  table   quot   fetchall       for table name in tables          table name   table name 0    tables is a list of single item tuples         table   pd read sql query  quot SELECT   from    LIMIT 0 quot  format table name   conn          print table name          for col in table columns              print   t-    col          print    table info c  conn   Results will be   table1     -column1     -column2  table2     -column1     -column2     -column3   etc

User · Answer

I ve implemented a sqlite table schema parser in PHP  you may check here  https   github com c9s LazyRecord blob master src LazyRecord TableParser SqliteTableDefinitionParser php  You can use this definition parser to parse the definitions like the code below    parser   new SqliteTableDefinitionParser   parser- gt parseColumnDefinitions  x INTEGER PRIMARY KEY  y DOUBLE  z DATETIME default   2011-11-10    name VARCHAR 100

User · Answer

I ve implemented a sqlite table schema parser in PHP  you may check here  https   github com c9s LazyRecord blob master src LazyRecord TableParser SqliteTableDefinitionParser php  You can use this definition parser to parse the definitions like the code below    parser   new SqliteTableDefinitionParser   parser- gt parseColumnDefinitions  x INTEGER PRIMARY KEY  y DOUBLE  z DATETIME default   2011-11-10    name VARCHAR 100

User · Answer

Here s a short and simple python program to print out the table names and the column names for those tables  python 2   python 3 follows    import sqlite3  db filename    database sqlite  newline indent     n      db sqlite3 connect db filename  db text factory   str cur   db cursor    result   cur execute  SELECT name FROM sqlite master WHERE type  table     fetchall   table names   sorted zip  result  0   print   ntables are   newline indent newline indent join table names   for table name in table names      result   cur execute  PRAGMA table info   s      table name  fetchall       column names   zip  result  1      print    ncolumn names for  s     table name  newline indent  newline indent join column names    db close   print   nexiting      EDIT  I have been getting periodic vote-ups on this  so here is the python3 version for people who are finding this answer   import sqlite3  db filename    database sqlite  newline indent     n      db sqlite3 connect db filename  db text factory   str cur   db cursor    result   cur execute  SELECT name FROM sqlite master WHERE type  table     fetchall   table names   sorted list zip  result   0   print    ntables are   newline indent newline indent join table names    for table name in table names      result   cur execute  PRAGMA table info   s      table name  fetchall       column names   list zip  result   1      print     ncolumn names for  s     table name              newline indent              newline indent join column names     db close   print    nexiting

User · Answer

I m not familiar with the Python API but you can always use  SELECT   FROM sqlite master

User · Answer

Apparently the version of sqlite3 included in Python 2 6 has this ability  http   docs python org dev library sqlite3 html    Convert file existing db db to SQL dump file dump sql import sqlite3  os  con   sqlite3 connect  existing db db   with open  dump sql    w   as f      for line in con iterdump            f write   s n    line

[python] List of tables, db schema, dump etc using the Python sqlite3 API

Examples related to python

Examples related to api

Examples related to sqlite

Examples related to dump