Accessing ODBC Database

[Paul Boddie]

tub4jxr at hotmail.com (Jose Rodriguez) wrote in message news:<89302408.0108061933.5d4a4638 at posting.google.com>...
> Can someone assist me with providing a clear example concerning how to
> access a database from the standpoint of:
> 
> a - establishing the connection to the datasource (DSN)

# Here follows some completely untested code for you to
# experience at your own risk! Good luck!

# Using mxODBC with the iODBC software, for example.
import mx.ODBC.iODBC
connection = mx.ODBC.iODBC.connect(dsn, user, password)

> b - how to do the SQL string to query a table

# Check the paramstyle first.
print mx.ODBC.iODBC.paramstyle
# Let's assume that it is "?"...

# Get a cursor.
cursor = connection.cursor()

# Execute a query with parameters according to the assumed
# paramstyle of "?". Here, we'll join two tables implicitly.
cursor.execute("SELECT ANIMAL.NAME, ANIMAL.CAGE, STAFF.NAME" \
    " FROM ANIMAL, STAFF WHERE ANIMAL.CAGE = STAFF.CAGE" \
    " AND ANIMAL.NAME = ?", (the_name,))

# Get the results.
results = cursor.fetchall()
# You should get a list of 3-tuples.

> c - writing a record (insert) to the datasource

# Execute an action with parameters.
cursor.execute("INSERT INTO ANIMAL (NAME, CAGE) VALUES (?, ?)",
    (new_name, new_cage))

> d - deleting a record from the datasource (and moving record pointer
> accordingly)

# Execute another action with parameters.
cursor.execute("DELETE FROM STAFF WHERE NAME = ?", (keeper_name,))

> e - closing the connection

# Close the cursor.
cursor.close()

# Commit or rollback?
connection.commit() # or connection.rollback()

# Close the connection.
connection.close()

> I am currently just overly confusing myself as to how this is done
> (should be done) and what modules to import.  I would like to use
> Python to quickly come up with code periodically to do simple record
> manipulations on a DSN.  Currently I use Winbatch to do my system
> automation but it looks like Python can do everything that WinBatch
> can and then some.

Well, I seem not to be giving the correct mxODBC package "path" in the
example imports on one of my Web pages, so I can understand your
problems in that department. However, it should be a case of following
the installation instructions for that package, at least, and making
sure that the appropriate directory sits on your PYTHONPATH or in your
site-packages directory.

Usage of the DB-API is very similar to numerous other database
libraries: open a connection, open a cursor on that connection,
execute things on that cursor, close the cursor, commit or rollback
the changes on the connection, and then close the connection. A
recommendation from me to prevent possible nightmares: always use
parameters in your queries and actions (that's what the paramstyle
business is about) - it will avoid you ever needing to "quote" any of
your data.

Paul