Python SQL query string formatting

by

Sorry for posting to such an old thread — but as someone who also shares a passion for pythonic ‘best’, I thought I’d share our solution.

The solution is to build SQL statements using python’s String Literal Concatenation (http://docs.python.org/), which could be qualified a somewhere between Option 2 and Option 4

Code Sample:

sql = ("SELECT field1, field2, field3, field4 "
       "FROM table "
       "WHERE condition1=1 "
       "AND condition2=2;")

Works as well with f-strings:

fields = "field1, field2, field3, field4"
table = "table"
conditions = "condition1=1 AND condition2=2"

sql = (f"SELECT {fields} "
       f"FROM {table} "
       f"WHERE {conditions};")

Pros:

  1. It retains the pythonic ‘well tabulated’ format, but does not add extraneous space characters (which pollutes logging).
  2. It avoids the backslash continuation ugliness of Option 4, which makes it difficult to add statements (not to mention white-space blindness).
  3. And further, it’s really simple to expand the statement in VIM (just position the cursor to the insert point, and press SHIFT-O to open a new line).

More Related Contents:

  1. How to use % symbol correctly
  2. How do I print curly-brace characters in a string while using .format?
  3. How can I fill out a Python string with spaces?
  4. Python’s many ways of string formatting — are the older ones (going to be) deprecated?
  5. python list in sql query as parameter [duplicate]
  6. How can I format a decimal to always show 2 decimal places?
  7. Format a datetime into a string with milliseconds
  8. Passing table name as a parameter in psycopg2
  9. What’s the difference between %s and %d in Python string formatting?
  10. Connecting to Microsoft SQL server using Python
  11. sqlalchemy: how to join several tables by one query?
  12. Format strings vs concatenation
  13. PYODBC–Data source name not found and no default driver specified
  14. Logging variable data with new format string
  15. Python Pandas write to sql with NaN values
  16. How to get a single result from a SQL query in python?
  17. How to implement conditional string formatting?
  18. How do I escape curly-brace ({}) characters in a string while using .format (or an f-string)?
  19. pyodbc insert into sql
  20. Most Pythonic way to print *at most* some number of decimal places [duplicate]
  21. Python String Formats with SQL Wildcards and LIKE
  22. How to perform OR condition in django queryset?
  23. How to convert escaped characters? [duplicate]
  24. Getting random row through SQLAlchemy
  25. Select NULL Values in SQLAlchemy
  26. How to check the existence of a row in SQLite with Python?
  27. How to quote a string value explicitly (Python DB API/Psycopg2)
  28. Pandas analogue to SQL MINUS / EXCEPT operator, using multiple columns
  29. What’s the difference between %s and %d in string formatting?
  30. How to delete a record in Django models?

Leave a Comment