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]

showing mysql databases
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 showing all the tables present there
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 data from a table
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

Printing all the entities from a MySQL table with the help of a python program





Subhankar Rakshit

Meet Subhankar Rakshit, a Computer Science postgraduate (M.Sc.) and the creator of PySeek. Subhankar is a programmer, specializes in Python language. With a several years of experience under his belt, he has developed a deep understanding of software development. He enjoys writing blogs on various topics related to Computer Science, Python Programming, and Software Development.

Post a Comment (0)
Previous Post Next Post