python连接MySQL

MySQL驱动安装命令

1
2
1pip install mysql-connector-python --allow-external mysql-connector-python

2

 把MySQL编码全部改为utf-8

在MySQL的客户端命令行可以用以下命令查询编码

1
2
1show variables like '%char%';

2

如果MySQL的版本≥5.5.3，可以把编码设置为utf8mb4，utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符。

连接并使用数据库代码：

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
1import mysql.connector

2

3conn = mysql.connector.connect(user='root',password='lanse0305',database='lcb')

4

5cursor = conn.cursor()

6

7#cursor.execute("insert into xinxi(name)values('LCB'),('LJH'),('HSB'),('LP')")#插入

8

9#cursor.execute('delete from xinxi where id = 1')#删除

10

11cursor.execute("update xinxi set id = 1 where name='LCB'")#更新

12cursor.execute("update xinxi set id = 2 where name='LJH'")

13cursor.execute("update xinxi set id = 3 where name='HSB'")

14cursor.execute("update xinxi set id = 4 where name='LP'")

15

16conn.commit()#提交事务

17

18cursor.execute('select * from xinxi')#查询

19

20values = cursor.fetchall()#获取查询信息（返回一个list，里面一条记录为一个tuple）

21

22print(values)

23

24print(cursor.rowcount)#记录数

25

26cursor.close()

27

28conn.close()

29

30

PyMySQL

下载命令：pip install PyMySQL

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

2

3db = pymysql.connect("localhost","root","lanse0305","lcb")

4

5cursor =db.cursor()

6

7cursor.execute("delete from xinxi where name='hello'")

8

9cursor.execute("insert into xinxi(id,name)value(10,'hello')")

10

11db.commit()

12

13cursor.execute("select * from xinxi order by id")

14

15res = cursor.fetchall()

16

17for x in res:

18    print(x)

19

20