Подключиться к базе данных MySQL на Python с помощью PyMySQL

Что такое PyMySQL?
Установка PyMySQL
Модель Database
Подключить MySQL из Python с PyMySQL
Пример Query
Пример Insert
Пример Update
Пример Delete
Вызов процедуры
Вызов функции

1. Что такое PyMySQL?

Чтобы подключиться из Python в некоторые базы данных вам нужен Driver (Драйвер), это библиотека используемая для контакта с базой данных. С базой данных MySQL у вас есть три выбора приведенных ниже:

MySQL/connector for Python
MySQLdb
PyMySQL

Driver	Описание
MySQL/Connector for Python	Библиотека, предоставленная самим сообществом MySQL.
MySQLdb	MySQLdb это библиотека позволяющая подключиться в MySQL с Python, она написана на языке С, оно бесплатна в использовании и является открытым исходным кодом.
PyMySQL	Это библиотека позволяющая подключиться к MySQL с Python, и яляется чистой библиотекой Python. Цель PyMySQL это замена MySQLdb и работает на CPython, PyPy и IronPython.

PyMySQL это проект открытых исходных кодов, и его исходный код вы можете посмотреть ниже:
https://github.com/PyMySQL/PyMySQL

2. Установка PyMySQL

Чтобы установить PyMySQL на Windows (или Ubuntu/Linux) откройте окно CMD и выполните следующую команду:

pip install PyMySQL

3. Модель Database

"simplehr" это модель базы данных, используемая во многих руководствах на o7planning. В этой статье я тоже использую ее. Вы можете создать собственную базу данных с помощью инструкции по ссылке:

Пример базы данных

4. Подключить MySQL из Python с PyMySQL

Пример подключения к MySQL с помощью Python и запросить таблицу Department:

connectExample.py

import pymysql.cursors   
# Подключиться к базе данных.
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 " 
        # Выполнить команду запроса (Execute Query).
        cursor.execute(sql) 
        print ("cursor.description: ", cursor.description) 
        print() 
        for row in cursor:
            print(row) 
finally:
    # Закрыть соединение (Close connection).      
    connection.close()

Результат примера:

connect successful!!
cursor.description: (('Dept_No', 253, None, 80, 80, 0, False), ('Dept_Name', 253, None, 1020, 1020, 0, False))

{'Dept_No': 'D10', 'Dept_Name': 'ACCOUNTING'}
{'Dept_No': 'D20', 'Dept_Name': 'RESEARCH'}
{'Dept_No': 'D30', 'Dept_Name': 'SALES'}
{'Dept_No': 'D40', 'Dept_Name': 'OPERATIONS'}

Утилитарный модуль:

Советую создать утилитарный module чтобы создать подключение к базе данных. Здесь я создал module с названием "myconnutils", этот модуль определяет функцию getConnection() возвращающую connection.

myconnutils.py

import pymysql.cursors   
# Функция возвращает connection.
def getConnection(): 
    # Вы можете изменить параметры соединения.
    connection = pymysql.connect(host='192.168.5.129',
                                 user='root',
                                 password='1234',                             
                                 db='simplehr',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection

5. Пример Query

Следующий пример - это запрос таблицы Employee , Python использует %s как очередь (placeholder) для параметров, он не зависит от вида параметров. Например:

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

sql2 = "Select * from Employee Where Dept_Id = %s "

queryExample.py

# Использовать ваш утилитарный модуль.
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() 
    # Выполнить sql и передать 1 параметр.
    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:
    # Закрыть соединение
    connection.close()

6. Пример Insert

insertExample.py

# Использовать ваш утилитарный модуль.
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 строка данных
    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)  
    # Выполнить sql и передать 3 параметра.
    cursor.execute(sql, (grade, 2000, 1000 ) ) 
    connection.commit()  
finally: 
    connection.close()

Output:

connect successful!!
Row Result: {'Max_Grade': 2}
Insert Grade: 3

