Python Mysql Connectivity

How do you configure python for use with mysql? You require the MySQLdb module for connecting to mysql server using the python code. This module is used for firing queries to the database server and handling of result sets from python code.

First of all, check if the module is available or not :

jayant@jayantbox:~$ python
Python 2.5.2 (r252:60911, Apr 21 2008, 11:17:30)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb;
Traceback (most recent call last):
File "", line 1, in
ImportError: No module named MySQLdb
>>>

If you are getting an importError, then the module is not installed. Download the source files from http://sourceforge.net/projects/mysql-python

Compile and install the module:

tar -xvzf MySQL-python-1.2.2.tar.gz
.
.
cd MySQL-python-1.2.2/
.
.
python setup.py build
.
.
sudo python setup.py install
.
.
Installed /usr/lib/python2.5/site-packages/MySQL_python-1.2.2-py2.5-linux-x86_64.egg
Processing dependencies for MySQL-python==1.2.2


I faced a problem during the build process. The setup process was unable to find the mysql_config program in the path. So i did
export PATH=/usr/local/mysql/bin:$PATH
And then i ran the build process and it was successful.

Now again try importing the MySQLdb module in the python environment

jayant@jayantbox:/usr/share/python$ python
Python 2.5.2 (r252:60911, Apr 21 2008, 11:17:30)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
Traceback (most recent call last):
File "", line 1, in
File "/usr/lib/python2.5/site-packages/PIL/__init__.py", line 19, in

File "build/bdist.linux-x86_64/egg/_mysql.py", line 7, in
File "build/bdist.linux-x86_64/egg/_mysql.py", line 6, in __bootstrap__
ImportError: libmysqlclient_r.so.16: cannot open shared object file: No such file or directory
>>>


Oops... the library is not able to locate my shared library. So, i added the library path to the LD_LIBRARY_PATH environment variable

export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH

And then try

jayant@jayantbox:~$ python
Python 2.5.2 (r252:60911, Apr 21 2008, 11:17:30)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>


Bingo, now importing the mysqldb module has been successful.
Lets try running some queries using python

jayant@jayantbox:~$ python
Python 2.5.2 (r252:60911, Apr 21 2008, 11:17:30)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> db=MySQLdb.connect(host='localhost',user='root',passwd='jayant',db='mysql',unix_socket='/tmp/mysql.sock')
>>> cursor = db.cursor()
>>> cursor.execute("show tables")
23L
>>> result = cursor.fetchall()
>>> for row in result:
... print row[0]
...
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
>>>


Now you can create apps backed with mysql db operations using python