Python、SQLAlchemy 在 connection.execute 中传递参数

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19314342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-19 13:25:25  来源:igfitidea点击:

Python, SQLAlchemy pass parameters in connection.execute

pythonsqlsqlalchemyparameter-passing

提问by Denis

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

我正在使用 SQLAlchemy connection.execute(sql) 将选择结果转换为映射数组。有以下代码


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)
给了我很好的数据结构(当然我将它用于小数据集)。但是为了向 sql 添加参数,我目前正在使用格式,例如

return __sql_to_data(sql_get_profile.format(user_id))

QuestionHow to modify procedure to make possible something like

问题如何修改程序以使其成为可能

return __sql_to_data(sql_get_profile,user_id)

采纳答案by Mark Hildreth

The tutorialgives 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...

首先,获取您的 SQL 字符串并将其传递给sqalchemy.sql.text()。这不是必需的,但可能是个好主意......

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.

text() 提供的优于普通字符串的优势是对绑定参数、每个语句执行选项以及绑定参数和结果列类型行为的后端中立支持,允许 SQLAlchemy 类型构造在执行语句时发挥作用按字面指定。

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

请注意,即使您没有使用text(),也不应该只使用sql.format(...). 这会导致更大的SQL 注入攻击风险。

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

接下来,您可以使用关键字参数为您已经使用的execute()函数指定实际参数。

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)

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

values将是一本字典。然后你可以像这样使用你的函数......

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 documentationfor that function for a few different ways.

使用关键字作为参数只是指定execute()函数参数的一种方式。您可以通过几种不同的方式阅读函数的文档