We now have a youtube channel. Subscribe!
Home
Python

A Guide to Python MySQL Database Connection



Hello dear readers! welcome back to another section of our tutorial on Python. In this tutorial, we will be discussing about the MySQL Database Access in Python.

The Python program standards for database interface is the Python DB-API. Most Python database interfaces adhere to this standard.

You can choose the right database for your application. The Python Database API support a wide range of database servers such as -

  • Sybase
  • Interbase
  • Oracle
  • MySQL
  • GadFly
  • mSQL
  • Informix
  • Microsoft SQL Server 2000
  • PostgreSQL

Here is the list of available python database interfaces; use the link: Python database interfaces and APIs. Note - Its compulsory that you download a separate DB API module for each of the database that you need to have access to. For example, if you need to access an Oracle database as well as a MySQL database, you have to download both the Oracle and the MySQL database modules.


The Python DB API provides a basic standards for working with databases using Python structures as well as syntax where possible. This API includes the following -

  • Importing the API module
  • Opening a connection with database
  • Issuing SQL statements and stored procedures
  • Closing the connection

We would discuss all the concepts using MySQL. So let us now discuss about MySQLdb module.


What is MySQLdb?

The MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API version 2.0 and it is built on top of the MySQL C API.


How to Install MySQLdb?

Before you proceed, make sure that you have MySQLdb installed on your computer. Just type the following in your Python script and execute it -

#!/usr/bin/python

import MySQLdb

If it produces the following output, it means that MySQLdb module is not installed -

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

To install MySQLdb module, use the following command line -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Note - Make sure you have root privilege to install above module.

Database Connection

Before you go ahead to connecting to a MySQL database, make sure of the following -

  • Make sure that you have created a database TESTDB.
  • Make sure that you have created a table EMPLOYEE in TESTDB.
  • Make sure that this table has these fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
  • Make sure that the User ID "testuser" and the password "test1234" are set in order to access TESTDB.
  • Make sure Python MySQLdb module is well installed in your computer.
  • Make sure that you have properly gone through the MySQL tutorial in order to understand the basics.

Example

The following is the example of connecting with MySQL database "TESTDB"

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.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

# disconnect from server
db.close()

Output

When the above script is executed, it produces the following result on my windows system -

Database version : 5.0.45


If a connection is established with the datasource, then a Connection Object is returned and saved into db for further use, otherwise db is set to None. Next, the db object is used to create a cursor object, which in turn is used to execute SQL queries. Lastly, before coming out, it ensures that the database connection is closed and the recourses are released.

Creating Database Table

As soon as a database connection is finally established, then we are ready to create tables and records into the database tables by using the execute method of the created cursor.

Example

Let us create a Database table called EMPLOYEE -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

INSERT Operation

This operation is required when you want to create your records into a database table.

Example

Following example, executes the SQL INSERT statement to create a record into EMPLOYEE table -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Win', 'Paul', 27, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()


Above example can be written as follows to create the SQL queries dynamically -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Win', 'Paul', 27, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Example

The following code segment is another form of execution where parameters can be passed directly -

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

READ Operation

The READ operation on a database means to fetch some useful information from the database.

So once our database connection is established, you are ready to make a query into the database. You can use either fetchone() method in fetching a single record or fetchall() method for fetching of multiple values from a database table.

  • fetchone() - It fetches the next row of a query result set. 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, it then retrieves all the remaining rows from the result set.
  • rowcount - It is a read- only attribute and returns the number of rows that were affected by the execute() method.

Example

Following procedure below queries all the records from EMPLOYEE table having a salary more than 1000 -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# disconnect from server
db.close()

Output

When the above code is executed, it will produce the following result -

fname=Win, lname=Paul, age=27, sex=M, income=2000


UPDATE Operation

The UPDATE Operation on any database means updating one or more records which are already available.

Example

The following procedure updates all the records having SEX as 'M'. Here, we increase AGE of all the males by one year -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

DELETE Operation

The DELETE Operation is required when you want to delete some records from your database.

Example

Below is the procedure to delete all the records from EMPLOYEE where AGE is more than 20 -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

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

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Performing Transactions
Transactions are a mechanism which ensures data consistency. Transactions have the following four properties -

  • Atomicity - It is either a transaction completes or nothing happens at all.
  • Consistency - A transaction should start in a consistent state and leaves the system in a consistent state.
  • Isolation - The intermediate results of a transaction are not visible outside of the current transaction.
  • Durability - As soon as a transaction was committed, the effects will be constant even after a system failure.

The Python DB API v2.0 provides two methods to either commit or rollback a transaction.

Example
I believe you already know how to implement transactions. Below is a similar example -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()


COMMIT Operation
Commit is the operation, which gives a green signal to database to finalize the changes, and after this operation is over, no change can be reverted back.

Example
Below is a simple example to call a commit() method -

db.commit()

ROLLBACK Operation
If you are not satisfied with one or more of the changes and you want to revert those changes, then use the rollback() method.

Example
Below is a simple example to call a rollback() method -

db.rollback()

Disconnecting Database
In order to disconnect Database connection, then make use of the close() method.

db.close()

lf the connection to the database is closed by the user using the close() method, any outstanding transactions are rolled back by DB. However, instead of depending on any DB lower level implementation details, your application would be better off calling the commit and rollback explicitly.


Handling Errors
There are many sources of errors. A few examples are a syntax error which occurs in an executable statement, a connection failure, or calling on the fetch() method for an already finished or cancelled statement to handle.

The DB API specifies a number of errors that must exist in each DB module. The following table list these errors -

Sr.No.Exception & Description
1
Warning
Used for non-fatal issues. Must subclass StandardError.
2
Error
Base class for errors. Must subclass StandardError.
3
InterfaceError
Used for errors in the database module, not the database itself. Must subclass Error.
4
DatabaseError
Used for errors in the database. Must subclass Error.
5
DataError
Subclass of DatabaseError that refers to errors in the data.
6
OperationalError
Subclass of DatabaseError that refers to errors such as the loss of a connection to the database. These errors are generally outside of the control of the Python scripter.
7
IntegrityError
Subclass of DatabaseError for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys.
8
InternalError
Subclass of DatabaseError that refers to errors internal to the database module, such as a cursor no longer being active.
9
ProgrammingError
Subclass of DatabaseError that refers to errors such as a bad table name and other things that can safely be blamed on you.
10
NotSupportedError
Subclass of DatabaseError that refers to trying to call unsupported functionality.

Your Python script should handle all of these errors, but before using any of the above exceptions, make sure that your MySQLdb has full support for that exception.


Alright guys! This is where we are rounding up for this tutorial post. In our next tutorial, we are going to be discussing about the Python Network Programming.

Feel free to ask your questions where necessary and i will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.

Post a Comment

Hello dear readers! Please kindly try your best to make sure your comments comply with our comment policy guidelines. You can visit our comment policy page to view these guidelines which are clearly stated. Thank you.
© 2023 ‧ WebDesignTutorialz. All rights reserved. Developed by Jago Desain