get list of tables, db schema, dump etc in sqlite databases

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 sqlite's APIs.

Is there anything like that? Am I missing something?

Answers


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
)

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.


Need Your Help

What WPF control should I inherit from?

wpf design xaml control

I've written a WPF control which accepts a number of UIElement objects as input and displays them docked either vertically or horizontally. The control exposes functions for enumerating, removing and

What is the baudrate limit in the termios.h?

global-variables termios baud-rate

This is a quick snippet of code from a serial program I've been working with to interface with a microcontroller. The code has been verified to work but I want to add the global define to make the ...