Accepted answer


SQLAlchemy has a text() function for wrapping text which appears to correctly escape the SQL for you.

I.e.

res = executeSql(sqlalchemy.text(sql))

using SQL to fetch data from a database, using SqlAlchemy. query contains the SQL like operator '%'.


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

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.


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()


if you don't want to escape % characters or use sqlalchemy.text(), is to use a regular expression.

Instead of:

select id from ref_geog where short_name LIKE '%opt'

Try (for case-sensitive match):

select id from ref_geog where short_name ~ 'opt$' 

or (for case-insensitive):

select id from ref_geog where short_name ~* 'opt$'
c.execute("SELECT * FROM t WHERE a = %s")

In other words, if you provide parameter (%s) in query, but you forget to add query params. In this case error message is very misleading.

cur_psql.mogrify needs positional parameters when you use it like that, but you can use named parameters like (%(customerName)s,%(contactName)s,%(address)s,%(city)s,%(postal_code)s,%(country)s)

With that said, instead of having python make a huge string of parameters, consider instead using cur_psql.executemany:

cur_psql.executemany("INSERT INTO tablename (name, contact, address, city, cap, country) VALUES (%(customerName)s,%(contactName)s,%(address)s,%(city)s,%(postal_code)s,%(country)s)", cur_msql)