Python: Access a MySQL db without MySQLdb module

Question

Is there another way to connect to a MySQL database with what came included in the version of Python (2.5.1) that is bundled with Mac OS 10.5.x? I unfortunately cannot add the the MySQLdb module to the client machines I am working with...I need to work with the stock version of Python that shipped with Leopard.

Answer 1

Why not install a user (non-system) copy of MySQLdb? These are the files you'd need to install:

/usr/lib/pyshared/python2.6
/usr/lib/pyshared/python2.6/_mysql.so
/usr/share/pyshared
/usr/share/pyshared/MySQLdb
/usr/share/pyshared/MySQLdb/constants
/usr/share/pyshared/MySQLdb/constants/CLIENT.py
/usr/share/pyshared/MySQLdb/constants/REFRESH.py
/usr/share/pyshared/MySQLdb/constants/FLAG.py
/usr/share/pyshared/MySQLdb/constants/FIELD_TYPE.py
/usr/share/pyshared/MySQLdb/constants/__init__.py
/usr/share/pyshared/MySQLdb/constants/ER.py
/usr/share/pyshared/MySQLdb/constants/CR.py
/usr/share/pyshared/MySQLdb/__init__.py
/usr/share/pyshared/MySQLdb/cursors.py
/usr/share/pyshared/MySQLdb/times.py
/usr/share/pyshared/MySQLdb/connections.py
/usr/share/pyshared/MySQLdb/converters.py
/usr/share/pyshared/MySQLdb/release.py
/usr/share/pyshared/_mysql_exceptions.py

Even if you can't install into /usr/lib and /usr/share/pyshared, you could install it anywhere else, as long as it is in a directory listed in the client's PYTHONPATH.

If installing a user copy of MySQLdb is for some reason not an option, then you could do the following, though be warned: it's a horrid way to interact with mysqld for reasons that I list below:

Open up a terminal and type something like

mysql -u USER -pPASSWORD -D DATABASE -Bse "select * from table;"

-B tells mysql to run in "batch" mode
-s tells mysql to run in "silent" mode
-e tells mysql to execute the following statement

If this works, then you could use python's subprocess module to call mysql commands (such as the one above).

For example,

import subprocess
user='xxxxxx'
password='xxxxxxxx'
database='xxxxxxxx'
cmd=['mysql', '-u', user, '-p%s'%password, '-D', database, '-Bse', "select * from table;"]
proc=subprocess.Popen(cmd,stdout=subprocess.PIPE)
retval=proc.communicate()[0]
print(retval)

As mentioned above, you lose a lot by doing this. Namely,

1. retval is just a giant string. You lose all the information about where fields and records begin and end,
2. you lose automatic conversion to Python data types,
3. you lose informative error exceptions.