Re: Value substitutions with a dictionary.
От | Adrian Klaver |
---|---|
Тема | Re: Value substitutions with a dictionary. |
Дата | |
Msg-id | 5286928D.6060909@gmail.com обсуждение исходный текст |
Ответ на | Value substitutions with a dictionary. (jared <afonit@gmail.com>) |
Ответы |
Re: Value substitutions with a dictionary.
(jared <afonit@gmail.com>)
|
Список | psycopg |
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
В списке psycopg по дате отправления: