Ok, it's caused by func.to_date. Here's an even simpler testcase.
Try to convert a date to a string::
def test2():
start_date = '12/26/03'
query = select([func.to_date(cal_dim.c.adwkenddt,'MM/DD/RR')])
compiled = query.compile()
result = query.execute()
for r in result:
print r
On 9/22/07, m h <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
