PyMySQL

发表于 | 分类于


PyMySQL介绍

  1. PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用 mysqldb。
  2. Django 中也可以使用 PyMySQL 连接 MySQL 数据库。

PyMySQL安装

pip3 install pymysql

连接数据库

  • 前提需要:
  1. 你有一个MySQL数据库，并且已经启动。
  2. 你有可以连接该数据库的用户名和密码
  3. 你有一个有权限操作的database

基本使用

连接数据库并查询数据

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

# http://www.cnblogs.com/liwenzhou/p/8032238.html
if __name__ == '__main__':
    # 拿到用户输入用户名和密码
    username = input('请输入用户名: ')
    password = input('请输入密码: ')

    # 去数据库里面判断用户名和密码是否正确

    # 1. 连接数据库
    conn = pymysql.connect(
        host="localhost",
        port=3306,
        database="userinfo",
        user="root",
        password="123",
        charset="utf8"        # 千万记得么有 -
    )

    # 光标 获取输入sql语句的光标对象
    cursor = conn.cursor()
    
    # SQL语句
    sql = "select * from info where username = 'leo' and password = '123';"
    
    # 执行语句
    ret = cursor.execute(sql)
    print(ret)  # 1 返回结果的行数

    # 关闭连接
    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

if __name__ == '__main__':

    username = input("请输入用户名; ")
    passowrd = input("请输入密码: ")

    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()
    sql = "select * from info where username = %s and password = %s ;"

    print(sql)
    print('*' * 100)

    ret = cursor.execute(sql,[username,passowrd]) # 让pymysql帮我们拼接sql语句
    if ret:
        print('登录成功')
    else:
        print('登录失败!')

    cursor.close()
    conn.close()

增删改查操作

增

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
import pymysql

if __name__ == '__main__':
    username = input("请输入要添加的用户名: ")
    password = input("请输入用户的密码: ")

    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()

    sql = "insert into info (username,password) VALUES(%s,%s)"
    # 执行
    try:
        cursor.execute(sql,[usernam,password])
        # 提交事务: 读操作不用提交，写操作一定要提交
        conn.commit()
        # 提交之后，获取刚插入的数据ID
        last_id = cursor.lastrowid
        print(last_id)
    except Exception as e:
        print("报错: ",str(e))
        conn.rollback() # 有异常，回滚事务
        # 插入数据失败回滚
        # 在执行增删改操作时，如果不想提交前面的操作，可以使用 rollback() 回滚取消操作。       

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

获取插入数据的ID(关联操作时会用到)

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

if __name__ == '__main__':
    # username = input("请输入要添加的用户名: ")
    # password = input("请输入用户的密码: ")

    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()

    # 创建班级
    sql1 = "insert into class (name) VALUES(%s)"

    # 创建学生
    sql2 = "insert into student (name,cid) VALUES(%s,%s)"


    # 执行
    try:
        cursor.execute(sql1,['全棧10期'])
        new_id = cursor.lastrowid   # 拿到前一条语句执行后的id值
        print(new_id)
        cursor.execute(sql2, ['leo',new_id])
        # 提交事务: 读操作不用提交，写操作一定要提交
        conn.commit()
        # 提交之后，获取刚插入的数据ID
        last_id = cursor.lastrowid
        print(last_id)

    except Exception as e:
        print("报错: ",str(e))
        conn.rollback() # 有异常，回滚事务

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

批量执行 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
48
49
import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()

    data = [
        ("k", "123"),
        ("w", "123"),
        ("m", "123"),
    ]

    # 创建用户
    sql = "insert into info (username,password) VALUES(%s,%s)"

    # 批量执行
    try:
        # 一次执行多个语句 传入可迭代的数据类型
        cursor.executemany(sql,data)
        new_id = cursor.lastrowid   # 拿到前一条语句执行后的id值
        print(new_id)

        # for 循环执行语句 一次只执行一条语句
        # for i in data:
        #     cursor.execute(sql,i)
        #     new_id = cursor.lastrowid   # 拿到前一条语句执行后的id值
        #     print(new_id)

        # 提交事务: 读操作不用提交，写操作一定要提交
        conn.commit()
        # 提交之后，获最新的数据ID
        last_id = cursor.lastrowid
        print(last_id)

    except Exception as e:
        print("报错: ",str(e))
        conn.rollback() # 有异常，回滚事务

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

删

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
if __name__ == '__main__':
    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()

    # sql = "delete from info WHERE username = %s ;"
    # cursor.execute(sql,'k')

    sql = "delete from info WHERE id = %s ;"
    cursor.execute(sql,10)

    # 提交
    conn.commit()
    # 关闭
    cursor.close()
    conn.close()

改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    cursor = conn.cursor()

    sql = "update info set password = %s WHERE username = %s ;"
    cursor.execute(sql,["000","rubin"])

    # 提交
    conn.commit()
    # 关闭
    cursor.close()
    conn.close()

查 指定返回的数据格式为 字典格式

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(
        host = "localhost",
        port = 3306,
        user = "root",
        password = "123",
        database = "userinfo",
        charset = "utf8"
    )

    # cursor = conn.cursor()

    # 指定返回的数据格式为 字典格式
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # sql语句
    sql = "select * from info;"

    # 执行
    # 返回的是受影响的行数，不是具体数据
    cursor.execute(sql)
    # print("{} rows in set" .format(ret))

    # 返回所有数据 元组 ((1, 'leo', '123')...
    ret = cursor.fetchall()
    print(ret)

    # 获取一条数据,查一条打印一条，没有就是None
    # ret = cursor.fetchone()
    # print(ret)
    #
    # ret = cursor.fetchone()
    # print(ret)

    # 获取指定数量数据
    # ret = cursor.fetchmany(3)
    # print(ret)

    # ret = cursor.fetchone()
    # print(ret)

    # ret = cursor.fetchall()
    # print(ret)  # [] 按照光标移动查询 之前已经都查过了

    # 按照光标位置查询 absolute 绝对定位 到第一条
    # cursor.scroll(1,mode="absolute")
    # ret = cursor.fetchall()
    # print(ret)

    # 按照光标位置查询  相对定位 从当前位置向下移动
    # ret = cursor.fetchmany(2)
    # print(ret)
    #
    # cursor.scroll(1,mode="relative")
    # ret = cursor.fetchall()
    # print(ret)


    # 提交 查询无需提交
    # conn.commit()

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

查询多条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host="你的数据库地址", user="用户名",password="密码",database="数据库名",charset="utf8")
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 查询数据的SQL语句
sql = "SELECT id,name,age from USER1;"
# 执行SQL语句
cursor.execute(sql)
# 获取多条查询数据
ret = cursor.fetchall()
cursor.close()
conn.close()
# 打印下查询结果
print(ret)

剥皮函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
if __name__ == '__main__':
    list1 = [11, [22, 3], [4, ], [55, 66], 8, [9, [7, [12, [34, [26]]]]]]
    # 去除多余嵌套的列表,得到[11, 22, 3, 4, 55, 66, 8]

# 小剥皮
def func(x):
    return [a for b in x for a in b]

def func2(x):
    ret = []
    for b in x:
        if isinstance(b,list):
            for a in func2(b):
                ret.append(a)
        else:
            ret.append(b)
    return ret

list2 = [11,22]
list2 = [11,[22,33]]
list2 = [11,[22,33,[44,55]]]
ret = func2(list1)
print(ret)