Hi
I have some weird behavior due to a class wit both "Id" and "id" columns.
Id is the primary key like on every declarative class.
id is just a data column
class Application()
__tablename….
Id = Column(Integer, primary_key=True)
id = Column(Unicode(…
controllers = relation('Controller',primaryjoin
="ApplicationController.Application==Application.Id",single_parent =
True,secondary='ide_applicationcontroller',secondaryjoin="ApplicationController.Controller==Controller.Id",
cascade='all')#, delete-orphan' )
x = A(Id=1,id='Blablabla'
It feels like a missing check for a PK or a .lower() where it should not be, on
the other hand it might very well be expected behavior on my setup (Pg 9.0,
prostgresql+psycopg, SA 7.3)
There are Id and id colums and Camelcase my columnames so that is not the issue
On a session.merge the querybuilder seems to favour "id" over the PK column Id
and inserts the data from the instances "id" field.
I can Add fine and understand why.
Some background on the relation:
there are two classes Application and Controller with a many-to many relation
ApplicationController (With two columns, Application and controller, both are
PK and FK (to the corresponding table.Id
tablenames are according to 'ide_'+self.name.lower()
the failing query (see the primaryjoin of the controllers relation of the
Application class above):
2012-03-04 19:10:59,255 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-03-04 19:10:59,257 INFO sqlalchemy.engine.base.Engine SELECT
ide_application."Id" AS "ide_application_Id", ide_application."appFolder" AS
"ide_application_appFolder", ide_application."autoCreateViewport" AS
"ide_application_autoCreateViewport", ide_application."defaultUrl" AS
"ide_application_defaultUrl", ide_application."enableQuickTips" AS
"ide_application_enableQuickTips", ide_application.name AS
ide_application_name, ide_application.launch AS ide_application_launch,
ide_application."onLaunch" AS "ide_application_onLaunch", ide_application.id AS
ide_application_id
FROM ide_application
WHERE ide_application."Id" = %(param_1)s
2012-03-04 19:10:59,257 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2012-03-04 19:10:59,261 INFO sqlalchemy.engine.base.Engine SELECT
ide_controller."Id" AS "ide_controller_Id", ide_controller."Name" AS
"ide_controller_Name", ide_controller."onLaunch" AS "ide_controller_onLaunch",
ide_controller.id AS ide_controller_id
FROM ide_controller, ide_applicationcontroller
WHERE ide_applicationcontroller."Application" = %(param_1)s AND
ide_applicationcontroller."Controller" = ide_controller."Id"
2012-03-04 19:10:59,261 INFO sqlalchemy.engine.base.Engine {'param_1':
u'Blablabla'}
gives, param_1: should be the value from Id (Interger, PK) …{'param_1' : 1}…
db.merge(x)
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/session.py", line
1301, in merge
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/session.py", line
1393, in _merge
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/properties.py", line
767, in merge
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/attributes.py", line
453, in get
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/strategies.py", line
563, in _load_for_state
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py", line 1947,
in all
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py", line 2057,
in __iter__
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py", line 2072,
in _execute_and_instances
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py", line
1405, in execute
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py", line
1538, in _execute_clauseelement
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py", line
1646, in _execute_context
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py", line
1639, in _execute_context
File "build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/default.py", line
330, in do_execute
sqlalchemy.exc.DataError: (DataError) invalid input syntax for integer:
"Blablabla"
LINE 3: WHERE ide_applicationcontroller."Application" = E'Blablabla...
^
'SELECT ide_controller."Id" AS "ide_controller_Id", ide_controller."Name" AS
"ide_controller_Name", ide_controller."onLaunch" AS "ide_controller_onLaunch",
ide_controller.id AS ide_controller_id \nFROM ide_controller,
ide_applicationcontroller \nWHERE ide_applicationcontroller."Application" =
%(param_1)s AND ide_applicationcontroller."Controller" = ide_controller."Id"'
{'param_1': u'Blablabla'}
I do not understand where the "E" is coming from, I've check and checked and
never reference to "id" anywhere by mistake
I can not freely choose my columnnames and do not really want to run code for
every parameter just to see if there is an id field and put the value in
whatever other storage name, that will add unwanted load to my system. Is this
a bug? or do I hit a Feature of SA where it does not matter if id or Id is used
and somewhere a .lower() takes care of that. Can I switch it off if so?
Martijn
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
