volx wrote:
>
> Here's my program, modified as you suggest. It also creates the table
> so you can try it on any instance of Oracle. No joy on the result.
> Seems like the set_inputsizes isn't called? How can I tell for
> certain?
>
> import sqlalchemy.types as sqltypes
> from exceptions import NotImplementedError
>
> class OracleChar(sqltypes.CHAR):
> """Patched OracleChar type to fix padding issue
>
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/965287c91b790b68
> """
> def get_col_spec(self):
> return "CHAR(%(length)s)" % {'length' : self.length}
>
> def get_dbapi_type(self, dbapi):
> raise NotImplementedError('gotcha')
> return dbapi.FIXED_CHAR
is this using 0.5 or current trunk ? There's some additional restrictions
in trunk on setinputsizes due to some cx_oracle bugs I reported.
Unfortanately my macbook is in the shop so I won't be able to test this
myself until possibly next week. Basically the the "set_input_sizes()"
in sqlalchemy/base/default.py should be called for all oracle executions
and the datatype on the bind parameter generated by "mycol == <some
value>" should be of your type, i.e. isinstance((mycol==5).right.type,
OracleChar).
>
> from sqlalchemy.databases import oracle
> oracle.OracleChar = OracleChar
>
> from sqlalchemy import Table, Column, Integer, MetaData
> from sqlalchemy.databases.oracle import OracleChar
> from sqlalchemy.sql import select, insert
> from sqlalchemy import create_engine
>
> metadata = MetaData()
>
> price_sources = Table('victor_price_sources', metadata,
> Column('pricesource', Integer, primary_key=True),
> Column('desciption', OracleChar(100))
> )
>
> dburl = "oracle://user:pas...@servername:1521/dbname"
>
> e = create_engine(dburl, echo=True)
> metadata.bind = e
> metadata.create_all()
> con = e.connect()
>
> ins = price_sources.insert().values(pricesource=1, desciption='EJV')
> con.execute(ins)
>
> s = select([price_sources], price_sources.c.desciption=='EJV')
> all = con.execute(s).fetchall()
> print all
>
> s = select([price_sources])
> all = con.execute(s).fetchall()
> print all
>
> And the results:
>
> In [1]: import sandbox
> 2009-10-01 13:24:25,015 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> SELECT USER FROM DUAL
> 2009-10-01 13:24:25,018 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {}
> 2009-10-01 13:24:25,022 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> select table_name from all_tables where table_name=:name and
> owner=:schema_name
> 2009-10-01 13:24:25,022 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {'name': 'VICTOR_PRICE_SOURCES', 'schema_name': 'CDOCOLLAT'}
> 2009-10-01 13:24:25,027 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> CREATE TABLE victor_price_sources (
> pricesource INTEGER NOT NULL,
> desciption CHAR(100),
> PRIMARY KEY (pricesource)
> )
>
>
> 2009-10-01 13:24:25,027 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {}
> 2009-10-01 13:24:25,082 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> COMMIT
> 2009-10-01 13:24:25,084 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> INSERT INTO victor_price_sources (pricesource, desciption) VALUES
> (:pricesource, :desciption)
> 2009-10-01 13:24:25,084 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {'pricesource': 1, 'desciption': 'EJV'}
> 2009-10-01 13:24:25,089 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> COMMIT
> 2009-10-01 13:24:25,092 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> SELECT victor_price_sources.pricesource,
> victor_price_sources.desciption
> FROM victor_price_sources
> WHERE victor_price_sources.desciption = :desciption_1
> 2009-10-01 13:24:25,092 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {'desciption_1': 'EJV'}
> []
> 2009-10-01 13:24:25,105 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> SELECT victor_price_sources.pricesource,
> victor_price_sources.desciption
> FROM victor_price_sources
> 2009-10-01 13:24:25,105 INFO sqlalchemy.engine.base.Engine.0x...9ccc
> {}
> [(1,
> 'EJV
> ')]
>
>
>
> On Oct 1, 10:10 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> volx wrote:
>>
>> > Micheal:
>>
>> > Thanks for your help thus far. Unfortunately I don't think the
>> > get_dbapi_type method gets called. From reading your code I see how it
>> > should be called from pre_exec -> setinputsizes -> get_dbapi_type but
>> > empirical evidence shows otherwise. Here's my little harness:
>>
>> > import sqlalchemy.types as sqltypes
>> > from exceptions import NotImplementedError
>>
>> > class OracleChar(sqltypes.CHAR):
>> > """Patched OracleChar type to fix padding issue
>> >
>> http://groups.google.com/group/sqlalchemy/browse_thread/thread/965287...
>> > """
>> > def get_col_spec(self):
>> > return "CHAR(%(length)s)" % {'length' : self.length}
>>
>> > def get_dbapi_type(self, dbapi):
>> > raise NotImplementedError('gotcha')
>> > return dbapi.FIXED_CHAR
>>
>> > from sqlalchemy.databases import oracle
>> > oracle.OracleChar = OracleChar
>>
>> > from sqlalchemy.ext.sqlsoup import SqlSoup
>>
>> > dburl = "oracle://....."
>> > soup = SqlSoup(dburl)
>> > all = soup.price_sources.filter_by(desciption='EJV').all()
>> > print all # shows empty list, we should not even get here due to
>> > exception I raise
>>
>> > all = soup.price_sources.filter_by
>> > (desciption='EJV
>> > ').all()
>> > print all # shows record in list
>>
>> > Returns empty list.
>>
>> SqlSoup uses table reflection and in this case you'd have to ensure that
>> the column in question is assigned the CHAR datatype, which its very
>> likely that it's not. I would start by using a hand-constructed Table
>> object and testing the behavior against that using simple select()
>> constructs.
>>
>>
>>
>> > On Sep 30, 3:44 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> >> volx wrote:
>>
>> >> > cx_Oracle actually has thread on that a topic at
>> >> >http://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 is the thing. We call setinputsizes() with oracle. So perhaps
>> >> OracleChar() needs to have dbapi.FIXED_CHAR established as its
>> >> get_dbapi_type(). would you care to try monkeypatching this (in
>> 0.5,
>> >> the
>> >> fix for 0.6 would be different) ?
>>
>> >> class OracleChar(sqltypes.CHAR):
>> >> def get_col_spec(self):
>> >> return "CHAR(%(length)s)" % {'length' : self.length}
>>
>> >> def get_dbapi_type(self, dbapi):
>> >> return dbapi.FIXED_CHAR
>>
>> >> from sqlalchemy.databases import oracle
>> >> oracle.OracleChar = OracleChar
>>
>> >> > 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
-~----------~----~----~----~------~----~------~--~---
