-1

I wish to insert nested lists into mysql table using Python. I want to insert new rows for new ID values and update certain columns for those with existing ID values. However, for one of these nested lists (Fruits, s[2]) I only want to update these rows within mysql Table if those records are empty! I realise the query I have put only works for new inserting id numbers and does not address updating existing rows. That's where I need help!

s = [[1, 20, 21],[1999, 1998, 1990], ['Apple', 'Pear', 'Bannana']

id = s[0] 
yearborn = s[1] 
favfruit = s[2]

query = '''INSERT INTO table (PersonID, Year, Fruit) 
           VALUES (%s, %s, %s)'''

cursor.executemany(query, s)
GMB
  • 188,822
  • 23
  • 52
  • 100
  • Related: https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql – Aziz Jun 03 '20 at 15:06

1 Answers1

0

I want to insert new rows for new ID values and update certain columns for those with existing ID values.

In MySQL, you can use the insert ... on duplicate key syntax, and some conditional logic to only update column fruit if it is initially null:

insert into table (personid, year, fruit) 
values (%s, %s, %s)
on duplicate key update 
    year = values(year), 
   fruit = coalesce(fruit, values(fruit));

For this to work, you need a unique (or primary key) constraint on column personid.

GMB
  • 188,822
  • 23
  • 52
  • 100
  • I have this but for some reason "fruit = coalesce...." does not seem to have any effect? –  Jun 04 '20 at 09:24
  • What do you mean by *does not have any effect*? – GMB Jun 04 '20 at 09:31
  • So the first line "values (year) works fine. But the coalesce() does not appear to change the values. Is this perhaps because no null value has been set within the mysql table. It's definately a blank entry but appears like this --> ' '? I'm not sure if I'm just guessing here though? coalesce assumes null entries? –  Jun 04 '20 at 13:24
  • 1
    @Sam: `coalesce()` operates on `null` values. If your values are empty strings, then: `fruit = case when fruit = '' then values(fruit) else fruit end`. Or you can handle both: `fruit = case when fruit = '' or fruit is null then values(fruit) else fruit end` – GMB Jun 04 '20 at 13:26
  • Thanks for clarifying. –  Jun 04 '20 at 13:29