python - Flask SQLAlchemy：IntegrityError：（由于查询调用的自动刷新而引发）在for循环中
问题描述
我正在尝试在 for 循环中将曲目添加到播放列表，如下所示：
data = zip(playlists, artists, tracks, uris)
count=0
for d in data:
# check if playlist is new to database
if db.session.query(Playlist).filter_by(title=d[0]).count() < 1:
# ADD FIRST TRACK
track = Track(title=d[2],
artist=d[1],
uri=d[3],
track_id=1)
db.session.add(track)
playlist = Playlist(title=d[0],
user=User.query.get(1))
playlist.tracks.append(track)
db.session.add(playlist)
else:
# ADD TRACKS
track = Track(title=d[2],
artist=d[1],
uri=d[3],
track_id=count+1)
db.session.add(track)
# look for existent playlist
pl = Playlist.query.filter_by(title=d[0]).first()
print ('EXISTENT PLAYLIST', pl)
# add tracks to existent playlist
pl.tracks.append(track)
count+=1
db.session.commit()
这些是我的模型：
class Playlist(db.Model):
"""
Model for storing playlist information belonging to a specific user
"""
__tablename__ = 'playlist'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(50), unique=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user = db.relationship('User',
backref=db.backref('playlists', lazy='dynamic'))
tracks = db.relationship('Track', secondary=tracks,
backref=db.backref('playlists', lazy='dynamic'))
def __init__(self, title, user):
self.title = title
self.user = user
def __repr__(self):
return '<Playlist %r>' % (self.title)
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id' : self.id,
'title': self.title,
'user': self.user.serialize,
'tracks': [ item.serialize for item in self.tracks]
}
class Track(db.Model):
"""
Model for storing track information
"""
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(140))
artist = db.Column(db.String(140))
uri = db.Column(db.String(50), unique=True)
track_id = db.Column(db.String(140), unique=True)
def __init__(self, title, artist, uri, track_id):
self.title = title
self.artist = artist
self.uri = uri
self.track_id = track_id
def __repr__(self):
return '<Track %r>, <Artist %r>' % (self.title, self.artist)
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id' : self.id,
'title': self.title,
'artist': self.artist,
'uri': self.uri,
'track_id': self.track_id
}
一切正常，所有曲目都被创建并添加到第一个播放列表中，但是一旦我移动到第二个播放列表，我就会得到以下回溯：
IntegrityError：（由于查询调用自动刷新而引发；如果此刷新过早发生，请考虑使用 session.no_autoflush 块）（sqlite3.IntegrityError）唯一约束失败：track.track_id [SQL：u'INSERT INTO track（标题，艺术家，uri，track_id）值（？，？，？，？）'] [参数：（'无意图'，'脏投影仪'，'spotify：track：4Ozug40zUcMiE2s1neJQRb'，1）]
完整追溯：
127.0.0.1 - - [13/Aug/2018 17:43:29] "POST /login HTTP/1.1" 500 -
Traceback (most recent call last):
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1836, in __call__
return self.wsgi_app(environ, start_response)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
response = self.make_response(self.handle_exception(e))
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/Users/vitorpatalano/Documents/Code/Apps/myapp/app.py", line 459, in do_admin_login
if db.session.query(Playlist).filter_by(title=d[0]).count() < 1:
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3089, in count
return self.from_self(col).scalar()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2843, in scalar
ret = self.one()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2814, in one
ret = self.one_or_none()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
ret = list(self)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2854, in __iter__
self.session._autoflush()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1407, in _autoflush
util.raise_from_cause(e)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1397, in _autoflush
self.flush()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2171, in flush
self._flush(objects)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2291, in _flush
transaction.rollback(_capture_exception=True)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2255, in _flush
flush_context.execute()
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
rec.execute(self)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
uow
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
mapper, table, insert)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements
execute(statement, params)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
exc_info
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/Users/vitorpatalano/anaconda2/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
**IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: track.track_id [SQL: u'INSERT INTO track (title, artist, uri, track_id) VALUES (?, ?, ?, ?)'] [parameters: ('No Intention', 'Dirty Projectors', 'spotify:track:4Ozug40zUcMiE2s1neJQRb', 1)]**
127.0.0.1 - - [13/Aug/2018 17:43:29] "GET /login?__debugger__=yes&cmd=resource&f=style.css HTTP/1.1" 200 -
127.0.0.1 - - [13/Aug/2018 17:43:29] "GET /login?__debugger__=yes&cmd=resource&f=debugger.js HTTP/1.1" 200 -
127.0.0.1 - - [13/Aug/2018 17:43:29] "GET /login?__debugger__=yes&cmd=resource&f=jquery.js HTTP/1.1" 200 -
127.0.0.1 - - [13/Aug/2018 17:43:29] "GET /login?__debugger__=yes&cmd=resource&f=ubuntu.ttf HTTP/1.1" 200 -
127.0.0.1 - - [13/Aug/2018 17:43:29] "GET /login?__debugger__=yes&cmd=resource&f=console.png HTTP/1.1" 200 -
我错过了什么？
解决方案
从您的代码中不可能知道您尝试插入的数据是什么样的，但是通过阅读您的模型定义和错误，您违反了 Track 表中的唯一约束。您的 Trackuri属性和属性具有唯一约束，这意味着您在整个数据库中track_id只能拥有一个唯一的轨道uri和一个。track_id用户是否有可能在两个不同的播放列表中拥有相同的曲目？如果这是可能的，那么您应该删除 Track 表定义中的唯一约束，track_id并uri查看此错误是否会消失。
推荐阅读
- powershell - PowerShell Url 登录和状态检查
- file - 在dos中一次重命名多个文件
- html - 如何在设置变量之前隐藏元素 CSS
- windows - 如何在 Windows 上使用多个版本的 Julia
- sharepoint - 根据逗号分隔值创建新列表项 (Sharepoint)
- reactjs - 通过 Azure CLI 部署基于 Azure DevOps git repo 的 ReactJs Azure Static Web App
- python - 如何写入many2many字段并在UI中显示？
- r - 如何在r中的另一个数据框的字符串中显示数据框变量值？
- reactjs - 使用 waitForResponse 时剧作家测试失败
- blockchain - 合约构造函数参数，也是合约