python操作mysql
1. PyMySQL连接数据库操作
1. 安装 PyMySQL
什么是 PyMySQL？
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
安装PyMySQL
C:\Users\liqiang>e: E:\>cd E:\pythonWorkSpace\FirstProject\venv\Scripts E:\pythonWorkSpace\FirstProject\venv\Scripts>pip3 install PyMySQL
2. 进行数据库连接
1. 连接数据库插卡可能版本
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL 查询 cursor.execute("SELECT VERSION()") # 使用 fetchone() 方法获取单条数据. data = cursor.fetchone() print("Database version : %s " % data) # 关闭数据库连接 db.close()
结果:
Database version : 5.7.10-log
2.数据库进行插入操作
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = "INSERT INTO user" \ "(id, name ) \ VALUES (%s, '%s')" % \ (3, 'Mac') try: # 执行sql语句 cursor.execute(sql) # 执行sql语句 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
3. 数据库查询操作
Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
fetchone(): 该方法获取下一个查询结果集。结果集是一个对象。fetchone()函数它的返回值是单个的元组,也就是一行记录,如果没有结果,那就会返回null。
fetchall(): 接收全部的返回结果行。fetchall()函数,它的返回值是多个元组,即返回多个行记录,如果没有结果,返回的是()。
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
(1)查询ID等于1的数据
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM user \ WHERE id = %s" % (1) try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchone() # 打印结果 print("id=%s,name=%s" % \ (results[0], results[1])) except: print("Error: unable to fetch data") # 关闭数据库连接 db.close()
结果:
id=1,name=zhangsan
(2)查询ID大于1的数据
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "SELECT * FROM user \ WHERE id > %s" % (1) try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: id = row[0] name = row[1] # 打印结果 print("id=%s,name=%s" % \ (id, name)) total = cursor.rowcount print("总数: %s" % \ (total)) except: print("Error: unable to fetch data") # 关闭数据库连接 db.close()
结果:
id=2,name=lisi
id=3,name=Mac
4. 执行修改操作:
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "UPDATE user SET name = '%s' WHERE id = %s" % ('wangwu', 3) try: # 执行SQL语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
5.删除操作
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 sql = "delete from user WHERE id = %s" % ( 3) try: # 执行SQL语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # 发生错误时回滚 db.rollback() # 关闭数据库连接 db.close()
补充:PyMySQL查询数据库并映射为dict类型数据:
#!/usr/bin/python3 import pymysql # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor(cursor = pymysql.cursors.DictCursor) # SQL 查询语句 sql = "SELECT * FROM user" try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for result in results: # 打印结果 print("result: %s" % (result)) except: print("Error: unable to fetch data") # 关闭数据库连接 db.close()
结果:
result: {'id': 1, 'name': 'zs', 'userName': 'zhangsan'}
result: {'id': 2, 'name': 'li', 'userName': 'lisi'}
result: {'id': 3, 'name': 'ww', 'userName': 'wangwu'}
2. Python MySQL - mysql-connector 驱动
mysql-connector 是 MySQL 官方提供的驱动器。我们可以使用 pip 命令来安装 mysql-connector：
python -m pip install mysql-connector
第一种: 检测是否安装成功:
import mysql.connector
执行以上代码，如果没有产生错误，表明安装成功。
第二种验证方式:查看模块 (pip list 可以查看python安装的模块)
C:\Users\Administrator>pip list | findstr mysql
mysql-connector 2.2.9
1.连接数据库查看版本:
import mysql.connector # 打开数据库连接 db = mysql.connector.connect( host="localhost", # 数据库主机地址 user="root", # 数据库用户名 passwd="123456" # 数据库密码 ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL 查询 cursor.execute("SELECT VERSION()") # 使用 fetchone() 方法获取单条数据. data = cursor.fetchone() print("Database version : %s " % data) # 关闭数据库连接 db.close()
结果:
Database version : 5.7.10-log
2. 连接到mysql指定的库:
import mysql.connector # 打开数据库连接 db = mysql.connector.connect( host="localhost", # 数据库主机地址 user="root", # 数据库用户名 passwd="123456", # 数据库密码 database="Exam9" # 链接指定的库，库不存在会报错 ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 execute() 方法执行 SQL 查询 cursor.execute("SELECT VERSION()") # 使用 fetchone() 方法获取单条数据. data = cursor.fetchone() print("Database version : %s " % data) # 关闭数据库连接 db.close()
补充:手动安装模块pymysql
模块是一个包含所有你定义的函数和变量的文件，其后缀名是.py。模块可以被别的程序引入，以使用该模块中的函数等功能。这也是使用 python 标准库的方法。(模块类似于Java的class文件)
(1)到git上下载对应模块: (一般模块都能找到)
https://github.com/PyMySQL/PyMySQL
(2)解压之后，进入到目录下面 会有setup.py
(3) 进行安装:
Administrator@MicroWin10-1535 MINGW64 ~/Desktop/pytest/新建文件夹/新建文件夹/PyMySQL-0.9.3
$ pip setup.py install
(4)测试: 引入模块不报错即安装成功
import pymysql
补充:一个例子，python读取数据库，并读取url、method、param去访问请求，最后将结果记录输出到html中:
#!/usr/bin/python3 import pymysql from urllib import request import urllib.parse import chardet import json # 访问请求的方法 def requestUrl(result): url = str(result['url']); method = str(result['method']); data = str(result['param']); if url is None or method is None: return; if data is not None: data = str(data); data = data.replace("form=" , ""); # 去掉form= #数组参数处理 if data.startswith('[') and data.endswith(']'): datas = json.loads(data); if len(datas) > 0: data = json.dumps(datas[0]) else : data = '{"time": 1}'; elif "{}" == data or "" == data: data = '{"time": 1}'; else: data = '{"time": 1}'; try: # POST请求 if 'POST' in method: # 将序列化后的字符串转换成二进制数据，因为post请求携带的是二进制参数 last_data = bytes(data, encoding='utf-8'); response = urllib.request.urlopen(url, data=last_data); responseResult = response.read().decode('utf-8') result['responseResult'] = responseResult else: data_string=urllib.parse.urlencode(data); new_url = url + "?" + data_string; response=urllib.request.urlopen(new_url) responseResult = response.read().decode('utf-8') result['responseResult'] = responseResult except Exception as e: result['responseResult'] = "error,原因: " + str(e) # 输出爬取到的数据到本地磁盘中 def out_html(datas): if datas is None: return; file = open('D:\\out.html', 'w', encoding='utf-8') file.write("<html>") file.write(r''' <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> '''); file.write("<head>") file.write("<title>爬取结果</title>") # 设置表格显示边框 file.write(r''' <style> table{width:100%;table-layout: fixed;word-break: break-all; word-wrap: break-word;} table td{border:1px solid black;width:300px} </style> ''') file.write("</head>") file.write("<body>") file.write("<table cellpadding='0' cellspacing='0'>") # 遍历datas填充到表格中 for data in datas: file.write("<tr>") file.write("<td>%s</td>" % data['interfaceName']) file.write('<td><a href='+str(data['url'])+'>'+str(data['url'])+'</a></td>') file.write("<td>%s</td>" % data['method']) file.write("<td>%s</td>" % data['param']) file.write("<td>%s</td>" % data['responseResult']) file.write("</tr>") file.write("</table>") file.write("</body>") file.write("</html>") #主函数用法 if __name__ == '__main__': # 打开数据库连接 db = pymysql.connect("localhost", "root", "123456", "pycraw") # 使用cursor()方法获取操作游标 cursor = db.cursor(cursor = pymysql.cursors.DictCursor) # SQL 查询语句 sql = "SELECT * FROM interface "; try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for result in results: requestUrl(result); out_html(results); print("处理完成") except Exception as e: print(e); # 关闭数据库连接 db.close()
结果:
【当你用心写完每一篇博客之后,你会发现它比你用代码实现功能更有成就感!】
