python 管理mysql pymysql

创建数据库

1
pip3 install pymysql -i http://pypi.douban.com/simple --trusted-host pypi.douban.com

02-创建一张新表_哔哩哔哩_bilibili

报错

"'cryptography' package is required for sha256_password or caching_sha2_password auth methods" 93e99722521024206663b818aa4af307.png 原因： 没有安装cryptography 解决 

1
pip3 install cryptography -i https://pypi.douban.com/simple
1
pip3 install cryptography -i https://pypi.tuna.tsinghua.edu.cn/simple
## 连接数据库 ### 语法 
1
2
db = pymysql.connect( host=host , user=user, password = password , db = name)
print("数据库连接成功")

创建表格成功

语法

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



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Emai CHAR(20),Age INT)"
    cur.execute(sql)
    print("表格创建成功")
except pymysql.Error as e:
    print("表格创建失败"+str(e))

实例

1
2
3
cur.execute("DROP TABLE IF EXISTS Student")
sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Emai CHAR(20),Age INT)"
cur.execute(sql)

向表中插入数据

语法

1
2
3
4
sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
value = ('Mike','1212434@uu.com',20)
cur.execute(sql, value)
db.commit()

实例

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



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age INT)"
    cur.execute(sql)
    sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
    value = ('Mike','1212434@uu.com',20)
    cur.execute(sql, value)
    db.commit()
    print("数据提交成功")
except pymysql.Error as e:
    print("表格创建失败"+str(e))

查询数据库中的数据

语法

1
2
3
4
5
6
7
8
sql = "SELECT * FROM Student"
 cur.execute(sql)
 results = cur.fetchall()
 for row in results:
     name = row [0]
     email = row [1]
     age = row [0]
     print('name:%s ,email:%s ,age:%s'%(name,email,age))

实例

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



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age INT)"
    cur.execute(sql)
    sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
    value = ('Mike','1212434@uu.com',20)
    cur.execute(sql, value)
    value = ('UUU','44523434@uu.com',17)
    cur.execute(sql, value)
    db.commit()

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))
    print("数据提交成功")
except pymysql.Error as e:
    print("表格创建失败"+str(e))
db.close()

更新数据表中的语句

语法

1
2
3
4
5
sql = "update Student set Name =%s where Name =%s"
value = ("Jhon","Mike")
cur.execute(sql,value)
db.commit()
print("数据更新成功")

实例

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



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age INT)"
    cur.execute(sql)
    sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
    value = ('Mike','1212434@uu.com',20)
    cur.execute(sql, value)
    value = ('UUU','44523434@uu.com',17)
    cur.execute(sql, value)
    db.commit()

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))


    sql = "update Student set Name =%s where Name =%s"
    value = ("Jhon","Mike")
    cur.execute(sql,value)
    db.commit()
    print("数据更新成功")

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))


except pymysql.Error as e:
    print("表格创建失败"+str(e))
db.close()

删除数据库中的数据

语法

1
2
3
4
sql = "delete from Student where Name =%s"
value =("Jhon")
cur.execute(sql,value)
db.commit()

实例

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



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age INT)"
    cur.execute(sql)
    sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
    value = ('Mike','1212434@uu.com',20)
    cur.execute(sql, value)
    value = ('UUU','44523434@uu.com',17)
    cur.execute(sql, value)
    db.commit()

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))


    sql = "update Student set Name =%s where Name =%s"
    value = ("Jhon","Mike")
    cur.execute(sql,value)
    db.commit()
    print("数据更新成功")

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))

    sql = "delete from Student where Name =%s"
    value =("Jhon")
    cur.execute(sql,value)
    db.commit()
    print("数据删除成功")


    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))



except pymysql.Error as e:
    print("表格创建失败"+str(e))
db.close()

删除表

语法

1
2
3
4
sql = " DROP TABLE IF EXISTS Student"
cur.execute(sql)
db.commit()
print("删除表成功")
### 实例 
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
import pymysql



host = "localhost"
user = 'root'
password = '123456'
name = 'test_db'

try:
    db = pymysql.connect( host=host , user=user, password = password , db = name)
    print("数据库连接成功")
    cur = db.cursor()
    cur.execute("DROP TABLE IF EXISTS Student")
    sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Email CHAR(20),Age INT)"
    cur.execute(sql)
    sql = "INSERT INTO Student(Name ,Email, Age) VALUE(%s,%s,%s)"
    value = ('Mike','1212434@uu.com',20)
    cur.execute(sql, value)
    value = ('UUU','44523434@uu.com',17)
    cur.execute(sql, value)
    db.commit()

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))


    sql = "update Student set Name =%s where Name =%s"
    value = ("Jhon","Mike")
    cur.execute(sql,value)
    db.commit()
    print("数据更新成功")

    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))

    sql = "delete from Student where Name =%s"
    value =("Jhon")
    cur.execute(sql,value)
    db.commit()
    print("数据删除成功")


    sql = "SELECT * FROM Student"
    cur.execute(sql)
    results = cur.fetchall()
    for row in results:
        name = row [0]
        email = row [1]
        age = row [0]
        print('name:%s ,email:%s ,age:%s'%(name,email,age))

    sql = " DROP TABLE IF EXISTS Student"
    cur.execute(sql)
    db.commit()
    print("删除表成功")

except pymysql.Error as e:
    print("表格创建失败"+str(e))
db.close()