python - Is there a way to check and load missing data to SQL?

Question

I am trying to figure out a way to check data I am loading into a SQL table from a dataframe so I can load missing data and avoid loading duplicate data.

Here is a really rough idea.

sql_data = []
data = [(2020-01-01, Monday, 20, 0.1), (2020-01-02, Tuesday, 12, 0.4), (2020-01-01, Wednesday, 26, 0.3)]
          
        
cursor.execute('''Select * FROM Table ''')
for row in cursor.fetchall():
   sql_data.append(row)
    
if data in sql_data:
   pass
else:
   query = '''INSERT INTO Table (Time, Day, Number, Decimal)
                                 VALUES (?, ?, ?, ?)'''
   cursor.execute(query, data)
   conn.commit()

question from:https://stackoverflow.com/questions/65851661/is-there-a-way-to-check-and-load-missing-data-to-sql

Answer 1

Consider rarely used EXCEPT clause (part of UNION and INTERSECT set operator family) since SQL Server supports scalar values in SELECT without a FROM data source:

query = '''INSERT INTO Table (Time, Day, Number, Decimal)
           SELECT ?, ?, ?, ?
           EXCEPT
           SELECT Time, Day, Number, Decimal
           FROM Table
        '''
cursor.executemany(query, data)
conn.commit()

Online Demo