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.
--
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.
--- pysybase.py 2010-06-09 11:16:55.000000000 -0400
+++ pysybase.py.new 2010-06-09 11:17:54.000000000 -0400
@@ -33,6 +33,12 @@
else:
return sqltypes.Numeric.result_processor(self, dialect, type_)
+class _SybInteger(sqltypes.Integer):
+ def bind_processor(self, dialect):
+ def process(value):
+ return int(value)
+ return process
+
class SybaseExecutionContext_pysybase(SybaseExecutionContext):
def set_ddl_autocommit(self, dbapi_connection, value):
@@ -62,7 +68,8 @@
colspecs={
sqltypes.Numeric:_SybNumeric,
- sqltypes.Float:sqltypes.Float
+ sqltypes.Float:sqltypes.Float,
+ sqltypes.Integer:_SybInteger
}
@classmethod
