我遇到了SQLAlchemy 的奇怪行为
我试图向数据库添加一个新对象。这是在函数add_tag()中完成的
class News(Base):
__tablename__ = "news"
id = Column(Integer, primary_key=True)
title = Column(String)
description = Column(String)
def add_tag(self, tag_name, session):
logging.debug("adding news tags " + tag_name)
tag = session.query(Tag).filter_by(name=tag_name).first()
logging.debug(tag)
if tag:
nt = NewsTags()
nt.tag_id = tag.id
self.tags.append(nt)
else:
new_tag = Tag()
logging.debug('Creating a new tag: ' + tag_name)
new_tag.name = tag_name
session.add(new_tag)
session.commit()
nt = NewsTags()
nt.tag_id = new_tag.id
self.tags.append(nt)
class NewsTags(Base):
__tablename__ = "news_tags"
news_id = Column(ForeignKey("news.id"), primary_key=True)
tag_id = Column(ForeignKey("tag.id"), primary_key=True)
news = relationship("News", backref="tags")
class Tag(Base):
__tablename__ = "tag"
id = Column(Integer, primary_key=True)
name = Column(String(30))
news = relationship(NewsTags, backref="tag", post_update=True)
我首先检查对象是否已经存在。如果没有，我会尝试创建一个新的，并将其添加到会话中(添加到DB中(。
我已经在表中有了一些对象(通过其他方式创建(。参见表tag:中的值复制粘贴
id, Name
1 Sports
2 Business
3 Finance
4 World
5 US
6 UK
7 Technology
8 Science
9 Health
10 Video Games
11 IT
12 Startups
13 Europe
14 Apps
15 Space
我想我可能已经手动(通过SQL插入(将一些数据添加到数据库中，但其中一些是通过SQLAlchemy创建的。
在尝试添加新对象时，我遇到了非常奇怪的行为。
首先，它试图创建一个id为1的对象，但由于重复而失败。我没有保存堆栈跟踪，但我再次运行了它，它试图添加id为2的对象，这是堆栈跟踪。
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "tag_pkey"
DETAIL: Key (id)=(2) already exists.
全栈跟踪：
DEBUG:root:adding news tags AI
2022-03-28 06:52:33,188 INFO sqlalchemy.engine.Engine SELECT tag.id AS tag_id, tag.name AS tag_name
FROM tag
WHERE tag.name = %(name_1)s
LIMIT %(param_1)s
INFO:sqlalchemy.engine.Engine:SELECT tag.id AS tag_id, tag.name AS tag_name
FROM tag
WHERE tag.name = %(name_1)s
LIMIT %(param_1)s
2022-03-28 06:52:33,188 INFO sqlalchemy.engine.Engine [cached since 0.005467s ago] {'name_1': 'AI', 'param_1': 1}
INFO:sqlalchemy.engine.Engine:[cached since 0.005467s ago] {'name_1': 'AI', 'param_1': 1}
DEBUG:root:None
DEBUG:root:Creating a new tag: AI
2022-03-28 06:52:33,191 INFO sqlalchemy.engine.Engine INSERT INTO tag (name) VALUES (%(name)s) RETURNING tag.id
INFO:sqlalchemy.engine.Engine:INSERT INTO tag (name) VALUES (%(name)s) RETURNING tag.id
2022-03-28 06:52:33,191 INFO sqlalchemy.engine.Engine [generated in 0.00015s] {'name': 'AI'}
INFO:sqlalchemy.engine.Engine:[generated in 0.00015s] {'name': 'AI'}
2022-03-28 06:52:33,192 INFO sqlalchemy.engine.Engine ROLLBACK
INFO:sqlalchemy.engine.Engine:ROLLBACK
Traceback (most recent call last):
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
self.dialect.do_execute(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "tag_pkey"
DETAIL: Key (id)=(2) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/app/assign.py", line 59, in <module>
main()
File "/app/assign.py", line 46, in main
parse_feeds(session)
File "/app/models/VitalNewsFeed.py", line 77, in parse_feeds
feed.parse_feed(session)
File "/app/models/VitalNewsFeed.py", line 63, in parse_feed
new_news = News(entry=e, source=self.source, tags=self.tags, session=session)
File "<string>", line 4, in __init__
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 480, in _initialize_instance
manager.dispatch.init_failure(self, args, kwargs)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 477, in _initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
File "/app/models/VitalNews.py", line 178, in __init__
self.add_tag(tag, session)
File "/app/models/VitalNews.py", line 121, in add_tag
session.commit()
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1431, in commit
self._transaction.commit(_to_root=self.future)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit
self._prepare_impl()
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
self.session.flush()
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3363, in flush
self._flush(objects)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3503, in _flush
transaction.rollback(_capture_exception=True)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3463, in _flush
flush_context.execute()
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
util.preloaded.orm_persistence.save_obj(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 244, in save_obj
_emit_insert_statements(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1237, in _emit_insert_statements
result = connection._execute_20(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1620, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1487, in _execute_clauseelement
ret = self._execute_context(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1851, in _execute_context
self._handle_dbapi_exception(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2032, in _handle_dbapi_exception
util.raise_(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1808, in _execute_context
self.dialect.do_execute(
File "/app/.heroku/python/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "tag_pkey"
DETAIL: Key (id)=(2) already exists.
[SQL: INSERT INTO tag (name) VALUES (%(name)s) RETURNING tag.id]
[parameters: {'name': 'AI'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
我再次尝试运行脚本。现在它厌倦了创建id＝3
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "tag_pkey"
DETAIL: Key (id)=(3) already exists.
[SQL: INSERT INTO tag (name) VALUES (%(name)s) RETURNING tag.id]
[parameters: {'name': 'AI'}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
id=4
等等。每次都失败了，我不得不重新运行脚本。就好像它不知道标签表中id的最后一个值一样。在几次失败后，它创建了id＝9，这不是重复的，脚本继续正常工作。但这个错误很奇怪。在创建新对象之前，它不应该检查主键是否可用吗？现在这个错误不会在dev上重现。但当我推送我的代码来刺激时，同样的事情再次发生。
我正在使用PostgreSQL和SQLAlchemy。
这不是SQLAlchemy的错。是PostgresSQL和我用PostgreSQL客户端Postico在没有自动增量的情况下插入到表中，这打破了顺序。
运行"的SQL命令；SELECT setval('tag_id_seq'，(SELECT MAX(id(FROM标记(，true("修复它。