Accessing ODBC Database
Paul Boddie
paul at boddie.net
Tue Aug 7 05:55:50 EDT 2001
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
More information about the Python-list
mailing list