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
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
-~----------~----~----~----~------~----~------~--~---
