本文详细介绍了如何使用Python的pymysql模块操作MySQL数据库，包括建立数据库连接、创建游标、执行SQL查询和更新、以及数据库回滚。示例代码演示了查询、插入、更新数据的过程，并展示了不同类型的游标使用。同时，文章还列举了常用的MySQL命令语句，如创建和删除数据库、数据的增删查改、字段管理、索引操作等。
1. python操作mysql
主要流程是：创建链接mysql实例db，创建游标cursor ，写sql语句并用cursor.excute(sql语句)执行语句，db.commit()提交更改，cursor.close()关闭游标，关闭数据库连接db.close()
1.1 链接数据库
第一步：创建一个MySQL链接对象
import pymysql
db = pymysql.connect(host="8.131.240.176",
port=1234,
user="root",
password="nlp123456",
db="TEST",
charset="utf8")
参数说明：
host：数据库服务器主机名可以是IP或域名
user：用户登陆名
password：用户名密码
db：要使用的数据库名
port：端口号默认3306
charset: 指定编码集
链接对象的一些方法
conn = pymysql.connect(host, port, user, password, db)
conn.close() 关闭链接
conn.commit() 提交更改到数据库服务器
cursor = conn.cursor(cursor=None) 创建一个新的游标来执行查询，cursor指定游标类型：Cursor、SSCursor、DictCursor或SSDictCursor，没有指定即使用光标
conn.open 如果链接处于打开状态则返回true
conn.ping(reconnect=True) 检查服务器是否存在，reconnect为True时重新链接,默认为True
conn.rellback() 回滚当前事务
def connect_db(host="172.10.942.92", user="root", port=6666, password="123456", db="youxuepai", charset="utf8"):
db = pymysql.connect(host=host, port=port,user=user, password=password,db=db,charset=charset)
return db
db = connect_db()
is_open = db.open
print("第一次：", is_open)
db.close()
is_open2 = db.open
print("第二次：", is_open2)
db.ping()
is_open3 = db.open
print("第三次：", is_open3)
输出：
第一次： True
第二次： False
第三次： True
1.2 创建游标
在 MySQL 中，存储过程或函数中的查询有时会返回多条记录，而使用简单的 SELECT 语句，没有办法得到第一行、下一行或前十行的数据，这时可以使用游标来逐条读取查询结果集中的记录。通俗理解：游标就是一个标识，用来标识数据取到了什么地方，可以把他理解成数组中的下标
cursor = db.cursor()
游标对象API
cursor.callproc(procname) 查看数据库存储过程
cursor.close()关闭游标
cursor.execute(query,args=None)执行查询，query查询参数为字符串，args可以是元组，列表或字典，用于查询的参数，返回类型为INT
cursor.executemany(query,seq_of_parameters)多次查询返回结果
cursor.fetchall()获取所有行
cursor.fetchmany(size=None)获取指定的行数
cursor.fetchone()获取下一行
1.3 完整实例
查询
import pymysql
#创建数据库链接，分别指定主机、用户、密码和数据库名,必须保证用户有权限链接
db=pymysql.connect('10.0.1.198','test1','123.com','test')
#创建游标对象
cursor = db.cursor()
#使用execute()方法执行SQL语句
sql = 'select * from test1'
cursor.execute(sql)
#获取单条数据
print(cursor.fetchone())
#获取N条数据
print(cursor.fetchmany(3))
#获取所有数据，序列形式
data = cursor.fetchall()
print(data)
#关闭游标
cursor.close()
#关闭链接
db.close()
输出
第一个print
(1, '2021-07-15 11:17:19', '7c492d236dddbd9b6a451bcfe1833917', '这个字怎么读', '指读', '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n')
第二个print
((1, '2021-07-15 11:17:19', '7c492d236dddbd9b6a451bcfe1833917', '这个字怎么读', '指读', '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n'), (2, '2021-07-15 11:17:19', '7c492d236dddbd9b6a451bcfe1833917', '这个字怎么读', '指读', '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n'), (2468, '2021-07-19 19:19:01', '3cce80935584e439cf61e1a21512eec3', '小忧这个字怎样读', '指读', '2021-07-19/118/zici1/2021-07-19/19/19-02-388-0290934587613503626/image.jpg'))
第三个print：输出形式如第二个print，显示太多，此处略
在上例中将cursor如下创建，则效果如下：
#创建游标对象，指定数据类型为字典，将打印key,value
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
输出：
{'id': 1, 'DetailTime': '2021-07-15 11:17:19', 'UserId': '7c492d236dddbd9b6a451bcfe1833917', 'Question': '这个字怎么读', 'Skill': '指读', 'PictureRoot': '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n'}
[{'id': 1, 'DetailTime': '2021-07-15 11:17:19', 'UserId': '7c492d236dddbd9b6a451bcfe1833917', 'Question': '这个字怎么读', 'Skill': '指读', 'PictureRoot': '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n'}, {'id': 2, 'DetailTime': '2021-07-15 11:17:19', 'UserId': '7c492d236dddbd9b6a451bcfe1833917', 'Question': '这个字怎么读', 'Skill': '指读', 'PictureRoot': '2021-07-15/230/zici1/2021-07-15/11/18-49-242-0290934630562572224/image.jpg\n'}, {'id': 2468, 'DetailTime': '2021-07-19 19:19:01', 'UserId': '3cce80935584e439cf61e1a21512eec3', 'Question': '小忧这个字怎样读', 'Skill': '指读', 'PictureRoot': '2021-07-19/118/zici1/2021-07-19/19/19-02-388-0290934587613503626/image.jpg'}]
更新数据库
import pymysql
conn = pymysql.connect(host='10.0.1.198',port=3306,user='test1',passwd='123.com',db='test')
cursor = conn.cursor()
sql = "update test1 set age=28 where id=4"
cursor.execute(sql)
#提交语句到数据库
conn.commit()
cursor.close()
conn.close()
查询操作可以不使用db.commit()但是诸如插入、更新等对数据库表做出改变时必须使用 commit
插入多条语句
import pymysql
conn = pymysql.connect(host='10.0.1.198',port=3306,user='test1',passwd='123.com',db='test')
cursor = conn.cursor()
l1 = [
('k1','aa',22,'2222'),
('k2','bb',23,'3333'),
('k3','cc',24,'4444'),
('k4','dd',25,'5555')
]
#定义数据库语句
sql = "insert into test1(name,sex,age,tel) values(%s,%s,%s,%s)"
#executemany()插入多条数据
cursor.executemany(sql,l1)
#获取新增数据自增ID
print(cursor.lastrowid)
#提交语句到数据库
conn.commit()
cursor.close()
conn.close()
2. mysql常用命令语句
（1）创建数据库/删除数据库
# 1. 创建数据库
CREATE TABLE TEST.BAIKE_CITIAO (
CITIAO_ID INT PRIMARY KEY AUTO_INCREMENT, # 创建CITIAO_ID字段并设置成主键自增，INT类型
CITIAO VARCHAR(100) NOT NULL,
URL VARCHAR(300) UNIQUE, # 创建URL字段，可变300字节，并设置成不可重复
SUMMARY TEXT,
ATTRIBUTE TEXT,
CONTENT TEXT,
DATA_SOURCE VARCHAR(200),
INSERT_DATE DATETIME,
EDIT_DATE DATETIME
);
# 2. 删除表
DROP TABLE table_name ;
（2）数据库内容增删查改
# 1. 删除数据
DELETE FROM table_name [WHERE Clause]; # 若未指定where子句，删除整个表中的数据
# 2. 插入数据
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
# 3. 查询
SELECT column_name1,column_name2 FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M];
# 3.1 模糊匹配like
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'; # 通常与%和_结合使用
# 4. 更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause];
（3）数据库字段增删查改
# 1. 删除某个字段
ALTER TABLE table_name DROP filed;
# 2. 增加一个字段
ALTER TABLE table_name ADD new_field INT;
# 3. 修改字段名称
ALTER TABLE table_name CHANGE old_filed_name new_filed_name INT;
# 4. 修改字段类型
ALTER TABLE table_name CHANGE old_field_name old_filed_name INT(新的类型);
# 5. 修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;
（4）索引相关
# 1. 创建索引
CREATE INDEX indexName ON table_name (column_name[(length)])
# 注：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
# 例：CREATE INDEX QUERY_INDEX ON QUERY_FREQUENCY_NEW(QUERIES(255));
# 2. 删除索引
DROP INDEX [indexName] ON mytable;
# 3. 查看表的索引信息
SHOW INDEX FROM table_name;
（5）正则（模糊）
'%a' # 以a结尾的数据
'a%' # 以a开头的数据
'%a%' # 含有a的数据
'_a_' # 三位且中间字母是a的
'_a' # 两位且结尾字母是a的
'a_' # 两位且开头字母是a的
（6）排序
# 设定多个字段来排序,使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列
SELECT field1, field2,...fieldN FROM table_name1, table_name2 ... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
（7）复制表
# 一下以复制query_data这张表为例
# 1. 获取被复制数据表的完整结构
SHOW CREATE TABLE query_data;
输出：
CREATE TABLE `query_data` (
`DATA_ID` int(11) NOT NULL,
`DETAIL_TIME` text COLLATE utf8mb4_unicode_ci,
`UserId` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Question` text COLLATE utf8mb4_unicode_ci,
`Skill` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`PictureRoot` varchar(400) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`DATA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 2. 根据1的输出结果，修改表名，创建表
CREATE TABLE `query_data_copy` (
`DATA_ID` int(11) NOT NULL,
`DETAIL_TIME` text COLLATE utf8mb4_unicode_ci,
`UserId` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Question` text COLLATE utf8mb4_unicode_ci,
`Skill` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`PictureRoot` varchar(400) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`DATA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 3. 从旧表拷贝数据到新表
INSERT INTO query_data_copy (DATA_ID, DETAIL_TIME, UserId, Question, Skill, PictureRoot)
SELECT DATA_ID, DETAIL_TIME, UserId, Question, Skill, PictureRoot
FROM runoob_tbl;
3. 数据库回滚
3.1 什么是回滚
通常在对数据库插入数据时，会用for循环，游标多次execute提交数据代码，最后一起 commit进行数据库提交，成功写入数据库。如果其中存在一条插入语句语法错误，若设置了回滚，因为有db.rollback()数据回滚操作，所以之前在游标cursor中的所有操作都会被清空clear，所以最后就算执行db.commit()操作也不会将没有错误的其他语句执行成功。总结回滚就是确保数据库操作的原子性问题，多次操作要么都执行，要么都不执行
3.2 程序中设置回滚
import pymysql
class MysqlDBUtils(object):
def __init__(self, host, port, username, password, db):
self.host = host
self.port = port
self.username = username
self.password = password
self.db = db
self.conn = None
self.cursor = None
def db_init(self, charset='utf8'):
self.conn = pymysql.connect(host=self.host, port=self.port, user=self.username,
password=self.password, db=self.db, charset=charset)
self.cursor = self.conn.cursor()
def sql_execute(self):
insert_sql1 = "INSERT INTO t_user(user_id, user_name) VALUES (5, '嘻嘻');"
insert_sql2 = "INSERT INTO T_HOBBY(user_id, hobby) VALUES ('我是错误的ID', '骑马');"
self.db_init()
conn = self.conn
cursor = self.cursor
try:
cursor.execute(insert_sql1)
cursor.execute(insert_sql2)
cursor.close() # 先关游标
except Exception as e:
conn.rollback() # 设置回滚
print(e)
finally:
cursor.close()
conn.commit()
conn.close()
参考
python3之pymysql模块
MySQL教程
python中实现Mysql数据回滚rollback()以及原理分析
扫一扫
3506
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
