Connecting python to mysql

Assumption

Python, and mysql are installed and working. So you should be able to connect mysql and start python from shell as below.

%> mysql -hlocalhost -uroot -p

%> python
>>>

On python prompt if following command does not produce an error that means python-mysql api can load without problems;

>>> import MySQLdb
>>>

If it produces error as shown below that most probably means that python-mysqldb (python MySQL api) is not installed properly.

“Traceback (most recent call last):
File ”
“, line 1, in
import MySQLDb
ImportError: No module named MySQLDb”

Solution:

On my Ubuntu system I uninstalled and installed python-mysql api by running

%> sudo apt-get remove python-mysql
%> sudo apt-get install python-mysql

This fixed the problem. If you still get error, try purge.

%> sudo apt-get –purge remove python-mysql

If mysql is not installed on the default path then make sure mysql_config is in the your path.

%> export PATH=$PATH:/path_to_mysql_config

If it still does not solve the issue and you decide to remove mysql completely and reinstall it. I found using Synaptic Package Manager is much easier as it automatically installs all dependencies along.
Assuming (hopefully) by now python and mysql is working fine.

Working with python-mysql:

import MySQLdb
cn = MySQLdb.connect (
host = “localhost”,
user = “dbuser”,
passwd = “password”,
db = “play”
)
cr = cn.cursor()

#””” lets you span multiple rows

cr.execute(“””
create table if not exists state (
name varchar(40),
abbr char(2))
“””)
cr.execute(“””
Insert into state
values
(‘New York’,’NY’),
(‘New Jersey’,’NJ’),
(‘Maryland’, ‘MD’),
(‘Virginia’,’VA’),
(‘Washington’, ‘WA’)
“””)

cr.execute(“Select * from state”)
while 1:
row = cr.fetchone()
if row == None:
break
print “%s –> %s” % (row[0], row[1]

Python & mysql are real fun!

Tags: mysql python connection python-mysql api