操作数据库 

 1 import pymysql
 2 ip =  'XX,XX,XX,XX'
 3 user = 'XXX'
 4 password = 'XXX' #密码必须是字符串
 5 db = 'XXX'
 6 port = 3306 #端口号必须写int类型
 7 
 8 connect = pymysql.connect(host=ip,user=user,db=db,
 9                 password=password,port=port,autocommit=True,charset="utf8")  # 连接数据库 autocommit=True 自动提交
10 
11 # 建立游标
12 cursor = connect.cursor(pymysql.cursors.DictCursor)     # 传游标类型 pymysql.cursors.DictCursor 指定游标是字典类型
13 # sql = 'show tables;' #获取数据库全部的表
14 sql = "create table fmz_huyuemei (id int primary  key auto_increment," \
15       "name varchar(50) not null," \
16       "sex int default 0, " \
17       "phone varchar(11) unique );"
18 sql = 'select * from fmz;'
19 # sql = 'drop table fmz_huyuemei'
20 # sql = "insert into fmz(id,name,sex,phone) VALUES (59,'hym',1,130451215)"
21 # sql = "update fmz set sex=1 where id =59; "
22 cursor.execute(sql) #执行sql语句
23 # print(cursor.description) # 每个字段的描述
24 # result = cursor.fetchall() #获取全部 始终返回的都是一个二维数组
25 # connect.connect() #提交  不提交还可以回滚
26 # connect.rollback() #回滚
27 # print(result)
28 # print(cursor.fetchone()) #获取一条
29 # print(cursor.fetchmany(3)) #获取多条数据
30 
31 # 数据量大的时候，直接循环cursor
32 # for i in cursor:#直接循环游标，每次取的就是表里面的每一条数据
33 #     print(i)
34 
35 cursor.close()
36 connect.close()
37 
38 def op_mysql(mysql_info:dict,sql:str,all = True):# :dict或:str 指定类型 def op_mysql(mysql_info:dict,sql:str)  -> list 指定函数返回值的类型是列表
39     connect = pymysql.connect(**mysql_info)     # **解开字典 如host+"XXX"
40     cursor = connect.cursor()
41     cursor.execute(sql)
42     result = None
43     if sql.strip().lower().startswith('select'):
44         if all:
45             result = cursor.fetchall()
46         else:
47             result = cursor.fetchone()
48     cursor.close()
49     connect.close()
50     return result
51 mysql_info = {"host":"XX,XX,XX,XX",
52               "user":"XXX",
53               "password":"XXX",
54               "db":"XXX",
55               "port":3306,"autocommit":True,"charset":"utf8"}
56 sql = "select * from fmz;"
57 
58 a = op_mysql(mysql_info,sql,False)
59 print(a)

 

posted @ 2021-05-25 17:16  等待iing  阅读(60)  评论(0)    收藏  举报