On Feb 2, 9:22 pm, Tim Roberts <[EMAIL PROTECTED]> wrote:
> Also, psycopg will do the quoting for you. You don't do it. So this is
> what you want:
Second the above, it is much cleaner to leave the quoting to
psycopg2. I know, I wrote my own quoting logic for dynamically
generated queries and I was happy as happy as a turkey on boxing day
to dump that code overboard when I realized I didn't need it anymore.
However, I think you are better off using dictionary based
substitutions:
cur.execute("SELECT * FROM names WHERE name= %(name)s ",
{"name":"S"} )
On small and simple queries it doesn't matter much and using dicts is
actually a bit harder. On complex queries you may find:
- lots of binds.
- the same bind variable being used in several places
- optimization/adjustments may require you to rewrite the query
several times and shuffling the binds around into different
positions.
Maintaining positional binds is then a huge hassle compared to name-
based binds.
For the lazy, using dicts has another advantage:
name = "S"
firstname = "F"
cur.execute("SELECT * FROM names WHERE name = %(name)s and firstname =
%(firstname)s ", locals() )
Both MySQL and postgresql can work from the above examples as they
both accept name-binds and dicts. Something which is not all that
clear in the docs. Not all dbapi implementations allow this though.
Last but definitely not least, using dicts allows you to re-use bind
data more efficiently/painlessly:
di_cust = {"cust_id":
mynewcustid,"another_field_only_on_customer_table":3}
execute("insert into customer (cust_id....) values (%
(cust_id)s...)",di_cust)
for order in get_order_lines():
di_order = {"order_id":order.order_id}
#grab shared field data from the customer dict.
another_field_only_on_customer_table will be ignored later.
di_order.update(di_cust)
execute("insert into customer_orders (cust_id,order_id....) values (%
(cust_id)s, %(order_id)s...)",di_order)
None of this is very attractive if you believe in only using ORMs, but
it is bread and butter to looking after complex sql by yourself.
Cheers
--
http://mail.python.org/mailman/listinfo/python-list
