Re: Value substitutions with a dictionary.

Adrian, thanks for pointing out the mistake.

On Fri, Nov 15, 2013 at 4:30 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> 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(dot)klaver(at)gmail(dot)com
>