How to Use MySQL Database in Python?

Posted in /  

How to Use MySQL Database in Python?

Vinay Khatri
Last updated on June 10, 2022

    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, 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. DATABASE CREATION WITH PYTHON MYSQL 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. create tables using Python MySQL

    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:

    Leave a Comment on this Post

    0 Comments