Python 3.6 +pymysql Operation MySQL Database

Version info: python:3.6 mysql:5.7 pymysql:0.7.11

# -*-coding:utf-8-*-

Classmysqltools ():"""Connect to MySQL
Library, table Operations"""    def __init__(self,host,dbname,user,passwd,charset="UTF8"): Self.host=host Self.dbname=dbname Self.user=User self.passwd=passwd Self.charset=CharSetdefconnectmysqldatabase (self):"""Connect DB"""        Try:            #Connect DBConnect = Pymysql.connect (host=self.host,user=self.user,passwd=self.passwd,db=self.dbname,charset=self.charset) Cursor=connect.cursor () databaseconnectinfo= Self.user +"@"+"Self.host"+"/"+Self.dbnamePrint("Info:connect Database%s success."%databaseconnectinfo)returnConnect,cursorexcept: Traceback.print_exc ()Print("error:function connectmysqldatabase connect MySQL database failed.")    defExecutesqlline (self,sqlline):"""execute a single SQL statement"""        ifSqlline andisinstance (SQLLINE,STR):Print("info:now start connect MySQL dababase.") Connect,cursor=self.connectmysqldatabase () Executeresult=""            Try:                #cursor Execution SQLCursor.execute (sqlline) Executeresult= Cursor.fetchall ()#get all execution resultsCursor.close ()#Close CursorsConnect.commit ()#Confirm Submission                Print("Info:execute sql sucess. Sqlline =", Sqlline)exceptException as E:Print("error:execute SQL Failed.errorinfo =", E)Print("error:function executesql Execute failed.sqlline =", Sqlline) connect.rollback ()#Roll Back DB                returnSTR (e) +"Sqlline ="+Sqlline#Disconnect Connectionconnect.close ()Print("Info:connect closed.\n")            returnExecuteresultElse:            Print("Error:param sqlline is empty or type is not str.sqlline =", Sqlline)defExecutebatchsql (self,sqllist):"""bulk execution of SQL Exp:executebatchsql ([Sql_1, Sql_2,        Sql_3, ...]) """finalresultlist= []        ifsqllist: forSqlinchSqllist:executeresult=self.executesqlline (SQL) finalresultlist.append (executeresult)Else:            Print("Error:param sqllist is empty.")        returnFinalresultlist

Test code:

#-*-coding:utf-8-*- fromMy_code.work_toolsImportWorktoolsmysql= Worktools.mysqltools ("localhost","Testdbname","Rootuername","passwd")#execute single-line SQLRet1 = Mysql.executesqlline ("Show Databases")#Batch ExecutionRet2 =Mysql.executebatchsql (["Show Databases",                            "Show Tables",                            "Update students_info Set name = ' Wang Dahua d ' WHERE id = 2",                            "SELECT * from Students_info",                            "Error SQL Test" # exception SQL Test                                ])Print("Ret1 =", Ret1)Print("---------------------") forIinchRet2:Print(i)

Test table:

Execution Result:

Ret1 = ((' Information_schema ',), (' MySQL ',), (' Performance_schema ',), (' Sakila ',), (' sys ',), (' TestDB ',), (' World ',))
---------------------
(' Information_schema ',), (' MySQL ',), (' Performance_schema ',), (' Sakila ',), (' sys ',), (' TestDB ',), (' World ',))
((' Students_info ',),)
()
((1, ' Chen ', ' Male ', 25, ' 20176666 ', ' 1351234 '), (2, ' Wang Dahua d ', ' female ', 19, ' 19920816 ', ' 10086 '), (3, ' Li Qiang new ', ' Male ', 18, ' 19 941025 ', ' 10000 '), (4, ' Wang Peng ', ' Male ', 20, ' 19970405 ', ' 10010 '), (5, ' Zhongji ', ' Male ', 22, ' 19970420 ', ' 123456789 '), (6, ' Wang Dahua ', ' Female ', 15, ' 19981024 ', ' 12345678 ')
(1064, "You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' error SQL Test ' on line 1 ") Sqlline = Error SQL Test

Python 3.6 +pymysql Operation MySQL Database