passing variables to Oracle via mxODBC

[Roeland Rengelink]

ari_deja at ivritype.com wrote:
> 
> I'm having an interesting problem talking with an oracle database via
> ODBC,
> using the mxDateTime and mxODBC modules.
> 
> The problem original appeared as below, but I seem to have since
> generalized it to "anything that is a variable that is in the
> cursor.execute statement doesn't appear to be parsed prior to being
> passed to Oracle."
> 
> For instance:
> 
> The problem comes in trying to insert the current date/time from the
> system clock, into the record. First, I use the "now" constructor to
> create what I think is a string with the current date, and then print
> it out to check:
> 
>         now=DateTime.now()
>         print now
> 

now is not a string

use now = str(DateTime.now()) instead

print does the string-conversion silently.

[snip]

> That part works great. But then I try to use Oracle's
> (8i) "TO_DATE" function to convert this to something that Oracle
> understands:
> 
>         cursor.execute("""
>                 insert (mystring,mydate) into bar.test values
> ('foo',TO_DATE('now','YYYY-MM-DD HH:MI:SS'))
>                 """)
> This, invariably gives me the result:
> ------------------------
> 2000-10-02 14:32:27.09
> Traceback (innermost last):
>   File "D:\PROGRA~1\Python\Tools\idle\ScriptBinding.py", line 131, in
> run_module_event
>     execfile(filename, mod.__dict__)
>   File "D:\Program Files\Python\junk\dbtest6.py", line 20, in ?
>     crsr.execute("""
> OperationalError: ('S1000', 1858, '[Oracle][ODBC][Ora]ORA-01858: a
> non-numeric character was found where a numeric was expected\012', 3205)
> ---------------------------------------
> 
> Which suggests some sort of typing error, or that the variable isn't
> being parsed properly. This sort of makes sense--at what point, for
> instance, is "now" converted to the value set by DateTime()? 

cursor.execute seems to take a string as argument. The way to tell
Python to replace part of a string with the values of variables is by
using the % 
operator.  

>>>a, b = 'nice', 'Python'
>>>"A %s day to work on %s" % (a, b)

results in

A nice day to work on Python

See the manual for variations on the %s formatting directive.
 
In your case you'll need something like

        cursor.execute("""
                 insert (mystring,mydate) into bar.test values
 (%s ,TO_DATE(%s,'YYYY-MM-DD HH:MI:SS'))
                 """ % (foo, str(now)))




> Help?
> 

Hope so.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Roeland