pymysql的增删查改

先查下后台数据

USE test12

SELECT * FROM `data`

 

 代码如下：

 

 

import pymysql

database = pymysql.connect("127.0.0.1", "root", "123", "test12", charset="utf8")
cursor = database.cursor()
# 增
sql = "insert into data (date,company,province,price,weight) values ('2020-12-31','河北粮食1','河北','2200','45.1');"
cursor.execute(sql)
database.commit()
database.close()
# 改
sql1 = "update data set date = '2020-12-30' where date = '2020-12-31';"
cursor.execute(sql1)
database.commit()
database.close()
# 删
sql2 = "delete from data where date ='2020-12-30';"
cursor.execute(sql2)
database.commit()
database.close()
# 查客商
sql3 = "select company from data where date='2018-07-21';"
# 查客商和价格
sql3 = "select company,price from data where date='2018-07-21';"
# 查客商和总价
sql3 = "select company,price*weight from data where date='2018-07-21';"
# 查不同的客商总量满足日期以公司名分组
sql3 = "select company,sum(weight) from data where date='2018-07-21' group by company;"
cursor.execute(sql3)
result = cursor.fetchall()
print(result)
database.commit()
database.close()