Pymysql基本操作

1. 基本介绍

Python3 操作 MySQL 数据库 可以使用的模块是 pymysql 和 MySQLdb。

这个两个模块都是通过自己的 API 执行原生的 SQL 语句实现的。

MySQLdb 是最早出现的一个操作 MySQL 数据库的模块，核心由C语言编写，接口精炼，性能最棒，缺点是环境依赖较多，安装稍复杂，特别是 windows 中不好安装。更新较慢。

pymysql 为替代 MySQLdb 而生，纯 Python 实现，API 的接口与 MySQLdb 完全兼容，安装方便。

在建链接之前，我们需要做好一些前期工作：建库建表

create database host_info

授权一个用户：

grant all on host_info.* to shark@'%'  identified by 'QFedu123@';

建立连接：

# PyMySQL安装
pip3 install pymysql


创建链接的基本使用　

# 导入pymysql模块
import pymysql
 
# 创建连接
conn = pymysql.connect(
    host='127.0.0.1',      #数据库地址
    port=3306,             # 数据库端口
    user='shark',          # 连接数据库的用户
    passwd='QFqwe123..',   # 连接数据库的密码
    db='host_info',        # 数据库的库名，需要先在 MySQL 里创建  
    charset='utf8mb4'      # 字符集
)
 
# 得到一个可以执行SQL语句的光标对象((1,), (2,))
#cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# 得到一个可以执行SQL语句并且将结果作为字典返回的游标[{"id": 1}, {"id": 2}]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 定义 sql 语句, 创建第一个表 服务器基础信息表 base_info
sql = """
create table info
 (id int auto_increment primary key, 
  name varchar(64) not null, 
  age int
)"""

# 执行 sql 语句
cursor.execute(sql)
  
# 提交更改
conn.commit()

# 关闭游标对象
cursor.close()

# 关闭连接对象
conn.close()

!mysql -uroot -pQFqwe123.. -e "use host_info;show tables "  #查看是否创建成功
+---------------------+
| Tables_in_host_info |
+---------------------+
| info                |
+---------------------+

插入单条数据：

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',     
    port=3306,            
    user='shark',          
    passwd='QFqwe123..',   
    db='host_info',          
    charset='utf8mb4'      
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

info = {"name": "shark1", "age": 18}  # 数据

sql = '''
 insert into info(
   name,age
) values(%s, %s);''' #定义sql语句, 插入一条数据，并且使用 pymysql 定义的变量占位符

cursor.execute(sql, tuple(info.values()))

print(cursor.rowcount) # 输出数据库中受影响的行数
conn.commit()          # 提交更改 

!mysql -ushark -pQFqwe123.. -e "select * from host_info.info"  #查看数据库是否更新

+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | shark1 |   18 |
+----+--------+------+

插入多条数据：

conn连接数据库，cursor游标 记得操作！！！

data = [
     ('shark2', '14'),
     ('shark3', '25'),
     ('shark4','100')
]


sql = '''
 insert into info(
   name,age
) values(%s, %s);'''


cursor.executemany(sql,data)

conn.commit()
!mysql -ushark -pQFqwe123.. -e "select * from host_info.info"
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | shark1 |   18 |
|  2 | shark2 |   14 |
|  3 | shark3 |   25 |
|  4 | shark4 |  100 |
+----+--------+------+

cursor.close()  #结束操作记得！！！
conn.close()

查询：

conn连接数据库，cursor游标 记得操作！！！

# 定义一个查询语句
In [191]: query_sql = """
     ...: select id, name, age 
     ...: from info 
     ...: where id > %s;
     ...: """


# 执行查询语句，并且返回得到结果的行数
row_nums = cursor.execute(query_sql, (1))

获取到数据结果集具有迭代器的特性:
1. 可以通过索引取值，可以切片
2. 结果集中的数据每次取出一条就少一条
"""
  
In [222]: cursor.fetchone()                         # 获取结果集中的第一条数据
Out[222]: {'id': 2, 'name': 'shark2', 'age': 99}

In [223]: cursor.fetchmany(2)                       # 获取结果集中,接下来的 2 条数据
Out[223]: 
[{'id': 3, 'name': 'shark3', 'age': 14},
 {'id': 4, 'name': 'shark4', 'age': 25}]

In [224]: cursor.fetchall()                         # 获取结果集中剩余的全部数据 
Out[224]: [{'id': 5, 'name': 'shark5', 'age': 100}]

cursor.close()
conn.close()
print("-" * 10)
print(f"共返回数据{row_nums}:")
print("-" * 25)
print(json.dumps(one_data, indent=4))
print("-" * 25)
print(json.dumps(many_data, indent=4))
print("-" * 25)
print(json.dumps(all_data, indent=4))


In [245]: print(json.dumps(mary_data, indent=4))
[
    {
        "id": 4,
        "name": "shark4",
        "age": 25
    },
    {
        "id": 5,
        "name": "shark5",
        "age": 100
    }
]