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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com