环境声明

环境声明

  • 系统: Windows10家庭中文版
  • 硬件: 16G内存、8核CPU
  • Py版本: 3.7.6
  • pymysql版本: 1.0.2
  • Mysql版本: 5.7.26

环境部署

安装pymysql

1
2
# 安装
pip install pymysql

验证环境

1
2
import pymysql
print(pymysql.__version__)

属性方法

连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 连接数据库
conn = pymysql.connect(host="主机地址",
user="用户名",
password="密码",
port=服务端口,
database="数据库名",
charset="字符编码"
)

# 关闭连接数据库
conn.close()

# 选择数据库
conn.select_db(数据库名)

游标对象

1
2
3
4
5
# 创建游标对象
cursor = conn.cursor()

# 关闭游标
cursor.close()

执行SQL语句

1
2
3
4
5
# 单条语句
cursor.execute(sql语句)

# 多条语句
cursor.executemany(sql语句)

查询数据

1
2
3
4
fetchone()		# 获取单条数据
fetchall() # 获取全部数据
fetchmany(条数) # 获取游标处往后的n条数据
cursor.rowcount # 返回游标位置

增改删数据

  • 执行完添加、修改、删除后,需要提交事务
  • 当想要撤销添加、修改、删除时,可以进行事务回滚
1
2
3
4
5
# 提交事务
conn.commit()

# 事务回滚
conn.rollback()

连接数据库

语法

  • 执行一条查询语句时往往会得到N条数据,执行SQL语句取出这些返回结果的接口,就称之为游标
  • 如果不使用游标,查询语句会将所有数据一次性返回到界面中,且无法对该数据进行操作
  • 使用游标后,系统会将查询结果先保存起来,当需要数据时就借助游标一行一行的取数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import pymysql

"""方法1"""
conn = pymysql.connect(host="主机地址",
user="用户名",
password="密码",
port=服务端口,
database="数据库名",
charset="字符编码"
)

"""方法2"""
db_config = {
"host": "主机地址",
"user": "用户名",
"password": "密码",
"port": 服务端口,
"database": "数据库名",
"charset": '字符编码'
}
conn = pymysql.connect(**db_config)

"""方法3"""
conn = pymysql.connect("主机地址", "用户名", "密码")
conn.select_db('数据库名')


# 创建游标
cursor = conn.cursor()

# 关闭连接
cursor.close()
conn.close()

创建数据库

创建示例

  • 先连接到数据库,再创建游标对象,再执行SQL语句创建数据库,最后关闭连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 创建数据库
cursor.execute("drop database if exists test") # 数据库存在时删除数据库
cursor.execute("create database test") # 创建数据库test
print("数据库创建成功")

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
数据库创建成功
"""

创建数据表

创建示例

  • 先连接到数据库,再选择数据表,再创建游标对象,再执行SQL语句创建数据表,最后关闭连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
conn.select_db('test') # 选择test数据库
cursor = conn.cursor() # 创建游标对象

create_table_users = """\
create table users(
id int primary key,
username varchar(20) not null,
password varchar(20) not null,
age int(3) not null,
email varchar(20)
)
"""

try:
# 创建数据表
cursor.execute("drop table if exists user") # 数据表存在时删除数据表
cursor.execute(create_table_users) # 创建数据表users
print("数据表创建成功")

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
数据表创建成功
"""

插入表数据

插入方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 方法1
insert_status = cursor.execute("insert into users values(1,'admin','123456',18,'admin@qq.com')")
print("受影响行数: " + str(insert_status))


# 方法2 (防止SQL注入)
insert_users_sql = "insert into users values(%s,%s,%s,%s,%s)"
insert_status = cursor.execute(insert_users_sql, (1,'admin','123456',18,'admin@qq.com'))
print("受影响行数: " + str(insert_status))


# 插入多条数据
insert_users_data = [
(2, 'guest', '654321', 19, 'guest@qq.com'),
(3, 'user1', '123321', 18, 'user1@qq.com')
]
insert_users_sql = "insert into users values(%s,%s,%s,%s,%s)"
insert_status = cursor.executemany(insert_users_sql, insert_users_data)
print("受影响行数: " + str(insert_status))

插入单条数据

  • 先连接到数据库,再选择数据表,再创建游标对象,再执行SQL语句插入数据,再提交事务,最后关闭连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 插入一条数据
insert_users_sql = "insert into users values(%s,%s,%s,%s,%s)"
insert_status = cursor.execute(insert_users_sql, (1, 'admin', '123456', 18, 'admin@qq.com'))
print("受影响行数: " + str(insert_status))

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
conn.rollback() # 执行失败时回滚
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))

插入多条数据

  • 将单条要插入的数据存储到元组中,再将元组的数据存储到列表中
  • 注意这里使用的是executemany方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

insert_users_data = [
(2, 'guest', '654321', 19, 'guest@qq.com'),
(3, 'user1', '123321', 18, 'user1@qq.com')
]

try:
# 插入多条数据
insert_users_sql = "insert into users values(%s,%s,%s,%s,%s)"
insert_status = cursor.executemany(insert_users_sql, insert_users_data)
print("受影响行数: " + str(insert_status))

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
conn.rollback() # 执行失败时回滚
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))

查询表数据

