get list of tables, db schema, dump etc in Python sqlite3

Question

For some reason I can't find online or in the docs the way to get the equivalents of sqlite's interactive shell commands:

.tables
.dump

in Python's sqlite3 API.

Is there anything like that? Am I missing something?

-N.

Answer 1

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

SELECT * FROM sqlite_master;

Answer 2

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
)

Answer 3

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

Answer 4

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)