python操作mysql

最新推荐文章于 2024-03-30 21:48:44 发布

funNLPer

最新推荐文章于 2024-03-30 21:48:44 发布

阅读量306

点赞数

分类专栏：数据库文章标签： mysql python 数据库 sql

本文链接：https://blog.csdn.net/orangerfun/article/details/119337307

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用Python的pymysql模块操作MySQL数据库，包括建立数据库连接、创建游标、执行SQL查询和更新、以及数据库回滚。示例代码演示了查询、插入、更新数据的过程，并展示了不同类型的游标使用。同时，文章还列举了常用的MySQL命令语句，如创建和删除数据库、数据的增删查改、字段管理、索引操作等。

摘要由CSDN通过智能技术生成

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

funNLPer

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
python操作mysql

1. quick start1.1 链接数据库第一步：创建一个MySQL链接对象import pymysqldb = pymysql.connect(host="8.131.240.176", port=1234, user="root", password="nlp123456", db="TEST"
复制链接

扫一扫

专栏目录