寒假第二十四天

Day 3: 实现增删改查功能

目标：通过Python脚本实现基本的CRUD操作。

任务与进展：

1. 查询数据：

   • 根据导演名称查询电影列表。

2. 新增数据：

   • 手动输入新电影信息并插入数据库。

3. 修改数据：

   • 根据ID更新电影评分或简介。

4. 删除数据：

   • 根据ID删除记录。

代码片段：

python

复制

def query_movies(director):
    query = f"SELECT * FROM movies WHERE director LIKE '%{director}%'"
    result = pd.read_sql(query, engine)
    return result

def insert_movie(title, rating, director, description):
    sql = "INSERT INTO movies (title, rating, director, description) VALUES (%s, %s, %s, %s)"
    with engine.connect() as conn:
        conn.execute(sql, (title, rating, director, description))

def update_movie(movie_id, new_rating):
    sql = "UPDATE movies SET rating = %s WHERE id = %s"
    with engine.connect() as conn:
        conn.execute(sql, (new_rating, movie_id))

def delete_movie(movie_id):
    sql = "DELETE FROM movies WHERE id = %s"
    with engine.connect() as conn:
        conn.execute(sql, (movie_id,))

问题与解决：

• SQL注入风险：改用参数化查询（%s占位符）替代字符串拼接。

• 事务管理：通过with engine.connect() as conn自动提交事务。