Education · Open Source Software · Technology

Python + MySQL in Ubuntu (9.10)


Assuming you did a fresh install of Karmic Koala – Ubuntu 9.10

Here are the steps:

– Install Python:

apt-get install python2.6

– Install XAMPP/LAMP stack:

sudo tasksel install lamp-server

After you install follow the steps on setting up the root password see the notes here .

– Install MySQLdb:

I tried downloading the sourceforge gzip and untarring the file etc, not the right route … instead use apt-get:

sudo apt-get install python-mysqldb

Test this through Python

username@machinename:~$ python
Python 2.6.4 (r264:75706, Dec  7 2009, 18:45:15)
[GCC 4.4.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> exit()

If there any errors the one below then check your MySQLdb install.

>>> import MySQLdb
   Traceback (most recent call last):
     File "<stdin>", line 1, in ?
   ImportError: No module named MySQLdb

– Create a simple table:

If you have installed mysql then you should be able to do the following steps and create a dummy table and insert data into it:
username@machine-name:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.1.37-1ubuntu5.1 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.12 sec)

mysql> use test;
Database changed
mysql> create table employees( id INT, name VARCHAR(20), email varchar(20));
Query OK, 0 rows affected (0.18 sec)

mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| employees |
+—————-+
1 row in set (0.00 sec)

mysql> insert into employees(id,name,email) values (1,”emp1″,”emp1@mycomp.com”);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.employees;
+——+———+—————–+
| id   | name    | email           |
+——+———+—————–+
|    1 | emp1 | emp1@mycomp.com |
+——+———+—————–+
1 row in set (0.01 sec)

mysql> exit

– Query the table by connecting from Python and MySQLdb:

username@machine-name:~$ python
Python 2.6.4 (r264:75706, Dec  7 2009, 18:45:15)
[GCC 4.4.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> conn = MySQLdb.connect (host = "localhost", user = "root" , passwd ="abc123" , db = "test")
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT name from employees where id=1");
1L
>>> row = cursor.fetchone()
>>> print "employee name ", row[0]
employee name emp1
>>> cursor.close()
>>> conn.close()
>>> exit()

Errors:

If you specify the “row = cursor.fetchone()” before the “execute” line you will get:
_mysql_exceptions.ProgrammingError: execute() first

If you specify the table name wrong, say “employ” instead of “employees” you will get:
_mysql_exceptions.ProgrammingError: (1146, "Table 'test.employ' doesn't exist")

If you select for one item, say “Select name from”, then the row will have 1 item in it … if you access an index that is larger than the number of elements you specified (minus 1) – for example you do “print “employee name “, row[1]” in the above example, you will get:
IndexError: tuple index out of range

Conclusion:

I had tried to do Python2.6 and MySQL on Windows and wasted a fair bit of time on the MySQLdb install part.  Then I built a new home-development environment over Ubunut 9.10 and boy was this a cake-walk!

You should be on your merry way coding up cool python scripts that craw the web and gather information about pages and store them to a DB. Check out “Programming Collective Intelligence

References:

http://ubuntuguide.org/wiki/Ubuntu:Karmic – For Ubuntu Karmic notes
http://www.kitebird.com/articles/pydbapi.html – For Python db API
http://www.cyberciti.biz/faq/howto-linux-unix-creating-database-and-table/ – For MySQL how to

Advertisements

2 thoughts on “Python + MySQL in Ubuntu (9.10)

  1. There’s no official support for Python-MySQL bridge for Windows as far I know. It’s a bit tricky though to get it working on Windows. I’ve forgotten how I got mine to work a few years back. But not sure if there’s a standard MySQL support lately as I work on Linux myself as well.

    Nice article BTW 🙂

    1. Thank you Cillia (or Dave?). I wish there was because there are a lot of kids in my development team I want to get enthused about python and they all own laptops with some flavour of Windows on them. Maybe SQLLite is an option?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s