概述
版本信息: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数据库(实例讲解)所遇到的程序开发问题。
如果您也喜欢它,动动您的小指点个赞吧