Insert record from list if not exists in table

Question

cHandler = myDB.cursor()
cHandler.execute('select UserId,C1,LogDate from DeviceLogs_12_2019') // data from remote sql server database

curs = connection.cursor() 
curs.execute("""select * from biometric""")  //data from my database table

lst = []
result= cHandler.fetchall()
for row in result:
    lst.append(row)

lst2 = []
result2= curs.fetchall()
for row in result2:
    lst2.append(row)

t = []
r = [elem for elem in lst if not elem in lst2]
for i in r: 
    print(i)
    t.append(i)

for i in t:
    frappe.db.sql("""Insert into biometric(UserId,C1,LogDate) select '%s','%s','%s'  where not exists(select * from biometric where UserID='%s' and LogDate='%s')""",(i[0],i[1],i[2],i[0],i[2]),as_dict=1)

I am trying above code to insert data into my table if record not exists but getting error :

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1111'',''in'',''2019-12-03 06:37:15'' where not exists(select * from biometric ' at line 1")

Is there anything I am doing wrong or any other way to achieve this?

If you're using mariadb and MySQL, why tag SQL Server? Please only tag the RDBMS you are *actually* using. — Thom A, Dec 19 '19 at 11:34
If it was me, I'd forget about the non-sql stuff for now and instead focus on the query (and the associated logic of that). If you agree that that's a good idea, see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Dec 19 '19 at 11:52

Booboo · Accepted Answer · 2019-12-19T14:21:08.567

It appears you have potentially four problems:

There is a from clause missing between select and where not exists.
When using a prepared statement you do not enclose your placeholder arguments, %s, within quotes. Your SQL should be:
Your loop:

Loop:

t = []
r = [elem for elem in lst if not elem in lst2]
for i in r: 
    print(i)
    t.append(i)

If you are trying to only include rows from the remote site that will not be duplicates, then you should explicitly check the two fields that matter, i.e. UserId and LogDate. But what is the point since your SQL is taking care of making sure that you are excluding these duplicate rows? Also, what is the point of copying everything form r to t?

SQL:

Insert into biometric(UserId,C1,LogDate) select %s,%s,%s from DUAL where not exists(select * from biometric where UserID=%s and LogDate=%s

But here is the problem even with the above SQL:

If the not exists clause is false, then the select %s,%s,%s from DUAL ... returns no columns and the column count will not match the number of columns you are trying to insert, namely three.

If your concern is getting an error due to duplicate keys because (UserId, LogDate) is either a UNIQUE or PRIMARY KEY, then add the IGNORE keyword on the INSERT statement and then if a row with the key already exists, the insertion will be ignored. But there is no way of knowing since you have not provided this information:

for i in t:
    frappe.db.sql("Insert IGNORE into biometric(UserId,C1,LogDate) values(%s,%s,%s)",(i[0],i[1],i[2]))

If you do not want multiple rows with the same (UserId, LogDate) combination, then you should define a UNIQUE KEY on these two columns and then the above SQL should be sufficient. There is also an ON DUPLICATE KEY SET ... variation of the INSERT statement where if the key exists you can do an update instead (look this up).

If you don't have a UNIQUE KEY defined on these two columns or you need to print out those rows which are being updated, then you do need to test for the presence of the existing keys. But this would be the way to do it:

cHandler = myDB.cursor()
cHandler.execute('select UserId,C1,LogDate from DeviceLogs_12_2019') // data from remote sql server database
rows = cHandler.fetchall()
curs = connection.cursor() 
for row in rows:
    curs.execute("select UserId from biometric where UserId=%s and LogDate=%s", (ros[0], row[2])) # row already in biometric table?
    biometric_row = curs.fetchone()
    if biometric_row is None: # no, it is not
        print(row)
        frappe.db.sql("Insert into biometric(UserId,C1,LogDate) values(%s, %s, %s)", (row[0],row[1],row[2]))

I would suggest that there's a third problem: The logical premise is flawed. — Strawberry, Dec 19 '19 at 11:53
If you are selecting form a list, then the table is named `DUAL`. — Booboo, Dec 19 '19 at 12:51

Insert record from list if not exists in table

1 Answers1