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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---