Look into the pragmas for querying the database schema: http://www.sqlite.org/pragma.html
In particular, you want "pragma table_info(tablename)" Your code is trying to determine which column is the primary key. That information is in the results of table_info (though not the ordering of the columns if there are more than one). --Ned. http://nedbatchelder.com -----Original Message----- From: Scott Chapman [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 11:51 AM To: [email protected] Subject: [sqlite] Can you get a description of a table when the sql results are empty? I made a little python code below that fetches a sqlite3 table description (using apsw) but it won't work when the table is empty. �I made it tell me the table is empty in this case. � Is there a way to get the columns in a table without having to parse the SQL that created the table, when the table is empty? import re import sys import apsw db = apsw.Connection(sys.argv[1]) cursor = db.cursor() print sql="select tbl_name from sqlite_master where type='table'" cursor.execute(sql) tables=[] while True: � � try: � � � � db_row=cursor.next() � � except StopIteration: � � � � break � � tables.append(db_row) print "Tables:" for table_name in tables: � print table_name[0] � print � table_name=table_name[0] � print "========================================================" � print "table name: %s" % table_name � print � � � � � # Get the sql that created table and parse it to determine which is the primary key. � # I don't know of any other way to determine the primary key in sqlite3. � sql="select sql from sqlite_master where tbl_name='%s';" % table_name � cursor.execute(sql) � create_table_sql=cursor.next()[0] � print "Creation SQL: %s" % create_table_sql � print � match=re.search(r'.*[ (](.*?) integer primary key',create_table_sql,re.IGNORECASE) � if match: � � � pkey_column = match.group(1) � � � print "Primary key: %s" % pkey_column � � � print � � # Get a row from the table so I can determine the description � sql="select oid,* from %s limit 1" % table_name � cursor.execute (sql) � � � � � � # Get the column names and make the HTML <table> header row � try: � � � cdes=cursor.getdescription() � except apsw.ExecutionCompleteError: � � � # HERE's where I'd like to be able to go ahead and get the description! � � � print 'table: %s is empty!' % table_name � � else: � � � print "Columns in table %s:" % table_name � � � for header,dummy in cdes[1:]: � � � � � print ' �%s: %s' % (header,dummy) Scott
