SQLAlchemy stored procedure is not working in Postgres

Question

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

psycopg2 does not like the commit; in your SP, details here. I can get it to work with a vanilla psycopg2 connection if I remove the commit from the SP and add cnxn.commit() after crsr.execute(…) in Python. — Gord Thompson
– Gord Thompson, Commented May 26, 2022 at 23:15
@GordThompson I haven't tried the Psycopg2 vanilla SQL. I am working only with SQLAlchemy to get it done. However, I will try your answer as a backup plan. Thanks — Raja SGS
– Raja SGS, Commented May 26, 2022 at 23:19
1) I am not seeing a difference in your examples. 2)psycopg2 uses %s or %(name)s as parameters not ?. — Adrian Klaver
– Adrian Klaver, Commented May 26, 2022 at 23:21
@GordThompson Your code works on Postgres. I can keep your solution as a plan B. Thanks a lot. However, I am looking for SQLAlchemy supported solution. — Raja SGS
– Raja SGS, Commented May 26, 2022 at 23:29

Gord Thompson · Accepted Answer · 2022-05-26 23:40:34Z

3

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)

answered May 26, 2022 at 23:40

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raja SGS · Accepted Answer · 2022-05-26 23:42:18Z

0

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!

answered May 26, 2022 at 23:42

Raja SGS

632 silver badges6 bronze badges

2 Comments

Gord Thompson Over a year ago

Note that passing a raw string to session.execute() is deprecated and will be removed in SQLAlchemy 2.0. From now on we should be passing a TextClause to session.execute(). We can create it using text().

Raja SGS Over a year ago

Thanks @GordThompson for the heads-up. Sure I will make a note of it and fix them soon.

Collectives™ on Stack Overflow

SQLAlchemy stored procedure is not working in Postgres

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related