On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/23/2018 06:03 PM, tango ward wrote:
On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com >> wrote:
On 05/23/2018 05:11 PM, tango ward wrote:
Sorry, i forgot the values.
curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT name, age
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))
Pretty sure this would throw an exception as there are no parameter
markers in the query for the parameter values in the tuple to bind
to. So are you swallowing the exception in you code?
Sorry, I don't understand, where should I place the from clause?
I just saw a sample code like this in SO, so I gave it a shot
Not tested:
'''
INSERT INTO my_table(%(name)s, %(age)s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %(name))
''', {'name': Scott', 'age': 23})
-- Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com >
Updated my code to this:
curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s
The above is not going to work as you cannot use %s to substitute for identifiers, in this case the column names name and age.
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))
If I remove SELECT statement, I will get an error message: error : psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 12: WHERE NOT EXISTS
Try the example I showed previously. If you do not want to use the the named parameters e.g %(name)s then use use %s and a tuple like:
'''
INSERT INTO my_table(%s, %s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %s)
''', (Scott', 23, 'Scott'))--
Trying to coordinate with Lead Dev about adding Index On The Fly
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Thank you Master, the name=%s solved it.
