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?

1
130
4/6/2016 1:54:00 AM

Accepted Answer

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
)
95
11/20/2008 3:26:39 PM

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.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon