Flask+SQLAlchemy过滤查询及数据更新问题求助

我希望根据id获取对应数据行并更新其值，但无法执行过滤查询。我将Periodic_element类的元素存储在SQLite3数据库中，正在使用Flask和SQLAlchemy开发一个可修改、添加、删除周期元素的REST API。

from asyncio.windows_events import NULL
from enum import auto
from itertools import cycle
import json
from random import weibullvariate
from sqlite3 import dbapi2
from traceback import print_tb
from tracemalloc import start
from unicodedata import name
from flask import *
from flask_sqlalchemy import SQLAlchemy
from matplotlib.font_manager import json_dump
from sqlalchemy import Float, Identity, create_engine, ForeignKey, false, null, select
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.sql import select


engine = create_engine(
    'sqlite:///dati.db', connect_args={"check_same_thread": False}, echo=True,)


Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()


class Periodic_element(Base):

    __tablename__ = "dati"

    id = Column(Integer, Identity(start=1, cycle=True),
                primary_key=True, nullable=True)
    name = Column(String)
    weight = Column(Float)
    symbol = Column(String)

    def __init__(self, id, name, weight, symbol):
        self.id = None
        self.name = name
        self.weight = weight
        self.symbol = symbol

    def __repr__(self):
        return '<User %r>' % (self.name)

    def toJson(self):
        json = {
            "id": self.id,
            "name": self.name,
            "weight": self.weight,
            "symbol": self.symbol
        }
        return json


def add_record(id, name, symbol, weight):
    record = Periodic_element(id=None, name=name, symbol=symbol, weight=weight)
    session.add(record)
    session.commit()


def serve_record():
    x = session.query(Periodic_element).all()
    final_json = "[" 
    # session.commit()
    for element in x:
        final_json = final_json + json.dumps(element.toJson()) + ","

    final_json = final_json[:-1] + "]"
    print("Json is served")
    return final_json


def change_record():
    x = session.query(Periodic_element).filter(
        Periodic_element.name == "prova")


change_record()


Base.metadata.create_all(bind=engine)

文件"c:\Users\user\Desktop\Esercizi Lavoro\back-end\database.py"，第27行，in <module>
    Base.query = session.query_property()
AttributeError: 'Session'对象没有属性'query_property'
PS C:\Users\user\Desktop\Esercizi Lavoro\back-end> & "C:/Users/user/AppData/Local/Programs/Python/Python310/python.exe" "c:/Users/user/Desktop/Esercizi Lavoro/back-end/database.py"
2022-09-30 08:58:13,638 INFO sqlalchemy.engine.Engine SELECT dati.id AS dati_PS C:\Users\user\Desktop\Esercizi Lavoro\back-end> & "C:/Users/user/AppData/Local/Programs/Python/Python310/python.exe" "c:/Users/user/Desktop/Esercizi Lavoro/back-end/database.py"
2022-09-30 09:02:50,688 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-30 09:02:50,691 INFO sqlalchemy.engine.Engine SELECT dati.id AS dati_id, dati.name AS dati_name, dati.weight AS dati_weight, dati.symbol AS dati_symbol
FROM dati
WHERE dati.name = ?
2022-09-30 09:02:50,691 INFO sqlalchemy.engine.Engine [generated in 0.00025s] ('prova',)
1
2022-09-30 09:02:50,693 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-30 09:02:50,693 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("dati")
2022-09-30 09:02:50,693 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-30 09:02:50,695 INFO sqlalchemy.engine.Engine COMMIT

1. 解决query_property错误 #

你混用了原生SQLAlchemy和Flask-SQLAlchemy的会话管理方式。query_property()是Flask-SQLAlchemy中SQLAlchemy实例的方法，不是原生Session对象的属性。改用Flask-SQLAlchemy的标准用法：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///dati.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# 用db.Model作为模型基类，替换原生declarative_base
class Periodic_element(db.Model):
    __tablename__ = "dati"

    id = db.Column(db.Integer, db.Identity(start=1, cycle=True), primary_key=True)
    name = db.Column(db.String)
    weight = db.Column(db.Float)
    symbol = db.Column(db.String)

    def __init__(self, name, weight, symbol):
        # 主键id由数据库自动生成，无需手动传入
        self.name = name
        self.weight = weight
        self.symbol = symbol

    def toJson(self):
        return {
            "id": self.id,
            "name": self.name,
            "weight": self.weight,
            "symbol": self.symbol
        }

2. 实现根据ID查询并更新的功能 #

修改change_record函数，完成按ID定位元素并更新字段的逻辑：

def change_record(element_id, new_name=None, new_weight=None, new_symbol=None):
    # 根据ID查询目标元素
    element = Periodic_element.query.get(element_id)
    if element:
        # 更新指定字段
        if new_name:
            element.name = new_name
        if new_weight:
            element.weight = new_weight
        if new_symbol:
            element.symbol = new_symbol
        # 提交数据库修改
        db.session.commit()
        return element.toJson()
    else:
        return None  # 返回None表示未找到对应元素

3. 优化serve_record函数 #

手动拼接JSON字符串易出错，改用列表推导式生成数据后统一序列化：

def serve_record():
    elements = Periodic_element.query.all()
    elements_json = [element.toJson() for element in elements]
    return json.dumps(elements_json)

4. 修正add_record函数 #

移除不必要的id参数，主键由数据库自动生成：

def add_record(name, symbol, weight):
    record = Periodic_element(name=name, symbol=symbol, weight=weight)
    db.session.add(record)
    db.session.commit()
    return record.toJson()

5. 正确初始化数据库 #

在Flask应用上下文内创建数据表：

with app.app_context():
    db.create_all()

内容的提问来源于stack exchange，提问作者karak002