3.4 complains about string formatting inside of sql instructions.

You shouldn't be building up sql statements by string processing (can lead to xss attacks, it can kill performance, and it's just plain ugly).  Instead, use placeholders and pass parameters (note the comma):

    cursor.execute("..name = %s AND age = %s", (real_name, age))

or using named arguments:

    cursor.execute("..name = %(name)s AND age = %(age)s", dict(name=real_name, age=age))

See http://initd.org/psycopg/docs/usage.html for more.

Your response surprised me a bit.  

Using the curly brackets is a new standard with Python 3 (I failed to mention we use 3).  There is occasional talk about removing the older style, I don't know if it will happen, but the possibility of removing the newer method will almost certainly not happen.

Regarding it's subjectivity to xss attacks, I hadn't heard that.  Although in our environment it wouldn't apply.  Is the older style not subject?  Why is that?

The timing aspect is interesting, I'd be curious how different it is.  Are there any metrics?  This could be important to us, but it would be a lot of effort to change.  If significant, it could be worth the effort.  

As to esthetics, that would be subject to opinion.  If you like percent signs, parentheses and commas better than dollar signs, curly brackets and periods, you could be right.  I don't see that there is a difference.  Parentheses are used for many things, I've always thought the curly brackets stand out more as different.  

In any case, it is a coding standard we use.  And you never really addressed the issue that the code is valid but PyCharm signals an error.

I think you completely misunderstand what the %s does in my example code – it is not used for string formatting (and hence any reference to Python 3's use of {} for string formatting is irrelevant).

This is string formatting:

    cursor.execute("... and age = {}".format(42))

and so is this

    cursor.execute("... and age = %s" % 42)

note the %-sign before the 42. Both of these mean that the client will create a new string and send

    cursor.execute("... and age = 42")

to the server (a single string).

Problems arise if you're not careful with names such as `O'Malley` or user input like "42; drop table customers;" (sql injection).

Since it is sending a new string to the server every time, there is a great chance that the query optimizer cannot re-use its cached plans and it must parse and optimize the query again and again (and again).

This is not string formatting:

   cursor.execute("... and age = %s", (42,))

note the comma before `(42,)`, ie. we're passing 2 arguments to cursor.execute: a string with a placeholder (i.e. %s) and a tuple of arguments to bind to the placeholders.  It should also, for Postgres, always be %s regardless of which datatype is _bound_.  It's too bad that the postgres client chose to use %s as the placeholder since its association with string formatting is so strong (other clients use eg. "?" or ":1").

The client library (for reasonable clients) will send these two items on to the server as-is, and the query optimizer can happily re-use the query plan it cached after last time you called.  (Non-reasonable clients will at the very least do 100% correct quoting, ensuring that sql-injection attacks can't work).

ps: PyCharm was correct in signaling an error for the code you had (before you changed name to age). It didn't signal the correct error, but if you entered the expanded string into Postgres you would immediately find your syntax error (hint it's not there after you switched name to age ;-)  I guess PyCharm expects you to do it the correct way and use placeholders and pass data to be bound (read the link I suggested last time – passing pre-constructed strings really is a big bad no-no in database programming).

ok.  Thanks!

FWIW, the name vs. age was just a typo.  

I would like to comment that my approach may not be the best sql approach, but it is a valid Python approach.  The code I wrote works and should not be flagged as a sql error, regardless.  

But at least I can get rid of the errors, now.

This is definitely not resolved, even if the correct syntax is used.

return cursor.execute("select id from hosts where hostname = %s", (data['hostname']))

In this statement the %s is flagged as "expression expected" and afaik this is the correct python way to do this. How to get rid of the error?

I don't know if there is a problem in PyCharm or not, but the syntax is not correct

 

    (data['hostname'])

 

is a parenthesized expression, what you need is a 1-element tuple:

 

    (data['hostname'],)

 

note the added comma, iow., `(42)` is just 42, while `(42,)` is a tuple containing 42.  The full statement should then be:

 

    return cursor.execute("select id from hosts where hostname = %s", (data['hostname'],))

 

(normally you would return `cursor.fetchall()` to avoid having result-sets open, but that's another issue..)

Here is this issue, in IntelliJ. It is NOT fixed.

in case someone would arrive here. This is fixed https://youtrack.jetbrains.com/issue/PY-22877

Settings -> Tools -> Database -> User Parameters -> Enable in string literals with SQL injection

As of this date, this isn't yet fixed, or if it is, it is fixed for postgresql only.

I'm still seeing "errors" flagged by both IntelliJ and Pycharm using string.format("{named}", named=value) style, which is perfectly valid python (3) syntax, but regardless that all SQL inspections are disabled and the settings and described. The solution above switching to parameterised queries (which these are anyway) can't be used as most of the data being interpolated in this fashion are not able to be parameterised by the database - e.g. table and schema names, etc. sometimes complete where/order by/limit clauses and so on.

If I could just disable this inspection completely, I would. However it is a cosmetic issue that results in an extra dialog on vcs commit and annoying errors flagged in the UI.

 

 

 

 

I'm having a similar issue in PyCharm 2017.2 where I'm needing to build a SQL query based on a number of user inputs. As I build this, the names of tables and columns and the query syntax is changing, so String Formatting is required.

Take this example:

qry = 'select list, of, columns from table ' \
      'where special_names in ({0})'.format(','.join(['%s'] * len(special_names)))

Since the number of 'special_names' change, it would be difficult to add the inputs without string formatting. So PyCharm doesn't complain, I would need to incrementally build the string without formatting placeholders which is cumbersome and harder to read. Even then, PyCharm still complains about incomplete syntax.

I was able to fix this (and maybed have other problems masked, not sure) by adding a parameter pattern of "\{(\w+)\}".

This was done after the previous comment to enable string literals with SQL injection:

Settings -> Tools -> Database -> User Parameters -> Enable in string literals with SQL injection