def create_table(self, name):
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + name + ' (d_open double, d_yesterday double, d_close double, d_high double, d_low double, d_volumn bigint, d_money bigint, ' \
'n_open double, n_yesterday double, n_close double, n_high double, n_low double, n_volumn bigint, n_money bigint, time date)'
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create america table error ' + str(e))
def delete_table(self, name):
try:
sql = 'DROP TABLE ' + name
self.cursor.execute(sql)
except Exception as e:
self.db.rollback()
debug.log_error('delete error!' + str(e))
def update_value(self, tablename='', items='', content=''):
try:
sql = 'UPDATE ' + tablename + ' SET ' + items + ' ' + content
# print(sql)
self.cursor.execute(sql)
except Exception as e:
debug.log_error('update stock type error! ' + str(e))
def create_table(self, name):
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + name + ' (united_arab_emirates float, australian float, brazil float, canada float, switzerland float, denmark float, europe float, english float, hongkong float, indonesia float, india float, japan float, south_korea float,' \
' pataca float, norway float, new_zealand float, philippines float, russia float, saudi_arabia float, sweden float, singapore float, thailand float, turkey float, taiwan float, american float, south_africa float, time datetime)'
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create exchange rate table error ' + str(e))
def update_stock_basis_info(self, data):
sql = 'UPDATE ' + Stock.tablename_stock_basis_info + ' SET totalcapital = %s, currcapital = %s WHERE code_id = %s'
try:
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('update basis info error!' + str(e))
self.db.rollback()
def get_column_data_from_database(self, column_name='', tablename=''):
try:
sql = 'SELECT ' + column_name + ' FROM ' + tablename
self.cursor.execute(sql)
row = self.cursor.fetchall()
while row is not None:
return row
except Exception as e:
debug.log_error('get column data from database error ! ' + str(e))
def insert_summary_data(self, table_name, data):
sql = 'INSERT INTO ' + table_name + ' (open, yesterday, close, high, low, buy, sale, volumn, money, turnover, hightime, lowtime, time) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
try:
debug.log_info(str(data))
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('insert summary data error!' + str(e) + table_name)
self.db.rollback()
def insert_table(self, name, data):
sql = 'INSERT INTO ' + name + '(united_arab_emirates, australian, brazil, canada, switzerland, denmark, europe, english, hongkong, indonesia, india, japan, south_korea, pataca, norway, new_zealand, philippines, russia, saudi_arabia, sweden, singapore, thailand, turkey, taiwan, american, south_africa, time)' \
' values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
try:
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('insert exchange_rate data error! ' + str(e) +
name)
self.db.rollback()
def insert_summary_data(self, name, data):
sql = 'INSERT INTO ' + name + '(d_open, d_yesterday, d_close, d_high, d_low, d_volumn, d_money, n_open, n_yesterday, n_close, n_high, n_low, n_volumn, n_money, time) ' \
'values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
try:
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('insert exchange_rate data error! ' + str(e) +
name)
self.db.rollback()
def insert_realtime_data(self, table_name, data):
sql = 'INSERT INTO ' + table_name + '(price, money, volumn, turnover, time) values(%s, %s, %s, %s, %s)'
try:
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('insert realtime data error!' + str(e) +
table_name)
mail.send_mail('insert realtime data error!' + str(e) + table_name)
self.db.rollback()
def insert_basis_data(self, data):
# 需要市盈率信息
#前一年每股收益合--最近四季度每股收益和--最近报告每股净资产--总股本--流通股本--最近年度净利润--最近四个季度净利润--发行价
sql = 'INSERT INTO ' + Stock.tablename_stock_basis_info + ' (code_id, lastyear_mgsy, fourQ_mgsy, mgjzc, totalcapital, currcapital, profit, profit_four, issue_price ) values(%s, %s, %s, %s, %s, %s, %s, %s, %s)'
try:
self.cursor.execute(sql, data)
self.db.commit()
except Exception as e:
debug.log_error('insert basis data error!' + str(e) +
Stock.tablename_stock_basis_info)
self.db.rollback()
def connect_database(self):
try:
self.db = pymysql.connect(host='localhost',
user='******',
password='******',
port=3306,
db='stock_info')
self.cursor = self.db.cursor()
except Exception as e:
debug.log_error(' connect database error! ' + str(e))
def find_stock_basis_from_database(self, code_id, item='*', content=''):
try:
sql = 'SELECT ' + item + ' FROM ' + Stock.tablename_stock_basis_info + ' WHERE code_id = ' + code_id
self.cursor.execute(sql)
row = self.cursor.fetchone()
while row is not None:
if (item == '*'):
return row
else:
return row[0]
except Exception as e:
debug.log_error('find basic data error , will return 1 ' + str(e) +
code_id)
return 1
def create_table(self, name, type):
if (type == 'summary'):
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + name + ' (open float, yesterday float, close float, high float, low float, buy float, sale float, volumn double, money double, turnover float, hightime datetime, lowtime datetime, time date)'
# sql = 'ALTER TABLE ' + name + ' ADD COLUMN lowtime datetime AFTER hightime' #lowtime datetime
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create table summary error!' + str(e))
elif (type == 'basis'): # all stock basic info stores in one table
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + Stock.tablename_stock_basis_info + ' (code_id int, lastyear_mgsy float, fourQ_mgsy float, mgjzc float, totalcapital double, currcapital double, profit float, profit_four float, issue_price float)'
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create table basis error!' + str(e))
elif (type == 'realtime'):
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + name + ' (price float, money double, volumn double, turnover float, time datetime)'
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create table realtime error!' + str(e))
def get_stock_codes_info(self):
debug.log_info('start code info !')
time_change = True
minute_data = []
today_data = []
all_code_compare_time = {}
# all_code_real_price = {}
all_code_real_price_temp = {}
all_code_currcapital = {}
# code_all_id = mysql.China().get_column_data_from_database('code_id', mysql.Stock.tablename_stock_basis_info)
# 考虑修饰符
for item_id in China.code_all_id:
# code_id_item = str(item_id[0].zfill(6))
# all_code_currcapital[code_id_item] = self.database.find_stock_basis_from_database(code_id_item, item='currcapital', content='code_id = ' + code_id_item)
all_code_currcapital[item_id] = data_base.China.get_basis_data(
item_id, data_base.China.currcapital)
self.all_code_today_info[item_id] = [
0, '1991-06-18 00:00', 100000, '1991-06-18 00:00'
]
# all_code_real_price[item_id] = []
all_code_real_price_temp[item_id] = [
0, 0, 0, 0, debug.current_time
]
all_code_compare_time[item_id] = [True, debug.current_time]
debug.log_info('start record realtime!')
while (True):
for code_list_item in China.all_code_list:
try:
current_code_info = requests.get(code_list_item,
timeout=(6.1, 10))
code_results = current_code_info.text.split(';')
except Exception as e:
debug.log_error(str(e))
mail.send_mail('get code info error !' + str(e) +
debug.current_time())
continue
for code_item in code_results:
if (code_item == '\n'):
break
code_item_result = re.search(
'hq_str_.*?(\d+)=".*?,(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),(.*?),.*,(.*?),(.*?),(.*?)',
code_item, re.S)
if (code_item_result == None):
continue
if (all_code_compare_time[code_item_result.group(1)][0]):
all_code_compare_time[code_item_result.group(
1
)][1] = code_item_result.group(
13) + ' ' + code_item_result.group(14)[:-2] + '59'
all_code_compare_time[code_item_result.group(
1)][0] = False
record_time = code_item_result.group(
13) + ' ' + code_item_result.group(14)
real_time_data = (code_item_result.group(4),
code_item_result.group(6),
record_time[:-3])
if (float(code_item_result.group(5)) > self.
all_code_today_info[code_item_result.group(1)][0]
and float(code_item_result.group(5)) != 0):
self.all_code_today_info[code_item_result.group(
1)][0] = float(code_item_result.group(5))
self.all_code_today_info[code_item_result.group(
1)][1] = record_time[:-3]
# print('high ' , code_item_result.group(1) , self.all_code_today_info[code_item_result.group(1)][1])
if (float(code_item_result.group(6)) < self.
all_code_today_info[code_item_result.group(1)][2]
and float(code_item_result.group(6)) != 0):
self.all_code_today_info[code_item_result.group(
1)][2] = float(code_item_result.group(6))
self.all_code_today_info[code_item_result.group(
1)][3] = record_time[:-3]
# print('low ', code_item_result.group(1) , self.all_code_today_info[code_item_result.group(1)][3])
# 记录最高和最低价出现时间
if (record_time > all_code_compare_time[
code_item_result.group(1)][1]):
# add data to today
all_code_compare_time[code_item_result.group(
1)][0] = True
if (float(all_code_real_price_temp[
code_item_result.group(1)][0]) != 0):
final_data = (all_code_real_price_temp[
code_item_result.group(1)][0],
all_code_real_price_temp[
code_item_result.group(1)][1],
all_code_real_price_temp[
code_item_result.group(1)][2],
all_code_real_price_temp[
code_item_result.group(1)][3],
all_code_real_price_temp[
code_item_result.group(1)][4])
data_base.China.insert_realtime_data(
code_item_result.group(1), final_data)
# all_code_real_price[code_item_result.group(1)].append(final_data)
currcapital = float(
all_code_currcapital[code_item_result.group(1)])
volumn = float(code_item_result.group(9))
money = float(code_item_result.group(10))
if (currcapital == 0):
turnover = 0
all_code_real_price_temp[code_item_result.group(
1)][3] = turnover
else:
turnover = volumn / currcapital / 100
all_code_real_price_temp[code_item_result.group(
1)][3] = turnover
all_code_real_price_temp[code_item_result.group(
1)][0] = code_item_result.group(4)
all_code_real_price_temp[code_item_result.group(
1)][1] = money
all_code_real_price_temp[code_item_result.group(
1)][2] = volumn
all_code_real_price_temp[code_item_result.group(
1)][4] = record_time[:-3]
sleep(2)
if (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) >
time.strftime("%Y-%m-%d ", time.localtime()) + '15:02:20'):
debug.log_info('start record code summary info')
self.get_stock_code_summary_info(True,
self.all_code_today_info)
self.all_code_today_info.clear()
debug.log_info('finish record code summary info')
usa = America()
usa.get_stock_code_summary_info()
# if database last store time < now current collect data
#
# for keys, values in all_code_real_price.items():
# table_name = keys + '_realtime_' + time.strftime("%Y", time.localtime())
# # self.database.create_table(table_name, 'realtime')
# for item in values:
# if (str(item[4]) > str(item[4])[:-5] + '15:03'):
# debug.log_warning ('data error ! forbid to insert')
# break
# self.database.insert_realtime_data(table_name, item)
#
debug.log_info(
'insert realtime end ' +
time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
break
sleep(6)
def update_column_type(self, tablename='', columnname='', type=''):
try:
sql = 'alter table ' + tablename + ' modify ' + columnname + ' ' + type
self.cursor.execute(sql)
except Exception as e:
debug.log_error('update stock type error! ' + str(e))
def create_table(self, name, items=''):
try:
sql = 'CREATE TABLE IF NOT EXISTS ' + name + items
self.cursor.execute(sql)
except Exception as e:
debug.log_error('create table error!' + str(e))
def disconnect_database(self):
try:
self.db.close()
except Exception as e:
debug.log_error(' disconnect database error! ' + str(e))
