[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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?
On Oct 4, 10:01 am, Michael Bayer mike...@zzzcomputing.com wrote:
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.
V.
On Oct 3, 4:02 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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.
On Oct 3, 4:02 pm, Michael Bayer mike...@zzzcomputing.com wrote:
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] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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...
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
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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
[sqlalchemy] padding of CHAR fields, inconsistent where clause; Oracle example
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.
Regards,
Victor
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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.
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: padding of CHAR fields, inconsistent where clause; Oracle example
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...
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.
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
-~--~~~~--~~--~--~---
