Get row id in sqlite when iterating over a fetchall?
Your CREATE TABLE
commands are lacking field types. Notice what happens here:
In [27]: pin_schema = dict(rowid = 'INTEGER PRIMARY KEY AUTOINCREMENT', pinnedId = 'INTEGER')In [30]: pin_table_name = 'Pins'In [31]: sql = 'CREATE TABLE IF NOT EXISTS ' + pin_table_name + ' ({})'.format(','.join(pin_schema))In [32]: sqlOut[32]: 'CREATE TABLE IF NOT EXISTS Pins (pinnedId,rowid)'
All you get are the field names with no field types. Instead, either define pin_schema
and log_schema
as lists of strings which include both the field and the field type, or iterate through pin_schema.items()
:
sql = 'CREATE TABLE IF NOT EXISTS ' + pin_table_name + ' ({})'.format( ', '.join('{f} {t}'.format(f=field, t=field_type) for field, field_type in pin_schema.items()))In [35]: sqlOut[35]: 'CREATE TABLE IF NOT EXISTS Pins (pinnedId INTEGER, rowid INTEGER PRIMARY KEY AUTOINCREMENT)'
The lack of field types explains why the rowid
was not autoincrementing.