Python之PyMysql库

PyMysql库

导入PyMysql库：import pymysql

PyMysql连接对象

连接数据库，获得一个PyMysql连接对象conn

conn = pymysql.connect(
    host=None,
    port=0,
    user=None,
    password='',
    database=None,
    charset='',
    sql_mode=None,
    cursorclass=<class 'pymysql.cursors.Cursor'>,
    autocommit=False
)

参数含义如下（注意，原来的db-数据库的别名、passwd-密码的别名，已被弃用）：

host：数据库服务器地址
port：端口号，默认为3306
user：用户名
password：密码
database：所要连接的数据库名
charset：数据库编码
sql_mode：要使用的默认 SQL_MODE
cursorclass：设置游标类型
pymysql.cursors.Cursor 默认，元组类型
pymysql.cursors.DictCursor 字典类型
pymysql.cursors.SSCursor 无缓冲元组类型
pymysql.cursors.SSDictCursor 无缓冲字典类型
autocommit：是否自动提交，默认为False

连接对象的常用属性及方法

连接对象的常用属性及方法如下

一、begin() 开启事物，默认开启
语法：conn.begin()

二、close() 关闭连接
语法：conn.close()

三、commit() 提交事物
语法：conn.commit() ，对数据库进行了增删改操作后，均需要使用commit()提交修改

四、cursor(cursor=None) 创建游标对象
语法：cur = conn.cursor(cursor=None)，在创建游标对象时，可以使用cursor指定游标类型，默认是元组

五、open 返回连接开启状态
语法：conn.open，连接开启则返回True

六、ping(reconnect=True) 检查连接是否可用，不可用则抛出异常
语法：conn.ping(reconnect=True)，reconnect指连接不可用时是否重连，默认为True

七、rollback() 回滚事物
语法：conn.rollback()

八、select_db() 设置当前数据库
语法：conn.select_db(db_name)，参数为要指定的数据库名

PyMysql游标对象

通过连接对象conn来创建一个游标对象，游标对象用于和数据库进行交互
cur = conn.cursor()
在创建时，默认游标类型为pymysql.cursors.Cursor，可自行指定为其他游标类型，例如cur=conn.cursor(cursor=pymysql.cursor.DictCursor)

`pymysql.cursors.Cursor` 以元组形式返回sql执行结果

返回的结果中不含列名，仅包括数据

((425, 'mike', '15166668765', 0),)

`pymysql.cursors.DictCursor` 以列表字典 [dict, dict] 形式返回sql执行结果

返回的结果中包含key为列名，value为值，使用更方便

[{'id': 425, 'customer_name': 'mike', 'customer_phone': '15166668765', 'sex': 0}]

`pymysql.cursors.SSCursor` 流式游标，以元组形式返回sql执行结果

上述Cursor和DictCursor是普通游标，一次性返回所有的数据、进行查询时我们使用fetchall()或者fetchone()方法，都是默认先在内存中缓存所有数据再进行处理，大量的数据会导致内存资源消耗光，内存容易溢出；
而流氏游标是陆陆续续一条一条的返回查询数据，所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。流氏游标的使用方法类似于生成器，根据需要来循环获取结果。这样做的好处是客户端使用更少的内存，并且当通过慢速网络旅行或者结果集非常大时，返回行的速度要快得多。

在执行select语句之后，可以通过循环遍历cursor，此时的游标相当于生成器

......
cur.execute(sql)
for r in cur:
    print(r)


# 或者while循环
# r = cur.fetchone()
# while r:
#     print(r)
#     r = cur.fetchone()

这样就会将结果以元组形式打印出来

(425, 'mike', '15166668765', 0)
(426, 'mary', '15177778765', 1)
(427, 'lisa', '15188888765', 0)

注意

本质上对所有游标类的迭代都是在不断的调用 fetchone 方法，不同的是 SSCursor 对 fetchone 方法的实现不同；
流式游标虽然也有fetchall()方法，调用后的结果与普通游标一样返回所有数据，但是最好别去调用，这样会失去流式游标的优势；
若每条数据读取的间隔超过60s，可能会造成异常，这是因为MySQL的NET_WRITE_TIMEOUT的默认值为60，如果读取数据有处理时间较长的情况，则可以考虑修改此设置；
因为SSCursor是无缓存的，只要结果集没有读取完成，此时就不能使用此游标绑定的连接进行其他数据库操作，如果需要进行其他操作则要生成新的连接对象
可参考https://www.jianshu.com/p/b5178e73f1bf 和 https://cloud.tencent.com/developer/article/2076215

