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: sqlite-users@sqlite.org 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
