环境声明

环境声明

  • 系统: 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密码修改为123123，guest密码修改为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表的guest、user1用户删除
  • 执行多条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
()
"""