Python: Access a MySQL db without MySQLdb module

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.

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.

Or you could check out MySQL Connector/Python. It's still in development, but should work with Python 2.5. No MySQL libraries or other software needed.

If the problem is the inability, as so many people have mentioned, that the msqldb module is a problem a simpler way is 1. install the mysql db 2. install the pyodbc module 3. Load and configure the odbc mysql driver 4. perform sql manipulations with pyodbc, which is very mature and full functional.

hope this helps

You can simplify parsing the data returned by a command line batch call on mysql by using os.popen() rather than subprocess.call(), and modifying the query in the command.

• Dropping the command into os.popen(cmd).readlines() get the output as a list of returned lines, with records separated by a tab character.
• mysql option '-e' rather than '-Bse' returns a record or line with the names of the fields returned.

The output can now be put into a list of dictionaries keyed on the table fields names:

import os    
res = os.popen(cmd).readlines()
dicts = [dict(zip(res[0].split('\t'), record.split('\t'))) for record in res[1:]]

Caveats:

• Defaults in mysql installations for field separators might vary. To ensure (or know) a consistent separator character you might have interact with the mysql installation to set (or discover) its default.
• You might have to redo the command line passed to os.popen as a string, I don't know if os.popen supports submission of list objects as arguments.
• This worked on a linux box. I don't know why a Mac would be any different.
• I am but an egg and cannot comment on the advantages of subprocess.call() over os.popen(). I read in that popen() opens another process, and that its cousin os.system blocks the caller until answered.

This just cleans up a hack. You are probably still better off finding a way to use MySQLdb, or something similar for another database.

UPDATE: The Python library reference offers some more possibilities.
- If you really want to see the errors or warnings sent to standard error by mysql, use popen3.
- The Python documentation mentions popen4, which 'executes cmd as a subprocess'. Might get around blocking issues?