How to Connect Python with MySQL Server

In this tutorial, you'll learn how to connect python with MySQL server. We'll use PyMySQL module here.

Requirements

MySQL Server - one of the following

• MySQL >= 5.6

• MariaDB >= 10.0

You can read about the installation process and more details from here.

Installation

Make sure you've installed the PyMySQL module on your machine. Otherwise, install the current version of PyMySQL(1.0.2) using the following command.

• pip install PyMySQL  [For Windows users]

• pip3 install PyMySQL  [For Linux users]

Database Operations (without Python)

mysql>show databases;  [It will show all databases(see the image below). You will get some databases by default]

All the Databases

mysql>use login_details; [It will select the "login_details" database]
mysql>show tables; [It will show all the tables stored in the "login_details"]

Selecting a Database and Displaying all the Tables

mysql>select * from employee_register; [It shows all the data stored in the table: "employee_register"]

Showing all the entities from a table

 

 

Now we'll perform the same task using a Python Program.

Code

First, we will connect our python program to the MySQL server(see the yellow marked line). Next, the program will perform the same task we did just before through several MySQL commands.

Remember one thing, you have to put your own username and password(you should to set an username and password after installing the MySQL server. See how, from here) into the code(see the yellow line).

import pymysql

def connect_with_datbase():
    try:
        connect = pymysql.connect(
                                host="localhost", 
                                user="Type your username", 
                                password="Type Your Password", 
                                database="login_details"
                                )

        cur = connect.cursor()
        cur.execute("select * from employee_register")

        # Fetch all the data
        row = cur.fetchall()
        print(row)
        print("Type is: ",type(row))

        # Close the connection
        connect.close()
        
    except Exception as e:
        print(e)

if __name__ == "__main__":
    # Calling the function
    connect_with_datbase()

Output