How to access MySQL database in Python
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 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.
You can install pyMySQL using either pip
or easy_install
for windows.
pip install PyMySQL# OReasy_install PyMySQL
So before performing these database operations, I will first create a database named ‘python_tutorials’ in phpMyAdmin locally.
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)
The above code creates a new table.
Finally, I will close the connection.
db.close()
Full Code:
__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()
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()
Read data from the table
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")
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.
That’s it for the post. I hope now you learned how to perform CRUD operations on a DB and its tables.
Feel free to look at my other Python posts on GUI Programs, Math Programs and Basic Programs in Python.