pymysql #1: NOT IN, IN

실습 1

• NOT IN, IN 구문 뒤에 python list 읽어오기

Reference sites: stackoverflow, 구글 검색어: pymysql select in list

import pymysql
import csv
import pandas as pd
import glob
import os

db = pymysql.connect(host=" ", port= ,
                     user=" ", password=" ",
                     db=" ",
                     charset='utf8')

cursor = db.cursor()
print("database is connected")

file1 = open("data/cm1.csv", "r", encoding="utf-8")
file2 = open("data/cm2.csv", "r", encoding="utf-8")
file3 = open("data/cm3.csv", "r", encoding="utf-8")
workerList = []

csvReader1 = csv.reader(file1)
csvReader2 = csv.reader(file2)
csvReader3 = csv.reader(file3)
next(csvReader1)
next(csvReader2)
next(csvReader3)

for row in csvReader1:
    workerList.append(row[15])
for row in csvReader2:
    workerList.append(row[15])
for row in csvReader3:
    workerList.append(row[15])


format_strings = ','.join(['%s'] * len(workerList))
cursor.execute("DELETE FROM cmw_participants WHERE worker_id NOT IN (%s)" % format_strings, tuple(workerList))
db.commit()

cursor.execute("""
DELETE FROM scripts_web 
WHERE scripts_web.user_code 
NOT IN (SELECT user_code FROM cmw_participants)
""")
db.commit()

# res = cursor.fetchall()
#
# for data in res:
#     print(data)
#     num = num + 1

file1.close()
file2.close()
file3.close()
db.close()
print("end!")