Python MySQL DataBase Connection

A database is an ordered collection of data that can be accessed and modified in the future. When we program in Python, we do come across cases where we need to store the data at a place for future use. For this purpose, Python provides multiple modules. We will be dealing with a module named MySQL Connector. So without any delay, let us begin with an introduction to Database and Python MySQL DataBase Connection.

Introduction to Databases

As said above, a database is the collection of data in a structured manner for easy access and management. We have various database management systems which include:
1. MySQL
2. Oracle Database
3. SQL server
4. Sybase
5. Informix
6. IBM db2
7. NO SQL

We will be using the MySQL database system since it is easier and convenient to use. It uses the SQL (Structured Query Language) to do operations like creating, accessing, manipulating, deleting, etc., on the databases.

Python Modules to Connect to MySQL

To communicate with MySQL Python provides the below modules:

1. MySQL Connector Python
2. PyMySQL
3. MySQLDB
4. MySqlClient
5. OurSQL
All the above modules have almost the same syntax and methods to handle the databases. And all of them follow the rules defined in the Python Database API Specification v2.0 (PEP 249). But we prefer to use the MySQL Connector because of the following advantages:

1. It is written in pure Python, and its commands are self-sufficient to do operations on the databases.
2. It is compatible with Python 3.
3. It is actively maintained.

Communication Between Python and MySQL

Python communicates with MySQL by forming a connection with it. The process of communication happens in the following steps:
1. First, we install the MySQL connector module by writing the command,

pip install mysql-connector

2. Then the next step is to import the module by writing the below command.

import mysql.connector

3. Then we use the connect() method to send the connection request to Mysql. This function returns an MYSQL Connection object on a successful connection.
4. After this, we call the cursor() method to further do various operations on the database.
5. Now we can execute various operations and get the results by giving the query to the execute the () function.
6. We can also read the result by using the functions cursor.fetchall() or fetchone() or fetchmany().
7. When we are done working with the database we can close the cursor and the connection using the functions cursor.close() and connection.close().

Connecting to the MySQL

As said above, the first step after importing the module is to send the connection request using the connect() method. This function takes the following arguments:

1. Username: It is the username that one uses to work with MySQL server. The default username is root.

2. Password: This is the password given by the user when one is installing the MySQL database.

3. Host Name: This is the server name or IP address on which MySQL is running. We can either give it as ‘localhost’ or 127.0.0.0

4. Database name: This is the name of the database to which one wants to connect. This is an optional parameter.

The below code shows the way to connect to the MySQL server on our system.
Example of code to connect to the Mysql server:

import mysql.connector

"""
Connection to the server.
The username is root, the password is 1234 and the hostname is localhost
""" 
db=mysql.connector.connect(user="root",passwd="1234",host="localhost") 
 
print(db)

Output:

We got a connection object as an output showing that the connection was successful.

Creating a Database

Once we have formed the connection, the next step is to call the cursor() method that returns a cursor object that helps to communicate with the Mysql server. Then we can create the database by giving the query to the execute() function. We can give the same query as we use in Mysql to create a database. We use the cursor object as the reference while using the execute() method. Following is the way to do it:

Example of creating a database:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost") 
 
my_cursor=db.cursor() #getting the cursor object
my_cursor.execute("CREATE DATABASE students") #creating the database named students

Now we can check if the created database exists or not by listing all the databases on the server. We can do this by giving the query to show all the databases to the execute() function.

Example of listing all the databases:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='students') 
my_cursor=db.cursor()

query="SHOW DATABASES" #query to get all the database names
my_cursor.execute(query) #executing the query

for d in my_cursor: #getting all the names using the loop
    print(d)

Output:

We can see that the ‘students’ database we created is present in the list. So, we could successfully create the database.

There is another way to access the database that already exists in our server or the one which we created already. We can do this by giving the database parameter to the connect() method to connect to that specific database rather than all the databases in the server. The below code gives an example of doing this.

Example of code to connect to the database ‘students’:

import mysql.connector

#connecting to the students database
db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='students') 
print(db)

