Home > Software design >  SQLAlchemy stored procedure is not working in Postgres
SQLAlchemy stored procedure is not working in Postgres

Time：05-27

I have a table called city in PostgreSQL

CREATE TABLE CITY (
   CITYID serial PRIMARY KEY,
   CNAME VARCHAR (50) UNIQUE NOT NULL,
   STATE VARCHAR (50) NOT NULL
);

When I use simple select with SQLAlchemy it works fine

query = session.query(models.City)
cities = query.all() #works fine

cities = session.execute("SELECT cityid, cname FROM city where cityid = 1").fetchall() # works fine

By doing above, I make sure that my session works fine and connecting the DB properly.

When I try to call a stored procedure in SQLAlchemy it is not working.

Here is my 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;$$

Here is my SQLAlchemy code:

session.execute('CALL addcity (?, ?)', [('One', 'Two')])

The above code throws error as below:

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

I have tried changing the params like below:

session.execute('CALL addcity (?, ?)', [('One', 'Two')])

The above code throws

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)

When I checked SOF, most of the answers quoting the params as list of string. However, having strings inside the list as param didn't help either.

These are the library versions I have used:

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

CodePudding user response：

As noted in a comment to the question, psycopg2 does not like the commit; in the SP. If you remove that then this will work with 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)

CodePudding user response：

After @GordThompson and @AdrianKlaver asked some questions I have played around a bit and got this code as a solution

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 code

cname = "Five"
state = "Six"

session.execute('CALL addcity (:cname, :state)', {'cname' : cname, 'state': state})

session.commit()

This works like a charm! Thanks @GordThompson and @AdrianKlaver. You guys saved my time!

Page link：https//www.codepudding.com/Softwaredesign/421093.html

Prev:Python combinations of elements in dict
Next:Is there a way to plot a "function string" in python?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding