On 9/22/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> so the new error youre getting now puts it back in the "execution"
> camp. you need to narrow down the query to the specific column
> expression and/or parameter thats making it break.
Here's a simple query that fails. The deal breaker is the between
function for dates. When I add the between the query fails (note that
it's a stupid query but it gives the error)...
AD_WEEK_CALENDAR_DIM table
----------------------------------------------------
ADWKENDDT type: DATE
PRODUCT_DIM table
-----------------------------------
DEPTNM type: VARCHAR2(30)
::
#python code
#..set username, password, etc
engine = create_engine("oracle://%s:[EMAIL PROTECTED]:1521/%s"%(u, p, host, db))
connection = engine.connect()
meta = MetaData(engine)
cal_dim = Table('AD_WEEK_CALENDAR_DIM', meta, autoload=True)
prod_dim = Table('PRODUCT_DIM', meta, autoload=True)
def fail_query():
start_date = '12/26/03'
end_date = '12/30/03'
where =
and_(cal_dim.c.adwkenddt.between(func.to_date(start_date,'MM/DD/RR'),
func.to_date(end_date, 'MM/DD/RR'))
)
query = select([prod_dim.c.deptnm.label("department")],
whereclause=where
)
compiled = query.compile()
result = query.execute()
for r in result:
print r
fail_query()
Here's the error:
Traceback (most recent call last):
Traceback (most recent call last):
File "s4.py", line 43, in ?
fail_query()
File "s4.py", line 39, in fail_query
result = query.execute()
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py",
line 971, in execute
return self.compile(bind=self.bind, parameters=compile_params,
inline=(len(multiparams) > 1)).execute(*multiparams, **params)
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 832, in _execute_compiled
self.__execute_raw(context)
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File
"/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT "PRODUCT_DIM".deptnm AS department \nFROM "PRODUCT_DIM",
"AD_WEEK_CALENDAR_DIM" \nWHERE "AD_WEEK_CALENDAR_DIM".adwkenddt
BETWEEN to_date(:to_date, :to_date_1) AND to_date(:to_date_2,
:to_date_3)' {'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR',
'to_date_1': 'MM/DD/RR', 'to_date': '12/26/03'}
Here's the log statement/params::
SELECT "PRODUCT_DIM".deptnm AS department
FROM "PRODUCT_DIM", "AD_WEEK_CALENDAR_DIM"
WHERE "AD_WEEK_CALENDAR_DIM".adwkenddt BETWEEN to_date(:to_date,
:to_date_1) AND to_date(:to_date_2, :to_date_3)
{'to_date_2': '12/30/03', 'to_date_3': 'MM/DD/RR', 'to_date_1':
'MM/DD/RR', 'to_date': '12/26/03'}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
