概述

版本信息：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):
 """执行单条sql语句"""
 if sqlLine and isinstance(sqlLine,str):
  print("INFO:Now start connect MysqL dababase.")
  connect,cursor = self.connectMysqLDatabase()
  executeResult = ""
  try:
  #游标执行sql
  cursor.execute(sqlLine)
  executeResult = cursor.fetchall() #获取所有执行结果
  cursor.close()    #关闭游标
  connect.commit()   #确认提交
  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):
 """
 批量执行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")
#执行单行sql
ret1 = MysqL.executesqlLine("show databases")
#批量执行
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)

测试表：

执行结果：

ret1 = (('information_schema',),('MysqL',('performance_schema',('sakila',('sys',('testdb',('world',))
---------------------
(('information_schema',))
(('students_info',)
()
((1,'陈月白','male',25,'20176666','1351234'),(2,'王大花D','female',19,'19920816','10086'),(3,'李强新',18,'19941025','10000'),(4,'王鹏',20,'19970405','10010'),(5,'钟齐',22,'19970420','123456789'),(6,'王大花',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数据库(实例讲解)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持编程小技巧。

总结

以上是编程之家为你收集整理的python 3.6 +pyMysql 操作mysql数据库(实例讲解)全部内容，希望文章能够帮你解决python 3.6 +pyMysql 操作mysql数据库(实例讲解)所遇到的程序开发问题。

如果您也喜欢它,动动您的小指点个赞吧