Is there a way to get a schema of a database from within python?

Question

I'm trying to find out a way to find the names of tables in a database(if any exist). I find that from a sqlite cli I can use:

>.tables

Then for the fields:

>PRAGMA TABLE_INFO(table_name)

This obviously doesn't work within python. Is there even a way to do this with python or should I just be using the sqlite command-line?

Answer 1

From the sqlite FAQ:

From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

So to get a list of all table names execute:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

To get column names for a given table, use the pragma table_info command:

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column.

This command works just fine from python:

>>> import sqlite3
>>> conn = sqlite3.connect(':mem:')
>>> for row in conn.execute("pragma table_info('sqlite_master')").fetchall():
...     print row
... 
(0, u'type', u'text', 0, None, 0)
(1, u'name', u'text', 0, None, 0)
(2, u'tbl_name', u'text', 0, None, 0)
(3, u'rootpage', u'integer', 0, None, 0)
(4, u'sql', u'text', 0, None, 0)

Unfortunately pragma statements do not work with parameters; you'll have to manually insert the table name (make sure it's not sourced from an untrusted source and escape it properly).