Kết nối MySQL Database trong Python sử dụng PyMySQL

1- PyMySQL là gì?

Để kết nối từ Python vào một cơ sở dữ liệu bạn cần một Driver (bộ điều khiển), nó là một thư viện dùng để nói chuyện với cơ sở dữ liệu. Với cơ sở dữ liệu MySQL bạn có 3 sự lựa chọn Driver như vậy:
  1. MySQL/connector for Python
  2. MySQLdb
  3. PyMySQL
Driver Mô tả
MySQL/Connector for Python Đây là một thư viện được cung cấp bởi chính cộng đồng MySQL.
MySQLdb MySQLdb là thư viện giúp kết nối vào MySQL từ Python, nó được viết trên ngôn ngữ C, nó được cung cấp miễn phí và là mã nguồn mở.
PyMySQL Đây là một thư viện giúp kết nối vào MySQL từ Python, là một thư viện thuần Python. Mục tiêu của PyMySQL là thay thế cho MySQLdb và làm việc trên CPython, PyPyIronPython.
PyMySQL là một dự án mã nguồn mở, và mã nguồn của nó bạn có thể xem tại đây:

2- Cài đặt PyMySQL

Để cài đặt PyMySQL trên Windows (Hoặc Ubuntu/Linux) bạn cần mở cửa sổ CMD, và thực thi lệnh sau:

pip install PyMySQL

3- Database mẫu

"simplehr" là một cơ sở dữ liệu mẫu, được sử dụng trong nhiều hướng dẫn trên o7planning. Trong bài viết này tôi cũng sử dụng nó. Bạn có thể tạo cơ sở dữ liệu này theo hướng dẫn dưới đây:

4- Kết nối MySQL từ Python với PyMySQL

Ví dụ đơn giản sau sử dụng Python kết nối vào MySQL và truy vấn bảng Department:
connectExample.py
import pymysql.cursors


