Python 数据库操作 - PyMySQL
Python 数据库操作 - PyMySQL
数据库说明
数据库(Database,DB)是存放数据的仓库,按照数据结构来组织、存储和管理数据的仓库。按照数据库组织类型可以分为关系型数据库和非关系型数据库。常见关系型数据库有 MySQL、SQL Server、Oracle、DB2 等;常见非关系数据库有Redis(键值对存储)、Hbase(列存储)、MongoDB(文档型数据库)、InfoGrid(图数据库)等。
数据库管理系统(Database Management System,DBMS)是一种操纵和管理数据库的软件,用于建立、使用和维护数据库。数据库管理系统能够提供数据录入、修改、删除、查询操作;具有数据定义、数据操作、数据存储与管理、数据维护、通信等功能,且能够允许多用户使用。常见的数据库管理系统有 MySQL、SQL Server、Oracle、DB2 等;常见非关系数据库有Redis(键值对存储)、Hbase(列存储)、MongoDB(文档型数据库)、InfoGrid(图数据库)等。
PyMySQL
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2 中则使用 mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范,并包含了 pure-Python MySQL 客户端库,本身为开源软件,可以从 github 取得最新版本以及操作说明。
要安装 PyMySQL 之前先确认一下目前的开发环境,下图中展示了整个数据库的运营环境,MySQL 代表著数据库管理系统,而我们所撰写的 Python 程序则为应用系统,当应用系统要存取 MySQL 时,需要有特定的服务器接口,这个接口库就是 PyMySQL,因此要进行数据库操作时,需要事先完成下列事项:
- 安装 MySQL 数据库服务器。
- 创建数据库 TESTDB.
- 创建数据库 TESTDB 管理人员帐号。
- 安装 PyMySQL 模块。
数据库运作图
MySQL 数据库服务器的安装与设定需开发者请自行完成操作。下图显示在命令列安装 PyMySQL 模块,并进入交互模式,导入 PyMySQL 模块并显示版本。
pip3 install pymysql
安装并检验 PyMySQL 模块
创建数据库表
透过 connect() 方法连接数据库,需要提供数据库服务器主机,数据库管理者帐号、密码以及数据库名称;使用 execute() 方法来输入标准的 SQL语句来为数据库创建表,如下所示创建表 students,包含三个字段:学号 (xh)、姓名 (name)、年龄 (age)。
import pymysql
# 打开数据库连接
db = pymysql.connect(host='localhost',
user='testuser',
password='test123',
database='TESTDB')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 方法执行 SQL,如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS students")
# 使用预处理语句创建表
sql = """CREATE TABLE students (
xh INT NOT NULL,
name VARCHAR(20),
age INT)"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
事务机制 (TRANSACTION)
在 Python DB API 2.0 提供了事务机制 (transaction) 来确保数据一致性。事务具有四个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。而实践事务主要是以下两个方法 commit() 和 rollback()。
- 原子性(atomicity):一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
- 一致性(consistency):事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性(isolation):一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(durability):持续性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
数据库插入操作 INSERT
使用执行 SQL INSERT 语句向表 students 插入记录,使用变量向 SQL INSERT 语句中的 xh, name, age 字段传递参数,使用 executemany() 方法来做为一次新增多笔数据,要事先定义好数据的内容。
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',
user='testuser',
password='test123',
database='TESTDB')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
vals = [[100321,"Mohan", 20], [100322,"lisi", 20]]
try:
# 执行 sql 语句
cursor.executemany("INSERT INTO students(xh, name, age) VALUES (%s, %s, %s)", vals)
# 执行sql语句
conn.commit()
except:
# 发生错误时回滚
conn.rollback()
# 关闭数据库连接
conn.close()
数据库查询操作 SELECT
Python 查询 Mysql 使用 fetchone() 方法获取单条数据, 使用 fetchall() 方法获取多条数据。
将数据库数据转换成 DataFrame 数据框,好方便后续的操作,转换的方式有两种:
用 Pandas 的 read_sql() 方法;使用 list() 方法转换成列表类型,在透过 Pandas 转成数据框。
import pymysql
# 打开数据库连接
conn = pymysql.connect(host='localhost',
user='testuser',
password='test123',
database='TESTDB')
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# SQL 查询语句
query = "SELECT * FROM students"
try:
# 方法一
df1 = pd.read_sql(query, conn)
print("方法一\n", df1)
# 方法二
cursor.execute(query)
results = cursor.fetchall()
print(type(results))
df2 = pd.DataFrame(list(results),columns =['学号', '姓名', '年龄'])
print("方法二\n", df2)
except:
print ("Error: unable to fetch data")
# 关闭数据库连接
conn.close()
读取学生数据表的结果画面
数据库更新操作 UPDATE
更新操作用于更新数据表的数据,以下实例将 students 表中 的 age 字段递增 1。
import pymysql
# 打开数据库连接
db = pymysql.connect(host='localhost',
user='testuser',
password='test123',
database='TESTDB')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE students SET age = age + 1'
try:
# 执行SQL语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
数据库删除操作 DELETE
删除操作用于删除数据表中的数据,以下实例演示了删除数据表 students 中 name 为 Mohan 的数据。
import pymysql
# 打开数据库连接
db = pymysql.connect(host='localhost',
user='testuser',
password='test123',
database='TESTDB')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM students WHERE name = '%s'" % ('Mohan')
try:
# 执行SQL语句
cursor.execute(sql)
# 提交修改
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭连接
db.close()
参考来源
- Welcome to PyMySQL’s documentation!, https://pymysql.readthedocs.io/en/latest/
- PyMySQL/PyMySQL, https://github.com/PyMySQL/PyMySQL
- Python3 MySQL 数据库连接 - PyMySQL 驱动, https://www.runoob.com/python3/python3-mysql.html
- MySQL添加新用户、为新用户分配权限, https://blog.csdn.net/ljxfblog/article/details/80197277
- Inserting a list holding multiple values in MySQL using pymysql, https://stackoverflow.com/questions/42039794/inserting-a-list-holding-multiple-values-in-mysql-using-pymysql
相关文章
- 【Python Oracle】使用cx_Oracle 进行数据库操作介绍
- 使用mysql-connector-python操作MYSQL数据库
- Python 日期和时间_python 当前日期时间_python日期格式化
- Python操作SQLite数据库
- python: easyocr的安装和使用(easyocr 1.6.2 / Python 3.7.15 )
- python:ERROR: No matching distribution found for Pillow==9.1.0的处理(Python 3.6.8)
- python操作docker SDK:Docker SDK for Python
- Python编程语言学习:python语言中快速查询python自带模块&函数的用法及其属性方法、如何查询某个函数&关键词的用法、输出一个类或者实例化对象的所有属性和方法名之详细攻略
- Python:python代码编程带你玩转双色球(了解双色球的概率逻辑)
- Python:pmml格式文件的简介、安装、使用方法(利用python将机器学习模型转为Java常用的pmml格式文件)之详细攻略
- 100天精通Python(进阶篇)——第40天:pymongo操作MongoDB数据库基础+代码实战
- 〖Python 数据库开发实战 - MySQL篇⑱〗- 分组查询的应用
- 〖Python 数据库开发实战 - Python与Redis交互篇⑤〗- 利用 redis-py 实现哈希数据类型的常用指令操作
- 〖Python 数据库开发实战 - Python与Redis交互篇⑬〗- 综合案例 - 新闻管理系统 - 编辑新闻(编辑角色权限)
- 编程进阶:这些 Python 常用技巧真棒
- Python:mysql-connector-python模块对MySQL数据库进行增删改查
- 怎样将Emoj表情插入mysql5.6数据库__python+mysqldb
- Python接口自动化核心模块 - 数据库操作和日志
- Python接口测试实战之数据库操作,拿来就能用
- scrapy 管道里面使用mysql插入数据库 python操作mysql
- python数据库操作常用功能使用详解(创建表/插入数据/获取数据)
- Python 头部 #!/usr/bin/python 和 #!/usr/bin/env 的区别
- Python 操作SQLite数据库
- 【异常】前端ERR! stack Error: Can‘t find Python executable “python“, you can set the PYTHON env variable.
- 【Python】MongoDB数据库安装和Pymongo操作学习