我有两张桌子:
import flask_sqlalchemy
import sqlalchemy
from datetime import date
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mydburl'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class TableStorer(db.Model):
__tablename__ = 'storer'
store_id = db.Column('id', db.String, primary_key=True)
is_deleted = db.Column('is_deleted', db.Boolean, default=False)
class TableOpenStorer(db.Model):
__tablename__ = 'open_storer'
__table_args__ = (
db.PrimaryKeyConstraint('id', 'modified_datetime'),
)
store_id = db.Column('id', db.String, nullable=False)
modified_datetime = db.Column('modified_datetime', db.DateTime, nullable=False)
如您所见,在TableStorer
存储中,id应该是唯一的。
现在我想删除带有当前日期的TableOpenStorer
中的所有行,选择TableStorer
中的所有行,其中is_deleted=False
并将它们插入TableOpenStorer
。
代码:
def get_open_stores_query():
return TableStorer.query.filter_by(is_deleted=False)
def update_stores():
today = date.today()
# remove all today rows to avoid overwriting
TableOpenStorer.query.filter(TableOpenStorer.modified_datetime == today).delete()
#insert
db.session.add_all([TableOpenStorer(store_id=store_id, modified_datetime=today)
for (store_id,) in get_open_stores_query().with_entities(TableStorer.store_id).all()])
db.session.commit()
但有时我会看到错误:
DETAIL: Key (id, modified_datetime)=(1000283, 2021-02-10 00:00:00) already exists. [SQL: INSERT INTO open_storer (id, modified_datetime) VALUES (%(id)s, %(modified_datetime)s)]
想法一:来自TableStorer
的选择存储\u id包含重复项,但这很奇怪,因为存储\u id是主键,应该是唯一的
想法2:我尝试在删除操作未完成时插入值并获得重复项
如何检查想法N2
目前没有回答
相关问题 更多 >
编程相关推荐