Re: Value substitutions with a dictionary.

On 11/15/2013 01:13 PM, jared wrote:

I have always done this as noted in the docs:

cur.execute("INSERT INTO foo VALUES (%s)",  ("bar",))  # correct

However, in my current usecase I was wanting to do this with a dict:

allof = {
             'fruit': 'banana',
             'vegetable': 'broccoli'
             }
'a %(fruit)s tastes great' % allof


#-----------------------------------------



so I tried the following and it worked:



CREATE TABLE bbalup
(
   a character varying,
   b character varying
);



import psycopg2
from datetime import datetime

dateof = datetime.now()

try:
     conn = psycopg2.connect("dbname='' user='' host='' password='' ")
except:
     print("I am unable to connect")
cur = conn.cursor()
sample = {
           'a': 1,
           'b': 2
           }

#cur.execute("""insert into bbalup values('3','2','1')""")
cur.execute("""insert into bbalup(a, b) values(%(a)s, %(b)s)""" % (sample))
#cur.execute("""insert into bbalup(b,a,c) values(%s, %s, %s)""" % (sample))
conn.commit()
conn.close()





#-----------------------------------------


but when I did this it did not work.



CREATE TABLE bbalup
(
   some_text character varying,
   some_date timestamp without time zone
);


Then I executed this:

import psycopg2
from datetime import datetime

dateof = datetime.now()

try:
     conn = psycopg2.connect("dbname='' user='' host='' password='' ")
except:
     print("I am unable to connect")
cur = conn.cursor()
sample = {
           'some_text': 'a',
           'some_date': dateof
           }

#cur.execute("""insert into bbalup values('3','2','1')""")
cur.execute("""insert into bbalup(some_text, some_date)
values(%(some_text)s, %(some_date)s)""" % (sample))
#cur.execute("""insert into bbalup(b,a,c) values(%s, %s, %s)""" % (sample))
conn.commit()
conn.close()



And it gives me the below error:

ProgrammingError: syntax error at or near "15"
LINE 1: ...bbalup(some_text, some_date) values(a, 2013-11-15 15:59:05.1...




So it looks like using the dictionary method is not telling postgres it
is inserting a date, is there a way around this or is dictionary text
substitution not supporting - or am I looking at this in the wrong way?

You are doing it the wrong way, see below for details:

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

Basically this:

cur.execute("""insert into bbalup(some_text, some_date) values(%(some_text)s, %(some_date)s)""" % (sample))

should be:

cur.execute("insert into bbalup(some_text, some_date) values(%(some_text)s, %(some_date)s)",  sample)

thanks in advance.

--
Adrian Klaver
adrian.klaver@gmail.com