How to connect Python with MySQL.

Introduction

In this tutorial, will learn “How to connect the Python code with your MySQL Database”. We are going to use the mysql connector module to make the connection. You can use any other third-party modules to connect your python code with the MySQL database.

After making the connection we can create the database, tables, and insert records. To perform database operations the database user should have access to MySQL.

We will connect to Python with the MySQL database in 3 3 steps.

1. Install MySQL connector with pip3.
2. Create a user and give access to perform database operations.
3. Connect with MySQL and create one database.

How to install MySQL connector with Python pip3.

To install the MySQL connector you have to use the pip3 command. But we must need Python on our system. If you want to install Python on Ubuntu then follow the instruction.

$ pip3 install mysql-connector-python

OUTPUT:
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.29-cp38-cp38-manylinux1_x86_64.whl (25.2 MB)
     |████████████████████████████████| 25.2 MB 192 kB/s 
Requirement already satisfied: protobuf>=3.0.0 in /usr/lib/python3/dist-packages (from mysql-connector-python) (3.6.1)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.29

So now we will verify the installed package of mysql connector with “pip3 show” command.

$ pip3 show mysql-connector-python

OUTPUT:
Name: mysql-connector-python
Version: 8.0.29
Summary: MySQL driver written in Python
Home-page: http://dev.mysql.com/doc/connector-python/en/index.html
Author: Oracle and/or its affiliates
Author-email: 
License: GNU GPLv2 (with FOSS License Exception)
Location: /home/tastethelinux/.local/lib/python3.8/site-packages
Requires: protobuf
Required-by: 

Create a user and Grant Privileges to perform MySQL operations.

So while making a connection we will not use the root user. As per the best practices, we should create a new User and we are going to use the new user in our source code. Want to Install MySQL on a local system refer to the link.

$  mysql -h localhost -uUsername -pPassword

OUTPUT:
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So we have connected with the mysql shell now let’s create a new user.

mysql> CREATE USER 'tastethelinux'@'localhost' IDENTIFIED BY 'PASSWORD';

OUTPUT:
Query OK, 0 rows affected (0.41 sec)

Now grant all privileges to the created user “tastethelinux”@’localhost’.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tastethelinux'@'localhost';

OUTPUT:
Query OK, 0 rows affected (0.08 sec)

If you want to understand more about MySQL user permissions, then please refer to the article.

Connect MySQL with Python and create one database.

Now we have a new username and password for the mysql let’s connect with our source code.

##Import the Package
import mysql.connector

## Made the Connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

print("Database Connected Successfully")

So here we are using vim editor to write the code, you can use any editor of your choice. save and quit the file and run the code.

$ python3 python_connect_with_mysql.py

OUTPUT:
Database Connected Successfully

Once we got the above output then we can perform the operations for MySQL. So let’s create the database via source code.

##Import the Package
import mysql.connector

## Made the Connection
conn = mysql.connector.connect(
  host="localhost",
  user="tastethelinux",
  password="PASSWORD"
)

cursor = conn.cursor()
print("Database connected Successfully")

### Show all the Database
print("List of the Database before creating")
cursor.execute("show databases;")
#### Used for loop to print all the database
for x in cursor:
  print(x)

###Executing Query to create database with the name tastethelinux
cursor.execute("CREATE DATABASE tastethelinux;")
print("Database created")

### Show all the Database. 
print("List of the Database After tastethelinux database got created")
cursor.execute("show databases;")

#### Used for loop to print all the database
for x in cursor:
  print(x)

let’s save and quit the file. Now run the python source code in a shell.

$ python3 python_connect_with_mysql.py 

OUTPUT:
Database connected Successfully
List of the Database before creating
('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('test',)
Database created
List of the Database After tastethelinux database got created
('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('tastethelinux',)
('test',)

So in the source code, we have followed the below steps.

1. Connected with the database.
2. List all the Databases that is present in MySQL before creating a Database.
3. Created a tastethelinux database.
4. List all the databases that are present in MySQL after the database got created.

Conclusion

So in this article, we have connected Python source code with the MySQL Database in 4 steps. So in this article, we have connected Python source code with the MySQL Database in 4 steps.

1. We have installed the mysql connector module
2. Then we have created a user in the MySQL database.
3. We have written a code in Python and use the user, password and host to make the connection.
4. We have created a database via Python source code.

If you face any issue while connecting to Python with MySQL database then leave a comment. Any valuable feedback leaves a comment.