pymysql模块

 1 import pymysql
 2 
 3 #打开数据库连接
 4 conn = pymysql.connect(
 5     host = "localhost",
 6     user = "sq_test",
 7     password = "**********",
 8     port = 3306,
 9     database = "sq_test",
10     charset = "utf8",
11 )
12 #获取操作游标
13 cursor = conn.cursor()
14 #插入数据
15 def insert_data():
16     sql = "insert into mytable(name,age,job,city) values('Donfag',20,'IT','ShangHai')"
17     try:
18         cursor.execute(sql)
19         conn.commit()
20     except Exception as e:
21         print(e)
22         conn.rollback()
23     finally:
24         conn.close()
25 #批量插入数据
26 def many_insert_data():
27     sql = "insert into mytable(name,age,job,city) values(%s,%s,%s,%s)"
28     lst = []
29     data1 = ('Donfag1',21,'Teacher','BeiJin')
30     data2 = ('Donfag2',22,'Worker', 'HeiFei')
31     lst.append(data1)
32     lst.append(data2)
33     try:
34         cursor.executemany(sql,lst)
35         conn.commit()
36     except Exception as e:
37         print(e)
38         conn.rollback()
39     finally:
40         conn.close()
41 #查询数据
42 def get_data():
43     #fetchone():获取一个查询结果，结果集是一个对象
44     #fetchall():接收全部的返回结果
45     #rowcount:返回execute()方法影响的行数
46     sql = "select * from mytable"
47     try:
48         cursor.execute(sql)
49         results = cursor.fetchall()
50         record = cursor.rowcount
51         print(f'总共{record}条记录')
52         for row in results:
53             id = row[0]
54             name = row[1]
55             age = row[2]
56             job = row[3]
57             city = row[4]
58             print(f'id={id},name={name},age={age},job={job},city={city}')
59     except Exception as e:
60         print(e)
61     finally:
62         conn.close()
63 #更新数据
64 def update_data():
65     sql = "update mytable set age=30 where name='Donfag'"
66     try:
67         cursor.execute(sql)
68         conn.commit()
69     except Exception as e:
70         print(e)
71         conn.rollback()
72     finally:
73         conn.close()
74 #删除数据
75 def delete_data():
76     sql = "delete from mytable where id=3"
77     try:
78         cursor.execute(sql)
79         conn.commit()
80     except Exception as e:
81         print(e)
82         conn.rollback()
83     finally:
84         conn.close()