Verbinden von MySQL Datebase in Python mit PyMySQL

View more categories:

1- Was ist PyMySQL?

Um Python in einer Datenbank zu verbinden, brauchen Sie einen Driver (der Kontrolleur). Er ist eine Bibliothek zum Kontakt mit der Datenbank. Mit der Datenbank  MySQL haben Sie 3 Options des Driver wie folgend
  1. MySQL/connector for Python
  2. MySQLdb
  3. PyMySQL
Driver Die Bezeichnung
MySQL/Connector for Python Das ist eine Bibliothek, die von MySQL.gebietet wird
MySQLdb MySQLdb ist eine Bibliothek zum Verbindung ins MySQL aus Python. Es wird auf die Sprache C geschrieben.  Es is umsonst und eine Open Source Code
PyMySQL Das ist eine Bibliothek zur Verbindung ins MySQL vom Python. Sie ist eine ganze Python Bibliothek. Das Ziel von PyMySQL ist der Ersatzt des MySQLdb und auf CPython, PyPy und IronPython arbeiten
PyMySQL ist ein Open Source Code Projekt. Sie können seine Open Source Code hier sehen

2- PyMySQL installieren

Um PyMySQL aufs  Windows (Oder  Ubuntu/Linux) zu installieren, sollen Sie das Fenster  CMD öffnen und den folgenden Befehl durchführen

pip install PyMySQL

3- Die Muster-Database 

"simplehr" ist eine Musterdatenbank. Es wird in vielen Hinweise auf die o7planning benutzt. Ich benutze es auch in diesem Dokument. Sie können die Datenbank nach der folgenden Hinweise erstellen
  • TODO Link!

4- MySQL vom Python zum PyMySQL verbinden

Das folgdende einfache Beispiel benutzt  Python um ins  MySQL zu verbinden und die Tabelle Department abzufragen
connectExample.py
import pymysql.cursors  

# In die Database verbinden.
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 "
        
        # Den AbfragenBefehl implementieren (Execute Query).
        cursor.execute(sql)
        
        print ("cursor.description: ", cursor.description)

        print()

        for row in cursor:
            print(row)
            
finally:
    # Die Verbindung schließen (Close connection).      
    connection.close()
Das Ergebnis des Beispiel durchführen:
Die Utility Module 
Eine Vorschlage: Sie sollen eine Utility module um eine Verbindung mit der Datenbank zu erstellen. Hier erstelle ich eine Module mit dem Name von "myconnutils". Die Module definiert die Funktion getConnection() und gibt eine  connection.rück
myconnutils.py
import pymysql.cursors  

# Die Funktion gibt eine Verbindung zurück.
def getConnection():
    
    # Sie können die Verbindungsparameter ändern
    connection = pymysql.connect(host='192.168.5.129',
                                 user='root',
                                 password='1234',                             
                                 db='simplehr',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    return connection

5- Zum Beispiel Query

Das folgende Beispiel frägt die Tabelle  Employee ab, Python benutzt  %s als einen Platzhalter (placeholder) für die Parameter. Es hängt von dem Typ des Parameter nicht ab. Zum Beispiel

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

sql2 = "Select * from Employee Where Dept_Id = %s "
queryExample.py
# Ihre Utility Module benutzen.
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 implementieren und einen Parameter anweisen
    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:
    # Die Verbindung schließen
    connection.close()

6- Zum Beispiel Insert

insertExample.py
# Ihre Utility Module benutzen.
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)
    
    # Eine Datenlinie
    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 implementieren und 3 Parameter anweisen
    cursor.execute(sql, (grade, 2000, 1000 ) )
    
    connection.commit() 

finally: 
    connection.close()

7- Zum Beispiel Update

updateExample.py
# Ihre Utility Module benutzen.
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 implementieren und 3 Parameter anweisen
    rowCount = cursor.execute(sql, (850, newHireDate, 7369 ) )
    
    connection.commit() 
    
    
    print ("Updated! ", rowCount, " rows")

finally:
    # Die Verbindung schließen 
    connection.close()

8- Zum Beispiel Delete

deleteExample.py
# Ihre Utility Module benutzen.
import myconnutils 

connection = myconnutils.getConnection()

print ("Connect successful!")



try :
    cursor = connection.cursor()
    
    sql = "Delete from Salary_Grade where Grade = %s"  
    
    # Sql implementieren und 3 Parameter anweisen
    rowCount = cursor.execute(sql, ( 3 ) )
    
    connection.commit() 
    
    
    print ("Deleted! ", rowCount, " rows")

finally:
    # Die Verbindung schließen  
    connection.close()

9- Die Aufruf von dem Verfahren

Es gibt einige Problem wenn Sie auf einer Funktion (function) oder einem Verfahren (procedure) im  Python aufrufen. Ich stelle eine Situation wie folgend:
Sie haben einen Verfahren
  • 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
Der Verfahren hat 1 Input-Parameter p_Emp_Id und 4 Output-Parameter v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date. Sie rufen einen Verfahren aus   Python auf und weisen die Wert für p_Emp_Id zu um die 4 Output-Parameter zu nehmen. Leider ist die erhaltete Wert nicht sicher richtige (das wird klar in der Bezeichnung von DB-API erwähnt). Python kann die Wert aus den Absatz   SELECT nehmen
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.
    """
Allerdings können Sie die oben gemeinte Frage lösen, sollen Sie den Verfahren  Get_Employee_Info in einen anderen Verfahren einpacken (wrap) (wie  Get_Employee_Info_Wrap). Der Verfahren gibt die Wert durch den Absatz SELECT (Select clause) zurück.
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
Statt der Aufruf auf den Verfahren Get_Employee_Info im  Python rufen Sie den Verfahren Get_Employee_Info_Wrap.
callProcedureExample.py
# Ihre Utility Module benutzen.
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:
    # Die Verbindung schließen
    connection.close()
Das Beispiel durchführen

10- Die Aufruf der Funktion

Um auf die Funktion (function) im  Python aufzurufen,sollen Sie einen Abfragensatz (query clause) erstellen und den Befehl zum Abfragen implementieren
Unten ist die Funktion  Get_Emp_No, der Inputparameter ist p_Emp_Id und die Rückgabe von  Emp_No (die Kode des Mitarbeiter).
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
# Ihre Utility Modul benutzen.
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:
    # Die Verbindung schließen (Close connection).    
    connection.close()
Das Beispiel durchführen

View more categories: