Compare data to MySQL database by reading Excel data (ii)-code writing test

Through the previous step, the environment has been set up.

The following starts the actual combat,

First, write a function that links MySQL conn_sql.py

Import Pymysqldef Sql_conn (u,pwd,h,db):    conn=pymysql.connect (user=u,passwd=pwd,host=h,db=db)    #print (" Connection database "+db+" Succeeded!! ")    return conn

In writing, query the database for the statement: sql.py

Import Pymysqlimport conn_sqldef Dd_cz (dd_h):    conn=conn_sql.sql_conn ("Database connection name", "Database Password", "Database IP", "Database name")    Cursor=conn.cursor ()    cursor.execute ("SELECT count (ID) from user where ordnum=%s", Dd_h)    Data=cursor.fetchone ()    #print (' data= ', data)    return data

Note: (In the test, there is a problem is that in the Cursor.execute ("SELECT count (ID) from user where ordnum=%s"% dd_h) The argument in this sentence to boot, if replaced by a% number, a database error occurs. So be sure to use,)

When you create a zx_duibi.py

Import xlrd,sysimport xdrlibimport osfrom SQL import Dd_cz #打开Excel文件读取数据def open_excel (file= "1.xls"): Try:data        =xlrd.open_workbook (file) print ("Open sucess!") return data except Exception:print (STR (e)) #根据索引获取Excel表格中的数据 # parameter: File:excel file path Colnameindex: The index of the row that contains the table header column name #by_i Ndex: Index of Table def excel_table_byindex (file= "1.xls", colnameindex=0,by_index=0): #打开表 data=open_excel (file) #获取工作表 Ta    Ble=data.sheets () [By_index] nrows=table.nrows #行数 #获取某一行数据 colnames=table.row_values (colnameindex) list=[] #3跳转行数 for rownum in range (1,nrows): #获取某一行的值 row=table.row_values (rownum) if Row:col_ l={} Col_l[colnames[3]]=row[3] List.append (col_l) return listdef main (): Tables=exc El_table_byindex () #excel总数 i=0 #不存在的 j=0 for row in tables: #根据exl表中的数据, querying data in the database ddh=dd_          CZ (row["column Header"]) #列标题是否存在 ddh_cz=ddh[0] if Ddh_cz = = 0:  Print (column header:%s, no "% (row[" column Header "])) J+=1 else:print (" presence:%s "% (row[" column Header "])) i+=1             Print ("Total:%s"% i) print ("Does not exist for:%s"%j) if __name__== "__main__": Main ()

The program that compares the data in Excel and data is OK.

Reference Documentation:

The MySQL operation is: good people

Excel actions: Good People

Compare data to MySQL database by reading Excel data (ii)-code writing test