Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

How do I get a list of tables in SQLite database in Python?

To list all tables in an SQLite3 database, you should query the sqlite_master table and then use the fetchall() to fetch the results from the SELECT statement. The sqlite_master is the master table in SQLite3, which stores all tables.

How do I get a list of tables in SQLite database?

If you are running the sqlite3 command-line access program you can type ". tables" to get a list of all tables. Or you can type ". schema" to see the complete database schema including all tables and indices.

How do I see sqlite3 database in Python?

SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.

Does sqlite3 have schemas?

Every SQLite database contains a single "schema table" that stores the schema for that database.


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

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

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

SELECT * FROM sqlite_master;