Disregard this testcase.... it's late at night.... no need to convert
a date to a date.... but the previous testcase fails for .4beta and
works for .3.7!!!
On 9/22/07, m h <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
