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
Share this:
Tweet
Like this:
Like Loading...