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.
- Install MySQL connector with pip3.
- Create a user and give access to perform database operations.
- 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.
- Connected with the database.
- List all the Databases that is present in MySQL before creating a Database.
- Created a tastethelinux database.
- List all the databases that are present in MySQL after the database got created.
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.
- We have installed the mysql connector module
- Then we have created a user in the MySQL database.
- We have written a code in Python and use the user, password and host to make the connection.
- 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.