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()
python3.6 从ftp中下载文件并存入mysql数据库
最新推荐文章于 2024-05-15 10:50:06 发布