Python Mysql Database Access

There is a python module called MySQLdb through which we can access many databases like Mysql,Oracle,PostgreSQl,Sybase,GadFly etc.
Here i am going with Mysql on Linux operating system.
Other than MySQLdb module there is one more module called mysql-connector which also provide database access from python script.

Diffrence between MySQLdb and mysql-connector.
MySQLdb : MySQLdb is a C module that links against the MySQL protocol implementation in the libmysqlclient library. It is faster, but requires the library in order to work.

mysql-connector : mysql-connector is a Python module that reimplements the MySQL protocol in Python. It is slower, but does not require the C library and so is more portable.

MySQLdb:
MySQLdb is an interface or python module for connecting to a MySQL database server from Python.

Verify if MySQLdb  have installed in your machine. Try to run the following script.

    #!/usr/bin/python

    import MySQLdb

if it is throwing following error that mean MySQLdb module need to install in your machine.

    Traceback (most recent call last):
      File "test.py", line 3, in <module>
        import MySQLdb
    ImportError: No module named MySQLdb

To install MySQLdb module in your Unix/Linux OS, download zip file from MySQLdb Download page(http://pkgs.fedoraproject.org/repo/pkgs/MySQL-python/MySQL-python-1.2.2.tar.gz/532268f02870bea18c1d465e88afff30/MySQL-python-1.2.2.tar.gz) and run the following command.

You can also try wget command to download zip frile in your unix machine.

    $ wget http://pkgs.fedoraproject.org/repo/pkgs/MySQL-python/MySQL-python-1.2.2.tar.gz/532268f02870bea18c1d465e88afff30/MySQL-python-1.2.2.tar.gz
    $ gunzip MySQL-python-1.2.2.tar.gz
    $ tar -xvf MySQL-python-1.2.2.tar
    $ cd MySQL-python-1.2.2
    $ python setup.py build
    $ python setup.py install

Before starting create a test databases called mydb and table as customer with following table structure and values.

    mysql> select * from mydb.customer;

    +------------+--------------+----------------------+-----------+
    | customerid | customername | emailid              | accountno |
    +------------+--------------+----------------------+-----------+
    | 1001       | Neeraj singh | neeraj@gmail.com     |     30004 |
    | 1002       | Tanu         | tanu@gmail.com       |     30005 |
    | 1003       | Shivam Kumar | shivam@yahoo.com     |     30006 |
    | 1004       | vinay        | vinay.s@gmail.com    |     30007 |
    | 1005       | Ajay singh   | ajay.singh@gmail.com |     30008 |
    +------------+--------------+----------------------+-----------+

Example 01 : Read/Select operation

In read operation we need to use the following useful method to fetch or read the information from database.
fetchone() : It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.
fetchall(): It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
rowcount: This is a read-only attribute and returns the number of rows that were affected by an execute() method.

#!/usr/bin/python

import MySQLdb

# Open database connection
conn = MySQLdb.connect("localhost","mukesh","mks123","mydb" )


# prepare a cursor object using cursor() method
cursor = conn.cursor()


# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM customer \
       WHERE customerid > '%d'" % (1003)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   print "Total count :"+str(cursor.rowcount)
   results = cursor.fetchall()
   for row in results:
      customerid = row[0]
      customername = row[1]
      emailid = row[2]
      accountno = row[3]
      # Now print fetched result
      print "customerid=%s,customername=%s,emailid=%s,accountno=%d" % \
             (customerid, customername, emailid, accountno )
except :
   print "Error: unable to fecth data"


# Close the cursor and disconnect from server
finally:
        cursor.close()
        conn.close()

Read operation with fetchone() method

#!/usr/bin/python

import MySQLdb


# Open database connection
conn = MySQLdb.connect("localhost","mukesh","mks123","mydb" )


# prepare a cursor object using cursor() method
cursor = conn.cursor()


# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")


# Fetch a single row using fetchone() method.
data = cursor.fetchone()


print "Database version : %s " % data


# Close the cursor and disconnect from server
finally:
        cursor.close()
        conn.close()

        

Ouptput:
Database version : 5.5.49-0ubuntu0.12.04.1

Example 02 : Insert Operation

#!/usr/bin/python


import MySQLdb


# Open database connection
conn = MySQLdb.connect("localhost","mukesh","mks123","mydb" )


# prepare a cursor object using cursor() method
cursor = conn.cursor()


# Prepare SQL query to INSERT a record into the database.
#sql = """INSERT INTO customer(customerid,
#         customername, emailid, accountno)
 #        VALUES ('1006', 'Mohan', 'mohan@reddif.com', '30009')"""


# Prepare dynamic SQL query to INSERT a record into the database.
sql = "INSERT INTO customer(customerid,\
         customername, emailid, accountno) \
                 VALUES('%s','%s','%s','%d')" \
         %('1006', 'Mohan', 'mohan@reddif.com', 30009)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   conn.commit()
except:
   # Rollback in case there is any error
   conn.rollback()


# Close cursor and disconnect from server
conn.close()
cursor.close()

Example 03 : Update Operation
Following procedure will update all records which having customername 'Mohan'.

#!/usr/bin/python


import MySQLdb


# Open database connection
conn = MySQLdb.connect("localhost","mukesh","mks123","mydb" )


# prepare a cursor object using cursor() method
cursor = conn.cursor()


# Prepare SQL query to UPDATE required records
sql = "UPDATE customer SET emailid = 'mohan.kumar@reddif.com'\

                          WHERE customername = '%s'" % ('Mohan')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   conn.commit()
except:
   # Rollback in case there is any error
   conn.rollback()


# disconnect from server
conn.close()

Example 04 : Delete Operation

#!/usr/bin/python


import MySQLdb


# Open database connection
conn = MySQLdb.connect("localhost","mukesh","mks123","mydb" )


# prepare a cursor object using cursor() method
cursor = conn.cursor()


# Prepare SQL query to DELETE required records
sql = "DELETE FROM customer WHERE customerid = '%s'" % ('1006')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   conn.commit()
except:
   # Rollback in case there is any error
   conn.rollback()


# disconnect from server
conn.close()

After performing this delete operation through python script. Connect mysql db and verify the operation successfully proceeded or not.



************************END*******************************