oracle interface

[Andrew Dalke]

In searching I find there several different ways to
connect to an Oracle server on MS Windows:

mxODBC - http://www.egenix.com/files/python/mxODBC.html
built on top of the ODBC drivers for a given database

DCOracle2 - http://www.zope.org/Members/matt/dco2/
last update is 1.3 beta released 2 years ago?

cx_oracle - http://www.computronix.com/utilities.shtml

oc8py - http://sourceforge.net/projects/oci8py
Looks dead

Based on my own observations, people generally use the
first two packages. Left to me I would use DCOracle2
because I've used it under unix - I have rather little
MS Windows experience.

There hasn't been much discussion about either option
on c.l.py so I'ld be grateful for feedback.

Andrew
da***@dalkescientific.com

[wittempj]

We use cx_oracle, for us it has proven to be very stable running on
Solaris.

[Bernard Delmée]

We're so satisfied with cx_Oracle (HP-UX & Win32) that we
have not even bothered checking the other ones.

Highly recommended.

[infidel]

cx_Oracle rocks

[Grig Gheorghiu]

I subscribe to the other posters' opinion: cx_Oracle is what I use on
Windows, Linux and Solaris. Works great cross-platform and across
Oracle versions (I use it with Oracle 9 and 10, haven't tried 8 yet).

Grig

[Andrew Dalke]

A while back I asked about which Oracle client to use for
MS Windows. Turns out I also needed one for unix so I followed
people's advice and installed cx_Oracle.

I want to execute a query with an "IN" in the WHERE clause
and with the parameter taken from a Python variable. That
is, I wanted something like this to work

id_list = ["AB001", "AB002", "AB003"]

c.execute("""SELECT s.smiles FROM smiles_database s WHERE """
""" s.id IN :id_list""", id_list = id_list)

I couldn't get it to work. It complained

arrays can only be bound to PL/SQL statements

I tried looking at the source code but couldn't figure out
how to do this. In no small part due to my nearly complete
lack of experience with Oracle or for that matter SQL databases.

My solution was to build a new string to executed but it
wasn't pretty and I needed to explain to my client about
SQL injection; wanted to use repr(a_tuple) which was *almost*
correct.

How do I do what I want to do?

Andrew
da***@dalkescientific.com

[infidel]

Something like this might work for you:

ids= ['D102', 'D103', 'D107', 'D108']
in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])
sql = "select * from tablename where id in (%s)" % in_clause
import cx_Oracle as ora
con = ora.connect('foo/bar@geewhiz')
cur = con.cursor()
cur.execute(sql, ids)

[Andrew Dalke]

infidel wrote:

Something like this might work for you:

ids= ['D102', 'D103', 'D107', 'D108']
in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])
sql = "select * from tablename where id in (%s)" % in_clause
import cx_Oracle as ora
con = ora.connect('foo/bar@geewhiz')
cur = con.cursor()
cur.execute(sql, ids)

That's pretty much what I did but it seems inelegant.
I would rather do

ids = ['D102', 'D103', 'D107', 'D108']
.. connect and set up the cursor ..
cursor.execute("select * from tablename where id in :ids", ids)

and if 'ids' is seen to be a list or tuple then it does
the appropriate conversion. (I'm also fine with having
to use ()s in the SQL query, as in "id in (:ids)".)

The lack of a simple way to do this is error prone. I've seen
people do

cursor.execute("select * from tablename where id in (%s)" % repr(ids))

because the repr of a string is close enough that it works
for expected string values. But it opens up the possibility
of SQL injection problems.

Andrew
da***@dalkescientific.com

[infidel]

I think perhaps you are asking for something that the OCI doesn't
provide. At least I'd be rather surprised if it did. I know that the
SQL syntax doesn't provide for such a mechanism.

And really, it all boils down to the list comprehension:

in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])

.... elegance is certainly subjective, and the above statement isn't the
cleanest ever, but it solves your main problem while avoiding the other
problem you mentiong (sql injection). Seems "elegant enough" to me.

[Andrew Dalke]

infidel wrote:

I think perhaps you are asking for something that the OCI doesn't
provide.
But it doesn't need to be supported by the OCI.
And really, it all boils down to the list comprehension:

in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])
And why can't the equivalent to that be supported in the
DB-API interface, so I can pass in a list/tuple and have
it just work?
... elegance is certainly subjective, and the above statement isn't the
cleanest ever, but it solves your main problem while avoiding the other
problem you mentiong (sql injection). Seems "elegant enough" to me.

The problem I mentioned is supporting inexperienced developers
(scientists writing software without local programming support)
who, in my experience, don't know about this pitfall and are
more likely to use a close but wrong solution than this correct
one. repr(ids) is after all much easier to write.

Andrew
da***@dalkescientific.com

[Steve Holden]

Andrew Dalke wrote:

infidel wrote:

I think perhaps you are asking for something that the OCI doesn't
provide.

But it doesn't need to be supported by the OCI.

And really, it all boils down to the list comprehension:

in_clause = ', '.join([':id%d' % x for x in xrange(len(ids))])

And why can't the equivalent to that be supported in the
DB-API interface, so I can pass in a list/tuple and have
it just work?

... elegance is certainly subjective, and the above statement isn't the
cleanest ever, but it solves your main problem while avoiding the other
problem you mentiong (sql injection). Seems "elegant enough" to me.

The problem I mentioned is supporting inexperienced developers
(scientists writing software without local programming support)
who, in my experience, don't know about this pitfall and are
more likely to use a close but wrong solution than this correct
one. repr(ids) is after all much easier to write.

