padding of CHAR fields, inconsistent where clause; Oracle example

[volx]

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/mypass@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:mypass@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.

Regards,

Victor

[Michael Bayer]

that's a little strange. have you tried:


In [28]: import cx_Oracle

In [30]: con = cx_Oracle.connect('myuser/mypass@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.

[volx]

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
')]

[volx]

cx_Oracle actually has thread on that a topic at
http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.00E5.0%40uwinnipeg.ca

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.

[volx]

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

[Michael Bayer]

volx wrote:
>
> cx_Oracle actually has thread on that a topic at
> http://sourceforge.net/mailarchive/message.php?msg_id=47BED8B8.3983.00E5.0%40uwinnipeg.ca
>
> 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

[volx]

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/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.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.

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

[Michael Bayer]

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.

[volx]

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:passwd@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...

[Michael Bayer]

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).

[volx]

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
mik...@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

> ...
>
> read more »

[Michael Bayer]

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()

[volx]

Hi Mike:

Thank you for that. I will try it out on Monday.

I see there is a major refactoring around types in version 0.6. One
would be expected to define table classes with generic SQL types like
CHAR or generic "language" types like String and not with dialect
implementations like OracleChar. Am I right in that?

Still it would be nice understand why the "monkey patch" did not work
but it is probably not the best use of your time or mine. Thanks again
for the prompt fix and I will be upgrading to 0.6.

V.

> > mike...@zzzcomputing.com

> ...
>
> read more »

[Michael Bayer]

On Oct 3, 2009, at 5:18 PM, volx wrote:

>
> Hi Mike:
>
> Thank you for that. I will try it out on Monday.
>
> I see there is a major refactoring around types in version 0.6. One
> would be expected to define table classes with generic SQL types like
> CHAR or generic "language" types like String and not with dialect
> implementations like OracleChar. Am I right in that?

that's correct and that is the usage contract of all versions of
SQLAlchemy. in 0.6 we just made it very clear. the exception
though is when the DB-specific type accepts additional arguments, like
the MySQL types - but again we removed the need to deal with special
names like MSString and such - if you wanted a MySQL-specific
VARCHAR, you'd import VARCHAR from the MySQL dialect and utilitize its
special constructor arguments.

>
> Still it would be nice understand why the "monkey patch" did not work
> but it is probably not the best use of your time or mine. Thanks again
> for the prompt fix and I will be upgrading to 0.6.

its likely not a big fix in 0.5 but Oracle support really began most
fully in 0.6, where for example we got 100% unit test coverage.

[volx]

All has been working fine in 0.6, Mike. Thanks.

Out of curiosity how do you unit test against Oracle. Do you use some
kind of mock object?

> ...
>
> read more »

[Michael Bayer]

volx wrote:
>
> All has been working fine in 0.6, Mike. Thanks.
>
> Out of curiosity how do you unit test against Oracle. Do you use some
> kind of mock object?

the test suite has --db and --dburi options that allow any URI to be used
for tests. i.e. nosetests --dburi oracle://scott:tiger@localhost/xe .
The tests are sensitive to the backend in use so many oracle-specific
tests get invoked with that URL.