Tuesday, April 29, 2008

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

5 comments:

Priyaranjan said...

Hi

Your blog helped me a lot. But, whenever I come back, the import error problem recurs. I don't have root privilages. I have instlled my python in my local account and I can change anything in it locally. I have a mysql setup locally too. Can you help me resolve the issue?
Thanks.

Priyaranjan

Jayant Kumar said...

So, basically you are having issues with setting the environment variable LD_LIBRARY_PATH. If you have root access then you need to export this variable in /etc/profile file. But if you do not have the root access you can simply import the variable in your local .bash_profile file.

Add the following line at the end of either file "/etc/profile" or "/home/homedir/.bash_profile"

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

Marium said...

hi

v informative blog!but if u kindly specify the alternative for the window's users then it wud be a great asset!

akshat said...

Thanx a lot dear i was struggling for configuring MySQL with Python/Zope last three days but at last ur blog helped me a lot....

gr888 work keep it up....

Anonymous said...

i haveinstalled python .i have a mysqql installed in my local system . i wabt to configure mysql and python . i am geting a registry error . can u help me