我在 PostgreSQL 中有一張名為 city 的表
CREATE TABLE CITY (
CITYID serial PRIMARY KEY,
CNAME VARCHAR (50) UNIQUE NOT NULL,
STATE VARCHAR (50) NOT NULL
);
當我將簡單的選擇與 SQLAlchemy 一起使用時，它可以正常作業
query = session.query(models.City)
cities = query.all() #works fine
cities = session.execute("SELECT cityid, cname FROM city where cityid = 1").fetchall() # works fine
通過上述操作，我確保我的會話作業正常并正確連接資料庫。
當我嘗試在 SQLAlchemy 中呼叫存盤程序時，它不起作用。
這是我的 SP（Postgres）
create or replace procedure addcity(
cname varchar(20),
state varchar(20)
)
language plpgsql
as $$
begin
insert into city
(cname, state)
values (cname, state);
commit;
end;$$
這是我的 SQLAlchemy 代碼：
session.execute('CALL addcity (?, ?)', [('One', 'Two')])
上面的代碼拋出如下錯誤：
Traceback (most recent call last):
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 369, in run_asgi
result = await app(self.scope, self.receive, self.send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 59, in __call__
return await self.app(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/applications.py", line 208, in __call__
await super().__call__(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
await self.middleware_stack(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
raise exc from None
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
await self.app(scope, receive, _send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
raise exc from None
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
await self.app(scope, receive, sender)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 580, in __call__
await route.handle(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 241, in handle
await self.app(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 52, in app
response = await func(request)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/routing.py", line 219, in app
raw_response = await run_endpoint_function(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/routing.py", line 154, in run_endpoint_function
return await run_in_threadpool(dependant.call, **values)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/concurrency.py", line 40, in run_in_threadpool
return await loop.run_in_executor(None, func, *args)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/Users/str-kwml0020/projects/fastapi-crud-sqlite/./main.py", line 128, in add_city
session.execute('CALL addcity (?, ?)', ['One', 'Two'])
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1575, in _execute_20
args_10style, kwargs_10style = _distill_params_20(parameters)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/util.py", line 142, in _distill_params_20
raise exc.ArgumentError(
sqlalchemy.exc.ArgumentError: List argument must consist only of tuples or dictionaries
我嘗試更改如下引數：
session.execute('CALL addcity (?, ?)', [('One', 'Two')])
上面的代碼拋出
Traceback (most recent call last):
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/uvicorn/protocols/http/h11_impl.py", line 369, in run_asgi
result = await app(self.scope, self.receive, self.send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 59, in __call__
return await self.app(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/applications.py", line 208, in __call__
await super().__call__(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
await self.middleware_stack(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
raise exc from None
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
await self.app(scope, receive, _send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
raise exc from None
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
await self.app(scope, receive, sender)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 580, in __call__
await route.handle(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 241, in handle
await self.app(scope, receive, send)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/routing.py", line 52, in app
response = await func(request)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/routing.py", line 219, in app
raw_response = await run_endpoint_function(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/fastapi/routing.py", line 154, in run_endpoint_function
return await run_in_threadpool(dependant.call, **values)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/starlette/concurrency.py", line 40, in run_in_threadpool
return await loop.run_in_executor(None, func, *args)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/Users/str-kwml0020/projects/fastapi-crud-sqlite/./main.py", line 128, in add_city
session.execute('CALL addcity (?, ?)', [('One', 'Two')])
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1583, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1452, in _execute_clauseelement
ret = self._execute_context(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1814, in _execute_context
self._handle_dbapi_exception(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1995, in _handle_dbapi_exception
util.raise_(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1771, in _execute_context
self.dialect.do_execute(
File "/opt/anaconda3/envs/fastapi38/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near ","
LINE 1: CALL addcity (?, ?)
^
[SQL: CALL addcity (?, ?)]
(Background on this error at: https://sqlalche.me/e/14/f405)
當我檢查 SOF 時，大多數答案都將引數參考為字串串列。但是，將串列中的字串作為引數也無濟于事。
這些是我使用的庫版本：
fastapi==0.68.0
uvicorn==0.14.0
sqlalchemy_filters==0.12.0
SQLAlchemy==1.4.21
typing-extensions==3.10.0.0
Werkzeug==2.0.1
FastAPI-SQLAlchemy==0.2.1
Flask==2.0.1
Flask-SQLAlchemy==2.5.1
PyYaml
psycopg2-binary==2.9.1
uj5u.com熱心網友回復：
正如對該問題的評論中所述，psycopg2 不喜歡commit;SP 中的 。如果你洗掉它，那么這將適用于 SQLAlchemy：
import sqlalchemy as sa
from sqlalchemy.orm import Session
engine = sa.create_engine("postgresql://scott:[email protected]/test")
with Session(engine) as session, session.begin():
session.execute(
sa.text("CALL addcity (:param1, :param2)"),
{"param1": "One", "param2": "Two"},
)
# automatically commits when the context manager exits (assuming no errors)
uj5u.com熱心網友回復：
在@GordThompson 和@AdrianKlaver 問了一些問題后，我玩了一會兒，并得到了這個代碼作為解決方案
SP：
create or replace procedure addcity(
cname varchar(20),
state varchar(20)
)
language plpgsql
as $$
begin
insert into city
(cname, state)
values (cname, state);
# removed commit from the original SP
end;$$
Python代碼
cname = "Five"
state = "Six"
session.execute('CALL addcity (:cname, :state)', {'cname' : cname, 'state': state})
session.commit()
這就像一個魅力！感謝@GordThompson 和@AdrianKlaver。你們節省了我的時間！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/481675.html
標籤：Python PostgreSQL sqlalchemy 烧瓶-sqlalchemy