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?

Solution 1

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
)

Solution 2

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.

Solution 3

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

Solution 4

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

SELECT * FROM sqlite_master;

Solution 5

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

View more solutions

Comments

[unode]

I suggest renaming the question to something non python specific since the answer is actually universal to interfaces that use SQL.

[noamtm]

True, although I was expecting a python API when asking it. I'll try to find the right name.

[Bennett Brown]

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.

[Angel]

I'm trying: import sqlite3 con = sqlite3.connect("test.db") con.dump() It fails... I keep checking

[RobinL]

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

[nealmcb]

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.

[T.Woody]

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

[T.Woody]

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

[frans]

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

[a_a_a]

This seems to be the correct answer yet the OP approved another one...

[woggioni]

Jupyter Notebook and Pandas are not required to answer this question

[Mooncrater]

@a_a_a Umm 4 year difference?

[jbuddy_13]

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

[Pro Q]

@jbuddy_13 see the other answer

[user66081]

No need to slam such a useful answer. Although, as usual, a context manager is recommended.

[converter42]

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

[NL23codes]

I find this to be the cleanest and most useful answer.

[Kostas Markakis]

sorry but i really dont know why this is the accepted answer, the question is specific for python not SQL language

[ViFI]

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

[converter42]

@KostasMarkakis is it really that difficult to imagine how the information I listed might lead to a solution using the API?

[Tim]

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

[cards]

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