Python, SQLAlchemy pass parameters in connection.execute

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)

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

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, [email protected]',)]

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.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply