python sqlite3 table inspection

python_sqlite3.py

# two python functions to extract field names from sqlite3 database tables

def schema_fields(schema):
    """ 
    Extract fields defined in a (sqlite3) table schema.

    schema: string with CREATE TABLE statement for the table

    returns tuple of field names
    raises ValueError, if parsing failed
    """
    import re
    m = re.search(r"CREATE\s+TABLE\s+(\S+)\s*\(([\s\S]+)\)", schema, re.M)
    if m is None: raise ValueError("Unable to parse schema")
    return tuple(x.split()[0].strip() for x in m.group(2).split(','))

def sqlite3_table_fields(cur, table):
    """
    Get field names defined in an sqlite3 table.

    cur: sqlite3.Cursor object
    table: name of table

    returns tuple of field names; empty if table is undefined or parsing failed
    """
    cur.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name=?", (table,))
    r = cur.fetchone()
    if r is None: return tuple()
    return schema_fields(r[0])

# Copyright 2012 Daniel Meliza (dan ampersat meliza\org)
# License: Gnu Public License version 3