Web2py MySQL database reference error

Question

I'm using web2py with a MySQL database. The database scheme in my db.py file looks like the following:

db.define_table('app',
                 Field('name'),
                 Field('appdomain')
               )

db.define_table('role_group',
                 Field('appid', db.app),
                 Field('name')
               )

db.define_table('user',
                Field('userid', required=True),
                Field('name', required=True),
                primarykey=['userid'])

db.define_table('role',
                 Field('appid', db.app),
                 Field('role_group', db.role_group),
                 Field('name')
               )

db.define_table('role_authorization',
                 Field('userid', db.user, required=True),
                 Field('roleid', db.role, required=True),
                 primarykey=['userid', 'roleid']
               )

db.define_table('group_authorization',
                 Field('userid', db.user),
                 Field('groupid', db.role_group),
                 primarykey=['userid', 'groupid']
               )

When I run the following query:

userid = "fb|1234"
userAuthorizations = db((db.user.userid == userid)
                                & (db.tetra_user.userid == db.role_authorization.userid)
                                & (db.role_authorization.roleid == db.role.id)
                                & (db.role.appid == db.app.id)
                            ).select()

It returns this error: ValueError: invalid literal for long() with base 10: 'fb|1234'. I hope someone knows what causes this error and can help me to solve it.

Kind regards, Simon

Answer 1

First you need to set migrate to False in the legacy tables. So web2py won't intent to change them.

Second, try to specify the type of the field userid because the such error happens when you try to cast "fb|1234" to an integer:

>>> int("fb|1234") 
ValueError: invalid literal for int() with base 10: 'fb|1234'