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()