Andrew:

I cannot help but agree that a more sympathetic treatment of the various
sequence types would help tyros and pros alike.

Do you think this is a DB-API 3-ish kind of a thing, or would it layer
over DB-API 2 in a relatively platform-independent manner? I suspect
that some of the code has to be inside the driver (and hence possibly at
least partly written in C) to hook into platform-dependent features like
quoting string literals.

but-you-may-know-better-ly y'rs - steve
--
Steve Holden [phone] [phone]
Holden Web LLC http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/

[Andrew Dalke]

Steve Holden wrote:

Do you think this is a DB-API 3-ish kind of a thing, or would it layer
over DB-API 2 in a relatively platform-independent manner? ... but-you-may-know-better-ly y'rs - steve

I am a tyro at this. I had to find some tutorials on SQL
to learn there even was an IN clause for the WHERE statement.
All told I've had about 1 hour experience using DB-API 2.

I thought this would be a common enough need that others
would have chimed in by now saying "oh yes, you just need
to XYZ" where XYZ is something cleaner than "make a new
string to execute".

Andrew
da***@dalkescientific.com

[Steve Holden]

Andrew Dalke wrote:

Steve Holden wrote:

Do you think this is a DB-API 3-ish kind of a thing, or would it layer
over DB-API 2 in a relatively platform-independent manner?

...

but-you-may-know-better-ly y'rs - steve

I am a tyro at this. I had to find some tutorials on SQL
to learn there even was an IN clause for the WHERE statement.
All told I've had about 1 hour experience using DB-API 2.

I thought this would be a common enough need that others
would have chimed in by now saying "oh yes, you just need
to XYZ" where XYZ is something cleaner than "make a new
string to execute".

I seem to remember from a good while back that there have *been*
database (perhaps even specifically Oracle) interface modules that
supported a set-style interface in ways similar to your stated
requirements, but alas it's a long time ago and the details are dim.

Given that we now have a Decimal type, allowing us to fulfil an
outstanding DB API wish list item, maybe it's time for DB API 3. Anyone?

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/

[Daniel Dittmar]

Andrew Dalke wrote:

I want to execute a query with an "IN" in the WHERE clause
and with the parameter taken from a Python variable. That
is, I wanted something like this to work

id_list = ["AB001", "AB002", "AB003"]

c.execute("""SELECT s.smiles FROM smiles_database s WHERE """
""" s.id IN :id_list""", id_list = id_list)

I couldn't get it to work. It complained

arrays can only be bound to PL/SQL statements

Possible workarounds:
- use executemany: a few databases allow to execute several sets of
input parameters at once. And even fewer allow this for SELECTs, where
the result is one cursor created from the UNION of a SELECt for each set
of input parameters. Apart from being unlikely to work, this also
requires that *all* input parameters are lists of the same length
(unless the driver is smart enough to expand skalars to lists in this
context)

- specify a maximum number of input parameters 's.id in (:id0, :id1,
....)' and fill missing values with the first value

- create a class for this purpose. Statement are created on the fly, but
with placeholders so you don't run into the SQL Injection problem. As
it's an object, you could cache these generated statements base on the
size of the list

- create a temporary table, insert all the values into that table
(executemany is great for INSERTS) and then join with that table

You could also search comp.language.java.database where this is a
frequent question.

It is unlikely that this can be solved at the driver level. Without
support from the database, the driver would have to manipulate the SQL
statement. And there are few predicates where a list parameter is
useful. Expanding a list always yould lead to very bizarre error
messages. Expanding them only where useful would require a SQL parser.

Daniel

[Andrew Dalke]

Daniel Dittmar wrote:

Possible workarounds: ... - create a class for this purpose. Statement are created on the fly, but
with placeholders so you don't run into the SQL Injection problem. As
it's an object, you could cache these generated statements base on the
size of the list It is unlikely that this can be solved at the driver level. Without
support from the database, the driver would have to manipulate the SQL
statement.
And there are few predicates where a list parameter is useful. Expanding
a list always yould lead to very bizarre error messages. Expanding them
only where useful would require a SQL parser.

Perhaps I'm missing something fundamental here. I thought the
terms like :arg2 were already being parsed at the Python/driver
interface, to insert the right values from the Python args.

If that was so then it could be solved at the driver level pretty
easily; use the aformentioned "class for this purpose".

It sounds like you're saying that the interface is actually implemented
by passing the execute string and a database-specific dictionary-like
object; the latter created by the DB-API interface.

If so, I now understand the limitation.

Hmmmm.....

Andrew
da***@dalkescientific.com

[Daniel Dittmar]

Andrew Dalke wrote:

It sounds like you're saying that the interface is actually implemented
by passing the execute string and a database-specific dictionary-like
object; the latter created by the DB-API interface.

That's the way it's supposed to work. The program prepares a statement
with placeholders only once and sends it with varying parameters to the
database. That way, the statement has to be parsed by the database only
once and the execution plan can be reused (with Oracle even between
different sessions). It seems as if nothing ticks Oracle DBAs more off
than if you're complaining about poor performance, but you're not using
these prepared statements.

<History>
The :placeholder syntax comes from embedded SQL. You would write the
names of actual program variables there and a precompiler would generate
the code to 'bind' the program variables to SQL parameters. Embedded SQL
has fallen out of favour and new APIs (ODBC, JDBC) use the question mark
as the placeholder. The principle remains the same for many databases:
the SQL string is sent unchanged to the database. Additionally, a list
of actual values is sent for each execution.
</History>

Daniel