How to do multi-line f strings?

Python Help
1

My config: Python 3.9, Pycharm CE 2023.3.2, on Windows 10 Pro. I’m using Qt Designer and pyuic6 6.4.2. I’m new to Python and have not yet completed a 62 hour tutorial on Python. (I’m using Python 3.9 for the long tutorial I’m doing. I will upgrade Python later.)

In the Python tutorial I’m doing I’m creating long SQL statements and using a multi-line f string to make them like this:

        cursor = self.conn.cursor()
        sql = (f'UPDATE products SET name={name}, price={price}, ' +
               'description={description} WHERE  id={product_id}')
        print("Update: SQL=" + sql)
        cursor.execute(sql)
        self.conn.commit()

But when the code is executed the program throws an error because after the first line the {variables} do not put in the values.

Is it possible to do a multi-line f string? Or do I have to do it the more wordy way like this?

sql = 'UPDATE products SET name=' + name + ', price=' + str(price) + ', ' +
'description=' + description + ' WHERE  id=' + str(product_id)

I’d like to put the SQL in a variable for debugging purposes.

Thank you!

EDIT: I have tried these things also:

sql = 'UPDATE products SET name=' + name + ', price=' + str(price) + ', ' \
 'description=' + description + ' WHERE  id=' + product_id

sql = "UPDATE products SET name={}, price={}, description={} "
"WHERE id={}".format(name,price,description,product_id)

sql = "UPDATE products SET name={}, price={}, description={} "+
"WHERE id={}".format(name,price,description,product_id)

They all get some type of error.

2

Use a triple-quoted f-string:

print(f"""
{1 + 2}
{3 * 4}
""")
3 
sql = (f'UPDATE products SET name={name}, price={price}, '
       f' description={description} WHERE  id={product_id}')

UPDATE：
you should NEVER use string concate to make a sql query.

use something like this, PLEASE

4 Likes
4

This worked for string assignment. Thanks!

sql = (f'UPDATE products SET name={name}, price={price}, '
       f' description={description} WHERE  id={product_id}')

Now my SQL has an error, but that’s another question. :slight_smile:

5

Why did it work? What did you do wrong?

6

You should not use this technique to create SQL queries. This is the classic way to create SQL injection vulnerabilities, which are among the most commercially important security issues. Real companies have suffered massive damage because of this.

Instead, use your SQL library’s functionality for parameterized queries. See here for a summary:

13 Likes
7

The correct example has no “+” at the end of the first line, and has an “f” at the beginning of the second line.

2 Likes
8

If the Python tutorial you’re following actually suggests that, I would be very wary of it’s other advice.

We state quite clearly in the tutorial of Python’s built-in sqlite module (which has an API consistent with that which you’re using, and other SQL DB API backends should support the same if not similar) that you should never do this and use placeholders instead:

Notice that ? placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks (see How to use placeholders to bind values in SQL queries for more details).

Furthermore, it has a full how-to for how to use parameterized queries to avoid bugs and security issues in your code and generally make your life easier.

Also, if you’re using a lot of SQL queries in your program, you might want to consider using an ORM like SQLAlchemy to automatically wrap your raw SQL into a data model and access them like normal Python objects rather than manually building all the raw queries yourself; particularly for someone who sin’t isn’t a SQL expert, this can be much easier to read and use, abstract away the low-level messy implementation details, eliminate whole classes of bugs and security vulnerabilities, make your code more portable across different DB backends, and even improve performance in some cases.

5 Likes
9

No, that was totally me guessing how to make this SQL readable inside the code for me. My SQL statements can be 1000 characters long broken into 10 lines, which is average. Some are longer.

10

Right. SQLAlchemy has a specific stuff to make this kind of thing
readable and safe, see:
https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-selecting-data

Using format strings is prone to errors and injection attacks, see:

If you hand roll your own SQL you have to difficulty of correctly
escaping your values for use in the SQL. Using placeholders lets the DB
library handle that correctly for you, as does using an ORM such as
SQLAlchemy.

Cheers,
Cameron Simpson cs@cskk.id.au

4 Likes
11

The moral of the story isn’t to sanitize database inputs but to parameterize database queries.

12

Just curious, how do you quantize hours?

13

“Sanitise” often seems to mean “correctly quote to preserve the value” or whatever variation is needed to deliver the value to the system which uses it.

I’ve always thought the “sanitise the inputs” phrasing is awful and misleading. We’re not “cleaning it up” - we’re handling it correctly so that the subsystems (shell, db, whatever) receive it correctly and also handle it correctly.

So, yes: parameterise the inputs so that they get correctly conveyed to the database.

1 Like
14

IMO that’s incorrect usage. Sanitizing would be like taking the heading from a document and constructing a slug from it, by removing anything that isn’t URL-safe (which is how Discourse ends up with “how-to-do-multi-line-f-strings” for this one). It’s removing the dangerous parts. And while I have seen sanitization performed on database inputs, it’s the sort of thing that will get a web site onto The Daily WTF.

Better approaches are (a) escaping, and (b) parameterizing. Escaping, if done properly, IS safe… but it’s also pointless work. It’s like using Python 2 and recognizing that the input() function evaluates what you type, so you carefully convert all strings into Python source code format before passing them through… instead of just NOT evaluating it. It’s a waste of effort escaping them and then having the server parse the SQL, plus it means that every query is unique (no taking advantage of prepared statements or anything).

Parameterizing is really the only way anything should be done. Your query should be a simple string literal (unless you actually do something dynamic, like changing the column list or something), and then the parameters are passed separately. Sure, some database clients will just escape the values and slip them in, but better ones pass the parameters independently. Way way more efficient.

1 Like
15

When I first started the tutorial it was 62 hours. There are many sections and each section has many chapters. Each chapter has a length and the website called Udemy sums up the hours for you for that tutorial.

The author has updated or added some sections and now it’s 72 hours long. It’s a very good tutorial and covers a lot of stuff.

1 Like
16

The teacher has suggested this method for parameterizing stuff for long SQL statements.

# Use ? as placeholder for parameters 
sql = 'UPDATE products SET name=?, price=?, description=? WHERE id=?' 
# Pass the parameters as a tuple 
params = (name, price, description, product_id) 
cursor.execute(sql, params)

I think I mentioned a single SQL statement that I use in production can be 1000+ characters, or even 80-100 lines long with 2-3 fields per line. So I needed a way to make the SQL readable in the code for debugging purposes.

1 Like
17

There are other ways of making them more readable. Readability should never come at the cost of reliability. What the teacher suggested IS the correct way to parameterize a query.

2 Likes
18

There are also named placeholders, see the second example here:

(The first example is an example of how to NOT generate SQL.)

From the third paragraph:

An SQL statement may use one of two kinds of placeholders: question
marks (qmark style) or named placeholders (named style). For the qmark
style, parameters must be a sequence whose length must match the
number of placeholders, or a ProgrammingError is raised. For the named
style, parameters must be an instance of a dict (or a subclass), which
must contain keys for all named parameters; any extra items are
ignored.

You might adopt named placeholders, eg (modified rom the example to use
a named placehodler:

 cur.execute("SELECT * FROM lang WHERE first_appeared = :year", {'year': 1972})

These examples are from the sqlite3 module docs, but the same syntax
is supposed to be supported by any Python DB-API complaint db backend.

1 Like
19

I’m not sure if that’s true. A simple reading of PEP 249 suggests that there’s only required to be one parameter style for any given backend, and the execute method should be given the correct format. Supporting more options is great, but I don’t think it’s required.

1 Like
20

Hmm. Indeed.

Still, worth a try for the OP - it’s a readability gain if it works for their backend.