# Kết nối vào database
connection = pymysql.connect(host='192.168.5.134',
                             user='root',
                             password='1234',                            
                             db='simplehr',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

print ("connect successful!!")

try:


    with connection.cursor() as cursor:
        # SQL
        sql = "SELECT Dept_No, Dept_Name FROM Department "

        # Thực thi câu lệnh truy vấn.
        cursor.execute(sql)
      
        print ("cursor.description: ", cursor.description)

        print()

        for row in cursor:
            print(row)
          
finally:
    connection.close()
Kết quả chạy ví dụ:

Module tiện ích:

Một lời khuyên là bạn nên tạo ra một module tiện ích để tạo một kết nối tới cơ sở dữ liệu. Ở đây tôi tạo một module có tên "myconnutils", module này định nghĩa hàm getConnection() trả về một connection.
myconnutils.py
import pymysql.cursors
 
# Hàm trả về một connection
def getConnection():
 
    # Bạn có thể thay đổi các thông số kết nối.
    connection = pymysql.connect(host='192.168.5.134',
                                 user='root',
                                 password='1234',                            
                                 db='simplehr',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection
 

5- Ví dụ Query

Ví dụ sau đây truy vấn bảng Employee, Python sử dụng %s như là một "nơi giữ chỗ" (placeholder) cho các tham số, nó không phụ thuộc kiểu của tham số. Ví dụ:

sql1 = "Insert into Department (Dept_Id, Dept_No, Dept_Name) values (%s, %s, %s) "

sql2 = "Select * from Employee Where Dept_Id = %s "
queryExample.py
# Sử dụng module tiện ích của bạn.
import myconnutils


connection = myconnutils.getConnection()

print ("Connect successful!")

sql = "Select Emp_No, Emp_Name, Hire_Date from Employee Where Dept_Id = %s "

try :
    cursor = connection.cursor()
 
    # Thực thi sql và truyền 1 tham số
    cursor.execute(sql, ( 10 ) )
    
    print ("cursor.description: ", cursor.description)
    
    print()
    
    for row in cursor:
        print (" ----------- ")
        print("Row: ", row)
        print ("Emp_No: ", row["Emp_No"])
        print ("Emp_Name: ", row["Emp_Name"])
        print ("Hire_Date: ", row["Hire_Date"] , type(row["Hire_Date"]) )

finally:
    # Đóng kết nối
    connection.close()    
   

6- Ví dụ Insert

insertExample.py
# Sử dụng module tiện ích của bạn.
import myconnutils
import pymysql.cursors


connection = myconnutils.getConnection()

print ("Connect successful!")



try :
    cursor = connection.cursor()
  
    sql = "Select max(Grade) as Max_Grade from Salary_Grade "
    cursor.execute(sql)

    # 1 dòng dữ liệu
    oneRow = cursor.fetchone()
    
    # Output: {'Max_Grade': 4} or {'Max_Grade': None}
    print ("Row Result: ", oneRow)
  
    grade = 1
  
    if oneRow != None and oneRow["Max_Grade"] != None:
        grade = oneRow["Max_Grade"] + 1
  
    cursor = connection.cursor()
  
  
    sql =  "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) " \
         + " values (%s, %s, %s) "
  
    print ("Insert Grade: ", grade)

    # Thực thi sql và truyền 3 tham số
    cursor.execute(sql, (grade, 2000, 1000 ) )
  
    connection.commit()

finally:
    # Đóng kết nối  
    connection.close()      

7- Ví dụ Update

updateExample.py
# Sử dụng module tiện ích của bạn.
import myconnutils
import pymysql.cursors
import datetime


connection = myconnutils.getConnection()

print ("Connect successful!")

try :
    cursor = connection.cursor()
  
    sql = "Update Employee set Salary = %s, Hire_Date = %s where Emp_Id = %s "
  
    # Hire_Date
    newHireDate = datetime.date(2002, 10, 11)

    # Thực thi sql và truyền 3 tham số
    rowCount = cursor.execute(sql, (850, newHireDate, 7369 ) )
  
    connection.commit() 
  
    print ("Updated! ", rowCount, " rows")

finally:
    # Đóng kết nối  
    connection.close()      

8- Ví dụ Delete

deleteExample.py
# Sử dụng module tiện ích của bạn.
import myconnutils

connection = myconnutils.getConnection()

print ("Connect successful!")



try :
    cursor = connection.cursor()
  
    sql = "Delete from Salary_Grade where Grade = %s"
  
 
    # Thực thi sql và truyền 1 tham số
    rowCount = cursor.execute(sql, ( 3 ) )
  
    connection.commit()
  
  
    print ("Deleted! ", rowCount, " rows")

finally:
    # Đóng kết nối  
    connection.close()      

9- Gọi Thủ tục

Có một vài vấn đề khi bạn gọi một hàm (function) hoặc một thủ tục (procedure) trong Python. Tôi đặt ra môt tình huống như sau:
Bạn có một thủ tục:
  • Get_Employee_Info(p_Emp_Id, v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date)
get_Employee_Info
DELIMITER $$

-- Thủ tục này lấy ra thông tin của nhân viên
-- Đầu vào: p_Emp_ID (Integer)
-- Có 4 tham số đầu ra v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date

CREATE PROCEDURE get_Employee_Info(p_Emp_ID     Integer,
                                 out       v_Emp_No        Varchar(50) ,
                                 out       v_First_Name    Varchar(50) ,
                                 Out       v_Last_name    Varchar(50) ,
                                 Out       v_Hire_date      Date)
BEGIN
set v_Emp_No  = concat( 'E' , Cast(p_Emp_Id as char(15)) );
--
set v_First_Name = 'Michael';
set v_Last_Name  = 'Smith';
set v_Hire_date  = curdate();
END
Thủ tục trên có 1 tham số đầu vào p_Emp_Id và 4 tham số đầu ra v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date, bạn gọi một thủ tục này từ Python truyền vào giá trị cho p_Emp_Id để lấy ra 4 giá trị đầu ra. Thật đáng tiếc giá trị nhận được không được đảm bảo đúng (Điều này được nói rõ trong đặc tả DB-API). Python chỉ có thể lấy ra các giá trị từ một mệnh đề SELECT.
Đặc tả DB-API:
def callproc(self, procname, args=()):
    """Execute stored procedure procname with args

    procname -- string, name of procedure to execute on server

    args -- Sequence of parameters to use with procedure

    Returns the original args.

    Compatibility warning: PEP-249 specifies that any modified
    parameters must be returned. This is currently impossible
    as they are only available by storing them in a server
    variable and then retrieved by a query. Since stored
    procedures return zero or more result sets, there is no
    reliable way to get at OUT or INOUT parameters via callproc.
    The server variables are named @_procname_n, where procname
    is the parameter above and n is the position of the parameter
    (from zero). Once all result sets generated by the procedure
    have been fetched, you can issue a SELECT @_procname_0, ...
    query using .execute() to get any OUT or INOUT values.

    Compatibility warning: The act of calling a stored procedure
    itself creates an empty result set. This appears after any
    result sets generated by the procedure. This is non-standard
    behavior with respect to the DB-API. Be sure to use nextset()
    to advance through all result sets; otherwise you may get
    disconnected.
    """
Tuy vậy bạn vẫn có thể giải quyết được vấn đề ở trên, bạn cần phải bao bọc (wrap) thủ tục Get_Employee_Info ở trên bởi một thủ tục khác (Chẳng hạn Get_Employee_Info_Wrap), thủ tục này trả về các giá tri thông qua mệnh đề SELECT (Select clause).
get_Employee_Info_Wrap
DROP procedure IF EXISTS `get_Employee_Info_Wrap`;

DELIMITER $$

-- Thủ tục này bao bọc thủ tục Get_Employee_Info

CREATE PROCEDURE get_Employee_Info_Wrap(p_Emp_ID     Integer,
                                   out       v_Emp_No        Varchar(50) ,
                                   out       v_First_Name    Varchar(50) ,
                                   Out       v_Last_name    Varchar(50) ,
                                   Out       v_Hire_date      Date)
BEGIN

Call get_Employee_Info( p_Emp_Id, v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date);

-- SELECT

Select v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date;


END
Thay vì gọi thủ tục Get_Employee_Info trong Python, hãy gọi thủ tục Get_Employee_Info_Wrap.
callProcedureExample.py
 
# Sử dụng module tiện ích của bạn.
import myconnutils
import datetime


connection = myconnutils.getConnection()

print ("Connect successful!")



try :
    cursor = connection.cursor()
    
    # Get_Employee_Info_Wrap              
    # @p_Emp_Id       Integer ,
    # @v_Emp_No       Varchar(50)   OUTPUT
    # @v_First_Name   Varchar(50)   OUTPUT
    # @v_Last_Name    Varchar(50)   OUTPUT
    # @v_Hire_Date    Date          OUTPUT  
    
    v_Emp_No = ""
    v_First_Name= ""
    v_Last_Name= ""
    v_Hire_Date = None
    
    inOutParams = ( 100, v_Emp_No, v_First_Name , v_Last_Name, v_Hire_Date )
    
    resultArgs = cursor.callproc("Get_Employee_Info_Wrap" , inOutParams   )

    
    print ('resultArgs:', resultArgs )
    print ( 'inOutParams:', inOutParams )
    
    print (' ----------------------------------- ')
    
    for row in cursor:
        print('Row: ',  row )
        print('Row[v_Emp_No]: ',  row['v_Emp_No'] )
        print('Row[v_First_Name]: ',  row['v_First_Name'] )
        print('Row[v_Last_Name]: ',  row['v_Last_Name'] )
        
        # datetime.date
        v_Hire_Date =  row['v_Hire_Date']
        print('Row[v_Hire_Date]: ', v_Hire_Date )
  

finally:
    # Đóng kết nối    
    connection.close()      
Chạy ví dụ:

10- Gọi Hàm

Để gọi hàm (function) trong Python, bạn nên tạo một câu truy vấn (query clause), và thực thi câu lệnh truy vấn này.
Dưới đây là hàm Get_Emp_No, tham số đầu vào là p_Emp_Id và trả về Emp_No (Mã nhân viên).
Get_Emp_No
DROP function  if Exists `Get_Emp_No`;
 
DELIMITER $$

CREATE Function Get_Emp_No (p_Emp_Id  Integer) Returns Varchar(50)
Begin    

   return  concat('E', CAST(p_Emp_Id  as  char)) ;
  
END;
callFunctionExample.py
# Sử dụng module tiện ích của bạn.
import myconnutils
import datetime


connection = myconnutils.getConnection()

print ("Connect successful!")



try :
    cursor = connection.cursor()
    
    # Get_Employee_Info_Wrap              
    # @p_Emp_Id       Integer  
    
    v_Emp_No = ""
    
    inOutParams = ( 100 )
    
    sql = "Select Get_Emp_No(%s) as Emp_No "
    
    cursor.execute(sql, ( 100 ) )

    
    print (' ----------------------------------- ')
    
    for row in cursor:
        print('Row: ',  row )
        print('Row[Emp_No]: ',  row['Emp_No'] )
  

finally:
    # Đóng kết nối    
    connection.close()      
Chạy ví dụ: