Python MySQL Tutorial - A Complete Guide

Today in this tutorial we are going to discuss how we can develop and integrate a Python MySQL database.

What is MySQL

MySQL is an open-source relational database management system which can be used to store data in the form of tables. Moreover, a table is a collection of related data, consisting of columns and rows.

MySQL is a widely used free database software that runs on a server and provides a range of operations that can be performed over it. Certainly, we can integrate MySQL in our Python Program to perform data transactions over a database on a MySQL server.

Installing MySQL on Python

We can use Python MySQL using various modules or technologies already provided. Some of them are,

All of the above technologies use the same syntax and methods to connect and perform operations on a MySQL database following PEP 249.

Hence, we can access a MySQL database server using any one of the above modules. Also installing only one of them makes sense. We can install them using a simple PIP command in the shell as shown below.

pip install pymysql

Here, we have taken PyMySQL as an example.

Before you start, make sure you have a MySQL database set up in your system and it’s working correctly. You have the login and the password for the database to initiate connections.

Basic Methods in Python MySQL

Now that we installed pymysql, let us look at some basic methods that we are going to use today.

1. MySQL connect()

The pymysql.connect() method establishes a connection to the MySQL database from Python and returns a MySQLConnection object. This object then can be used to access the whole database and perform other operations. It takes in parameters like host, user, password and database as specifications of the database you are up for connecting.

user – For local servers user should be mentioned as “root”, or else you can create a user following this link,
password – It depends on what you used as a password while creating the user. A database may also be passwordless(for our example),
host – For a local server using xampp, we can use localhost for accessing the database. We can also use the server’s IP address or name to access the host,
database – It is the database name which you are set to use. If you already have created a database you can just put the name here.

2. MySQL cursor()

The cursor() method creates a cursor object that can be further used to perform CRUD(Create, Retrieve, Update, and Delete) operations over the database.

3. MySQL execute()

The execute() method executes a SQL query passed to it using the cursor previously created.

4. MySQL close()

The close() method defined in both the cursor and MySQLConnection class is used to close the respective objects.

5. MySQL commit()

The commit() method sends a COMMIT statement to the MySQL server and hence commits the current data transaction.

6. MySQL fetchall()

The fetchall() method fetches all rows of a query result set and returns a list of tuples using the cursor object.

7. MySQL is_connected()

This method checks whether the connection to the MySQL Server is available. It returns True if yes, and False if not.

Getting Started With Python MySQL

In this tutorial, we are going to use Xampp Control panel to connect to a local server and create our database. Starting Apache and MySQL in Xampp does the job.

Further, we can hit https://localhost/phpmyadmin/ directly to see our local server interface as shown below.

Here, mydatabase and studentsdb are some databases that I created earlier.

Creating a MySQL Database in Python

If you do not have a previously created database, you can easily create one by executing a CREATE query using the cursor.execute() method. After establishing a connection with the server. Look at the code below.

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  passwd=""
)

mycursor = mydb.cursor() #cursor created

#creating database with name classdb
mycursor.execute("CREATE DATABASE classdb;")

After executing the above code, the above change can be seen in the database using phpMyAdmin. Here, the new database classdb is successfully created.

If you already have a database, you can easily connect to it just by specifying the name inside the connect() method. As shown below.

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database created before
  passwd=""
)

mycursor = mydb.cursor() #cursor created

#work with the cursor here like printing initial database data

#closing the cursor
mycursor.close()

Operations on a Python MySQL Database

In this section, we are going to focus on the various operations(CRUD) that we can perform on a MySQL database.

1. Python MySQL – Create Table

After we have connected to the database we want to use, and create a cursor object, we can easily execute queries using the execute() method and CREATE a table.

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database
  passwd=""
)

mycursor = mydb.cursor() #cursor created

#work with the cursor
query = "CREATE TABLE Students(StudentID int  PRIMARY KEY AUTO_INCREMENT, Name CHAR(20), Lastname CHAR(20),Standard int);"

mycursor.execute(query)
#closing the cursor
mycursor.close()

As we can see, a table with columns StudentID, Name, Lastname, and Standard is created.

2. Python MySQL Insert

Now that we have already created a table, we can insert rows into it by yet again executing a query using the cursor object.

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database
  passwd=""
)

