Python Database Objects (PDO) 1.2.0 Released

Discussion in 'Python' started by Bryan J Gudorf, Nov 17, 2003.

  1. PDO, an open source python module for interfacing with RDBMS (SQL
    databases), has now reached 1.2.0! PDO provides an object oriented API,
    similar to that of ADO or JDBC, to python developers. PDO features column
    access by name. This new release adds support for the cx_Oracle, DB2 and
    adodbapi modules, allowing users to use PDO with a variety of database
    systems. 9 different DBAPI modules are now supported, allowing for PDO to
    be used with almost any RDBMS. Additional features, such as query
    parameters, have also been added in this release, as well as the usual
    bug-fixes.

    PDO is released under a BSD style license.

    PDO supports the following DBAPI python modules and databases:
    MySQLdb (supports MySQL)
    PySQLite (SQLite)
    pgdb (PostgreSQL)
    kinterbasdb (Firebird, Interbase)
    mxODBC (Many)
    pyDB2 (IBM's DB/2)
    cx_Oracle (Oracle)
    adodbapi (windows only - any RDBMS with an OLE DB provider or ODBC
    driver)

    As we move forward in this series we will be adding further
    functionality
    and support. Community support and feedback is appreciated so that we can
    make
    PDO as useful as possible.
    Downloads for Python Database Objects are available on SourceForge.Net
    or for more information please visit pdo.neurokode.com.

    Bryan J Gudorf
    ~NeuroKode Labs
     
    Bryan J Gudorf, Nov 17, 2003
    #1
    1. Advertisements

  2. Bryan J Gudorf

    Rene Pijlman Guest

    Bryan J Gudorf:
    I just browsed the documentation, but I don't quite understand what
    advantages PDO offers over using DBAPI directly. A resultset object looks
    very similar to a cursor.

    What are the advantages IYO?
     
    Rene Pijlman, Nov 17, 2003
    #2
    1. Advertisements

  3. Bryan J Gudorf

    Jon Franz Guest

    I'll reply to this, if that's ok. Keep in mind I can't sleep, so
    typos may abound.

    I guess what PDO comes down to is (an attempt at) ease-of-learning
    and ease-of-use without sacrificing power. PDO is built on the
    DBAPI, but just provides a (hopefully) higher-level interface.
    Having said that, lets dive into the differences between a DBAPI
    cursor and a PDO Resultset.

    A Resultset is actually very different from a cursor - The first and
    most blatant difference being that with a Resultset, you get column
    access by name.

    With a DBAPI cursor, you create the cursor first, then perform an
    execute on the cursor, then do a fetchXXX (depending upon
    if you want one row at a time or many, or all). The data returned
    is separate from the cursor, and is a sequence of sequences.
    If you do a fetchmany or fetchall, iteration over the results is
    your responsibility.
    Also, data about the result columns is stored separately
    from the columns themselves, in a .description field of the cursor.

    PDO Resultset objects are created with the same statement
    in which you perform your query, no manual cursor creation required
    beforehand.
    PDO Resultsets also allow for easy transversal of the results, in a
    forwards or backwards or random manner, via the .move(x), .next(),
    ..prev(), .moveto(n) and other methods. Each method will return 0 if
    the destination is out-of-bounds.
    Data about the columns is accessed as if it were intrinsic to the
    columns - thus Resultset columns in PDO are objects with member
    variables.

    Here's a quick example of a simple query and loop-over results, first
    via DBAPI, then via PDO.

    DBAPI:
    import MySQLdb

    mycon = MySQLdb(user='test', passwd='foobar', db='sample')
    mycursor = mycon.cursor()
    mycursor.execute("SELECT * FROM Customers")
    results = mycursor.fetchall()
    for row in range(0, len(results)):
    print "Name: " + row[1]
    print "Address: " + row[3]
    print "Size of 'Name' column in the db: " +
    str(mycursor.description[1][3])

    ------------
    PDO:
    import pdo

    mycon = pdo.connect("module=MySQLdb;user=test;passwd=foobar;db=sample")
    results = mycon.open("SELECT * FROM Customers")
    while results.next():
    print "Name: " + results['Name'].value
    print "Address: " + results['Address'].value
    print "Size of 'Name' column in the db: " + str(results['Name'].length)

    --------------------------------
    ~Jon Franz
    NeuroKode Labs, LLC

    ----- Original Message -----
    From: "Rene Pijlman" <>
    To: <>
    Sent: Monday, November 17, 2003 2:27 AM
    Subject: Re: Python Database Objects (PDO) 1.2.0 Released
     
    Jon Franz, Nov 17, 2003
    #3
  4. Bryan J Gudorf

    Rene Pijlman Guest

    Jon Franz:
    Thanks Jon, that clarifies it.
     
    Rene Pijlman, Nov 17, 2003
    #4
  5. I havent looked at the other DBAPI implementations lately, but with
    MySQLdb you can set the cursor type to DictCursor, and then you get
    back a sequence of dictionaries with the field names as the dict keys.


    -Geoff Howland
    http://ludumdare.com/
     
    Geoff Howland, Nov 18, 2003
    #5
  6. Bryan J Gudorf

    Jon Franz Guest

    I havent looked at the other DBAPI implementations lately, but with
    This is true, but unfortunately it isn't standard behavior according to the
    DBAPI 2.0 spec (pep 249).
    Since it isn't standard, it seems everyone that does provide it
    in the module provides it a different way.- if they provide it at all.

    With PDO you get this functionality everytime, with no need to change
    your code when you switch databases and DBAPI modules to get it.
    Plus, PDO's implementation of acccess-by-name uses less memory
    than a sequence of mapping objects - excluding the case of very small
    (1-3 record) results, that is. I can exapnd upon how/why if people
    are curious.

    cheers.

    ~Jon Franz
    NeuroKode Labs, LLC
     
    Jon Franz, Nov 18, 2003
    #6
  7. Sounds good, if you can update/insert back into the DB by the same
    dictionary then I'm sold. I'll go check it out. :)


    -Geoff Howland
    http://ludumdare.com/
     
    Geoff Howland, Nov 19, 2003
    #7
  8. Bryan J Gudorf

    Serge Orlov Guest

    [Jon Franz]
    Why don't you use iterators for that?
    for result in mycon.open("SELECT * FROM Customers"):
    print ...

    Besides you're even "abusing" .next() method which you use to provide
    iterations in a different way. Confusing...

    -- Serge Orlov.
     
    Serge Orlov, Nov 19, 2003
    #8
  9. Bryan J Gudorf

    Jon Franz Guest

    while results.next():
    Individual rows are not objects in PDO.

    Your idea for an iterator would call for something to be returned from
    the .open(), such as a sequence of dictionaries, which would remove
    the coupling of column values from the column description data.
    Alternately, it would cause a repetition of the data (or at least many
    extra references to it).
    Keeping the descriptive info with the data (at least as far as how it's
    accessed) was a design goal.

    How is it abusive to use it the way it was intended?

    It may help to quit thinking of a Resultset as a sequence of dictionaries -
    PDO explicitly avoids that. The call to .next() is updating an internal
    counter within the Resultset object, and returning a value based upon
    whether the new position was in-bounds or not.


    cheers.

    ~Jon Franz
    NeuroKode Labs, LLC
     
    Jon Franz, Nov 19, 2003
    #9
  10. Bryan J Gudorf

    Serge Orlov Guest

    Yes, if the .open() is an generator then it must return a sequence of items
    but only one at a time. If the loop body doesn't keep the result object
    it will be garbage collected pretty soon. You don't need to return
    a dictionary you can return a special "coupler" object that will bind
    the column description data (created only one time) with the column
    values. Of course, it means one more allocation per row and extra
    references, but I don't really think it's very expensive. After all it is
    idiomatic iteration over a sequence. Without hard data to prove
    that it's really expensive I don't think it's right to say it's expensive.
    Sorry about my wording, you're using it as inteded of course, but when
    I see any method with the name .next() used for iteration I immediately
    think about python iterators. Then I realized I was wrong.
    Isn't it premature optimization?

    -- Serge Orlov.
     
    Serge Orlov, Nov 19, 2003
    #10
  11. Bryan J Gudorf

    Wilk Guest

    Why did'nt you choose to make row and field objects ?
     
    Wilk, Nov 19, 2003
    #11
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.