In this tutorial, we will learn how to connect the MySQL database with the Python program and write different queries. We will start with a basic introduction, go through MySQL set up in Python, and then execute further queries for creating, reading, deleting, and updating the data. Let’s start with an introduction.
Using MySQL with Python
MySQL is one of the widely used and popular database management systems currently. It supports almost every popular programming language available out there like PHP, Python, Java, etc. However, to connect MySQL with any programming language, you need a driver that connects the programming language with the database and helps interact with it. In Python, you need a MySQL driver to access the MySQL database. Python offers a driver as a package called “Mysql-connector-Python,” which you can install using the PIP.
If you have little experience in Python, you must be aware of and familiar with a PIP, a package manager, and is already installed most of the time. Enough theory for now. Let’s see the setup.
Also read: MySQL STRCMP() – How to Compare Two Strings in MySQL?
Prerequisites
Before diving into the Python-MySQL programs, you should have-
- MySQL Server installed on your local machine. Or, you can also use MariaDB (comes with XAMPP) which is fork of MySQL and offers similar functions and features.
- Python installed on your local machine and make sure environment variable is already set. Make sure you have latest version of Python (Not necessary but should be >= version 3.*.*).
- PIP installed with a environment variable set.
I hope you are familiar with the above software and have already installed them on your computer.
Install Python MySQL connector
First, download and install the Python MySQL connector using the following PIP command.
python -m pip install mysql-connector-pythonCode language: Bash (bash)The connector is installed successfully. Test it if the installation was successful using a simple program.
Create a Python file with any name other than “MySQL” because it is a module name that we installed just now and write the following code.
import mysql.connectorCode language: Python (python)The import statement will import the connector and use it throughout the program. Now, simply run the program to check if you get an error or not on the output screen.
If the connector is working fine, the output should be empty.
Create Python-MySQL Connection
Before writing a program for the Python-MySQL connection, make sure you have started the MySQL server. If you have XAMPP installed on your machine, start apache and MySQL from the XAMPP-control panel. If you have MySQL and XAMPP installed already on your computer, you must start only one of them. In this tutorial, we will use the command line MariaDB. The same commands are applicable for MySQL; no change is there.
You first need a MySQL database username and password to create a connection. Following is the program to create a connection-
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password=""
)
print(mydb)
obj= mydb.cursor();
print(obj)Code language: Python (python)In this program, the username is “root” and the password is blank, so only double quotes are used without any character. The “mydb” is a connection object, and the “obj” is an object that helps us execute queries created using the cursor object available in Python. When we run the program, it should show the following output-
We will use the “obj” variable to execute the queries.
Create a MySQL Database Using Python
We use the “CREATE DATABASE dbname” query to create a database. The following code will check if the database with the name “mydb” already exists. If not, then makes a new one.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password=""
)
obj= mydb.cursor();
obj.execute("CREATE DATABASE IF NOT EXISTS mydb");Code language: Python (python)The obj.execute() statement will create a new database with the name mydb. If you run the program multiple times, it won’t show any error because it will simply ignore if the database exists. Let’s check if the database is created from a MySQL/MariaDB command line.
SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)
Alternatively, if you already have created a database and want to use it in your program without creating it, you can specify the name when making a connection. Check below-
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)Code language: Python (python)Note that if the specified database does not exist, you will get an error as “unknown database *”. So, correctly write a database name.
We have created a database successfully. We will now create a table, insert data into it and perform read, update, and delete operations.
Create a MySQL Table using Python
Let’s create a table with the name “students” to store basic information of students like id, name, and age.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj= mydb.cursor();
obj.execute("CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), age INT)")Code language: Python (python)After executing the program, it will create a “student “table. Check if it is created successfully from the terminal using the following command.
USE mydb;
SHOW TABLES;Code language: SQL (Structured Query Language) (sql)
Using Python to Insert Data into a MySQL Table
Now, insert some data through the Python program into the student table.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj= mydb.cursor();
obj.execute("INSERT INTO student(name,age) values('Raj',16),('Tom',15),('John',17)")
mydb.commit()Code language: Python (python)Make sure to write mydb.commit() statement to make the changes in the table. If not written, no changes will take place.
Let’s check if the data is correctly inserted in the table from the MySQL terminal.
SELECT * FROM student;Code language: SQL (Structured Query Language) (sql)
We have used the MySQL terminal to check the table data as of now. But what if we want to fetch the data using a Python program? Let’s take an example to fetch the data from the database.
Fetch Table Data using Python
We can use a “SELECT * FROM” query to fetch the data from the database table. There are multiple rows present in the student table, so we have to use a loop statement to get every record.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj= mydb.cursor();
obj.execute("SELECT * from student")
result= obj.fetchall()
for row in result:
print(row)Code language: Python (python)Here, obj.fetchall() statement is used to fetch all rows from the last executed statement. After executing the above program, you will output as below.
If you want to get the data of particular columns, specify the column names after the SELECT.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj= mydb.cursor();
obj.execute("SELECT id, name from student")
result= obj.fetchall()
for x in result:
print(x)Code language: Python (python)
Similarly, you can also fetch the first row only using the fetchone() statement.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj= mydb.cursor();
obj.execute("SELECT * from student")
result= obj.fetchone()
print(result)Code language: Python (python)We will have only one row here, so we don’t need to use a FOR loop.
Update MySQL Table Data with Python
We can update the existing data in the table using UPDATE statement. Make sure to write the commit() statement after the update query to save the changes in the database.
In the following program, we will update the age of a student named Raj.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj = mydb.cursor()
obj.execute("UPDATE student SET age=18 WHERE name='Raj'")
mydb.commit()
print("updated data is -")
obj.execute("SELECT * from student")
result= obj.fetchall()
for x in result:
print(x)Code language: Python (python)Here, we have used a WHERE clause to target the row which consists “Raj”. If we don’t write a WHERE clause, the query will update all the rows in the table instead of just one. Following is the result when we run the program.
Delete Table Data using Python
We can delete the table data using the “DELETE FROM” statement.
Remember to write commit() statement after deleting the record to save the changes in the table. Also, use WHERE clause to target a particular row or record. If you don’t use WHERE clause, you will lose all the data from the table.
In the following program, we will delete the student’s record whose id is 3. We can also perform the delete action by using name or age as well.
import mysql.connector as myconn
mydb = myconn.connect(
host="localhost",
user="root",
password="",
database="mydb"
)
obj = mydb.cursor()
obj.execute("DELETE FROM student WHERE id=3")
mydb.commit()
print("updated data is -")
obj.execute("SELECT * from student")
result= obj.fetchall()
for x in result:
print(x)Code language: Python (python)After the DELETE statement, the program will execute the SELECT query to display all the latest records.
Conclusion – Python MySQL Connection
In this article, we have learned the Python MySQL connection and different MySQL queries like SELECT, UPDATE and DELETE in the Python program, along with examples. MySQL with Python is a broad topic, and we can have a full-fledge series of articles on it. However, this tutorial will focus on the basic introduction and setup of MySQL with Python without going deep. You can expect a detailed tutorial on Python-MySQL here in the future. Till then, you can practice intermediate and advanced concepts like clauses, joins, and alter table commands. See you in the following tutorial!