Output:

Operations on Python

Now we are ready to do the CRUD (Create Read Update Delete) operations. These are described below:
1. Create: It is a command to create a table or a record in the database.
2. Read: It is a command to get any data from the database.
3. Update: It is the command for updating the table or its records.
4. Delete: It is a command to delete the table or its records.

We will be discussing each of these commands in the further sections.

Creating a Table

We can create the table by giving the query to create a table as an input to the execute() function. The below code gives an example of creating a table named ‘student_details’ holding the name, roll number, and class of each student.

Example of creating a table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

my_cursor=db.cursor()
query="CREATE TABLE student_details (name VARCHAR(255), roll INT(10), class INT(10))" #query to create a table
my_cursor.execute(query) #executing the query

We can check if the table is created or not by printing all the tables in the database. For example,

Example of checking all the tables in the database:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

query="SHOW TABLES" #query to show all the tables in the database
my_cursor.execute(query)

for tab in my_cursor: #printing all the tables
    print(tab)

Output:

We can see that the name of the table we create is present. If we create more tables then we can see all the names in the output.

Primary Key

Remember we can set a column as a primary key that holds unique and non-empty values. We can also do this by giving the corresponding query. Suppose if we want to set the roll number column in the above table while creating it we can write the below code.

Example of creating a table with a primary key:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

my_cursor=db.cursor()
query="CREATE TABLE student_details (name VARCHAR(255), roll INT(10) PRIMARY KEY, class INT(10))" #query to create a table with primary key
my_cursor.execute(query) #executing the query

Alter Table

In addition, we can also add a column to it. Say, suppose we want to add a section column to the table we created we can write the below code.
Example of altering the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

query="ALTER TABLE student_details ADD COLUMN section VARCHAR(3)" #query to add a column to the existing table
my_cursor.execute(query)

Now we have 4 columns in our table.

Inserting Data into Tables

Once we have created the table, we can now start inserting the information about the students. The below example shows the way to enter information about the students.
Example of inserting data into the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a common query to insert all the students
query = "INSERT INTO student_details(name,roll,class,section) VALUES(%s,%s,%s,%s)" # adding a common query to insert all the v
# information about a student in tuple form
stds = ("abc",1,5,'A')
 
my_cursor.execute(query,stds)
db.commit()

We use the commit() function to save all the changes done to the database. If we want to add multiple values then we need to use the executemany() function as shown below.

Example of inserting multiple data into the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a common query to insert all the students
query = "INSERT INTO student_details(name,roll,class,section) VALUES(%s,%s,%s,%s)" # adding a common query to insert all the v
#list containing the information about the students in tuple form
stds = [("pqr",2,6,'B'),("xyz",3,4,'D'),("rst",4,8,'C')] 
 
my_cursor.executemany(query,stds)
db.commit()
print(my_cursor.rowcount, "records are inserted.")

Output:
3 records are inserted.

We would have observed above that the cursor object holds the information about the most recent activity done with the database. So, we used it to get the confirmation about the number of records we added to the table. And we got the output as 3 rows, representing the three students in the list.

Read Operation

Since we have some information in the table, let us get information from the tables. Python provides two functions for this purpose:

1. fetchall(): This function fetches all the data from the last executed statement using the cursor
2. fetchone()- This statement fetches the first row of data from the last executed statement using the cursor.

Let us see the examples of each of these functions to get more understanding.
Example of the fetchall() function:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to read data of all the columns from the table
query = "SELECT * FROM student_details"

my_cursor.execute(query)
res = my_cursor.fetchall() #storing the output of fetchall() in the res

for row in res: #printing all the records in res
    print(row)

Output:

We can see that all the student details we inserted got printed here. Now let us see an example of fetchone().
Example of the fetchone() function:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to read data of all the columns from the table
query = "SELECT * FROM student_details"

my_cursor.execute(query)
res = my_cursor.fetchone()#storing the output of fetchone() in the res

for row in res: #printing all the records in res
    print(row)

Output:

We can observe that we got only the details of the first student. We can also get details of only a few columns.

