How to Use MySQL Database in Python?

MySQL is one of the most popular open-source Relational Database Management Systems . And it is used by many applications to store data. Here in this Python tutorial, I will walk you through how to code in Python to connect with MySQL database; although Python already Supports a Standard Database SQLite library.

How to Use MySQL Database in Python

This tutorial will focus on how to connect and use MySQL with Python. By the end of this tutorial, you will know:

• How to Connect MySQL Database in Python
• How to Create MySQL Databases in Python
• How to Create MySQL tables in Python
• How to Insert Data into a Table in Python
• How to Fetch Data from a Table.

Before diving into the code we need to install the required libraries which will connect our Python program to the MySQL Database.

Note: For this tutoriual Make sure that MySQL is install in your Systms.

Install Dependencies

Python MySQL connecter Driver/Library

Python does not support MySQL, so in order to make a connection between the MySQL database and Python we need to install the MySQL driver or module for our Python Environment. Run the following pip install command on your terminal or command prompt to install mysql-connector-python library.

pip install mysql-connector-python

Install MySQL

To create a MySQL database using Python you need to have MySQL installed and running on your system. You can also install the XAMPP which is an easy way to create and run a MySQL Server on the System. If you install MySQL then you will have a similar console connector for your MySQL client window.

Note: You will only get the most out of this Python article if you know the basic about Python, Database and MySQL.

Now I am assuming that you have installed the required Library and MySQL on your System, open your best Python IDE or text editor , and start coding with me.

How to Connect MySQL Database in Python

Our first objective would be, setting the connection between the Python and MySQL database. For that, we need some credentials, the Hostname which would be localhost in our case,  the U sername, by default its value is " root", and the P assword that you have set for your MySQL while installing it. To set the connection between Python and MySQL we will use the installed mysql-connector-python library connect() function.

#Python program to set connection with MySQL

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"   #enter your MySQL password
 
#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

if set_db_conn:
    print("The Connection between has been set and the Connection ID is:")
    #show connection id
    print(set_db_conn.connection_id)

Output

The Connection between has been set and the Connection ID is:
15

To set the connection we have used the mysql.connector.connect() function. And in the function, we have passed 3 arguments. These 3 arguments are the mandatory arguments, and apart from them the connect() function also supports some optional arguments which we will explore in the coming section. The 3 arguments we passed in the connect() function: host : the host specifies the Hostname for which we want to set the connection. For this tutorial, we will be using the localhost because we have installed MySQL in our system. In the hostname, you can also specify the IP address of your MySQL server database if it is stored in the server. user : The user argument specifies the username of the MySQL database, by default for the username for the localhost MySQL database root. password : The password argument specifies the password of your MySQL database. When you will install MySQL on your system you need to create a password. The MySQL is a distributed Relational Database Management System, which means multiple users can be live for the same database, so when we set a connection to our Database the MySQL give us a unique connection ID number, which distinguishes us from the other users. And when we are connected to our MySQL with Python we can access the connection id with connection_id property.

How to Create MySQL Databases in Python

In MySQL or any other SQL database we first generally create a Database and inside that Database, we can create tables that store data in relational format. By far you know how to connect Python with MySQL, now it's time to create a new Database in MySQL with Python. To create a New Database we use the MySQL CREATE DATABASE query followed by the database name. And to execute any MySQL query with Python we needed the cursor object and with the help of the cursor object execute() method we can run or execute any query from our Python program. Now let's create a new database by the name StudentsDatabase .

#Python program to create A MySQL Database.

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"

#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

#initialize cursor object
cursor = set_db_conn.cursor()

try:
    #create database
    cursor.execute("CREATE DATABASE IF NOT EXISTS StudentsDatabase")
    print("Database has been Created successfully")

    #use the database
    cursor.execute("USE StudentsDatabase")
    print("Now You are using the Students Database")

except:
    print("Error")

Output

Database has been Created successfully
Now You are using the Students Database

If you have ever worked with any SQL RDBMS like MySQL, you can tell that we are simply writing the SQL queries in the cursor.execute() function. The IF NOT EXISTS query will make sure that no error would raise even if the StudentsDatabase already in the Database. To see if the Database is successfully created or not you can open your MySQL Command Line Client and run the "SHOW DATABASES" query.

From the MySQL Command Line Client, you can see that the database has been created as studentsdatabase . MySQL and all the other SQL RDBMS are cases insensitive so the name StudentsDatabase is the same as studentsdatabase .

How to Create MySQL tables in Python

A MySQL Database is a collection of Tables and these tables contain data in a tabular or Relational Format. To create a table in MySQL Database we use the CREATE TABLE query.  Similar to the Create Database we will use the Python cursor's execute() method to run the Create Table query, and when we create the table we also specify the column names and their data type. Now let's create a table, Students for the StudentsDatabase database.

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"

