python 3.6 +pyMysql 操作mysql数据库(实例讲解)

作者:陈月白 时间:2024-01-19 16:38:39 

版本信息:python:3.6mysql:5.7pyMysql: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)

测试表:

python 3.6 +pyMysql 操作mysql数据库(实例讲解)

执行结果:


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, '李强新', '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

来源:http://www.cnblogs.com/chenyuebai/p/7821996.html

标签:python,3.6,pyMysql,mysql,数据库
0
投稿

猜你喜欢

  • Python pandas DataFrame基础运算及空值填充详解

    2022-01-01 22:19:49
  • Python实现大数据收集至excel的思路详解

    2022-02-03 10:25:25
  • 解决pycharm安装后代码区不能编辑的问题

    2022-07-26 22:26:24
  • Python使用grequests并发发送请求的示例

    2022-11-08 15:38:01
  • 彻底解决页面文字编码乱码问题

    2022-09-17 02:08:49
  • Python+OpenCV实现相机标定的方法详解

    2021-03-10 20:02:30
  • JS重载实现方法分析

    2023-10-07 08:09:04
  • MySQL实现字符串的拼接,截取,替换,查找位置的操作

    2024-01-13 13:17:22
  • python文件路径操作方法总结

    2023-04-30 21:00:15
  • 21行Python代码实现拼写检查器

    2023-10-11 03:33:03
  • Python Celery定时任务详细讲解

    2022-08-12 08:25:21
  • 50个常用sql语句 网上流行的学生选课表的例子

    2012-07-11 16:02:01
  • 讲解Access数据库中数据表的自动重新联接

    2008-11-28 16:29:00
  • 浅谈SQL Server中统计对于查询的影响分析

    2012-06-06 20:08:23
  • OpenCV如何去除图片中的阴影的实现

    2023-07-08 22:49:29
  • javascript中注册和移除事件的4种方式

    2024-04-22 12:49:02
  • jquery validate.js表单验证的基本用法入门

    2023-07-02 05:30:47
  • vue中的Object.freeze() 优化数据方式

    2024-04-10 16:10:25
  • JS性能优化笔记搜索整理

    2024-04-17 09:54:55
  • 18个帮你简化代码的Python技巧分享

    2021-08-12 02:41:12
  • asp之家 网络编程 m.aspxhome.com