Python Select, Insert, Update and Delete Data from MySQL: A Completed Guide – Python Tutorial

We often need to store some data into mysql database. There are four basic operations: select, insert, update and delete. In this tutorial, we will introduce how to operate mysql database using python for python beginners.

Preliminary

You should install python pymysql package.

pip install pymysql

Then we will create a python class to operate mysql.

Create a python class named MySQLUtil

We will add select, insert, update and delete operations in this python class.

The basic of MySQLUtil is:

#-*- coding: UTF-8 -*- 
import pymysql

class MySQLUtil():
    pass

Connect mysql in python

To operate mysql in python, we should connect it first. Here is an example:

    def connectDB(self, host, user, psw, db_name, charset='utf8'):
        self.db = pymysql.connect(host=host, user=user, password=psw, db= db_name, charset=charset)

Parameters are:

host: the ip address of mysql, such as localhost

user: the user name of mysql, such as root

psw: the password of a user

db_name: the database you plan to operate in mysql

charset: the charset of data in db_name, this parameter determines how to save data into db_name

Close mysql connection

If you do not operate mysql, you should close the connect.

    def colseDB(self):
        self.db.close()

Select data from mysql

Select is a basic operation in mysql. In order to select data from mysql using python, we haved created a function.

    def execQuery(self, sql):
        try:
            # execute sql statement
            cursor = self.db.cursor()
            cursor.execute(sql)
            # get all rows in mysql
            results = cursor.fetchall()
            return results
        except:
            print ("Error: unable to fecth data")  
            return None

sql is the select statement.

Update, insert and delete data from mysql

We can create a function to mange these three operations. Here is an example:

    def execSql(self, sql):
        #sql is insert, delete or update statement
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            # commit sql to mysql
            self.db.commit()
            cursor.close()
            return True
        except:
            self.db.rollback()
        return False

How to use python MySQLUtil class?

We will implement these four basic operations to show you how to use.

1.We should create a MySQLUtil  instance to connect mysql

mysql = MySQLUtil()
mysql.connectDB(host ='localhost', user = 'root', psw = '', db_name = 'enbook')

mysql is the instance of python MySQLUtil class.

2.Select data from a database ‘enbook‘ in mysql

We only need to provide a select sql statement to query data.

sql = 'select book_id, book_name from book limit 0,5'
books = mysql.execQuery(sql)
print(books)

The result is:

((1, 'Performance Ford - September 2013'), (2, "Hustler's Taboo August 2013 (USA)"), (3, 'Retro Ford - September 2013'), (4, 'Dirt Rider - October 2013'), (5, 'Lonely Wives June 2013 (USA)'))

3.Update data in ‘enbook‘

A update sql statement is needed.

sql = 'update book set book_status = 0 where book_id = 2'
mysql.execSql(sql)

4.Insert data into ‘enbook‘

book_title = 'Tutorial Example Ebook'
book_title = pymysql.escape_string(book_title)

sql = "insert into book (book_name) values('" + book_title + "')"
mysql.execSql(sql)

Notice: as to string book_title, we should use pymysql.escape_string() function to escape it.

5.Dete a row in ‘enbook‘

sql =  'delete from book where book_id = 3'
mysql.execSql(sql)

Only a delete sql statement is needed.

6.Close mysql connection

After having operated mysql, you should close the connection.

mysql.colseDB()