pandas 数据库操作

import pandas as pd
from sqlalchemy import create_engine

#
username="root"
password="@WSX3edc"
host="127.0.0.1"
port=3306
database="test"
engine = create_engine(
    "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
        username=username, password=password, host=host, port=port, database=database
    )
)

# 查询所有
def search_all():
    sql = "select * from goods"
    df = pd.read_sql(sql=sql, con=engine)
    data_lst = df.to_dict("records")
    return data_lst

# 通过名字查询
def search_by_name(name):
    sql = "select name, category, price, quantity from goods where name=%s"
    df = pd.read_sql(sql=sql, con=engine, params=(name,))
    return df.to_dict("records")

# 新增数据
def add_data(name,category, price, quantity):
    try:
        sql = "insert into goods(name, category, price, quantity) values(%s, %s, %s, %s)"
        pd.read_sql(sql=sql, con=engine, params=(name, category, price, quantity))
    except Exception as e:
        print(e)

# 更新数据
def update_by_name(name, price):
    try:
        sql = "update goods set price= %s where name=%s"
        pd.read_sql(sql=sql, con=engine, params=(price, name))
    except Exception as e:
        print(e)

update_by_name("香蕉","2.9")