Python中PyMySQL的基本操作介绍

1

pip install PyMySQL

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

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

import pymysql

# 简单的查找

# 连接数据库

    conn = pymysql.connect(host='127.0.0.1', user='root', password='******', database='authoritydb')

# cursor=pymysql.cursors.DictCursor,是为了将数据作为一个字典返回

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

    sql = 'select id,name from userinfo where id = %s'

# row返回获取到数据的行数

# 不能将查询的某个表作为一个参数传入到SQL语句中，否则会报错

# eg：sql = 'select id,name from %s'

# eg：row = cursor.excute(sql, 'userinfo') # 备注：userinfo是一个表名

# 像上面这样做就会报SQL语法错误,正确做法如下：

    row = cursor.execute(sql, 1)

# fetchall()（获取所有的数据），fetchmany(size)（size指定获取多少条数据），fetchone()（获取一条数据）

    result = cursor.fetchall()

    cursor.close()

    conn.close()

# 最后打印获取到的数据

    print(result)

# 补充

# 传入多个数据时

    sql = 'select id,name from userinfo where id>=%s and id<=%s'

    row = cursor.executemany(sql, [(1, 3)])

# 以字典方式传值

    sql = 'select id,name from userinfo where id>=%(id)s or name=%(name)s'

    rows = cursor.execute(sql, {'id': id, 'name': name})

# -------------------------------------------------

    import pymysql

# 复杂一点的查找，与MySQL的语句格式一样

    connect = pymysql.connect(host='localhost', user='root', password='****', database='authoritydb')

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

    username = input('username: ')

    sql = 'select A.name,authorityName from (select name,aid from userauth left join userinfo on' \

          ' uid=userinfo.id where name=%s) as A left join authority on authority.id=A.aid'

    row = cursor.execute(sql, username)

    result = cursor.fetchmany(row)

    cursor.close()

    connect.close()

    print(result)

# 调用函数

import pymysql

# 函数已经在mysql数据库中创建，这里只调用

# 函数的创建请访问后面的网址(https://blog.csdn.net/qq_43102443/article/details/107349451).

# in （指在创建函数时指定的参数只能输入）

    connect = pymysql.connect(host='localhost', user='root', password='******', database='schooldb')

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

    r = cursor.callproc('p2', (10, 5))

    result_1 = cursor.fetchall()

# 这个函数返回两个结果集

# 换到另一个结果集，在进行获取值

    cursor.nextset()

    result_2 = cursor.fetchall()

    cursor.close()

    connect.close()

    print('学生:', result_1, '\n老师:', result_2)

# out （指在创建函数时指定的参数只能输出）

    connect = pymysql.connect(host='localhost', user='root', password='*****', database='schooldb')

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

# 调用函数

    r = cursor.callproc('p3', (8, 0))

    result_1 = cursor.fetchall()

# 查询函数的第二个参数的值（从零开始计数）

    cursor.execute('select @_p3_1')

    result_2 = cursor.fetchall()

    cursor.close()

    connect.close()

    print(result_1, '\n', result_2)

# inout（指在创建函数时指定的参数既能输入，又能输出）

    connect = pymysql.connect(host='localhost', user='root', password='l@l19981019', database='schooldb')

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

    r = cursor.callproc('p4', (10, 0, 2))

    result_1 = cursor.fetchall()

    cursor.execute('select @_p4_1,@_p4_2')

    result_2 = cursor.fetchall()

    cursor.close()

    connect.close()

    print(result_1, '\n', result_2)

1

2

3

4

5

6

7

8

9

    connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')

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

    student_id, course_id, number = input('student_id,course_id,number[eg:1 2 43]: ').split()

    sql = 'insert into score(student_id,course_id,number) values(%(student_id)s,%(course_id)s,%(number)s)'

    rows = cursor.execute(sql,{'student_id': student_id, 'course_id': course_id, 'number': number})

# 这里一定要提交

    cursor.commit()

    cursor.close()

    connect.close()

1

2

3

4

5

6

7

8

9

connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')

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

    student_id = input('student_id: ')

    sql = 'delete from student where sid=%s'

    rows = cursor.execute(sql, student_id)

# 这里一定要提交

    cursor.commit()

    cursor.close()

    connect.close()

1

2

3

4

5

6

7

8

9

connect = pymysql.Connect(host='localhost', user='root', password='******', database='schooldb')

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

    id, name = input('id, name: ').split()

    sql = "update userinfo set name=%s where id=%s"

    rows = cursor.executemany(sql, [(name, id)])

# 这里一定要提交

    cursor.commit()

    cursor.close()

    connect.close()