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