Re: Insert data if it is not existing

On 05/23/2018 06:03 PM, tango ward wrote:
> 
> On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver 
> <adrian.klaver@aklaver.com <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@aklaver.com <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@aklaver.com