I missed one important statement - prepare must be called on the query: query = "select * from price_sources where description = :someparam" cursor.prepare(query) cursor.setinputsizes(dict(someparam=cx_Oracle.FIXED_CHAR)) cursor.execute(query, dict(someparam='EJV')).fetchall()
On Sep 30, 3:07 pm, volx <victor.o...@gmail.com> wrote: > cx_Oracle actually has thread on that a topic > athttp://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.0... > > It boils down to having to specify a type for input parameter. Is that > something I could "plug-in" as a decorator or would that need to be > hacked on SqlAlchemy level? > > Here's how: > > In [70]: query = "select * from price_sources where description > = :someparam" > In [71]: cursor.setinputsizes(someparam=cx_Oracle.FIXED_CHAR) > Out[71]: {'someparam': <cx_Oracle.FIXED_CHAR with value None>} > In [72]: cursor.execute(query, dict(someparam='EJV')).fetchall() > Out[72]: > [(149, > > 'EJV > ')] > > Any help would be appreciated. > > On Sep 30, 2:36 pm, volx <victor.o...@gmail.com> wrote: > > > I have just now and it looks that this post probably belongs on > > cx_Oracle mailing list. > > > In [47]: cursor.execute("select * from price_sources where desciption > > = :someparam", dict(someparam='EJV')).fetchall() > > Out[47]: [] > > In [49]: cursor.execute("select * from price_sources where desciption > > = :someparam", dict > > (someparam='EJV > > ')).fetchall() > > Out[49]: > > [(149, > > > 'EJV > > ')] > > > On Sep 30, 2:18 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > > > > On Sep 30, 2:07 pm, volx <victor.o...@gmail.com> wrote: > > > > > Hello all: > > > > > Consider table: > > > > > CREATE TABLE PRICE_SOURCES ( > > > > ID decimal(22) PRIMARY KEY NOT NULL, > > > > DESCRIPTION char(100) NOT NULL > > > > ) > > > > > and this screen dump from ipython session: > > > > > In [28]: import cx_Oracle > > > > In [29]: from sqlalchemy.ext.sqlsoup import SqlSoup > > > > In [30]: con = cx_Oracle.connect('myuser/myp...@mydb') > > > > In [31]: cursor = con.cursor() > > > > In [32]: cursor.execute("select * from price_sources where description > > > > = 'EJV'").fetchall() > > > > Out[32]: > > > > [(149, > > > > > 'EJV > > > > ')] > > > > > Note that the result has padding in the column "description" as it is > > > > of type CHAR(100), with is fixed length. > > > > > Now, let's do the same in SqlAlchemy. > > > > > In [33]: soup = SqlSoup('oracle://myuser:myp...@mydb') > > > > In [34]: soup.price_sources.filter_by(description='EJV').all() > > > > Out[34]: [] > > > > > As you can see it returns zero rows. Only after I pad the parameter > > > > for where clause will I get my row back: > > > > > In [35]: soup.price_sources.filter_by > > > > (desciption='EJV > > > > ').all() > > > > Out[35]: [MappedPrice_sources(pricesource=Decimal > > > > ('149'),desciption='EJV > > > > ')] > > > > > The same behavior happens even if I use ORM and regardless whether the > > > > column metadata is defined with String, OracleString or CHAR. > > > > > I can understand the query result being padded as it is consistent > > > > with behavior everywhere but the where clasues must not. It is a big > > > > problem if you try to use session.merge() because you have to pad > > > > contents of object fields otherwise the ORM will insert a duplicate. > > > > > Please help. I hope I am doing something wrong or missing something. > > > > that's a little strange. have you tried: > > > > In [28]: import cx_Oracle > > > In [30]: con = cx_Oracle.connect('myuser/myp...@mydb') > > > In [31]: cursor = con.cursor() > > > In [32]: cursor.execute("select * from price_sources where description > > > = :someparam", dict(someparam='EJV')).fetchall() > > > > ? > > > > since the usage of bind parameters is how it actually runs. If same > > > behavior there then it's a cx_oracle behavior. you can verify this > > > by turning on SQL echoing. As a workaround you can build yourself a > > > TypeDecorator that elongates/truncates incoming strings though that > > > seems a little inconvenient. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---