Best way of gluing strings in Python to compose SQL query

Question

I'm coming from PHP background. When composing SQL queries I tend to do something like this:

$query = '
SELECT
    *
FROM `table` AS t
WHERE 
    t.`categoryID` = '.$categoryID.'
';

if(!empty($recordID))
{
$query .= '
    AND t.`recordID` = '.$recordID.'
';
}

$data = $db->fetchAll($query);

What would be the best/most efficient way of doing this in Python?

What DB library are you using? Probably you should avoid string concatenation altogether as it risks introducing SQL injection vulnerabilities. — Mark Byers
– Mark Byers, Commented Feb 11, 2012 at 0:58
To expand on @MarkByers comment: This is what the Psycopg people call a a naïve approach to the composition of query strings, e.g. using string concatenation - initd.org/psycopg/docs/… . Instead use query parameters to avoid SQL injection attacks and to automatically convert Python objects to and from SQL literals. stackoverflow.com/questions/3134691/… — Matthew Cornell
– Matthew Cornell, Commented Jun 10, 2013 at 13:25

Georgy · Accepted Answer · 2019-02-15 12:36:42Z

10

There are many way you can achieve it using Python. The simplest would be to either use the format string syntax or the Template object.

The advantage of the format string syntax is that you don't need to use another object. Example:

query = "SELECT * FROM `table` AS t WHERE t.`categoryID`={}".format(category_id)
if record_id:
    query += " AND t.`recordID`={}".format(record_id)

Although most of the time the database Python wrapper will let you make it more secure (preventing SQL injection):

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Leo Tolstoy", "1"))

You maybe interested in these links:

Here is how it can work for Postgresql:

ps = db.prepare("SELECT * FROM information_schema.tables WHERE table_name = $1 LIMIT $2")
ps("tables", 1)

edited Feb 15, 2019 at 12:36

Georgy

14k7 gold badges69 silver badges80 bronze badges

answered Feb 11, 2012 at 1:00

charlax

26.2k19 gold badges64 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

marcin_koss Over a year ago

I like this solution much better. Thanks.

charlax Over a year ago

Thanks! Make sure you properly escape though, otherwise you may have SQL injection.

marcin_koss Over a year ago

Thanks, I take it escaping method in your second example should be sufficient?

charlax Over a year ago

Yes! What database are you using? You maybe interested in this discussion too: stackoverflow.com/questions/897020/…

Delrius Euphoria Over a year ago

I suppose f'' instead of format will also prevent sql injections ? Aren't both the same?

monkut · Accepted Answer · 2012-02-11 01:01:04Z

5

Use the python DB-API parameter substitution:

symbol = 'IBM'

# Do this 
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

From: http://docs.python.org/library/sqlite3.html

answered Feb 11, 2012 at 1:01

monkut

44.1k26 gold badges133 silver badges161 bronze badges

1 Comment

marcin_koss Over a year ago

That's a nice solution but it's not really what I was looking for. What I like about the way I do it in this PHP example is that it is very easy to follow the logic for complex queries that change based on some data.

Collectives™ on Stack Overflow

Best way of gluing strings in Python to compose SQL query

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related