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.
You can also watch this video on YouTube here
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.
|
1 2 3 4 5 |
pip install PyMySQL # OR easy_install PyMySQL |
Video Explanation
Check this video on youtube here.
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.
|
1 |
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.
|
1 |
db = pymysql.connect("localhost", "root", "", "python_tutorials") |
Next, I will prepare a cursor to execute the SQL queries.
|
1 |
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,
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 |
db.close() |
Full Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
__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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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)
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
|
1 2 3 4 5 6 7 8 9 |
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.
Hi
I was on FB, I saw your post. I almost skipped it but then I realized that it probably won’t take more than a minute to at least skim through your post to grasp the syntax that I could possibly need.
You did a brilliant job bro. You nailed it. This is exactly what I needed. Thanks for sharing 🙂
Also check out my blog at blog.codeonion.com . I am no different from you when it comes to sharing of knowledge 🙂
Thanks