For any fully functional deployable application, the persistence of data is indispensable. A trivial way of storing data would be to write it to a file in the hard disk, but one would prefer writing the application specific data to a database for obvious reasons. Python provides language support for writing data to a wide range of databases.
Python DB API
At the heart of Python support for database programming is the Python DB API (PEP – 249) which does not depend on any specific database engine. Depending on the database we use at the persistence layer, an appropriate implementation of Python DB API should be imported and used in our program. In this tutorial, we will be demonstrating how to use Python to connect to MySQL database and do transactions with it. For this, we will be using the MySQLdb Python package.
Before we proceed with connecting to the database using Python, we need to install MySQL connector for Python. This can be done in two ways:
- One way is to download the appropriate installer for the OS and bit version directly from the MySQL official site.
- Another way is to use pip to install it.
$ pip install mysql-connector-python
If there is a specific MySQL version installed in the local machine, then you may need a specific MySQL connector version so that no compatibility issues arise, which we can get using the following command:
$ pip install mysql-connector-python==<insert_version_number_here>
Finally, we need to install MySQL client module that will enable us to connect to MySQL databases from our Python application, which acts as the client:
$ pip install mysqlclient
Connecting to the Database
Once we have the connector installed in place, the
import MySQLdb statement should not throw any error on executing the Python file.
Note: It is assumed that the readers have basic understanding of databases in general and the MySQL database in specific, along with knowledge of structured query language (SQL). However, the basic process to create a database and a user has been explained in this section. Follow these steps:
- Ensure that your MySQL server is running. This can be checked via MySQL WorkBench -> Server Status.
- Open MySQL WorkBench or MySQL CLI. Create a new database. Let us call it
CREATE DATABASE pythondb; USE pythondb;
- Create a new user
pythonpwd123and grant access to
CREATE USER 'pythonuser'@'localhost' IDENTIFIED BY 'pythonpwd123' GRANT ALL PRIVILEGES ON pythondb.* To 'pythonuser'@'localhost' FLUSH PRIVILEGES
Checking your Connection to pythondb
Here is a simple script that can be used to programmatically test connection to the newly created database:
#!/usr/bin/python import MySQLdb dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb") cursor = dbconnect.cursor() cursor.execute("SELECT VERSION()") data = cursor.fetchone() if data: print('Version retrieved: ', data) else: print('Version not retrieved.') dbconnect.close()
Version retrieved: 5.7.19
The version number shown above is just a dummy number. It should match the installed MySQL server’s version.
Let us take a closer look at the sample program above to learn how it works. First off,
import MySQLdb is used to import the required python module.
MySQLdb.connect() method takes hostname, username, password, and database schema name to create a database connection. On successfully connecting with the database, it will return a connection object (which is referred to as
Using the connection object, we can execute queries, commit transactions and rollback transactions before closing the connection.
Once we get the connection object, we need to get a
MySQLCursor object in order to execute queries using
execute method. The result set of the transaction can be retrieved using the
fetchmany methods, which will be discussed later in this tutorial.
There are three important methods related to database transactions apart from the execute method. We will learn briefly about these methods now.
dbconnect.commit() method informs the database that the changes executed before calling this function shall be finalized and there is no scope for rolling back to the previous state if the transaction is successful.
Sometimes, if transaction failure occurs, we will need to change the database to the previous state before the failure happened so that the data is not lost or corrupted. In such a case, we will need to rollback the database to the previous state using
dbconnect.close() method is used to close the connection to database. To perform further transactions, we need to create a new connection.
Create a New Table
Once the connection with
pythondb is established successfully, we are ready to go to the next step. Let us create a new table in it:
import MySQLdb dbconnect = MySQLdb.connect("localhost","pythonuser","pythonpwd123","pythondb" ) cursor = dbconnect.cursor() cursor.execute("DROP TABLE IF EXISTS MOVIE") query = "CREATE TABLE MOVIE( \ id int(11) NOT NULL,\ name varchar(20),\ year int(11),\ director varchar(20),\ genre varchar(20),\ PRIMARY KEY (id))" cursor.execute(query) dbconnect.close()
After executing the above script, you should be able to see a new table
movie created for the schema
pythondb. This can be viewed using MySQL WorkBench.
Performing CRUD Operations
Now we’ll perform some insert, read, modify, and delete operations in the newly created database table via the Python script.
Creating a New Record
The following script demonstrates how to insert a new record into MySQL database using a Python script:
#!/usr/bin/python import MySQLdb dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb") cursor = dbconnect.cursor() query = 'insert into movie(id, name, year, director, genre) \ values (1, "Bruce Almighty", 2003, "Tom Shaydac", "Comedy")' try: cursor.execute(query) dbconnect.commit() except: dbconnect.rollback() finally: dbconnect.close()
Reading Rows from a Table
Once a new row is inserted in the database, you can fetch the data in three ways using the cursor object:
cursor.fetchall()– can be used to get all rows
cursor.fetchmany()– can be used to get a selected number of rows
cursor.fetchone()– can be used to get only the first row from the result set
For simplicity, we will use the “select all” SQL query and use a for loop over the result set of the
fetchall method to print individual records.
#!/usr/bin/python import MySQLdb dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb") cursor = dbconnect.cursor() query = "SELECT * FROM movie" try: cursor.execute(query) resultList = cursor.fetchall() for row in resultList: print ("Movie ID =", row) print ("Name =", row) print ("Year =", row) print ("Director = ", row) print ('Genre = ', row) except: print ("Encountered error while retrieving data from database") finally: dbconnect.close()
Movie ID = 1 Name = Bruce Almighty Year = 2003 Director = Tom Shaydac Genre = Comedy
Updating a Row
Let us now update the Genre of “Bruce Almighty” from Comedy to Satire:
import MySQLdb dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb") # The cursor object obtained below allows SQL queries to be executed in the database session. cursor = dbconnect.cursor() updatequery = "update movie set genre = 'Satire' where id = 1" cursor.execute(updatequery) dbconnect.commit() print(cursor.rowcount, "record(s) affected")
1 record(s) affected
Deleting a Record
Here is a Python script that demonstrates how to delete a database row:
import MySQLdb dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb") # The cursor object obtained below allows SQL queries to be executed in the database session. cursor = dbconnect.cursor() updatequery = "DELETE FROM movie WHERE id = 1" cursor.execute(updatequery) dbconnect.commit() print(cursor.rowcount, "record(s) deleted")
After executing the above script, you should be able to see the following output if everything goes well.
1 record(s) deleted
In this article, we learned how to use the Python DB API to connect to a database. Specifically, we saw how a connection can be established to a MySQL database using the MySQLdb implementation of Python DB API. We also learned how to perform transactions with the database.