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?
thanks in advance.