Re: Insert data if it is not existing

On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>

Thank you Master, the name=%s solved it.