Thread: 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