def daily2DB(src, db_name):
tables = []
for root, dirs, files in os.walk(src):
for file_name in files:
if file_name.find('.txt') == -1:
continue
else:
print "Processing daily to DB File: %s" % file_name
table_name = file_name.split('.')[0]
tables.append(table_name)
full_file_name = join(root, file_name)
txt_file = open(full_file_name)
stock_name = txt_file.readline().decode('gbk').encode('utf-8')
#for str in stock_name.split(" ", 3):
# print str.lstrip().rstrip()
title = txt_file.readline().decode('gbk').encode('utf-8')
#for str in title.lstrip().split("\t", 7):
# print str.lstrip()
mysql = MySQL(db_name)
mysql.connect()
col_type = list()
col_type.append("`ID` INT NOT NULL AUTO_INCREMENT")
col_type.append("`DATE` DATETIME NULL")
col_type.append("`START_PRICE` FLOAT NULL")
col_type.append("`HIGH_PRICE` FLOAT NULL")
col_type.append("`LOW_PRICE` FLOAT NULL")
col_type.append("`CLOSE_PRICE` FLOAT NULL")
col_type.append("`DEAL_AMOUNT` INT NULL")
col_type.append("`DEAL_PRICE` FLOAT NULL")
mysql.create_table_with_delete(table_name, "ID", col_type)
content = txt_file.readline()
data = list()
while content:
content = content.replace('\n', '')
contents = content.split(';', 7)
content = txt_file.readline()
if len(contents) < 7:
continue
else:
contents[0] = datetime.strptime(contents[0], "%m/%d/%Y").strftime("%Y-%m-%d %H:%M:%S")
data.append(contents)
mysql.insert_many(table_name, "`DATE`, `START_PRICE`, `HIGH_PRICE`, `LOW_PRICE`, \
`CLOSE_PRICE`, `DEAL_AMOUNT`, `DEAL_PRICE`", data)
mysql.close_connect()
def trans2db(db_name, trans_path):
mysql = MySQL(db_name)
mysql.connect()
col_type = list()
col_type.append("`ID` INT NOT NULL AUTO_INCREMENT")
col_type.append("`DEAL_DATE` DATETIME NULL")
col_type.append("`DEAL_PRICE` FLOAT NULL")
col_type.append("`DEAL_GAP` FLOAT NULL")
col_type.append("`TOTAL_LOT` INT NULL")
col_type.append("`TOTAL_AMOUNT` INT NULL")
col_type.append("`DEAL_TYPE` INT NULL")
col_type.append("`OTHER` FLOAT NULL")
try:
for root1, dirs1, files1 in os.walk(trans_path):
for dir1 in dirs1:
mysql.create_table(dir1, "ID", col_type)
next_dir = root1 + "\\" + dir1
for root2, dirs2, files2 in os.walk(next_dir):
for file2 in files2:
if file2.find('.txt') != -1:
data = list()
file_path = join(next_dir, file2)
file_name = file2.split(".")[0]
with open(file_path, 'r') as fp:
text_list = fp.readlines()
print "Processing file to DB.... " + file_path
for i in range(1, len(text_list)):
text = text_list[i].decode('gb2312').encode('utf-8')
text_elem = text.split("\t")
deal_time_str = file_name + ';' + text_elem[0].strip()
check_ret = check_data(text_elem)
if check_ret != 0:
print "###########" + str(check_ret)
if check_ret != 7:
print text_elem[check_ret - 1]
continue
deal_time = datetime.strptime('1900-01-01;00:00:00', "%Y-%m-%d;%H:%M:%S")
try:
deal_time = datetime.strptime(deal_time_str, "%Y-%m-%d;%H:%M:%S")
except Exception:
print deal_time_str
#deal nums
deal_price = 0
deal_gap = 0
deal_lot = 0
deal_amount = 0
try:
deal_price_str = text_elem[1].strip()
deal_gap_str = text_elem[2].strip()
deal_lot_str = text_elem[3].strip()
deal_amount_str = text_elem[4].strip()
deal_price = float(deal_price_str)
if deal_gap_str.find('-') == -1:
deal_gap = float(deal_gap_str)
else:
deal_gap = 0
deal_lot = float(deal_lot_str)
deal_amount = float(deal_amount_str)
except Exception, e:
print e
print deal_price_str
print deal_gap_str
print deal_lot
print deal_amount
deal_type_str = text_elem[5].strip()
deal_type = 0
if deal_type_str == "买盘":
deal_type = 1
elif deal_type_str == "卖盘":
deal_type = -1
else:
deal_type = 0
if i == 1:
ret = mysql.query(dir1, "DEAL_DATE", deal_time)
if len(ret) > 0:
print "pass this file!"
break
else:
pass
else:
pass
data.append([deal_time, deal_price, deal_gap, deal_lot, deal_amount, deal_type])
if len(data) > 0:
mysql.insert_many(dir1, "`DEAL_DATE`, `DEAL_PRICE`, `DEAL_GAP`, \
`TOTAL_LOT`, `TOTAL_AMOUNT`, `DEAL_TYPE`", data)
else:
pass
fp.close()
os.remove(file_path)
else:
continue
os.rmdir(next_dir)
except IOError, e:
print "ERROR: " + dir1 + " FILE: " + file2
mysql.close_connect()