Python、SQLAlchemy 在 connection.execute 中传递参数 [英] Python, SQLAlchemy pass parameters in connection.execute

问题描述

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

<预><代码>def __sql_to_data(sql):结果 = []连接 = engine.connect()尝试:行 = connection.execute(sql)对于行中的行:result_row = {}对于 row.keys() 中的 col:result_row[str(col)] = str(row[col])结果.追加(result_row)最后:连接.close()返回结果

和例如

__sql_to_data(sql_get_scan_candidate)

给了我很好的数据结构(当然我将它用于小数据集).但是为了向 sql 添加参数，我目前正在使用格式，例如

返回 __sql_to_data(sql_get_profile.format(user_id))

问题如何修改程序以实现类似

返回__sql_to_data(sql_get_profile,user_id)

解决方案

教程为此提供了一个很好的例子:

<预><代码>>>>从 sqlalchemy.sql 导入文本>>>s = 文本(... "SELECT users.fullname ||', ' ||address.email_address AS 标题...来自用户，地址"... "WHERE users.id =addresses.user_id "... "AND users.name BETWEEN :x AND :y "... "AND (addresses.email_address LIKE :e1 ";...或addresses.email_address LIKE :e2)")SQL>>>conn.execute(s, x='m', y='z', e1='[e-mail]', e2='[e-mail]').fetchall()[(你温迪威廉姆斯，[e-mail]'，)]

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

<块引用>

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

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

接下来，您可以使用关键字参数指定实际参数到 execute() 您已经使用过的函数.

现在，在您的示例中，您有一个包装执行功能的函数.因此，如果您想将其用于多个查询，您需要使参数能够接收您的参数.你可以像字典一样简单地做到这一点:

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

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

sql = 'SELECT ...'数据 = { 'user_id' : 3 }结果 = _sql_to_data(sql, 数据)

使用关键字作为参数只是为 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)

解决方案

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='[e-mail]', e2='[e-mail]').fetchall() 
[(u'Wendy Williams, [e-mail]',)]

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.

这篇关于Python、SQLAlchemy 在 connection.execute 中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！