全部查询

  • 使用execute()方法返回的只是受影响的行数,并不能拿到真正的查询数据
  • 使用execute()方法后再对游标对象使用fetchall(),获取到所有数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象


try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句
results = cursor.fetchall() # 获取所有记录列表

# 对查询结果进行遍历
print(results)
for row in results:
uid = row[0]
username = row[1]
password = row[2]
age = row[3]
email = row[4]
print(f"id={uid}, username={username}, password={password}, age={age}, email={email}")

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
((1, 'admin', '123456', 18, 'admin@qq.com'), (2, 'guest', '654321', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
id=1, username=admin, password=123456, age=18, email=admin@qq.com
id=2, username=guest, password=654321, age=19, email=guest@qq.com
id=3, username=user1, password=123321, age=18, email=user1@qq.com
"""

逐条查询

  • 每调用一次fetchone()方法就会获取到一条数据,当没有数据可以获取时返回None
  • 该方法类似于迭代器,每次获取一条,直到数据被取完
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句

print(cursor.fetchone()) # 获取第一条数据
print(cursor.fetchone()) # 获取第二条数据
print(cursor.fetchone()) # 获取第三条数据
print(cursor.fetchone()) # 获取第四条数据

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
(1, 'admin', '123456', 18, 'admin@qq.com')
(2, 'guest', '654321', 19, 'guest@qq.com')
(3, 'user1', '123321', 18, 'user1@qq.com')
None
"""
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 遍历
# 省略几行代码....

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句

while True:
results = cursor.fetchone()
if results is None:
break
print(results)

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))

获取指定条数数据

  • fetchmany() 方法用来获取游标处的指定条数数据
  • 注意游标是会移动的,当获取两条数据后游标的位置也移动到了第二条数据的后面
  • 这时候再获取一次数据就返回第三条数据,以此类推
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句

print(cursor.fetchmany(2)) # 获取游标处两条数据
print(cursor.rowcount) # 返回当前游标位置
print(cursor.fetchmany(2)) # 获取游标处两条数据

cursor.close() # 关闭游标
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果(无结果返回空元组)
"""
((1, 'admin', '123456', 18, 'admin@qq.com'), (2, 'guest', '654321', 19, 'guest@qq.com'))
3
((3, 'user1', '123321', 18, 'user1@qq.com'),)
"""

更新表数据

修改单条数据

  • users表的admin密码修改为111111
  • 执行单条SQL使用execute()方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

# 修改数据
update_users_sql = "update users set password=%s where username=%s"
update_status = cursor.execute(update_users_sql, (111111, 'admin')) # 将admin的密码改为111111
print("受影响行数: " + str(update_status))

# 查看修改结果
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
((1, 'admin', '123456', 18, 'admin@qq.com'), (2, 'guest', '654321', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
受影响行数: 1
((1, 'admin', '111111', 18, 'admin@qq.com'), (2, 'guest', '654321', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
"""

修改多条数据

  • users表的admin密码修改为123123guest密码修改为aaaaaa
  • 执行多条SQL使用executemany()方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

# 修改数据
update_users_sql = "update users set password=%s where username=%s"
update_users_data = [
("123123", "admin"),
("aaaaaa", "guest")
]
update_status = cursor.executemany(update_users_sql, update_users_data)
print("受影响行数: " + str(update_status))

# 查看修改结果
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
((1, 'admin', '123456', 18, 'admin@qq.com'), (2, 'guest', '654321', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
受影响行数: 2
((1, 'admin', '123123', 18, 'admin@qq.com'), (2, 'guest', 'aaaaaa', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
"""

删除表数据

删除单条数据

  • users表的admin用户删除
  • 执行单条SQL使用execute()方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

# 删除数据
delete_users_sql = "delete from users where username=%s"
delete_status = cursor.execute(delete_users_sql, "admin")
print("受影响行数: " + str(delete_status))

# 查看修改结果
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
((1, 'admin', '123123', 18, 'admin@qq.com'), (2, 'guest', 'aaaaaa', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
受影响行数: 1
((2, 'guest', 'aaaaaa', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
"""

删除多条数据

  • users表的guestuser1用户删除
  • 执行多条SQL使用executemany()方法
  • 注意元组只有一条数据时数据后面要加逗号,如('admin',)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
import pymysql

db_config = {
"host": "localhost",
"user": "root",
"password": "root",
"port": 3306,
"database": "test",
"charset": 'utf8'
}

conn = pymysql.connect(**db_config) # 连接数据库
cursor = conn.cursor() # 创建游标对象

try:
# 查询数据
select_users_sql = "select * from users"
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

# 删除数据
delete_users_sql = "delete from users where username=%s"
delete_users_data = [
("guest",),
("user1",)
]

update_status = cursor.executemany(delete_users_sql, delete_users_data)
print("受影响行数: " + str(update_status))

# 查看修改结果
cursor.execute(select_users_sql) # 执行查询语句
print(cursor.fetchall()) # 修改前的所有数据

cursor.close() # 关闭游标
conn.commit() # 提交事务
conn.close() # 关闭连接

except pymysql.Error as e:
print("MySQL ERROR %d: %s" % (e.args[0], e.args[1]))


# 结果
"""
((2, 'guest', 'aaaaaa', 19, 'guest@qq.com'), (3, 'user1', '123321', 18, 'user1@qq.com'))
受影响行数: 2
()
"""