`pymysql.cursors.SSDictCursor` 流式游标，以字典形式返回结果

......
cur.execute(sql)
for r in cur:
    print(r)


# 或者while循环
# r = cur.fetchone()
# while r:
#     print(r)
#     r = cur.fetchone()

会将结果以字典形式打印出来

{425, 'mike', '15166668765', 0}
{426, 'mary', '15177778765', 1}
{427, 'lisa', '15188888765', 0}

游标对象的属性

一、description
只读属性，以嵌套元组形式返回所执行select语句返回的结果的列名及其他信息，若所执行的sql不返回行或者没有通过execute执行sql，则此属性返回None

cur = conn.cursor()
sql = "SELECT 'id', 'title' FROM `test`.`app_info`;"
cur.execute(sql)
print(cur.description)

返回值如下（若查询的是全部列，则会返回所有列的信息），分别为列名name、type_code、display_size、internal_size、precision、scale、null_ok：

(('id', 253, None, 8, 8, 31, False), ('title', 253, None, 20, 20, 31, False))

二、rowcount
只读属性，返回查询结果的行数、或者受影响的行数（注意，SSCursor和SSDictCursor不支持返回结果的行数，调用此属性不会返回正确的结果，因为SSCursor和SSDictCursor类似于生成器，通过不断迭代来取返回的数据）

cur = conn.cursor()
sql = "SELECT 'id', 'title' FROM `test`.`app_info`;"
cur.execute(sql)
print(cur.rowcount)

结果如下：

三、arraysize
可读/可写属性，用于指定fetchmany()方法一次读取的行数，默认值为1
cur.arraysize=5

游标对象的方法

一、close() 关闭游标对象
cur.close()

二、execute(sql, args) 执行sql语句
语法：cur.execute(sql, args)

sql：要执行的sql语句
args：sql语句的参数，可以为数字、字符串、list、tuple、dict
- If args is a list or tuple, %s can be used as a placeholder in the query.如果参数是数字、字符串、list或者tuple，可以用%s当占位符
- If args is a dict, %(name)s can be used as a placeholder in the query.如果参数是dict，可以用%(name)s当占位符，name为dict中的key

返回值：会返回受影响的行数，没有则返回None

cur = conn.cursor()
sql = "SELECT 'id', 'title' FROM `test`.`app_info`;"
n = cur.execute(sql)

# sql = "SELECT 'id', 'title' FROM `test`.`app_info` where id < %s;"
# n = cur.execute(sql, 10)
# sql = "SELECT 'id', 'title' FROM `test`.`app_info` where id < %s and id > %s;"
# n = cur.execute(sql, (10, 2))
# sql = "SELECT 'id', 'title' FROM `test`.`app_info` where id < %(id1)s and id > %(id2)s;"
# n = cur.execute(sql, {'id1': 9, 'id2': 2})

print(n)
print(cur.rowcount)

三、executemany() 执行多行sql语句
语法：cur.executemany(sql, args)，此方法可以提升多行insert或者replace的性能，否则就相当于使用execute()循环

sql：要执行的sql语句
args：sql语句的参数，tuple或者list

返回值：会返回受影响的行数，没有则返回None

sql = "INSERT INTO `test`.`app_info`(`title`, `image_url`, `is_delete`, `creator`, `gmt_created`) VALUES (%s, 'xxx.png', 0, 'test', '2022-10-26 17:15:35');"
cur.executemany(sql, ['ceshi01', 'ceshi02'])    # insert两条title分别为 ceshi01', 'ceshi02' 的数据

四、fetchone() 获取查询结果集的下一行
语法：cur.fetchone()
返回值：有数据时返回结果集的下一行数据，无则返回None

cur.execute(sql)
r = cur.fetchone()
print(r)    # 会根据游标类型打印出结果

# 此写法用于解释有数据时返回结果集的下一行数据，无则返回None，没必要这样取数据哈
# r = cur.fetchone()
# while r:
#     print(r)
#     r = cur.fetchone()

五、fetchmany(size=None) 获取结果集中的多行数据
语法：cur.fetchmany(size)，size可不指定，默认为None，不指定时根据arraysize属性的值来取数据，指定size后根据size的值来取数据
返回值：取出的多行数据

sql = "SELECT * FROM `test`.`app_info`;"
cur.execute(sql)
res = cur.fetchmany(size=2)
print(res)

六、fetchall() 获取结果集中的所有剩余行
语法：cur.fetchall()
返回值：取出的结果集

posted @ 2022-12-26 17:11 hook_what 阅读(644) 评论(0) 编辑收藏举报

刷新页面返回顶部

hook_what