Using MyODBC with Python

[Steve Holden]

[posted & mailed]

"Sabine Richter" <sabine at textraeume.de> wrote ...
> Hello,
>
> I downloaded and installed myODBC 3.51. As far as I understand, it is
> only the driver, which provides the connection to the database. In my
> example, it is MySQL 3.23.49.
> The manual says that in my application I have to allocate the
> environment handle, set the version, allocate the connection handle,
> conect to the server etc.. But don't I have to have a library / module
> which provides functions which I can use to do all that? But where is
> it? Or do I have to write all that by myself? I don't know where to
> begin.
> Please, may anybody help me understand the architecture and how to use
> myODBC with Python? Perhaps anybody has a code snippet for me?
> I have to be productive with that till tomorrow morning. So it is urgent
> for me.
>

Well, since I see

    X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)

in your mail headers I will assume you are using Windows 2000. I also have
Win2K, and once I installed the MyODBC (in my case 2.50.90.00, but I doubt
it will make a difference) I could use the Start Menu |Settings |Control
Panel to open Administrative Tools. There I see the Data Sources (ODBC)
applet, which I use to create new ODBC sources. You should use system DSNs
if you want them to be available to all users.

In creating a new source you must specify the MySQL database name and a host
of options -- I usually set "read options from C:\My.cnf" and "Disable
transactions". The first is useful because it allows your client programs to
establish their configuration parameters (username, password, etc.) from the
global data file. This may not be appropriate if you want authenticated user
access to the database. The second one I set because the default table type
(and therefore MySQL databases by default) don't support transactions.

The thing you seem to have skipped is that MyODBC is simply an ODBC driver
for MySQl. Your program needs an ODBC database module so it can make use of
the data sources you set up with MyODBC (and other ODBC drivers, come to
that). You could use the one that comes with the Win32 extensions, but I
would recommend Marc-Andre Lemburg's mxODBC, which you will find at

    http://www.egenix.com/files/python/

Note that a license is required for commercial use. This allows you to say
stuff like:

    import mx.ODBC as odbc
    conn = odbc.connect("SystemDSNName")
    curs = conn.cursor()
    curs.execute("SELECT * FROM tablename")
    data = curs.fetchall()

and you should see a list of tuples in "data", each corresponding to a row
retrieved from the table. The rest's up to you! Sorry about the similarity
in names between MyODBC and mxODBC: each is a separate component playing a
different role.

regards
 Steve
--
Steve Holden: http://www.holdenweb.com/ ; Python Web Programming:
http://pydish.holdenweb.com/pwp/