Python, SQLAlchemy pass parameters in connection.execute

Question

I am using SQLAlchemy connection.execute(sql) to transform select results to array of maps. Have following code


def __sql_to_data(sql):
    result = []
    connection = engine.connect()
    try:
        rows = connection.execute(sql)
        for row in rows:
            result_row = {}
            for col in row.keys():
                result_row[str(col)] = str(row[col])
            result.append(result_row)
    finally:
        connection.close()
    return result

and e.g.

__sql_to_data(sql_get_scan_candidate)

gives me nice data structure (Of course I am using this for small data sets). But in order to add parameter to sql I am currently using format e.g.

return __sql_to_data(sql_get_profile.format(user_id))

Question How to modify procedure to make possible something like

return __sql_to_data(sql_get_profile,user_id)

score 117 · Accepted Answer · edited May 21 '21 at 12:28

117

The tutorial gives a pretty good example for this:

>>> from sqlalchemy.sql import text
>>> s = text(
...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...         "FROM users, addresses "
...         "WHERE users.id = addresses.user_id "
...         "AND users.name BETWEEN :x AND :y "
...         "AND (addresses.email_address LIKE :e1 "
...             "OR addresses.email_address LIKE :e2)")
SQL>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() 
[(u'Wendy Williams, wendy@aol.com',)]

First, take your SQL string and pass it to sqalchemy.sql.text(). This isn't necessary, but probably a good idea...

The advantages text() provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally.

Note that even if you didn't use text(), you should NEVER just use sql.format(...). This leads to greater risk of SQL injection attacks.

Next, you can specify the actual arguments using keyword parameters to the execute() function you've already been using.

Now, in your example, you have a function that wraps the execute functionality. So, if you want to use this for multiple queries, you'll need to make the parameters able to receive your arguments. You could do this pretty simple as a dictionary:

def _sql_to_data(sql, values):
    ...
    conn.execute(sql, values)

values would be a dictionary.You could then use your function like this...

sql = 'SELECT ...'
data = { 'user_id' : 3 }
results = _sql_to_data(sql, data)

Using keywords as your parameters is just one way of specifying the arguments to the execute() function. You can read the documentation for that function for a few different ways.

edited May 21 '21 at 12:28

Tobias Feil

2,399
3
25
41

answered Oct 11 '13 at 17:53

Mark Hildreth

42,023
11
120
109

3

Thank you, my bad, by some reason I was not able to find this in doc. My only one excuse SQLAlchemy doc is somewhat fragmented with emphasis on ORM. – Denis Oct 12 '13 at 10:11
One more question - how fetchAll() and then itterating in results data structure relates to for row in rows in terms of memory consumption? – Denis Oct 12 '13 at 10:36
@Denis: I'm not sure exactly what you mean. Perhaps you should create another StackOverflow question, and try to be more specific about what you are asking. – Mark Hildreth Oct 12 '13 at 19:45
1

`text` does not let you create a list of values which is a tremendous downside and often makes it unusable in many of my cases making format the only real way. – swade Jul 12 '18 at 16:49
@stevenwade Format is never the real way, unleas you mean formatting the required placeholders for the list values, and SQLAlchemy even does it for you using [`bindparam(..., expanding=True)`](https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.bindparam.params.expanding). On the other hand some DB-API drivers adapt arrays out of the box to suitable SQL. – Ilja Everilä Nov 10 '18 at 12:51
I was spinning my wheels on this for awhile until I came across this post. Using python3.7 and the `text` method was indeed necessary. Thanks for the great answer! – aaron May 07 '19 at 20:48
Hi @MarkHildreth, I've tried your solution but I have the following error: `get_bind() got an unexpected keyword argument 'name'`. I'm trying to execute the following query: `query = sqlalchemy.sql.text("INSERT INTO public.migrations (name) VALUES (:name)")` and I have the following tupple object: `migrations = ({"name": "__init__.yaml"})` – br1 Feb 12 '20 at 10:14
3

In 2.0 style, values must be passed as a dict (or list of dicts); individual keyword arguments are no longer accepted. – snakecharmerb Aug 24 '22 at 15:36
1

9 years later @MarkHildreth still answering my questions – tjholub Sep 23 '22 at 16:07
With the query being `text` one can also use the `params=` argument for `execute` and provide a dictionary. – t3chb0t Jul 17 '23 at 14:05

score 9 · Answer 2 · answered Jan 27 '23 at 07:05

9

This is the latest way to bind params for session execute

stmt = text("SELECT * FROM attendance WHERE user_id =:x")
            stmt = stmt.bindparams(x="1")
            res= session.execute(stmt).all()

answered Jan 27 '23 at 07:05

Tanjin Alam

1,728
13
15

Python, SQLAlchemy pass parameters in connection.execute

2 Answers2

Linked

Related