Intro to Python for MySQL and Simple Examples

Before we start

We need to install several packages to execute the examples in this tutorial.

If you do not already have MySQL installed, we must install it.

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account.

$ apt-cache search MySQLdb

python-mysqldb - A Python interface to MySQL

`python-mysqldb-dbg - A Python interface to MySQL (debug extension)

`bibus - bibliographic database

eikazo - graphical frontend for SANE designed for mass-scanning

We don not know the package name for the MySQLdb module. We use the apt-cache command to figure it out.

$ sudo apt-get install python-mysqldb

Here we install the Python interface to the MySQL database. Both _mysql and MySQL modules.

Next, we are going to create a new database user and a new database. We use the mysql client.

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 30

Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;



In [1]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import _mysql
import sys

try:
        con = _mysql.connect('localhost','testuser','test623','testdb')

        con.query("SELECT VERSION()")
        result = con.use_result()

        print "MySQL version: %s" % \
                result.fetch_row()[0]

except _mysql.Error,e:

        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

finally:
        if con:
                con.close()









    



MySQL version: 5.5.35-1ubuntu1

Using MySQLdb Module



In [2]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb
import sys

try:
        con = mdb.connect('localhost','testuser', 'test623','testdb')

        cur = con.cursor()
        cur.execute("SELECT VERSION()")

        ver = cur.fetchone()

        print "DataBase version: %s" % ver

except mbd.Error,e:

        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit(1)

finally:
        if con:
                con.close()









    



DataBase version: 5.5.35-1ubuntu1

Creating Table using MySQLdb Module



In [3]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb

con = mdb.connect('localhost','testuser','test623','testdb')

with con:

        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

Retrieving Created Table using MySQLdb Module



In [4]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb

con = mdb.connect('localhost','testuser','test623','testdb');

with con:

        cur = con.cursor()
        cur.execute("SELECT * FROM Writers")

        rows = cur.fetchall()

        for row in rows:
                print row









    



(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')

Retrieving Table as Dictionary using DictCursor Method



In [1]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb

con = mdb.connect('localhost','testuser','test623','testdb');

with con:

        cur = con.cursor(mdb.cursors.DictCursor)
        cur.execute("SELECT * FROM Writers LIMIT 4")

        rows = cur.fetchall()

        for row in rows:
                print row["Id"], row["Name"]









    



1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola

Transaction - commit() | rollback()

* commit()



In [2]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb
import sys

try:
        con = mdb.connect('localhost','testuser','test623','testdb')

        cur = con.cursor()
        cur.execute("DROP TABLE IF EXISTS Writers")
        cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
        cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
        cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

        con.commit()

except mdb.Error,e:
        if con:
                con.rollback()

        print "Error %d: %s" % (e.args[0],e.args[1])
        sys.exit()

finally:
        if con:
                con.close()

* rollback()



In [4]:

    
#!/usr/bin/python
# _*_ coding: utf-8 _*_

import MySQLdb as mdb
import sys

con = mdb.connect('localhost','testuser','test623','testdb')

cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS Writers")
cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchette')")

con.rollback()

sys.exit()

if con:
        con.close()









    



An exception has occurred, use %tb to see the full traceback.

SystemExit






    



To exit: use 'exit', 'quit', or Ctrl-D.



In [5]:

    
%tb









    



---------------------------------------------------------------------------
SystemExit                                Traceback (most recent call last)
<ipython-input-4-933feb4e3365> in <module>()
     19 con.rollback()
     20 
---> 21 sys.exit()
     22 
     23 if con:

SystemExit:

I had no issue of this kind when I ran it on terminal. Not sure on this!



In [ ]: