import xlrd
import pymysql
import datetime
#打開Excel文件
def open_excel():
book = xlrd.open_workbook("C:/Users/Administrator/Downloads/XXX.xls")
sheet = book.sheet_by_index(0)
return sheet
#連接數據庫
db = pymysql.connect(
host="10.10.10.10",
user="uc",
passwd="Password@uc",
db="qw",
charset='utf8'
)
#編寫SQL
def search_count():
cursor = db.cursor()
select1 = "delete from tables_name>='20200101'"
cursor.execute(select1)
#插入Excel文件中的數據
def insert_deta():
sheet = open_excel()
cursor = db.cursor()
now_date = datetime.datetime.now().strftime('%Y-%m-%d')
now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
for i in range(1, sheet.nrows):
case_input_code = sheet.cell(i, 0).value
discover_type = sheet.cell(i, 1).value
case_hap_date = sheet.cell(i, 2).value
item_name = sheet.cell(i, 3).value
from_pro = sheet.cell(i, 4).value
from_city = sheet.cell(i, 5).value
cust_counts = sheet.cell(i, 6).value
item_counts = sheet.cell(i, 7).value
op_date = now_date
op_time = now_time
value = (case_input_code, discover_type,case_hap_date,item_name,from_pro,from_city,cust_counts,item_counts,op_date,op_time)
sql = "INSERT INTO tables_name(case_input_code, discover_type,case_hap_date,item_name,from_pro,from_city,cust_counts,item_counts,op_date,op_time)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql, value)
db.commit()
cursor.close()
#執行
search_count()
insert_deta()
#關閉數據庫
db.close()
print("ok ")
閱讀更多 小豬伴 的文章
