在现有表中创建新列并使用python更新列

Question

在这里，我试图将一个表与两个包含数百万条记录的表进行比较，因此我希望分别比较dem，并在主表“tabl”名称状态中创建一个单独的列，以便可以更新该表本身

步骤：在email name“status”旁边的主表中创建一个新列

步骤2：与tab1、tab2表比较时，必须更新该列

list_of_tables=['tab1','tab2'] 
for tab in list_of_tables:
    cursor.execute("select main.*,if({}.email is not null ,'MATCH','NONMATCH') stataus from main left join {} on main.email={}.email".format(tab,tab,tab))
     data_2 = cursor.fetchall()
     print data_2
     data3=list(data_2)
     data_3=pd.DataFrame(data3)
     upload(ftp,data_3,FILEPATH)

def upload(ftp,data_3,FILEPATH):
    data_4=data_3.to_csv(Out_file,index=False,header=None)


main:

email
abc@gamil.com
xyz@email.com
ijk@gmail.com
ghi@gmail.com
pqr@gmail.com
yup@gmail.com
tab1:

email
ijk@gmail.com
yup@gmail.com
tab2:

email
xyz@email.com
pqr@gmail.com

要求结果

email           valid
abc@gamil.com   non-match
xyz@email.com   match
ijk@gmail.com   match
ghi@gmail.com   non-match
pqr@gmail.com   match
yup@gmail.com   match

but getting like dis:

abc@gamil.com       non-match
xyz@email.com       non-match
ijk@gmail.com       match
ghi@gmail.com       non-match
pqr@gmail.com       non-match
yup@gmail.com       match
abc@gamil.com       nonmatch
xyz@email.com       match
ijk@gmail.com       nonmatch
ghi@gmail.com       nonmatch
pqr@gmail.com       nonmatch
yup@gmail.com       nonmatch

Answer 1

data3 = data2.to_sql('main', con=engine, if_exists='replace',index=False)
cursor.execute("alter table main add status varchar(128)")
for i in ['tab1','tab2']:
    cursor.execute("update main left join {} on main.email={}.email set status=if({}.email is not null,'match','nonmatch')".format(i,i,i))
    conn.commit()
    data_2=cursor.fetchall()
    data3=data_2
    print data_2

查询是好的，但我希望for循环运行是一个错误的地方，因为我得到的是最后一个tab2比较结果，而不是tab1和tab2都比较结果。你知道吗

Scott Boston更新：

您仍然可以运行：

update main left join (select * from tab1 union all select * from tab2) total on main.email=total.email
set status=if(total.email is not null,'match','nonmatch');

Answer 2

终于明白了：

data3 = data2.to_sql('main', con=engine, if_exists='replace',index=False)
    cursor.execute("alter table main add status varchar(128) **not null default 'nonmatch**'")
    d=cursor.fetchall()
    print d

    for i in ['tab1','tab3','tab2']:
        cursor.execute("update main **join** {} on main.email={}.email set status=if({}.email is not null,'match','nonmatch')".format(i,i,i))
        conn.commit()