python 3.6 +pyMysql 操作mysql數(shù)據(jù)庫(實(shí)例講解)

更新時(shí)間：2017年12月20日 14:10:34 作者：陳月白

下面小編就為大家分享一篇python 3.6 +pyMysql 操作mysql數(shù)據(jù)庫的實(shí)例講解，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

版本信息：python:3.6　　mysql:5.7　　pyMysql:0.7.11

################################################################# 
#author: 陳月白 
#_blogs: http://www.cnblogs.com/chenyuebai/ 
################################################################# 
# -*- coding: utf-8 -*-
class MysqlTools():
 """
 連接mysql
 庫、表操作
 """
 def __init__(self,host,dbname,user,passwd,charset="utf8"):
 self.host = host
 self.dbname = dbname
 self.user = user
 self.passwd = passwd
 self.charset = charset

 def connectMysqlDatabase(self):
 """連接db"""
 try:
  #連接db
  connect = 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.dbname
  print("INFO:connect database %s success."%databaseConnectInfo)
  return connect,cursor
 except:
  traceback.print_exc()
  print("ERROR:FUNCTION connectMysqlDatabase connect mysql database failed.")

 def executeSqlLine(self,sqlLine):
 """執(zhí)行單條sql語句"""
 if sqlLine and isinstance(sqlLine,str):
  print("INFO:now start connect mysql dababase.")
  connect,cursor = self.connectMysqlDatabase()
  executeResult = ""
  try:
  #游標(biāo)執(zhí)行sql
  cursor.execute(sqlLine)
  executeResult = cursor.fetchall() #獲取所有執(zhí)行結(jié)果
  cursor.close()    #關(guān)閉游標(biāo)
  connect.commit()   #確認(rèn)提交
  print("INFO:execute sql sucess. sqlLine = ", sqlLine)
  except Exception as e:
  print("ERROR：execute sql failed.errorInfo =",e)
  print("ERROR:FUNCTION executeSql execute failed.sqlLine =",sqlLine)
  connect.rollback()   #回滾db
  return str(e) + " sqlLine = " + sqlLine
  #斷開連接
  connect.close()
  print("INFO:connect closed.\n")
  return executeResult
 else:
  print("ERROR:param sqlLine is empty or type is not str.sqlLine = ",sqlLine)

 def executeBatchSql(self,sqlList):
 """
 批量執(zhí)行sql
 exp: executeBatchSql([sql_1,
    sql_2,
    sql_3,
    ......
    ])
 """
 finalResultList = []
 if sqlList:
  for sql in sqlList:
  executeResult = self.executeSqlLine(sql)
  finalResultList.append(executeResult)
 else:
  print("ERROR:param sqlList is empty.")
 return finalResultList

測試代碼：

# -*- coding: utf-8 -*-
from my_code.work_tools import WorkTools
mysql = WorkTools.MysqlTools("localhost","testdbname","rootuername","passwd")
#執(zhí)行單行sql
ret1 = mysql.executeSqlLine("show databases")
#批量執(zhí)行
ret2 = mysql.executeBatchSql([
       "show databases",
       "show tables",
       "update students_info set name = '王大花D' where id = 2",
       "select * from students_info",
       "error sql test"　　#異常sql測試
        ])
print("ret1 = ",ret1)
print("---------------------")
for i in ret2:
 print(i)

測試表：

執(zhí)行結(jié)果：

ret1 = (('information_schema',), ('mysql',), ('performance_schema',), ('sakila',), ('sys',), ('testdb',), ('world',))
---------------------
(('information_schema',), ('mysql',), ('performance_schema',), ('sakila',), ('sys',), ('testdb',), ('world',))
(('students_info',),)
()
((1, '陳月白', 'male', 25, '20176666', '1351234'), (2, '王大花D', 'female', 19, '19920816', '10086'), (3, '李強(qiáng)新', 'male', 18, '19941025', '10000'), (4, '王鵬', 'male', 20, '19970405', '10010'), (5, '鐘齊', 'male', 22, '19970420', '123456789'), (6, '王大花', 'female', 15, '19981024', '12345678'))
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'error sql test' at line 1") sqlLine = error sql test

以上這篇python 3.6 +pyMysql 操作mysql數(shù)據(jù)庫(實(shí)例講解)就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: