python操作MySQL

查询：

 1 from pymysql import *
 2 
 3 
 4 class JD(object):
 5     def __init__(self):
 6         # 连接数据库
 7         self.conn = connect(host="127.0.0.1",port=3306,database="jing_dong",user="root",password="root",charset="utf8")
 8         # 创建cursor对象
 9         self.cursor = self.conn.cursor()
10 
11     def __del__(self):
12         print("数据库关闭")
13         # 关闭cursor
14         self.cursor.close()
15         # 关闭连接
16         self.conn.close()
17 
18 
19     def show_all_goods(self,sql):
20         """查询所有商品"""
21         # 执行sql语句
22         self.cursor.execute(sql)
23         for temp in self.cursor.fetchall():
24             print(temp)
25 
26     def show_cates(self,sql):
27         """查询分类"""
28         self.cursor.execute(sql)
29         for temp in self.cursor.fetchall():
30             print(temp)
31 
32     def show_brands(self,sql):
33         """查询品牌"""
34         self.cursor.execute(sql)
35         for temp in self.cursor.fetchall():
36             print(temp)
37 
38     @staticmethod
39     def print_menu():
40         """菜单"""
41         print("---京东---")
42         print("1.查询所有商品")
43         print("2.查询所有商品分类")
44         print("3.查询所有商品品牌")
45         return input("请输入您需要执行的指令：")
46 
47     def run(self):
48 
49         while True:
50             nums = self.print_menu()
51             if nums == "1":
52                 sql = "select * from goods"
53                 self.show_all_goods(sql)
54             elif nums == "2":
55                 sql = "select * from goods_cates"
56                 self.show_cates(sql)
57             elif nums == "3":
58                 sql = "select * from goods_brands"
59                 self.show_brands(sql)
60             elif nums == "q":
61                 break
62             else:
63                 print("你的输入有误，请重新输入：")
64 
65 
66 def main():
67     # 实例化京东对象
68     jd = JD()
69     # 运行
70     jd.run()
71 
72 
73 if __name__ == '__main__':
74     main()

增删改：

 1 from pymysql import *
 2 
 3 def main():
 4     # 创建Connection连接
 5     conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
 6     # 获得Cursor对象
 7     cs1 = conn.cursor()
 8     # 执行insert语句，并返回受影响的行数：添加一条数据
 9     # 增加
10     count = cs1.execute('insert into goods_cates(name) values("硬盘")')
11     #打印受影响的行数
12     print(count)
13 
14     count = cs1.execute('insert into goods_cates(name) values("光盘")')
15     print(count)
16 
17     # # 更新
18     # count = cs1.execute('update goods_cates set name="机械硬盘" where name="硬盘"')
19     # # 删除
20     # count = cs1.execute('delete from goods_cates where id=6')
21 
22     # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交 确认提交
23     conn.commit()
24     # 如果发现之前执行的语句有问题 就先别提交 rollback（）回滚 撤销提交
25     conn.rollback()
26 
27     # 关闭Cursor对象
28     cs1.close()
29     # 关闭Connection对象
30     conn.close()
31 
32 if __name__ == '__main__':
33     main()