Hello everyone, welcome back to programminginpython.com. Here I am going to show you how to access MySQL database and perform all database operations on the database in Python. In most of the applications, you need a database to store, update, and manage your data. So here I will show you all the CRUD(Create, Read, Update, Delete) operations which can be performed on a database so basically we are doing a MySQL database CRUD operations in python. So let’s get started.
First, you need some module to connect to MySQL, So here I will install a module called `PyMySQL`, which is an interface to connect to a database from python. It implements the Python database API version 2.0. As I cover most of the tutorials in the site using Python 3, am using PyMySQL, for Python 2, there is another module called MySQLdb, which is not supported in Python 3.
Limited time sale - 50% OFF Learn in-demand data and AI skills on the 17th of July
So before performing these database operations, I will first create a database named ‘python_tutorials’ in phpMyAdmin locally.
How to access MySQL database in Python
Create a MySQL database table in Python
First I will import the pyMySQL package, which I installed before.
import pymysql
Now I need to connect to the database, this pymysql has a function called connect('host', 'username', 'password', 'database_name') which takes 4 arguments.
db = pymysql.connect("localhost", "root", "", "python_tutorials")
Next, I will prepare a cursor to execute the SQL queries.
cursor = db.cursor()
So, now I can write queries like cursor.execute("SQL QUERY")
I will create a new database table by executing the following query,
sql = """CREATE TABLE PERSON (
ID INT NOT NULL,
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
PRIMARY KEY (ID) )"""
cursor.execute(sql)
__author__ = 'Avinash'
import pymysql
# Open database connection
db = pymysql.connect("localhost", "root", "", "python_tutorials")
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS PERSON")
# Create table as per requirement
sql = """CREATE TABLE PERSON (
ID INT NOT NULL,
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
PRIMARY KEY (ID) )"""
cursor.execute(sql)
# disconnect from server
db.close()
Inserting data into a table
Similar to creating a table, here also I will use a SQL query to insert data, but here I also use try and except, so if any error or problem occurs while inserting the data, the whole operation can be rolled back.
sql = """INSERT INTO PERSON(ID, FIRST_NAME,
LAST_NAME, AGE, SEX )
VALUES (1, 'ABC', 'ABCD', 20, 'M')"""
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except pymysql.Error:
# Rollback in case there is any error
db.rollback()
How to access MySQL database in Python
Update data in the table
This update operation is also similar to insert operation, I just will change my SQL query here. So I will change the age column value to 30 if the person is male(M)
sql = "UPDATE PERSON SET AGE = 30 WHERE SEX = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
For reading the data, I will use a simple query to get all data, but for showing the data, I will loop through all the data and fetch single rows data and print them.
sql = "SELECT * FROM PERSON"
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
# Now print fetched result
print("fname = %s,lname = %s,age = %d,sex = %s" % \
(fname, lname, age, sex, ))
except:
print("Error: unable to fetch data")
How to access MySQL database in python – programminginpython.com
Delete data from the table
For deleting I will use a simple query with some condition to delete elements/rows from the table.
sql = "DELETE FROM PERSON WHERE AGE > '%d'" % (29)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
So after running the above code, the only row in the table gets deleted as it satisfies the condition I gave i.e age > 29.
How to access MySQL database in Python
That’s it for the post and we performed MySQL database CRUD in Python. I hope now you learned how to perform CRUD operations on a DB and its tables.