7. Пример Update

updateExample.py

# Использовать ваш утилитарный модуль.
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) 
    # Выполнить sql и передать 3 параметра.
    # 
    rowCount = cursor.execute(sql, (850, newHireDate, 7369 ) ) 
    connection.commit()  
    print ("Updated! ", rowCount, " rows") 
finally:
    # Закрыть соединение
    connection.close()

Output:

connect successful!
Update! 1 rows

8. Пример Delete

deleteExample.py

# Использовать ваш утилитарный модуль.
import myconnutils 

connection = myconnutils.getConnection() 
print ("Connect successful!")  
try :
    cursor = connection.cursor() 
    sql = "Delete from Salary_Grade where Grade = %s"  
    
    # Выполнить sql и передать 1 параметр.
    rowCount = cursor.execute(sql, ( 3 ) ) 
    connection.commit()  
    print ("Deleted! ", rowCount, " rows") 
finally:
    # Закрыть соединение
    connection.close()

Output:

connect successful!
Deleted! 1 rows

9. Вызов процедуры

Существует неторые проблемы, когда вы вызываете функцию (function) или процедуру (procedure) в Python. Я даю следующую ситуацию:

You have a procedure:

Get_Employee_Info(p_Emp_Id, v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date)

get_Employee_Info

DELIMITER $$

-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters 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

Процедура выше имеет 1 входной параметр p_Emp_Id и 4 выходных параметра v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date, вы вызываете эту процедуру из Python передаете значение в p_Emp_Id, чтобы получить 4 выходных значения. К сожалению полученное значение не гарантирован быть правильным (Этот пункт описан подробнее в спецификации DB-API). Python может только получиь значения из пункта SELECT.

DB-API specification:

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.
    """

При этом вы все еще можете разрешить проблему выше, вам нужно обернуть (wrap) процедуру Get_Employee_Info выше другой процедурой (Например Get_Employee_Info_Wrap), эта процедура возвращает значения через пункт SELECT (Select clause).

get_Employee_Info_Wrap

DROP procedure IF EXISTS `get_Employee_Info_Wrap`;

DELIMITER $$

-- This procedure wrap 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

Вместо вызова процедуры Get_Employee_Info в Python, вызовите процедуру Get_Employee_Info_Wrap.

callProcedureExample.py

# Использовать ваш утилитарный модуль.
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:
    # Закрыть соединение.
    connection.close()

Запуск примера:

connect successful!
resultArgs: (100, '', '', '', None)
inOutParams: (100, '', '', '', None)
 -----------------------------------
Row: {'v_Emp_No': 'E100', 'v_First_Name': 'Michael', 'v_Last_Name': 'Smith', 'v_Hire_Date': datetime.date(2017, 5, 17)}
Row[v_Emp_No]: E100
Row[v_First_Name]: Michael
Row[v_Last_Name]: Smith
Row[v_Hire_Date]: 2017-05-17

10. Вызов функции

Чтобы вызвать функцию (function) в Python, вам стоит создать запрос(query clause), и выполнить данную команду запроса.

Ниже является функция Get_Emp_No, входной параметр это p_Emp_Id и возвращает Emp_No (Код сотрудника).

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

# Использовать ваш утилитарный модуль.
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:
    # Закрыть соединение (Close connection).    
    connection.close()

Запуск примера:

connect successful!
 -----------------------------------  
Row: {'Emp_No': 'E100'}
Row[Emp_No]: E100

Подключиться к базе данных MySQL на Python с помощью PyMySQL

1. Что такое PyMySQL?

2. Установка PyMySQL

3. Модель Database

4. Подключить MySQL из Python с PyMySQL

5. Пример Query

6. Пример Insert

7. Пример Update

8. Пример Delete

9. Вызов процедуры

10. Вызов функции

Pуководства Python

Pуководства Python

Новейшие статьи