FAQ
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
dalke at dalkescientific.com

Search Discussions

  • Wittempj at Apr 5, 2005 at 7:18 pm
    We use cx_oracle, for us it has proven to be very stable running on
    Solaris.



    From bogus@does.not.exist.com Tue Apr 5 21:25:02 2005
    From: bogus@does.not.exist.com ()
    Date: Tue, 05 Apr 2005 19:25:02 -0000
    Subject: (no subject)
    Message-ID: <mailman.1450.1163321523.2969.python-list@python.org>

    #! rnews 1227
    Newsgroups: comp.lang.python
    Path: news.xs4all.nl!newsspool.news.xs4all.nl!transit.news.xs4all.nl!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!nntp.abs.net!attws2!ip.att.net!NetNews1!xyzzy!nntp
    From: Harry George <harry.g.george at boeing.com>
    Subject: Re: IronPython 0.7 released!
    X-Nntp-Posting-Host: cola2.ca.boeing.com
    Content-Type: text/plain; charset=us-ascii
    Message-ID: <xqx8y3xhwny.fsf at cola2.ca.boeing.com>
    User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
    Lines: 19
    Sender: hgg9140 at cola2.ca.boeing.com
    Organization: The Boeing Company
    References: <1111603770.909447.18070 at z14g2000cwz.googlegroups.com> <acidnWJnCsYDPc_fRVn-1g at wideopenwest.com>
    Mime-Version: 1.0
    Date: Tue, 5 Apr 2005 19:05:05 GMT
    Xref: news.xs4all.nl comp.lang.python:370993

    Thomas Gagne <tgagne at wide-open-west.com> writes:
    Does the Python community think Microsoft's embrace is a good or bad thing?

    James wrote:
    "Please refer to uploaded documentation for full text of the Shared
    Source License for IronPython" says it all.

    Shared Source is an abomination, complete with the risk of
    cross-contaminating true OSS projects. I wouldn't voluntarily use the
    thing and I certainly wouldn't look at the source code.

    --
    harry.g.george at boeing.com
    6-6M21 BCA CompArch Design Engineering
    Phone: (425) 294-4718
  • Bernard Delmée at Apr 5, 2005 at 8:00 pm
    We're so satisfied with cx_Oracle (HP-UX & Win32) that we
    have not even bothered checking the other ones.

    Highly recommended.
  • Infidel at Apr 5, 2005 at 8:56 pm
    cx_Oracle rocks
  • Grig Gheorghiu at Apr 6, 2005 at 12:22 am
    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
  • M.-A. Lemburg at Apr 7, 2005 at 7:17 pm

    Andrew Dalke wrote:
    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
    mxODBC works nicely with Oracl on Windows. There are
    two options:

    1. MS Oracle ODBC driver:

    This is the MS version of an ODBC driver for Oracle.
    It is well integrated into MS transaction managers,
    authentication and other MS techniques, but doesn't
    support all the the 8i and 9i features.

    2. Oracle ODBC driver:

    This driver is supported by Oracle itself and does
    have support for 8i and 9i.

    If these don't work for you, there are also a number
    of commercial ODBC driver kits which support Oracle
    from the usual suspects (OpenLink, EasySoft, DataDirect,
    etc.).

    Usage is pretty straightforward:

    a) install the ODBC driver
    b) create an ODBC data source (this connects the ODBC
    driver with the database you want to talk to)
    c) install egenix-mx-base and egenix-mx-commercial
    d) fire up Python...

    from mx.ODBC.Windows import DriverConnect
    dbc = DriverConnect("DSN=<datasourcename>;UID=<username>;PWD=<password>")
    c = dbc.cursor()
    c.execute('select * from mytable')
    print c.fetchall()

    If you like it, contact sales at egenix.com and we'll let you
    know what else is needed :-)

    --
    Marc-Andre Lemburg
    eGenix.com

    Professional Python Services directly from the Source (#1, Apr 07 2005)
    Python/Zope Consulting and Support ... http://www.egenix.com/
    mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
    mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
    ________________________________________________________________________

    ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
  • Myles Strous at Apr 8, 2005 at 1:17 am
    cx_Oracle here too (Win32, connecting to Oracle 9).
    Thanks, Computronix! (And, of course, thanks to the Python team and
    community.)
  • Andrew Dalke at Apr 30, 2005 at 5:02 pm
    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
    dalke at dalkescientific.com
  • Infidel at May 2, 2005 at 3:27 pm
    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 at geewhiz')
    cur = con.cursor()
    cur.execute(sql, ids)
  • Andrew Dalke at May 2, 2005 at 4:33 pm

    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 at 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
    dalke at dalkescientific.com
  • Infidel at May 2, 2005 at 5:59 pm
    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 at May 3, 2005 at 4:15 am

    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
    dalke at dalkescientific.com
  • Steve Holden at May 3, 2005 at 4:56 am

    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 +1 703 861 4237 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
    Python Web Programming http://pydish.holdenweb.com/
  • Andrew Dalke at May 3, 2005 at 5:36 am

    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
    dalke at dalkescientific.com
  • Steve Holden at May 3, 2005 at 10:49 am

    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 +1 703 861 4237 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
    Python Web Programming http://pydish.holdenweb.com/
  • Daniel Dittmar at May 3, 2005 at 12:38 pm

    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 at May 10, 2005 at 7:45 pm

    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
    dalke at dalkescientific.com
  • Daniel Dittmar at May 10, 2005 at 10:43 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedApr 5, '05 at 6:43p
activeMay 10, '05 at 10:43p
posts18
users10
websitepython.org

People

Translate

site design / logo © 2022 Grokbase