passing variables to Oracle via mxODBC

[Paul Boddie]

Roeland Rengelink wrote:
> 
> 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.

This is indeed the case for string formatting in Python. However...

> 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)))

You would be better off using the parameter passing features of mxODBC:

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

I believe that :1 and :2 are consistent with Oracle's own placeholder notation.
(Places where values are to be "substituted" into the string are numbered from 1
to n, where n is the number of elements in the tuple provided as the second
argument to execute.) Other database systems use ? or other notations. Despite
the API having this feature documented [1,2], it surprises me that people will
recommend a harder, if slightly more familiar way (to a particular mindset) to
achieve something so straightforward.

Paul

[1] http://www.python.org/topics/database/DatabaseAPI-2.0.html#cursor (see
    execute)
[2] http://www.python.org/topics/database/DatabaseAPI-2.0.html#module (see
    paramstyle)