How do I get a list of tables, the schema, a dump, using the sqlite3 API?

Question

How do I get the equivalents of SQLite's interactive shell commands .tables and .dump using the Python sqlite3 API?

I suggest renaming the question to something non python specific since the answer is actually universal to interfaces that use SQL. — unode
– unode, Commented Nov 11, 2010 at 16:34
True, although I was expecting a python API when asking it. I'll try to find the right name. — noamtm
– noamtm, Commented Nov 15, 2010 at 8:54
If wanting to show tables from the sqlite3 command prompt, refer to stackoverflow.com/questions/82875/…. If using Python package sqlite3, see Davoud Taghawi-Nejad's answer here. I suggest the OP add Python back into the question title and select Davoud's answer. I found this page by googling "show tables Python sqlite3" since Google knows the old question title. Searches within SO would fail to land here. Without the Python angle, the linked duplicate question 82875 has received far more crowd wisdom. — Bennett Brown
– Bennett Brown, Commented Dec 7, 2014 at 17:13

ocodo · Accepted Answer · 2024-06-28 07:52:20Z

354

In Python:

import sqlit3

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.

edited Jun 28, 2024 at 7:52

ocodo

30.5k19 gold badges111 silver badges127 bronze badges

answered May 24, 2012 at 22:15

Davoud Taghawi-Nejad

16.9k14 gold badges69 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

T.Woody Over a year ago

For users coming to copy/paste: Be sure to cursor.close() and db.close()

MT1 Over a year ago

this seems to be the simplest but - import sqlite3 first if you are cutting and pasting

converter42 · Accepted Answer · 2008-11-20 15:26:39Z

113

You can fetch the list of tables and schemata by querying the SQLITE_MASTER table:

sqlite> .tab
job         snmptarget  t1          t2          t3        
sqlite> select name from sqlite_master where type = 'table';
job
t1
t2
snmptarget
t3

sqlite> .schema job
CREATE TABLE job (
    id INTEGER PRIMARY KEY,
    data VARCHAR
);
sqlite> select sql from sqlite_master where type = 'table' and name = 'job';
CREATE TABLE job (
    id INTEGER PRIMARY KEY,
    data VARCHAR
)

answered Nov 20, 2008 at 15:26

converter42

7,5462 gold badges32 silver badges25 bronze badges

3 Comments

jbuddy_13 Over a year ago

sqlite> .schema job invalid syntax in python...what am I missing?

converter42 Over a year ago

@jbuddy_13 sqlite> is the sqlite command line client prompt. The purpose of the example was to demonstrate how one could query the database to list tables and schema.

cards Over a year ago

Remark on the name of the "db schema": (at present time!) should be sqlite_schema and sqlite_master is kept for "historical compatibility", see doc

lesolorzanov · Accepted Answer · 2019-05-17 16:27:34Z

97

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()

edited May 17, 2019 at 16:27

lesolorzanov

3,6549 gold badges40 silver badges58 bronze badges

answered Oct 13, 2015 at 10:38

Davoud Taghawi-Nejad

16.9k14 gold badges69 silver badges84 bronze badges

2 Comments

T.Woody Over a year ago

For users coming to copy/paste: Be sure to cursor.close() and db.close().

frans Over a year ago

or better use with sqlite3.connect('database.db') as db:

finnw · Accepted Answer · 2008-11-20 14:07:20Z

37

I'm not familiar with the Python API but you can always use

SELECT * FROM sqlite_master;

answered Nov 20, 2008 at 14:07

finnw

48.8k24 gold badges150 silver badges223 bronze badges

Comments

user4157124 · Accepted Answer · 2024-06-29 20:22:43Z

Python 2 program to print table names and the column names for those tables:

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."

Python 3 version:

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.")

Mukesh Yadav · Accepted Answer · 2020-02-10 05:05:54Z

29

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)

edited Feb 10, 2020 at 5:05

answered Feb 1, 2020 at 16:29

Mukesh Yadav

2,4062 gold badges34 silver badges51 bronze badges

1 Comment

woggioni Over a year ago

Jupyter Notebook and Pandas are not required to answer this question

checkit · Accepted Answer · 2009-03-02 03:47:31Z

21

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)

answered Mar 2, 2009 at 3:47

checkit

4011 gold badge4 silver badges8 bronze badges

2 Comments

RobinL Over a year ago

Be careful with this, it will print all the data in your tables if they are populated with INSERT INTO statements!

ViFI Over a year ago

Thankfully, INSERT INTO statements are single line so easy to escape with if line.startswith("INSERT INTO") ...

ALee · Accepted Answer · 2021-03-18 22:35:44Z

9

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("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
    for table_name in tables:
        table_name = table_name[0] # tables is a list of single item tuples
        table = pd.read_sql_query("SELECT * from {} LIMIT 0".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.

edited Mar 18, 2021 at 22:35

answered Jan 19, 2021 at 22:57

ALee

911 silver badge3 bronze badges

1 Comment

Tim Over a year ago

Since this post is for newer programmers you could include the import statements import sqlite3 import pandas as pd

nealmcb · Accepted Answer · 2015-01-04 19:17:17Z

7

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.

edited Jan 4, 2015 at 19:17

nealmcb

13.7k7 gold badges72 silver badges97 bronze badges

answered Dec 10, 2014 at 3:01

user3451435

2232 silver badges6 bronze badges

1 Comment

nealmcb Over a year ago

I got lots of errors in the snippet you wrote, and don't see e.g. "db_alias" in either the referenced docs or in other examples. In the context of the other examples, I think you want this line, for e.g. the Job table: meta = cursor.execute("PRAGMA table_info('Job')") And your first line seems unrelated to the rest.

VecH · Accepted Answer · 2015-10-25 14:38:19Z

2

#!/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"

answered Oct 25, 2015 at 14:38

VecH

211 bronze badge

Comments

c9s · Accepted Answer · 2015-04-14 02:57:17Z

0

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->parseColumnDefinitions('x INTEGER PRIMARY KEY, y DOUBLE, z DATETIME default \'2011-11-10\', name VARCHAR(100)');

answered Apr 14, 2015 at 2:57

c9s

1,91719 silver badges16 bronze badges

Collectives™ on Stack Overflow

How do I get a list of tables, the schema, a dump, using the sqlite3 API?

11 Answers 11

2 Comments

3 Comments

2 Comments

Comments

Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

2 Comments

3 Comments

2 Comments

Comments

Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

Comments

Linked

Related