On Oct 2, 2009, at 6:05 PM, volx wrote:
>
> My module is called sandbox.py
> After importing it to ipython and letting it run, here's what I get
> for the test you suggested:
>
> In [47]: (sandbox.price_sources.c.desciption=='EJV').right.type
> Out[47]: OracleChar(length=100, convert_unicode=False,
> assert_unicode=None)
>
> The trouble that for some reason the code goes into
> sqlalchemy.types.String.get_dbapi_type instead of
> sqlalchemy.databases.oracle.OracleChar
I added an _OracleChar type to the oracle dialect in trunk (0.6) which
provides dbapi.FIXED_CHAR. I was able to reproduce your original
behavior, added a unit test which exercises the select lookup on the
CHAR column both with a straight Table as well as a reflected one, and
it all passes. There was no issue with the get_dbapi_type()
mechanism. That is in r6386.
test:
m = MetaData(testing.db)
t = Table('t1', m,
Column('id', Integer, primary_key=True),
Column('data', CHAR(30), nullable=False)
)
t.create()
try:
t.insert().execute(
dict(id=1, data="value 1"),
dict(id=2, data="value 2"),
dict(id=3, data="value 3")
)
eq_(t.select().where(t.c.data=='value 2').execute
().fetchall(),
[(2, 'value 2 ')]
)
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
assert type(t2.c.data.type) is CHAR
eq_(t2.select().where(t2.c.data=='value 2').execute
().fetchall(),
[(2, 'value 2 ')]
)
finally:
t.drop()
>
> In [14]: pdb.run('sandbox.test()')
>> <string>(1)<module>()
> (Pdb) b /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-
> py2.6.egg/sqlalchemy/engine/default.py:322
> Breakpoint 7 at /usr/local/lib/python2.6/site-packages/
> SQLAlchemy-0.5.5-py2.6.egg/sqlalchemy/engine/default.py:322
> (Pdb) c
> 2009-10-02 17:58:18,020 INFO sqlalchemy.engine.base.Engine.0x...97ec
> SELECT USER FROM DUAL
> 2009-10-02 17:58:18,020 INFO sqlalchemy.engine.base.Engine.0x...97ec
> {}
> 2009-10-02 17:58:18,025 INFO sqlalchemy.engine.base.Engine.0x...97ec
> select table_name from all_tables where table_name=:name and
> owner=:schema_name
> 2009-10-02 17:58:18,026 INFO sqlalchemy.engine.base.Engine.0x...97ec
> {'name': 'VICTOR_PRICE_SOURCES', 'schema_name': 'CDOCOLLAT'}
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/engine/default.py(322)set_input_sizes()
> -> dbtype = typeengine.dialect_impl(self.dialect).get_dbapi_type
> (self.dialect.dbapi)
> (Pdb) dbtype
> *** NameError: name 'dbtype' is not defined
> (Pdb) typeengine.dialect_impl(self.dialect)
>
> OracleChar(length=100, convert_unicode=False, assert_unicode=None)
>
> (Pdb) # we should be going into OracleChar.get_dbapi_type
>
> *** SyntaxError: unexpected EOF while parsing (<stdin>, line 1)
> (Pdb) s
> --Call--
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/types.py(124)dialect_impl()
> -> def dialect_impl(self, dialect, **kwargs):
> (Pdb) l
> 119 Column('data', MyType(16))
> 120 )
> 121
> 122 """
> 123
> 124 -> def dialect_impl(self, dialect, **kwargs):
> 125 try:
> 126 return self._impl_dict[dialect]
> 127 except AttributeError:
> 128 self._impl_dict = weakref.WeakKeyDictionary() #
> will be optimized in 0.6
> 129 return self._impl_dict.setdefault(dialect,
> dialect.type_descriptor(self))
> (Pdb) l
> 130 except KeyError:
> 131 return self._impl_dict.setdefault(dialect,
> dialect.type_descriptor(self))
> 132
> 133 def __getstate__(self):
> 134 d = self.__dict__.copy()
> 135 d.pop('_impl_dict', None)
> 136 return d
> 137
> 138 def get_col_spec(self):
> 139 """Return the DDL representation for this type."""
> 140 raise NotImplementedError()
> (Pdb) dialect
> <sqlalchemy.databases.oracle.OracleDialect object at 0xa1ae54c>
> (Pdb) n
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/types.py(125)dialect_impl()
> -> try:
> (Pdb) n
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/types.py(126)dialect_impl()
> -> return self._impl_dict[dialect]
> (Pdb) n
> --Return--
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/types.py(126)dialect_impl()->OracleCh...ode=None)
> -> return self._impl_dict[dialect]
> (Pdb) l
> 121
> 122 """
> 123
> 124 def dialect_impl(self, dialect, **kwargs):
> 125 try:
> 126 -> return self._impl_dict[dialect]
> 127 except AttributeError:
> 128 self._impl_dict = weakref.WeakKeyDictionary() #
> will be optimized in 0.6
> 129 return self._impl_dict.setdefault(dialect,
> dialect.type_descriptor(self))
> 130 except KeyError:
> 131 return self._impl_dict.setdefault(dialect,
> dialect.type_descriptor(self))
> (Pdb) dialect
> <sqlalchemy.databases.oracle.OracleDialect object at 0xa1ae54c>
> (Pdb) n
> --Call--
>> /usr/local/lib/python2.6/site-packages/SQLAlchemy-0.5.5-py2.6.egg/
>> sqlalchemy/types.py(477)get_dbapi_type()
> -> def get_dbapi_type(self, dbapi):
> (Pdb) l
> 472 return value
> 473 return process
> 474 else:
> 475 return None
> 476
> 477 -> def get_dbapi_type(self, dbapi):
> 478 return dbapi.STRING
> 479
> 480 class Text(String):
> 481 """A variably sized string type.
> 482
> (Pdb) l 1
> 1 # types.py
> 2 # Copyright (C) 2005, 2006, 2007, 2008, 2009 Michael Bayer
> mike...@zzzcomputing.com
> 3 #
> 4 # This module is part of SQLAlchemy and is released under
> 5 # the MIT License: http://www.opensource.org/licenses/mit-license.php
> 6
> 7 """defines genericized SQL types, each represented by a
> subclass of
> 8 :class:`~sqlalchemy.types.AbstractType`. Dialects define
> further subclasses of these
> 9 types.
> 10
> 11 For more information see the SQLAlchemy documentation on
> types.
>
>
>
>
> On Oct 1, 2:48 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>> 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/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
>>
>> 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
>>
>> ...
>>
>> read more ยป
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
