List of tables, db schema, dump etc using the Python sqlite3 API

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.

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

sqlite> .tabjob         snmptarget  t1          t2          t3        sqlite> select name from sqlite_master where type = 'table';jobt1t2snmptargett3sqlite> .schema jobCREATE 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)

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 sqlite3import pandas as pddef 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()