pythonsqlitedump

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

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

Solution

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