SQLAlchemy +异步‘t不捕获延迟异常
SQLAlchemy +异步‘t不捕获延迟异常
提问于 2022-06-13 16:07:30
我现在使用的是SQLAlchemy 1.4、异步code和FastAPI，我编写的代码如下：
try:
cr: sa.engine.CursorResult = await conn.execute(stmt)
return schemas.UserGroup(
**user_group_dict,
id=cr.inserted_primary_key[0],
)
except sa.exc.IntegrityError:
raise exceptions.conflict_exception()UserGroup表引用用户和组表，并具有唯一的键约束(用户、组)
user_groups = sa.Table(
"auth_user_groups",
metadata,
sa.Column(
"id",
sa.BigInteger,
primary_key=True,
index=True,
),
sa.Column(
"user_id",
sa.BigInteger,
sa.ForeignKey("auth_user.id"),
),
sa.Column(
"group_id",
sa.BigInteger,
sa.ForeignKey("auth_group.id"),
),
sa.UniqueConstraint("user_id", "group_id"),
)CREATE TABLE IF NOT EXISTS public.auth_user_groups
(
id bigint NOT NULL DEFAULT nextval('auth_user_groups_id_seq'::regclass),
user_id integer NOT NULL,
group_id integer NOT NULL,
CONSTRAINT auth_user_groups_pkey PRIMARY KEY (id),
CONSTRAINT auth_user_groups_user_id_group_id_94350c0c_uniq UNIQUE (user_id, group_id),
CONSTRAINT auth_user_groups_group_id_97559544_fk_auth_group_id FOREIGN KEY (group_id)
REFERENCES public.auth_group (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT auth_user_groups_user_id_6a12ed8b_fk_auth_user_id FOREIGN KEY (user_id)
REFERENCES public.auth_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED
)当我尝试插入一个复制的记录时，它可以正常工作。
但是，当我试图插入一个在用户和组中不存在的fk记录时，我无法捕获异常。
2022-06-14 01:02:50,978 INFO sqlalchemy.engine.Engine COMMIT
ERROR: Exception in ASGI application
Traceback (most recent call last):
File "/home/test/projects/fastapi-from-dj/venv/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 366, in run_asgi
result = await app(self.scope, self.receive, self.send)
File "/home/test/projects/fastapi-from-dj/venv/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
return await self.app(scope, receive, send)
...
File "/home/test/projects/fastapi-from-dj/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 685, in do_commit
dbapi_connection.commit()
File "/home/test/projects/fastapi-from-dj/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 741, in commit
self._handle_exception(error)
File "/home/test/projects/fastapi-from-dj/venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 682, in _handle_exception
raise translated_error from error
sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.ForeignKeyViolationError'>: insert or update on table "auth_user_groups" violates foreign key constraint "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id"
DETAIL: Key (user_id)=(15) is not present in table "auth_user".
(Background on this error at: https://sqlalche.me/e/14/gkpj)当然，我试图捕捉asyncpg.exceptions.ForeignKeyViolationError和Exception as e的异常，但失败了。
谢谢。
回答 1
Stack Overflow用户
发布于 2022-06-14 02:14:10
感谢@Gord Thompson的评论
我能够通过更改表FK约束来解决我的问题：
ALTER TABLE IF EXISTS public.auth_user_groups
DROP CONSTRAINT auth_user_groups_user_id_6a12ed8b_fk_auth_user_id;
ALTER TABLE IF EXISTS public.auth_user_groups
ADD CONSTRAINT auth_user_groups_user_id_6a12ed8b_fk_auth_user_id FOREIGN KEY (user_id)
REFERENCES public.auth_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT DEFERRABLE;因此，我现在有一张这样的桌子：
CREATE TABLE IF NOT EXISTS public.auth_user_groups
(
id bigint NOT NULL DEFAULT nextval('auth_user_groups_id_seq'::regclass),
user_id integer NOT NULL,
group_id integer NOT NULL,
CONSTRAINT auth_user_groups_pkey PRIMARY KEY (id),
CONSTRAINT auth_user_groups_user_id_group_id_94350c0c_uniq UNIQUE (user_id, group_id),
CONSTRAINT auth_user_groups_group_id_97559544_fk_auth_group_id FOREIGN KEY (group_id)
REFERENCES public.auth_group (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT auth_user_groups_user_id_6a12ed8b_fk_auth_user_id FOREIGN KEY (user_id)
REFERENCES public.auth_user (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)它没有一行：DEFERRABLE INITIALLY DEFERRED
我现在使用Django迁移的表，以便FastAPI将它们作为FastAPI使用。
我相信以下答案链接将对Django用户有用：
How can I set a table constraint "deferrable initially deferred" in django model?
谢谢。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72605864
复制相关文章
相似问题
腾讯云开发者
