TypeError:"dict"对象不支持在此查询的第二个实例上引发索引 [英] TypeError: 'dict' object does not support indexing thrown on second instance of this query

问题描述

因此，我正在使用以下代码根据烧瓶中的用户输入来构建查询:

So I am building a query based on user input in flask with this code:

    if empty_indic_form.validate_on_submit():
    query='select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('
    for i in indic_form.indicators.data:
        query=query+'\''+i+'\','
    query=query[:-1]+') '
    query=query+'and name in ('
    for c in order_c:
        query=query+c+','
    query=query[:-1]+')'
    data_return=db.engine.execute(query).fetchall()

我已经确认查询看起来像是应该的，甚至在更早的会话中它也返回了我期望的rowproxy对象列表.但是现在无论我做什么我都会收到此错误！

I have confirmed that query looks like what it is supposed to, and even had an earlier session where it was returning a list of rowproxy objects like I was expecting. But now I am getting this error no matter what I do!

我已将查询设置为模板中的变量，以便可以将其打印出来，这就是我得到的:

I have set query to a variable in the templates so I can print it out, and here is what I get:

select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('Energy savings of primary energy (TJ)','Adolescent birth rate (women aged 15-19 years)','Net migration rate','Transmission and distribution losses (%)') and name in ('Burkina Faso', 'Ghana', 'Saudi Arabia', 'Pakistan')

select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('Energy savings of primary energy (TJ)','Adolescent birth rate (women aged 15-19 years)','Net migration rate','Transmission and distribution losses (%)') and name in ('Burkina Faso', 'Ghana', 'Saudi Arabia', 'Pakistan')

我直接在Postgres DB上运行了该程序，结果非常出色.

I ran that directly on my Postgres DB and the result was grand.

在错误转储中，我注意到data_return=db.engine.execute(query).fetchall()行的构建带有一个空字典作为参数，当然，最后它会抛出该错误.我可以强迫它不要这样做吗?查询对象看起来像上面一样，现在又出了什么问题?刷新页面或转到主页后，我应该杀死数据库会话吗?

In the error dump I notice that the data_return=db.engine.execute(query).fetchall() line is building with an empty dictionary as the parameters, which of course throws that error in the end. Can I force it not to do this? The query object looks like it does above, whats wrong with it now? Should I perhaps be killing the db session upon refreshing the page or going to the home page?

推荐答案

方法中的基本错误是使用字符串串联来构建SQL查询.如果indic_form.indicators.data或order_c是用户提供的数据(例如来自HTTP请求的数据)，则您可能已经为

The fundamental error in your method is using string concatenation to build an SQL query. If indic_form.indicators.data or order_c is user provided data, for example from an HTTP request, you've probably opened yourself up for SQL injection. The error

TypeError: 'dict' object does not support indexing

是此串联的结果:您的查询字符串包含流氓"%"，它是 psycopg –通常与SQLAlchemy一起用于与Postgresql进行通信的DB-API.这正是不应该进行手动串联的原因.正确转义可能很困难.

is the result of this concatenation: your query string contains a rogue "%", which is part of the placeholder syntax of psycopg – the DB-API usually used with SQLAlchemy to talk to Postgresql. This is exactly the reason why manual concatenation should not be done. Getting escaping right can be hard.

使用元组自适应在Psycopg2中.在SQLAlchemy中，您可以使用 列运算符，或在版本1.2中引入的的扩展绑定参数 .

Passing a list of values to IN operator is done using tuples adaptation in Psycopg2. In SQLAlchemy you'd use the in_ column operator, or an expanding bind param that were introduced in version 1.2.

不幸的是，在您的特殊情况下，SQLAlchemy包装(引擎)有一个陷阱:由于您的所有参数都是元组，因此SQLAlchemy引擎认为您正在尝试将其迭代的参数元组传递为 text()来解决此问题. 构造，它允许DB-API不可知的绑定参数语法和字典作为参数容器:

Unfortunately in your particular case the SQLAlchemy wrapping, the engine, has a gotcha: as all your arguments would be tuples, the SQLAlchemy engine thinks that you're trying to pass it an iterable of argument tuples as multiparams and uses executemany() automatically. You can work around this by using the text() construct, which allows for DB-API agnostic bound parameter syntax and dictionaries as argument containers:

from sqlalchemy import text

...

if empty_indic_form.validate_on_submit():
    # Note that the placeholders shouldn't have enclosing parentheses
    query = """SELECT name, year, value, display_name
               FROM literal
               INNER JOIN ent ON ent_id = ent.id
               WHERE display_name IN :display_name
                 AND name IN :name"""

    data_return = db.engine.execute(
        text(query), {'display_name': tuple(indic_form.indicators.data),
                      'name': tuple(order_c)}).fetchall()

回顾一下:切勿使用字符串连接或手动格式化来构建SQL查询.始终使用占位符/绑定参数.

To recap: never build SQL queries using string concatenation or manual formatting. Always use placeholders / bound parameters.

这篇关于TypeError:"dict"对象不支持在此查询的第二个实例上引发索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！