Using ROWID function as id field in sqlite tables

64 views
Skip to first unread message

Woody

unread,
Jul 14, 2013, 7:05:03 PM7/14/13
to web...@googlegroups.com
This may be something that is known by people with more experience than me, but it took me a lot of experimenting to figure it out, so I thought I would post it here so others will find it.

I have legacy sqlite tables that do not have an id field.  This makes it difficult to update them using SQLFORM.

ROWID is a hidden auto-increment integer field that's included in all sqlite tables.  You can make ROWID explicit by declaring it as the first field in a table definition, then you can assign it the 'id' alias.

Here's an example:

db.define_table('my_sqlite_table',
                Field('ROWID', 'id'),
                Field('some_field', 'string'),
                Field('some_other_field', 'integer'),
                ........
                migrate=False)

You can then use 'id' as a field name just as if your table had an actual id field defined in the underlying table.  As far as I can tell, this hack works without any problems.  The use of the 'id' alias is documented, but  I haven't seen the use of 'ROWID' with the alias discussed.  This might be something that could be tested and added to the documentation.

Massimo Di Pierro

unread,
Jul 15, 2013, 4:40:42 AM7/15/13
to web...@googlegroups.com
yes this is not throughly tested but as far as we know it works. You can use any existing field as 'id'

Sundar

unread,
Feb 17, 2017, 7:17:34 AM2/17/17
to web2py-users
An old thread - but I seem to have some problems using 'rowid' as id.

The statement Field('rowid', 'id') causes the error: 

<type 'exceptions.SyntaxError'> invalid table/column name "rowid" is a "ALL" reserved SQL/NOSQL keyword


What could I be possibly doing wrong? (I tried rowid in lower as well as upper case)

Thanks.

=========================================================================

Leonel Câmara

unread,
Feb 19, 2017, 8:44:05 PM2/19/17
to web...@googlegroups.com
Sundar you have check_reserved=['all'] in your DAL declaration. ROWID is reserved in Oracle. Put check_reserved=['sqlite'].

Sundar

unread,
Feb 21, 2017, 1:25:16 AM2/21/17
to web2py-users
Thanks Leonel. It was my mistake. It works now.


On Monday, July 15, 2013 at 4:35:03 AM UTC+5:30, Woody wrote:
Reply all
Reply to author
Forward
0 new messages