mycursor = mydb.cursor() #cursor created

#work with the cursor
query1 = "INSERT INTO students(Name, Lastname, Standard) VALUES('Anik', 'Das', 6);"
query2 = "INSERT INTO students(Name, Lastname, Standard) VALUES('Subhash', 'Neel', 8);"
query3 = "INSERT INTO students(Name, Lastname, Standard) VALUES('Prateek', 'Neel', 8);"
query4 = "INSERT INTO students(Name, Lastname, Standard) VALUES('Prem', 'Dutta', 9);"

mycursor.execute(query1)
mycursor.execute(query2)
mycursor.execute(query3)
mycursor.execute(query4)

#closing the db
mydb.commit()
mydb.close()

The rows have been successfully inserted into the database.

3. Python Select MySQL

As we mentioned earlier, the fetchall() method fetches all rows of a query result set and returns a list of tuples using the cursor object.

Hence by applying a query to select some unique or desired data from the database, we can show them using the cursor’s fetchall() method and a for-loop.

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database
  passwd=""
)

mycursor = mydb.cursor() #cursor

#work with the cursor
res = "Select * from students;"

#executing the query
mycursor.execute(res)

rows = mycursor.fetchall()

#showing the rows
for row in rows:
   print(row)

#closing the db
mydb.commit()
mydb.close()

Output:

(1, 'Anik', 'Das', 6)
(2, 'Subhash', 'Neel', 8)
(3, 'Prateek', 'Neel', 8)
(4, 'Prem', 'Dutta', 9)

4. Python MySQL Update

Further, if we want to update any set of data we can use some UPDATE query as shown below. Here, we try to update the Lastname for the student with StudentID = 2(i.e., Subhash).

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database
  passwd=""
)

mycursor = mydb.cursor() #cursor

#query to update database data
update_query = "UPDATE students set Lastname = 'Seth' WHERE StudentID = 2"

#executing the query
mycursor.execute(update_query)

#showing the rows
res = "Select * from students;"
mycursor.execute(res)
rows = mycursor.fetchall()

for row in rows:
   print(row)

#closing the db
mydb.commit()
mydb.close()

Output:

It can be seen that the updation was successful.

5. Python MySQL Delete

Let us now try to delete a set of data using a cursor query. Here, we try to delete the entry or entries which have Standard > 8. Which in this case, is only one(StudentID 4 with Standard 9).

import pymysql

#server connection
mydb = pymysql.connect(
  host="localhost",
  user="root",
  database="classdb", #database
  passwd=""
)

mycursor = mydb.cursor() #cursor

#query to delete database data
delete_query = "DELETE FROM students WHERE Standard > 8"

#executing the query
mycursor.execute(delete_query)

#showing the rows
res = "Select * from students;"
mycursor.execute(res)
rows = mycursor.fetchall()

for row in rows:
   print(row)

#closing the db
mydb.commit()
mydb.close()

Output:

(1, 'Anik', 'Das', 6)
(2, 'Subhash', 'Seth', 8)
(3, 'Prateek', 'Neel', 8)

It is clear that the deletion was done successfully.

6. Python Drop a Table in MySQL

We can also remove or drop a table using DROP query in MySQL. This may be used to remove a table so that we can reuse the name or else to avoid table name collision.

#query to drop table
drop_query = "DROP TABLE IF EXISTS students;"

#executing the query
mycursor.execute(drop_query)

The above code snippet removes our previously created table, students.

Advantages of Python MySQL

MySQL is free and well-maintained software from Oracle. Hence, it is totally trustable and reliable.
It is easy to use, fast as well as free.
Requires only a basic knowledge of Structured Query Language.
It is platform-independent and hence a database created using Python can also be accessed using other platforms.
There are various modules to choose from, using which we can connect to a MySQL server.

Conclusion

So in this tutorial, we understood the use of MySQL in Python. We also took an example to elaborate on the CRUD operations over a database.

So that’s all for today, hope this article helps understand the basics of Python MySQL. For any further MySQL related questions, feel free to use the comments below.

References

PEP 249 — Python Database API,
MySQL Example – Journal Dev Post,
MySQL Connector/Python Coding Examples – Documentation.