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()以及原理分析