#!/usr/bin/python
# -*- coding: utf-8 -*-
# File Name: test
# Created on 2018/11/6 0006
# @author: Ethan
import os

import pandas as pd
from common import database
import time

start = time.time()

def do_file(fname):
    df = pd.read_excel(fname, sheetname=3)
    df = df[[u'省份', u'地市', u'订单数']]
    df['yyyymmdd'] = fname[18:-5] #取日期

    conn = database.get_database_connection()
    # 建立游标
    cursor = conn.cursor()

    # sql语句,注意%s要加引号，否则会报ora-01036错误
    query = "INSERT INTO a(PROVINCE_NAME,AREA_NAME,ORDER_NUM,YYYYMMDD) " \
            "VALUES ('%s', '%s', '%s', '%s')"
    # 逐行插入数据
    for i in range(len(df)):
        cursor.execute(query % (df.ix[i, 0], df.ix[i, 1], df.ix[i, 2], df.ix[i, 3]))
        if i % 1000 == 0:
            conn.commit()

    # 关闭游标
    conn.commit()
    cursor.close()
    conn.close()


rootdir = u"D:\\我的文档\\***报表\\**报表";
for s in [u'201801', u'201802', u'201803', u'201804', u'201805', u'201806', u'201807', u'201808',
          u'201809', u'201810', u'201811']:
    print time.time() - start, rootdir + s + u"01.xlsx"
    do_file(rootdir + s + u"01.xlsx")