[DB-SIG] Examples on how to use mxODBC?

Andy Todd andy47 at halfcooked.com
Wed Oct 22 04:53:21 EDT 2003

jgoldtest wrote:

> Hi all,
> I've got to appologize for posting what must be a stupid question but so far, I've been unable to figure it out for myself.  I'm trying to learn web development using python.  I've tried out Zope, Twisted, and finally Cherrypy. I work at a Microsoft  shop but I'm trying go get them away from .Net and into open source.  I figure if I can learn it, I can make it easy for everyone else by teaching it.  I may have taken on too much.  The learning curve is steep! 
> I've taken a break from the Web stuff for a bit to try and figure out how to access my database.  Ideally, I'd just like a few lines to show what you need to import, what objects and methods need to be executed to connect to a database and execute a select statement.  I guess I'd also be nice if it could read through the returned records.  I'm sure this is a really simple thing to do but I can't find any working examples.  I can find examples for accessing MySQL but I don't know how to convert them to  mx ODBC.  I've tried a number of things but nothing seems to work.
> I've found old examples that use the ODBC 1.0 spec and start with lines like "from calldll import odbc" but I can't find this "calldll" package.  It's all very confusing.  I downloaded and installed the mx ODBC.  I guess the documentation is good if you already know what you're doing but it's been pretty useless to me.  There are NO example programs that I can find that demonstrate step-by-step how you use it.
> Anyway,  a few pointers would be helpful.  I'll try not to be a pest.
> Joe Goldthwaite
> Phoenix, AZ
> Property of Bar-S Foods. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL.  If you are not the intended recipient, dissemination of this communication is prohibited.  If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately at 602.264.7272 or postmaster at bar-s.com.
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig


First, a couple of assumptions. As you are a 'Microsoft shop' I'm 
assuming that you know all about DSNs and have one configured for the 
database you want to access. I'm also assuming that you know a little 
SQL and that you have successfully installed Python and the mx 
utilities. Oh, and that you are running on some flavour of Windows (I'm 
assuming 2000 or XP but this should work with other versions).

Given that, fire up your Python interpreter and try;

 >>> db=ODBC.Windows.connect('<your dsn name here>')

This will create a connection object (referenced by 'db') to your 
database. Once you've got a connection, most of the work is done through 
a cursor;

 >>> myCursor=db.cursor()

Which creates a cursor object which you can reference through 'myCursor'.

Then, you will probably want to execute a select statement. This is 
achieved in two parts, executing the statement;

 >>> myCursor.execute('<your SQL Select statement here>')

And getting the results. There are a number 'fetch' methods available to 
you, here is just one (read the DB-API specification 

 >>> firstRow=myCursor.fetchone()

This will return the first row from your select statement into a tuple 
which can be referenced by 'firstRow'. To see what's in it just try;

 >>> firstRow
('<first element>', '<second element>', ... )

You can then merrily keep calling 'fetchone' until the cursor runs out 
of rows to return.

The only other things to note about mxODBC is that it uses the standard 
ODBC convention of using '?' in a SQL statement to mark a parameter. 
This is the qmark paramstyle from the DB-API 2.0 specification. This 
allows you to specify a value in your query at run time rather than when 
you write the code. An example of this (using my database) is;

 >>> stmt="SELECT currency_symbol, country_code FROM currencies WHERE 
 >>> myCursor.execute(stmt, ('UKP',))

That should get you started. The first rule of Python applies - the 
interpreter is interactive, and if you are using the rather marvelous 
Pythonwin then tooltips are an incredibly useful tool for investigating 
the program space, particularly which attributes and methods are 
available on the objects you have.

I'd also recommend taking a look at the mxODBC documentation, which is 
well written, clear, concise and complete. Its available here;


 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

More information about the DB-SIG mailing list