How can I insert in Python multiple rows of a Dictionary with a WHERE condition?-CodePudding

I need a little help. My problem is that I don't understand how to connect a WHERE condition in SQLite with the insertion of multiple elements from a dictionary.

My goal is to compare the Location Column from the Dictionary with the Country column from the existing table.

But I can't find a solution how to approach this, to implement a WHERE condition.

my code:

def add_countries_to_table(self, countryList):
       self.cursor.execute('''
          INSERT OR IGNORE INTO country (Country)
          VALUES (:Location)''', countryList)
       self.db.saveChanges()

Thanks for any help.

CodePudding user response：

Simple. You don't. An insert is an insert, there is no WHERE clause in it. You can define your Country to be UNIQUE and use an 'UPSERT' (UPDATE or INSERT) to ignore already existing values in the dictionary.

So INSERT INTO country (Country) VALUES(:Location) ON CONFLICT(X) DO NOTHING would be something close to the command you're looking for.

Alternatively you can try using a Set instead of a Dictionary to prevent duplicate values beforehand and let your program/script deal with duplicates instead of the DB.

CodePudding user response：

You need to use a playholder. I presume you tried that with (:Location). There are many possibilities for placeholder. Normally placeholder would be ? or %s. I presume you want to work with the values of the dict. If you want to insert multiple rows you need to use a tulpe.

def add_countries_to_table(self, countryList):
       ctry_tl = ()
       for row in countryList['Location']:
         ctry_tulpe = (row)
         ctry_tl.append(ctry_tuple)
       self.cursor.execute('''
          UPSERT INTO country 
          VALUES (?)''', ctry_tl)
       self.db.commit()