I find it fascinating that there are apparently so many drivers (even wildly popular ones like psycopg2) that do not make use of the underlying protocol feature of separate parameter passing. Another disadvantage of doing it “manually” like psycopg2 is doing it is that it’s slighty less efficient (as the string will need to be manipulated and each argument escaped) but also that you can’t refer to the same parameter multiple times: the nth occurance of %s always refers to the nth argument that will be spliced into the string, whereas the protocol-native way of $1, $2 etc allows you to efficiently refer to the same argument multiple times. It’s not only more efficient, but it’s also user-friendlier, as you don’t need to repeat yourself.
I was also unaware that this feature was added relatively recently (in 2003). This explains why so many people still tend to believe you have to use prepared statements, as if this is the only way to pass parameters separately (and of course because AFAIK MySQL does not have this feature).
Shameless plug: Of course the CHICKEN libpq bindings correctly pass parameters using the built-in protocol support :)
even if I’m surprised that psycopg2 don’t use native statement, the security warnign at the end looks like too much, psycopg2 is very unlikely to make escape wrong
Agreed, but it is a (minor) unnecessary risk. Personally, I think it’s most annoying that psycopg2 doesn’t use the native syntax, if you’re used to that you have to remember to use %s.
Actually %s is one of the three native syntax in python (?,%s,:1,:name), The more diverse the native syntax are the harder they are to remember for a python developper
Don’t forget {} (the newer(?) str.format() syntax). It’s a tradeoff of course, but it emphasizes Python rather than Postgres. libpq could’ve also used %s (that’s the placeholder for strings in C printf() too) but they didn’t.
I find it fascinating that there are apparently so many drivers (even wildly popular ones like psycopg2) that do not make use of the underlying protocol feature of separate parameter passing. Another disadvantage of doing it “manually” like psycopg2 is doing it is that it’s slighty less efficient (as the string will need to be manipulated and each argument escaped) but also that you can’t refer to the same parameter multiple times: the nth occurance of
%salways refers to the nth argument that will be spliced into the string, whereas the protocol-native way of$1,$2etc allows you to efficiently refer to the same argument multiple times. It’s not only more efficient, but it’s also user-friendlier, as you don’t need to repeat yourself.I was also unaware that this feature was added relatively recently (in 2003). This explains why so many people still tend to believe you have to use prepared statements, as if this is the only way to pass parameters separately (and of course because AFAIK MySQL does not have this feature).
Shameless plug: Of course the CHICKEN libpq bindings correctly pass parameters using the built-in protocol support :)
even if I’m surprised that psycopg2 don’t use native statement, the security warnign at the end looks like too much, psycopg2 is very unlikely to make escape wrong
Agreed, but it is a (minor) unnecessary risk. Personally, I think it’s most annoying that psycopg2 doesn’t use the native syntax, if you’re used to that you have to remember to use
%s.Actually %s is one of the three native syntax in python (?,%s,:1,:name), The more diverse the native syntax are the harder they are to remember for a python developper
Don’t forget
{}(the newer(?)str.format()syntax). It’s a tradeoff of course, but it emphasizes Python rather than Postgres.libpqcould’ve also used%s(that’s the placeholder for strings in Cprintf()too) but they didn’t.unless I mistake {} is not an option in the DB-API standard api for python
Ah, you can use
:nameand:1in the DB-API? That’s kind of cool and goes a long way towards readability and power.you can only use it if your driver enable it. A driver chose one of several of the three options