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

Answer 2

You should be able access the table names from the sqlite_master table.

SELECT name FROM sqlite_master WHERE type='table';

The names of the columns are not directly accessible. The easiest way to get them is to query the table and get the column names from the query result.

SELECT * FROM table_name LIMIT 1;

Answer 3

Here's a convenient printer I wrote based Martijn's response:

def printSchema(connection):
    for (tableName,) in connection.execute(
        """
        select NAME from SQLITE_MASTER where TYPE='table' order by NAME;
        """
    ):
        print("{}:".format(tableName))
        for (
            columnID, columnName, columnType,
            columnNotNull, columnDefault, columnPK,
        ) in connection.execute("pragma table_info('{}');".format(tableName)):
            print("  {id}: {name}({type}){null}{default}{pk}".format(
                id=columnID,
                name=columnName,
                type=columnType,
                null=" not null" if columnNotNull else "",
                default=" [{}]".format(columnDefault) if columnDefault else "",
                pk=" *{}".format(columnPK) if columnPK else "",
            ))

Answer 4

To get the field names, use cur.description after the query:

import sqlite3.dbapi2 as sqlite
con = sqlite.connect(":memory:")
cur = con.cursor()
con.executescript("""
    create table test (name, address);
    insert into test (name, address) values ("Jer", "Monterey Street");
""")

cur.execute("select * from test where 1=0")
rs = cur.fetchall()  ## will be [] because of where clause
field_names = [r[0] for r in cur.description]