python3.6 从ftp中下载文件并存入mysql数据库

from ftplib import FTP
from sqlalchemy import create_engine
import pymysql
import os
import pandas as pd
import time
import datetime

def upload_1day_1min(date='null'):

    ftp = FTP(host='192.456.121.234', user='mark',
              passwd='123456', timeout=999)

    print(ftp.getwelcome())
    file_list = sorted(ftp.nlst())
    bufsize = 1024
    if date == 'null':
        date = file_list[-1].split('.')[0]

    filename = date+'.rar'
    print(filename)

    path = './' + 'rarfile'+'/'+date + '/'
    print(path)

    if not(os.path.exists(path)):
        os.makedirs(path)

    path_file = path + filename
    file_handle = open(path_file, 'wb')

    ftp.retrbinary('RETR '+filename, file_handle.write, bufsize)

    file_handle.flush()
    file_handle.close()
    ftp.close()

    path_unrar = './' + 'unrarfile'+'/'+date + '/'
    if not(os.path.exists(path_unrar)):
        os.makedirs(path_unrar)

    a = "unrar x -ibck %s %s" % (path_file, path_unrar)
    os.system(a)
    print("unrar file success -------------------------------------------------------")

    dir = path_unrar
    #dir = './' + 'unrarfile'+'/'+'20180424' + '/'
    file_path_list = []
    for root, dirs, files in os.walk(dir):
        for file in files:
            file_path_list.append(os.path.join(root, file))

    con_stock1min = create_engine(
        'mysql+pymysql://public:123456@192.168.0.11:3319/stock1min?charset=gbk')

    for path in file_path_list:
        stock_name = path.split('/')[-1].split('.')[0]

        df = pd.read_csv(open(r'{0}'.format(path), encoding='gbk'))
        del df['代码']
        df.columns = ['datetime', 'open', 'high',
                      'low', 'close', 'lots', 'vol', 'fq']
        df.index = df['datetime']
        df.index = pd.to_datetime(df.index, format='%Y-%m-%d %H:%M:%S')
        del df['datetime']

        print(df.index[0])
        try:
            sql_string = "DELETE FROM {3} WHERE datetime>='{1}' and datetime<='{2}'".format(
                stock_name, (df.index)[0], (df.index)[-1], stock_name)
            con_stock1min.execute(sql_string)

        except Exception:
            pass

        df.to_sql(name=stock_name, con=con_stock1min, if_exists='append')



        try:
            sql_string = "ALTER TABLE `{0}`  ADD PRIMARY KEY ( `datetime` )".format(
                stock_name)
            con_stock1min.execute(sql_string)
        except Exception:
            pass


if __name__ == '__main__':

    upload_1day_1min()