Native ODBC access for python on linux?

[callmebill]

I'm getting my feet wet with making Python talk to MySQL via ODBC. I
started on Windows, and it went smoothly enough due to the ODBC stuff
that apparently is native to Python at least on windows (I've been
following ch. 13 of Mark Hammond's py on win32 book).

But now I'm trying to do equivalent stuff on linux (Fedora Core 3) with
python 2.3.5 and mysql. I'd like to stick with packages that are
native to python, rather than relying on external stuff (e.g., MySQLdb
and mxODBC). Is this possible, or do I have to use 3rd party pieces to
use ODBC with Python under linux?


As an aside, I've only used ODBC to access db's, period. I've never
used, for example, MySQL's API for working with a MySQL db. I'm
curious to see what that looks like, if anyone has any examples
(python, c, or otherwise).

Thanks in advance for any help.
-Bill

 

[Larry Bates]

ODBC is a vanilla interface that puts a layer between the program
and the database. In theory, this would allow you to write a
program that supports ODBC compliant databases and it would work
with any of them. In practice it always seems like this doesn't
work as well as everyone had hoped (performance is quite often a
problem with ODBC interface and time/date handling can be an
issue).

There are people that provide ODBC drivers for Linux but normally
they are used to communcate back to MSSQL servers or other Windows
applications from Linux box.

The native interfaces are almost always better performing and
support more features because ODBC is basically a lowest common
denominator approach. The native API can provide access to all
the (even unique) features of the particular database

IMHO-For Python to MySQL on Linux use the native interface is
the way to go.

Larry Bates

 

[Grig Gheorghiu]

I concur with Larry. I find that by properly abstracting the database
connection code in my own class, I can then use any DB-API-compliant
Python module to connect to a variety of databases. I use for example
cxOracle to connect to Oracle and kinterbasdb to connect to firebird. I
haven't tried connecting to MySQL natively yet, but I don't think it
would be any different.

Grig

 

[Thomas Bartkus]

I'm getting my feet wet with making Python talk to MySQL via ODBC. I
started on Windows, and it went smoothly enough due to the ODBC stuff
that apparently is native to Python at least on windows (I've been
following ch. 13 of Mark Hammond's py on win32 book).

But now I'm trying to do equivalent stuff on linux (Fedora Core 3) with
python 2.3.5 and mysql. I'd like to stick with packages that are
native to python, rather than relying on external stuff (e.g., MySQLdb
and mxODBC). Is this possible, or do I have to use 3rd party pieces to
use ODBC with Python under linux?


As an aside, I've only used ODBC to access db's, period. I've never
used, for example, MySQL's API for working with a MySQL db. I'm
curious to see what that looks like, if anyone has any examples
(python, c, or otherwise).

Thanks in advance for any help.
-Bill

We have a Linux/Apache/MySQL server. We use the ODBC driver to write
Windows clients using VB. I only understand ODBC as a means to bring the
Linux/Apache/MySQL to speaking terms with the Microsoft world. ODBC serves
as the translator between these 2 alien systems.

Although I hear rumors about ODBC drivers on Linux, I confess I don't
understand the need. Certainly you can use Python with the MySQLdb module
from any Linux machine and query away at the server. As long as the MySQL
server accepts your IP/usr/pwd, it will respond to the SQL you pass it from
Python.

IOW - who/what needs an ODBC driver here?
Thomas Bartkus

 

[callmebill]

Can you tell me what I have to use in order to utilize the MySQL native
API? There's some gap (chasm, really) in my knowledge that is keeping
me from following that route. If you could provide a small example or
a couple names, that would be extremely helpful.

Thanks again for your time.

 

[Grig Gheorghiu]

1. Download and install MySQL-python from
<http://sourceforge.net/project/showfiles.php?group_id=22307&package_id=15775>

2. Try to connect to a MySQL database that you have running. In this
example I'm connecting to the bugs database installed with Bugzilla,
and I'm connecting as MySQL user root (not recommended for 'production'
code):

# python
Python 2.4 (#1, Nov 30 2004, 16:42:53)
[GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.((1L, 'tweakparams', 'Can tweak operating parameters', 0, '', 1), (2L,
'editusers', 'Can edit or disable users', 0, '', 1), (4L,
'creategroups', 'Can create and destroy groups.', 0, '', 1), (8L,
'editcomponents', 'Can create, destroy, and edit components.', 0, '',
1), (16L, 'editkeywords', 'Can create, destroy, and edit keywords.', 0,
'', 1), (32L, 'editbugs', 'Can edit all aspects of any bug.', 0, '.*',
1), (64L, 'canconfirm', 'Can confirm a bug.', 0, '.*', 1))((1L, 'tweakparams', 'Can tweak operating parameters', 0, '', 1),
(2L, 'editusers', 'Can edit or disable users', 0, '', 1),
(4L, 'creategroups', 'Can create and destroy groups.', 0, '', 1),
(8L, 'editcomponents', 'Can create, destroy, and edit components.', 0,
'', 1),
(16L, 'editkeywords', 'Can create, destroy, and edit keywords.', 0,
'', 1),
(32L, 'editbugs', 'Can edit all aspects of any bug.', 0, '.*', 1),
(64L, 'canconfirm', 'Can confirm a bug.', 0, '.*', 1))
For more info on how to use the MySQLdb module, see the docs at
<http://sourceforge.net/docman/?group_id=22307>

Hope this helps,

Grig

 

[Mike Meyer]

Although I hear rumors about ODBC drivers on Linux, I confess I don't
understand the need. Certainly you can use Python with the MySQLdb module
from any Linux machine and query away at the server. As long as the MySQL
server accepts your IP/usr/pwd, it will respond to the SQL you pass it from
Python.

IOW - who/what needs an ODBC driver here?

ODBC provides one thing that db native api's don't - binary
interoperability. If you have a binary application that talks odbc,
you can talk to pretty much any db that someone has writte an odbc
driver for. To talk to the db native api, the application has to be
built against that api.

For instance, I use the unixODBC driver to connect applix office's
spreadsheet tool to my postgresql databases.

If everything you run is open source - especially if it's written in
Python - this is pretty much irrelevant.

<mike

 

[callmebill]

All,

This info was very helpful, and I'm up and running with MySQLdb on
linux, and the native ODBC support on Windows.

One last question I have: In vbs (specifically with .asp) I can make a
connection to an ODBC provide _without_ the need to specify a system
DSN in the Control Panel. It's easy to do with MySQLdb.connect(
host,user,pass,etc), but is this possible with the ODBC module in
Python on win32?

Thanks again for all the help. This has really narrowed the gap in my
knowledge significantly.

 

[Thomas Bartkus]

All,

This info was very helpful, and I'm up and running with MySQLdb on
linux, and the native ODBC support on Windows.

One last question I have: In vbs (specifically with .asp) I can make a
connection to an ODBC provide _without_ the need to specify a system
DSN in the Control Panel. It's easy to do with MySQLdb.connect(
host,user,pass,etc), but is this possible with the ODBC module in
Python on win32?

Ahhh - But that's the point!

If you are "up and running with MySQLdb on linux" then you should be up and
running with with the same code on Windows. The exact same Python script
you run on Linux will work for you on windows! I *think* you might be able
to attach the ODBC driver but it would be a complication and an unnecessary
one at that. Just point MySQLdb.connect() at the servers IP address.

Go to SourceForge and download the MySQLdb installation for win32
[MySQL-python.exe-1.2.0.win32-py2.4.exe].

Sometimes you can't believe it because it's too easy
Thomas Bartkus

 

[callmebill]

I know... I'm expecting pain, and when that pain doesn't arrive I
assume that I did something wrong.

Actually, I was using the odbc/dbi stuff that ships with the win32
distribution for my windows work, and using MySQLdb for my linux work.
Most of the code is the same. But I suppose the big benefit of using
MySQLdb on both (which certainly works) is that there is no abiguity as
to what the user is running for an OS. You don't have a special case
"If you're on Windows, do this; if you're on linux, do that"...
Rather, by having everyone indiscriminately require MySQLdb, then that
forces a unity. That's probably the way to go, huh?

 

[Grig Gheorghiu]

That's exactly the way to go. In my case, I'm using cx_Oracle to
connect from Python to Oracle and the same exact code runs on Windows,
Linux, Solaris and soon on AIX.

Grig