Example of the reading details of selected columns:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to read data of name and roll columns from the table
query = "SELECT name,roll FROM student_details"

my_cursor.execute(query)
res = my_cursor.fetchall()

for row in res:
    print(row)

Output:

Update Table

This is the third operation in the CRUD operations. As usual, we do this operation by giving the query to the execute() function. The below example shows a way to update the section of one of the students.

Example of the updating records of the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to update the section of one of the students
query = "UPDATE student_details SET section = 'D' WHERE roll = '4'"

my_cursor.execute(query)
db.commit()
print(my_cursor.rowcount, "record(s) are affected")

Output:

We are using the WHERE command here in the query to give the condition and then SET it to the new value. The rows which satisfy the given condition will be updated. Here, we are changing the section of the student whose roll number is 4. Since there is only one person with this roll number, only one record is affected.

Now if we see the table we will be able to observe the changes. Let us look at the records.

Example of reading data from the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

query = "SELECT * FROM student_details"

my_cursor.execute(query)
res = my_cursor.fetchall()

for row in res:
    print(row)

Output:

We can do an operation similar to the above one in another way by giving a common query. Now let us change the section to ‘C’ where the section is ‘D’.

Example of the updating records of the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to update the section of one of the students
query = "UPDATE student_details SET section = %s WHERE section = %s"
vals = ("C", "D") #storing the values to be updated

my_cursor.execute(query,vals)
db.commit()
print(my_cursor.rowcount, "record(s) are affected")

Output:

Since we have two records with section ‘D’, we can see that 2 rows got affected.

Delete Operation

We can delete record(s) from the table by giving the corresponding query. For example, to delete the record where the name of the student is ‘abc’ we can write the below code.

Example of the deleting records of the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

# adding a query to delete the record from the students
query = "DELETE FROM student_details WHERE name = 'abc'"

my_cursor.execute(query)
db.commit()
print(my_cursor.rowcount, "record(s) are affected")

Output:

Now as we have deleted a row let us see the table.
Example of reading data from the table:

import mysql.connector

db=mysql.connector.connect(user="root",passwd="1234",host="localhost",database='student_details') 
my_cursor=db.cursor()

query = "SELECT * FROM student_details"

my_cursor.execute(query)
res = my_cursor.fetchall()

for row in res:
    print(row)

Output:

We can see that the first record got deleted. Similar to the update we can do the delete operation using the common command with %s escape sequence.

We can delete all the values using the query,

DELETE FROM student_details

We can also delta the table itself using the below command.

DROP TABLE student_details

Interview Questions on Python MySQL DataBase Connection

Q1. Write a program to get all the tables in the given database.
Ans. Below is the example of getting all tables:

my_cursor.execute("SHOW TABLES")

for t in my_cursor: #printing all the tables
    print(t)

Q2. Write a program to create a table with a primary key and a unique key.
Ans. Below is the example of creating a table:

query="CREATE TABLE food_items (id INT(5) PRIMARY KEY, name VARCHAR(255) UNIQUE KEY, price INT(10))" 

my_cursor.execute(query) 

Q3. Write a program to read the name and the section of the students who are in class 4.

Ans. Below is the example of raveling array:

query = "SELECT name,section FROM student_details WHERE class = 4"

my_cursor.execute(query)
res = my_cursor.fetchall()

for row in res:
    print(row)

Q4. Write a program to change the section of the students to ‘B’ whose roll number is greater than 10.
Ans. Below is the program for the same:

query = "UPDATE student_details SET section='B' WHERE roll >3"

my_cursor.execute(query)

Q5. Write a program to delete the students who are in class 5 and section ‘A’.
Ans. Below is the program for the same:

query = "DELETE FROM student_details WHERE class=5 AND section='A'"

my_cursor.execute(query)

Quiz on Python MySQL DataBase Connection

Conclusion

In this article, we learned about MySQL and how to form the connection between MySQL and Python using the module MySQL connectors. We also learned to apply CRUD operations in Python.

Hope you understood all the concepts discussed. Happy learning!