一、增
# 增
def insert_data(name, category,price,quantity):
with connection.cursor() as cursor:
sql = "insert into goods(name, category, price, quantity) values(%s, %s, %s, %s)"
cursor.execute(sql, [name, category, price, quantity])二、查
# 查
def select_by_name(name):
with connection.cursor() as cursor:
sql = "select name, category, price, quantity from goods where name=%s"
cursor.execute(sql, [name,])
ret = cursor.fetchall()
data = pd.DataFrame(data=ret, columns=["name", "category", "price", "quantity"]).to_dict("records")
return data
def select_all():
with connection.cursor() as cursor:
sql = "select name, category, price, quantity from goods"
cursor.execute(sql)
ret = cursor.fetchall()
data = pd.DataFrame(data=ret, columns=["name", "category", "price", "quantity"]).to_dict("records")
print(data)
return data结果： [{'name': '香蕉', 'category': '水果', 'price': 3.2, 'quantity': 10.0}, {'name': '苹果', 'category': '水果', 'price': 5.4, 'quantity': 20.0}, {'name': '菠萝', 'category': '水果', 'price': 7.2, 'quantity': 12.0}, {'name': '青椒', 'category': '蔬菜', 'price': 2.7, 'quantity': 32.0}, {'name': '土豆', 'category': '蔬菜', 'price': 1.2, 'quantity': 28.0}, {'name': '番茄', 'category': '蔬菜', 'price': 3.1, 'quantity': 40.0}]三、改
# 改
def update_by_name(price, name):
with connection.cursor() as cursor:
sql = "update goods set price = %s where name=%s"
cursor.execute(sql, [price, name])
sql = "select name, category, price, quantity from goods where name=%s"
cursor.execute(sql, [name,])
ret = cursor.fetchall()
data = pd.DataFrame(data=ret, columns=["name", "category", "price", "quantity"]).to_dict("records")
return data四、删除