#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

#initialize cursor object
cursor = set_db_conn.cursor()

try:
    #use the database
    cursor.execute("USE StudentsDatabase")
    print("You are using  StudentsDatabase")

    #create table
    cursor.execute("""CREATE TABLE IF NOT EXISTS Students(
                                    `ROLL_NO` INTEGER PRIMARY KEY,
                                   ` NAME` VARCHAR(25) NOT NULL,
                                   `CLASS` VARCHAR(25) NOT NULL,
                                   `CONTACT`  VARCHAR(25)   )
                                   """)
    print("Table has been created successfully")
except:
    print("Error")

Output

You are using StudentsDatabase
Table has been created successfully

From the above example, you can see that I have used tripel double-inverted quotes to write the CREATE TABLE query in multiple lines, and use the backtick (`) not single inverted quote(') to specify the Table columns names like `name`,  `roll_no`,  `class`, and `contact`. Now you can open your MySQL Command Line Client and run the SHOW TABLES query to check if the students table is created or not.

How to Insert Data into a Table in Python

After creating the table successfully, now it's time to insert data in that table. To insert data in a SQL  Table we use the SQL Insert Query . Now let's insert some data in our just created students table.

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"

#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

#initialize cursor object
cursor = set_db_conn.cursor()

try:
    #use the database
    cursor.execute("USE StudentsDatabase")
    print("You are using  StudentsDatabase")

    rollnumbers = [1,2,3,4,5]
    names = ['Rahul', 'Ram', 'Ravi', 'Himanshu', 'Neeraj']
    cls = [11, 12, 11, 11, 12]
    contacts = ["9876547382", "9763736353", "9655635245", "8973653425", "7898765545"]

    for i in range(5):
        print(f"Inserting values {rollnumbers[i]},{names[i]}, {cls[i]}, {contacts[i]}")
        cursor.execute("""INSERT INTO Students(ROLL_NO, NAME, CLASS, CONTACT)
                                         VALUES(%s, %s, %s, %s)""",
                                         params=(rollnumbers[i], names[i], cls[i], contacts[i])
                                      )
    #commit or save insertions
    set_db_conn.commit()

except:
    print("Error")

Output

You are using StudentsDatabase
Inserting values 1, Rahul, 11, 9876547382
Inserting values 2, Ram, 12, 9763736353
Inserting values 3, Ravi, 11, 9655635245
Inserting values 4, Himanshu, 11, 8973653425
Inserting values 5, Neeraj, 12, 7898765545

In the above example, you can see that we have used "%s" as placeholders for the values or params parameters,  this prevents the Script from the SQL Injection . In this example, we have also used the commit() function, this function will commit the changes and make sure that all the insertions save to the database table. If you miss the commit() function, no changes will be made to the real database. Just to be doubly sure run the SELECT * FROM students on your MySQL Command Line Client.

How to Fetch MySQL Data from a Table in Python?

After Inserting the data into the students table let's fetch that data using the Python execute() function, MySQL SELECT * FROM students query and cursor.fetchall() method. The cursor.fetchall() method will return a list of tuples that represent every row from the tables based on the cursor.execute() statement.

#Python program to Fetch MySQL Data from a Table in Python

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"

#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

#initialize cursor object
cursor = set_db_conn.cursor()

try:
    #use the database
    cursor.execute("USE StudentsDatabase")
    print("You are using  StudentsDatabase")

    #execute the select all query
    cursor.execute("SELECT * FROM students")

    for row in cursor.fetchall():
        print(row)

    #close the cursor
    cursor.close()

    #close the connection
    set_db_conn.close() 
    
except:
    print("Error")

Output

You are using StudentsDatabase
(1, 'Rahul', '11', '9876547382')
(2, 'Ram', '12', '9763736353')
(3, 'Ravi', '11', '9655635245')
(4, 'Himanshu', '11', '8973653425')
(5, 'Neeraj', '12', '7898765545')

Conclusion

With this, we have reached the end of our Python tutorial on "How to use MySQL Database in Python". To use MySQL with Python, you should have knowledge of MySQL databases and queries, because using MySQL with Python is all about executing SQL queries with the Python cursor.execute() method. And when you are making some changes in the Database or tables such as inserting, updating, or deleting values you need to make sure that you commit those changes with commit() function.

People are also reading:

• Enumerate in Python
• How to Convert a List to Dictionary in Python?
• What can you do with Python?
• Bottle Framework in Python
• What is CherryPy Python?
• SQL Interview Questions
• Python Find all files in a Directory
• Del, Remove and Pop on Python Lists
• Open Port Banner in Python
• How to find the IP address of a system/website using Python?
• Python Chat Room Application