On Mar 4, 2012, at 2:18 PM, Martijn Moeling wrote:
> 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
the "E" is part of how psycopg2/libpq renders bound parameters before passing
off to the backend, and is normal.
>
> I can not freely choose my columnnames
OK well I can confirm the use case is not supported at all on SQLAlchemy - SQLA
is case insensitive in how it deals with result set column names. It's been
that way since the beginning and nobody has ever asked for it to be changed,
much less even noticed it, most likely. Had I been making this decision
today, I would not have chosen the lower() option, as it adds performance
overhead in any case and is for the vast majority of cases completely
unnecessary. However, I don't recall if I was coming up against DBAPIs that
were not giving me the correct casing within cursor.description, and I'm not
sure if all DBAPIs handle this correctly, so there is some risk to changing the
behavior.
So I'd point out that this use case you have here is exceedingly unusual. Many
databases don't support it, not even SQLite, which won't let me create such a
table, even if I quote both names:
duplicate column name: Id u'\nCREATE TABLE a (\n\t"id" INTEGER NOT
NULL, \n\t"Id" INTEGER, \n\tPRIMARY KEY ("id")\n)\n\n' ()
nor will the current release of MySQL:
(1060, "Duplicate column name 'Id'") '\nCREATE TABLE a (\n\t`id`
INTEGER NOT NULL AUTO_INCREMENT, \n\t`Id` INTEGER, \n\tPRIMARY KEY (`id`),
\n\tUNIQUE (`Id`)\n)\n\n' ()
So generally, mixing overlapping names based on case within relational
databases is a really bad idea, and is just asking for trouble at every stage.
> 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.
So if your system is truly "generic" and you can't anticipate what existing
names are present, I'd point out that right off, your approach will not work
for SQLite or MySQL, which will not allow such a naming convention in any case.
The logic we're talking about here would take place at configuration time in
any case, and would be a completely minuscule check that runs just once per
class at import time, so there's no "load" issue. Also, what if you were
given a class that actually had the lowercase name "id" on it already? If you
have no control over column names, don't you need to check for that name
already existing? Or are you publishing a restriction that this name is
"reserved", and in which case why not say that all casing conventions of "id"
are reserved as well ?
> 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?
The model for the statement compiler and result proxy works on a case
insensitive model right now as far as how columns are located in result sets, I
can't even get a plain row back using such a casing convention.
Ticket #2423 (http://www.sqlalchemy.org/trac/ticket/2423) illustrates a patch
that would allow the behavior to be configurable. As anticipated, it adds 7%
method call overhead to key performance tests, when the "case insensitive"
behavior is turned on as it doubles the function call overhead for each
lower(). Which leads me to want to release it in 0.8 with the default
behavior reversed, thereby avoiding the double-function call for the vast
majority of cases that aren't attempting to refer to columns in a
case-insensitive manner. But I'd have to do a deep sweep across all the
DBAPIs I can find, because if any of them are lowercasing on their end, then
the change will be incompatible - those DBAPIs might want to signal that the
flag turns back on.
--
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.
