CRUD operations in Python using MYSQL Connector
In this post, you will learn how to perform crud operations in Python using MySQL.
Python is a high-level, general-purpose open source programming language. It is both object-oriented and procedural. Like other programming languages, Python can also easily connect to different popular databases. In this article, you will learn to perform CRUD operations in Python using the MySQL Connector. MySQL Connector Python is written in pure Python and is compatible with Python 3. It is self-sufficient to execute database queries through Python.
Install MySQL Connector
The Python MySQL installer is available for different operating systems. You can download the MSI version from here.
Python MySQL InstallerThe installer requires 'python.exe' in your system PATH, otherwise it will fail to install. So make sure to add Python to your system environment.
You can also install MySQL Connector using the pip command.
pip install mysql-connector
This connector is already installed in my system. It returns the following-
c:\python37\Scripts>pip install mysql-connector
Requirement already satisfied: mysql-connector in c:\python37\lib\site-packages (2.2.9)
This is also a way to check the successful installation of MySQL connector.
Python connecting to MySQL
First, we need to import the MySQL connector into the Python file to connect to the database.
import MySQL.connector
Next, use the connect() constructor with all the required parameters to create a connection to the MySQL server.
conn = MySQL.connector.connect(user='database_username', password='database_password',
host='hostname', database='database_name')
conn.close()
Let's create a python file 'mscon.py' and check whether the database connection is successful or not.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
if conn:
print ("Connected Successfully")
else:
print ("Connection Not Established")
Open 'cmd' and execute the above file-
Connected Successfully
Python MySQL Create Table
In this section, you will learn to create a MySQL table using Python. The mysql.connector.connect() method is used to connect to the MySQL database. Next, we used the cursor() method to create a new cursor object. This cursor object is used further to execute the MySQL statement. At last, we have closed the cursor object and database connection object.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
create_employee = """CREATE TABLE employee (
Id int(11) NOT NULL,
Name varchar(250) NOT NULL,
Dept varchar(50) NOT NULL,
Age int(11) NOT NULL,
Contact varchar(250) NOT NULL,
PRIMARY KEY (Id)) """
cursor = conn.cursor()
result = cursor.execute(create_employee)
print("Table created successfully")
if(conn.is_connected()):
cursor.close()
conn.close()
print("MySQL connection is closed")
Output of the above code:
Table created successfully
MySQL connection is closed
Python MySQL Insert Table
This section demonstrates how to insert a new row into the 'employee' table. After executing the INSERT statement using the execute() function, you must commit it. The commit() method is used to commit the changes to the database.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
insert_employee = """INSERT INTO employee (Id, Name, Dept, Age, Contact)
VALUES
(101,'Roza','Developer',30,9821212123),
(102,'Priska','Program Manager',32,6756098734)"""
cursor = conn.cursor()
result = cursor.execute(insert_employee)
conn.commit()
print("Data inserted successfully.")
if(conn.is_connected()):
cursor.close()
conn.close()
print("MySQL connection is closed")
Output of the above code:
Data inserted successfully.
MySQL connection is closed
Python MySQL Select Table
Here is the code to read data or select data from the 'employee' table. After executing the SELECT statement using the execute() function, you can fetch data. We can use any of the fetchall(), fetchmany(), fetchone() method based on our need to return list data. In the given code, we have used the fetchall() method to fetch all records from the MySQL table.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
select_employee = """SELECT * FROM employee"""
cursor = conn.cursor()
cursor.execute(select_employee)
result = cursor.fetchall()
print("Total number of rows selected : ", cursor.rowcount)
print("\nSelected data are -")
for row in result:
print("Id : ",row[0])
print("Name : ",row[1])
print("Dept : ",row[2])
print("Age : ",row[3])
print("Contact : ",row[4])
if(conn.is_connected()):
cursor.close()
conn.close()
print("MySQL connection is closed.")
Output of the above code:
Total number of rows selected : 4
Selected data are -
Id : 101
Name : Roza
Dept : Developer
Age : 30
Contact : 9821212123
Id : 102
Name : Priska
Dept : Program Manager
Age : 32
Contact : 6756098734
Id : 103
Name : Loriya
Dept : Software Testing
Age : 29
Contact : 2309678904
Id : 104
Name : Somya
Dept : Developer
Age : 33
Contact : 8976456789
MySQL connection is closed.
Python MySQL Update Record
Here is the code to update the record in the 'employee' table. We can update a single row, multiple rows, a single column, or multiple columns using the Python MySQL update operation. After executing the UPDATE statement using the execute() function, you must commit it using the commit() method.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
cursor = conn.cursor()
update_employee = """UPDATE employee SET Contact=9232894873 WHERE Id = 102"""
cursor.execute(update_employee)
conn.commit()
print("Data Updated Successfully.")
select_employee = """SELECT * FROM employee WHERE Id = 102"""
cursor.execute(select_employee)
result = cursor.fetchone()
print(result)
if(conn.is_connected()):
cursor.close()
conn.close()
print("MySQL connection is closed.")
Output of the above code:
Data Updated Successfully.
(102, 'Priska', 'Program Manager', 32, '9232894873')
MySQL connection is closed.
Python MySQL Delete Record
These are the Python codes to delete a record from the 'employee' table. We prepare the DELETE statement query and then execute the DELETE query using the cursor.execute() method to get the number of rows affected. Once the record is successfully deleted, we need to commit our changes to the database using the commit() method.
import mysql.connector
conn = mysql.connector.connect(user='root', password='',
host='localhost',database='school')
cursor = conn.cursor()
delete_employee = """DELETE from employee WHERE Id = 103"""
cursor.execute(delete_employee)
conn.commit()
print("Data Deleted Successfully.")
select_employee = """SELECT * FROM employee"""
cursor.execute(select_employee)
result = cursor.fetchall()
print("Total number of rows selected : ", cursor.rowcount)
Output of the above code-
Data Deleted Successfully.
Total number of rows selected : 3
Related Articles
How to read and write a file using DjangoDjango pass variable from view to HTML template
Get data from MySQL in Django View with Models
Django serialize queryset into JSON and display in template
Django Custom User Model SignUp, Login and Logout
Django Export Model Data to CSV
Windows commands to Create and Run first Django app
Django Send Mail on Contact form submission
How to insert data in MySQL database from an HTML form using Django
How to generate QR Code in Python using PyQRCode
Python programs to check Palindrome strings and numbers
CRUD operations in Python using MYSQL Connector
Fibonacci Series Program in Python
Convert MySQL query result to JSON in Python
Display data from MySQL in HTML table using Python
Insert XML Data to MySQL Table using Python