How to use positional parameters in SQLAlchemy

Question

How does one use positional / unnamed parameters in SQLAlchemy? This doesn't work.. I know named parameters work but sometimes I prefer unnamed parameters.

engine = sqlalchemy.engine.create_engine('mysql://py:123@localhost/py', echo=True)
con = engine.connect()
res = con.execute(text("select uid, name from users where uid = ?"), 1);

Traceback (most recent call last):
  File "/home/olaf/py/./test.py", line 10, in <module>
    res = con.execute(text("select uid, torrent_pass from xbt_users where uid = ?"), 1);
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1090, in _execute_clauseelement
    keys = list(distilled_params[0].keys())
AttributeError: 'list' object has no attribute 'keys'```

Related, using positional parameters in SQLAlchemy 2.x.

snakecharmerb
– snakecharmerb

2025-04-12 06:13:32 +00:00
Commented Apr 12 at 6:13 — snakecharmerb
– snakecharmerb, Commented Apr 12 at 6:13

Suraj Rao · Accepted Answer · 2022-09-09 14:18:58Z

1

Wrapping the query in text is breaking your code, you need to adjust the syntax. Here is an example from the documentation https://docs.sqlalchemy.org/en/14/core/sqlelement.html?highlight=text#sqlalchemy.sql.expression.text

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

Below you will find a working version of your code:

>>> from sqlalchemy.sql import text
>>> s = text("select uid, name from users where uid = :uid")
>>> conn.execute(s, uid=1).first()

edited Sep 9, 2022 at 14:18

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Sep 9, 2022 at 14:06

Konstantin Grigorov

1,67816 silver badges21 bronze badges

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

Comments

Vova · Accepted Answer · 2021-04-10 15:17:37Z

-2

Try to use like that:

res = con.execute(
    "SELECT uid, name from users where uid = %s", 1)

answered Apr 10, 2021 at 15:17

Vova

3,6173 gold badges19 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to use positional parameters in SQLAlchemy

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related