Sybase reports "conversion from datatype 'CHAR' to 'INT' is not allowed." when using string instead of int for filterby
Cserna, Zsolt
Hi all,
We're trying to use sybase dialect in sqlalchemy, but it creates invalid select parameters when we are trying to use a python-string (or unicode object) variable to specify a sql-int variable.
That problem occurs when we use pylons web framework with formalchemy with sqlalchem+sybase - which actually specifies all the parameters in unicode objects to filter_by() method.
I'm using the nightly snapshot from sqlalchemy.org.
A simple example to re-produce this error is the following (only the relevant parts):
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(64)),
Column('fullname', String(64)),
Column('password', String(64))
)
session.query(User).filter_by(id="1").order_by(User.id)
Here, we want to filter to id="1", where "1" is a string value, but it's an integer in the database. If I execute the example, it says (again, only the relevant parts):
2010-06-09 16:59:50,458 INFO sqlalchemy.engine.base.Engine.0x...ae2c BEGIN
2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.id = @id_1 ORDER BY users.id
2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c {'@id_1': '1'}
sqlalchemy.exc.DatabaseError: (DatabaseError) Msg 257, Level 16, State 1, Line 1
Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.
As you can see, sqlalchemy constructed a parameters dictionary containing a string as a value, however this field is integer typed in the database and sybase requires the value to be specified as an integer (it doesn't convert it to int automatically). If I specify int typed variable in the example above to filter_by then it's ok.
I think it's a bug in the sqlalchemy as the ORM should do the type conversion in this case.
I've attached a patch (diffed to the nightly snapshot) which solved me this problem. As I'm not an sqlalchemy expert I assume this will convert my str python type to int when I'm using Integer in sqlalchemy in any query. Do you see any problem with it? If not, could it be merged to trunk? :)
Thank you in advance,
Zsolt
--------------------------------------------------------------------------
NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law.
Michael Bayer
On Jun 9, 2010, at 11:27 AM, Cserna, Zsolt wrote:
>
> Hi all,
>
> We're trying to use sybase dialect in sqlalchemy, but it creates invalid select parameters when we are trying to use a python-string (or unicode object) variable to specify a sql-int variable.
> That problem occurs when we use pylons web framework with formalchemy with sqlalchem+sybase - which actually specifies all the parameters in unicode objects to filter_by() method.
> I'm using the nightly snapshot from sqlalchemy.org.
>
> A simple example to re-produce this error is the following (only the relevant parts):
>
> metadata = MetaData()
> users_table = Table('users', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', String(64)),
> Column('fullname', String(64)),
> Column('password', String(64))
> )
>
> session.query(User).filter_by(id="1").order_by(User.id)
>
> Here, we want to filter to id="1", where "1" is a string value, but it's an integer in the database. If I execute the example, it says (again, only the relevant parts):
>
> 2010-06-09 16:59:50,458 INFO sqlalchemy.engine.base.Engine.0x...ae2c BEGIN
> 2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
> FROM users
> WHERE users.id = @id_1 ORDER BY users.id
> 2010-06-09 16:59:50,459 INFO sqlalchemy.engine.base.Engine.0x...ae2c {'@id_1': '1'}
>
> sqlalchemy.exc.DatabaseError: (DatabaseError) Msg 257, Level 16, State 1, Line 1
> Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.
SQLAlchemy doesn't do type coercion out of the box. Some DBAPIs do, which is why you may have not noticed this issue on other platforms. You can build that functionality yourself using TypeDecorator:
Cserna, Zsolt
>
> SQLAlchemy doesn't do type coercion out of the box. Some
> DBAPIs do, which is why you may have not noticed this issue
> on other platforms. You can build that functionality
> yourself using TypeDecorator:
>
I've fixed my problem with the TypeDecorator you suggested. Thanks.
Just to fix that, sqlalchemy doesn't do type coercion by default, and it will never do it. Is it correct?
In my test application I was able to replace the Integer type to the decorated one, which is fine, but requires the same 4-line code for my projects. It would be better if sqlalchemy would do it for me by default but if it doesn't fit to the design I can accept that.
Thanks,
Michael Bayer
On Jun 10, 2010, at 8:51 AM, Cserna, Zsolt wrote:
>>
>>
>> SQLAlchemy doesn't do type coercion out of the box. Some
>> DBAPIs do, which is why you may have not noticed this issue
>> on other platforms. You can build that functionality
>> yourself using TypeDecorator:
>>
>> http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.htm?highlight=typedecorator#sqlalchemy.types.TypeDecorator
>
>
> I've fixed my problem with the TypeDecorator you suggested. Thanks.
>
> Just to fix that, sqlalchemy doesn't do type coercion by default, and it will never do it. Is it correct?
>
> In my test application I was able to replace the Integer type to the decorated one, which is fine, but requires the same 4-line code for my projects. It would be better if sqlalchemy would do it for me by default but if it doesn't fit to the design I can accept that.
from string-> something is not always a clean cut affair (though it is for int()) , but it also adds overhead to the usage of bind parameters, which can add up if you're doing an executemany() style call with thousands of parameter sets. Its better that this additional behavior is left up to extensions.
Any non-trivial SQLAlchemy project would usually have a "util.py" of some sort that contains a set of SQLAlchemy idioms for usage throughout the application. In mine I usually have a whole set of types that are specific to the use case at hand, column-generating macros that enforce naming schemes, stuff like that.