def mainfunc():
date_lastday = datetime.datetime.now() + datetime.timedelta(days=-1)
date_year = date_lastday.year
pd_cre = pd.read_csv(merc_root + '/credential/credential.csv',
index_col='item')
code = pd_cre.loc['mysql', 'cred']
crypto_obj = AESCipher('Always Love You')
passwd = crypto_obj.decrypt(code)
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# initial read
sql = "SELECT symbol,trading_date,open,high,low,close,volume FROM `eq_symbol_ohlc_" + str(
date_year - 10) + "` WHERE `symbol`='AAPL'"
df = pd.read_sql(sql, con=connection)
for yy in range(date_year - 9, date_year + 1):
sql = "SELECT symbol,trading_date,open,high,low,close,volume FROM `eq_symbol_ohlc_" + str(
yy) + "` WHERE `symbol`='AAPL'"
df_temp = pd.read_sql(sql, con=connection)
df = df.append(df_temp, ignore_index=True)
connection.close()
print(df)
def mainfunc():
pd_cre=pd.read_csv('../../credential/credential.csv', index_col='item')
code =pd_cre.loc['mysql','cred']
crypto_obj=AESCipher('Always Love You')
passwd=crypto_obj.decrypt(code)
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `eq_all_basics` (`name`, `symbol`, `bbid`, `rhid`) VALUES (%s, %s, %s, %s)"
cursor.execute(sql, ('org','OO', 'dfjojfofcd', 'very-secret'))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
finally:
connection.close()
def mainfunc():
# Read the password
pd_cre = pd.read_csv('../../credential/credential.csv', index_col='item')
code = pd_cre.loc['mysql', 'cred']
crypto_obj = AESCipher('Always Love You')
passwd = crypto_obj.decrypt(code)
# connect database
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db=dbname,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
pos = 0
files = os.listdir(eq_path)
for item0 in files:
itm_arr = item0.split('_')
itm_symbol = itm_arr[0]
itm_fin = itm_arr[1]
if itm_fin == 'Balance%20Sheet.xlsx':
print('Deal with ' + item0)
file_path = eq_path + item0
# Deal with the financial record
df = pd.read_excel(file_path)
df = df.T // 1000 # Change the unit to kdollars
print(df)
exit()
with connection.cursor() as cursor:
sql = "SELECT `rhid` FROM `eq_all_basics` WHERE `symbol`=%s"
cursor.execute(sql, itm_symbol)
result = cursor.fetchone()
if result is None:
pos = pos + 1
print(str(pos) + ' failed!')
continue
sql = "INSERT INTO `eq_balance_sheet` (`rhid`, `symbol`, `issued_date`, `cash_equiv`,`invest_curr`, `cash_st_invest`, `receivables`, `inventory`, `curr_ast`, `ppe_net`, `goodwill`, `invst_ncurr`, `tax_ast`, `ast_ncurr`, `total_ast`, `payables`, `debt_curr`, `curr_liab`, `debt_ncurr`, `total_debt`, `def_revenue`, `tax_liab`, `dep_liab`, `liab_ncurr`, `total_liab`, `other_income`, `ret_earning`, `equity`, `invest`, `md5id`) 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, %s, %s, %s)"
for idx, df_itm in df.iterrows():
data_stream = [
result['rhid'], itm_symbol,
idx.strftime('%Y-%m-%d %H:%M:%S')
]
data_stream.extend(df_itm.tolist())
md5id = hashlib.md5()
md5id.update(
(itm_symbol +
idx.strftime('%Y-%m-%d %H:%M:%S')).encode('utf-8'))
data_stream.append(md5id.hexdigest())
cursor.execute(sql, tuple(data_stream))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
connection.close()
def mainfunc():
pd_cre = pd.read_csv('../../credential/credential.csv', index_col='item')
code = pd_cre.loc['mysql', 'cred']
crypto_obj = AESCipher('Always Love You')
passwd = crypto_obj.decrypt(code)
dbname = 'mercuriusdb'
connection = create_engine("mysql+pymysql://lzhenn:" + passwd +
"@localhost/" + dbname)
connection2 = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
for item in eq_market:
files = os.listdir(eq_path + item)
pos = 0
for item0 in files:
df = pd.read_csv(eq_path + item + '/' + item0, parse_dates=True)
itm_arr = item0.split('.')
with connection2.cursor() as cursor:
sql = "SELECT bbid FROM `eq_all_basics` WHERE `symbol`=%s"
cursor.execute(sql, itm_arr[0])
result = cursor.fetchone()
if result is None:
pos = pos + 1
print(str(pos) + ' failed!')
continue
print(result['bbid'] + '-->' + itm_arr[0])
sql = 'CREATE TABLE IF NOT EXISTS ' + 'pr_' + result[
'bbid'] + '(trading_date date NULL,\
open float NOT NULL,\
high float NOT NULL,\
low float NOT NULL,\
close float NOT NULL,\
adj_close float NULL,\
volumn int NOT NULL)'
#cursor.execute(sql)
# Create a new record
df.to_sql(name=result['bbid'],
con=connection,
if_exists='replace',
index=False)
#cursor.executemany(sql)
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
connection.close()
def __init__(self):
"""Initilize the dbapi for the mercuriusdb
dbapi object named as mc_dbapi
Args: self
Returns: None
"""
pd_cre=pd.read_csv(const.MERC_ROOT+'/credential/credential.csv', index_col='item')
code =pd_cre.loc['mysql','cred']
crypto_obj=AESCipher('Always Love You')
passwd=crypto_obj.decrypt(code)
self.connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
def get_us_eq_hist_api(initime_obj, outtime_obj, symbol):
end_year=outtime_obj.year
strt_year=initime_obj.year
pd_cre=pd.read_csv(merc_root+'/credential/credential.csv', index_col='item')
code =pd_cre.loc['mysql','cred']
crypto_obj=AESCipher('Always Love You')
passwd=crypto_obj.decrypt(code)
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
# search bbid
with connection.cursor() as cursor:
sql = "SELECT bbid FROM `eq_symbol_id_name` WHERE `symbol`=%s"
cursor.execute(sql, symbol)
result = cursor.fetchone()
if result is None:
print('failed!')
else:
sql = "SELECT symbol FROM `eq_symbol_id_name` WHERE `bbid`=%s"
cursor.execute(sql, result)
result_all = cursor.fetchone()
# initial read
sql = "SELECT symbol,trading_date,open,high,low,close,volume FROM `eq_symbol_ohlc_"+str(date_year-10)+"` WHERE `symbol`='AAPL'"
df=pd.read_sql(sql, con=connection)
for yy in range(date_year-9,date_year+1):
sql = "SELECT symbol,trading_date,open,high,low,close,volume FROM `eq_symbol_ohlc_"+str(yy)+"` WHERE `symbol`='AAPL'"
df_temp=pd.read_sql(sql, con=connection)
df=df.append(df_temp, ignore_index=True)
connection.close()
print(df)
def mainfunc():
pd_cre = pd.read_csv(merc_root + '/credential/credential.csv',
index_col='item')
code = pd_cre.loc['mysql', 'cred']
crypto_obj = AESCipher('Always Love You')
passwd = crypto_obj.decrypt(code)
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
json_str = open(datapath).read()
json_str = json_str.replace('\\', '')
json_str = json_str.strip('"')
data = json.loads(json_str)
pos = 0
for item in data: # loop the data
with connection.cursor() as cursor:
sql = "SELECT bbid FROM `eq_symbol_id_name` WHERE `symbol`=%s"
cursor.execute(sql, data[item]['symbol'])
result = cursor.fetchone()
if result is None:
pos = pos + 1
print(data[item]['symbol'] + ' failed!')
continue
print(result['bbid'] + '-->' + data[item]['symbol'])
year = data[item]['date'][0:4]
mcid = str(md5_unique(data[item]['symbol'] + data[item]['date']))
try: # test the existence of variables
data[item]['open']
data[item]['high']
data[item]['low']
data[item]['close']
data[item]['volume']
data[item]['unadjustedVolume']
except:
print(data[item]['symbol'] + ' variable not found!!!')
continue
with connection.cursor() as cursor: # create the table is not exist
sql = 'CREATE TABLE IF NOT EXISTS eq_symbol_ohlc_' + str(
year) + '(symbol varchar(10) NOT NULL,\
bbid varchar(30) NOT NULL,\
mcid char(32) NOT NULL primary key,\
trading_date date NULL,\
open float NOT NULL,\
high float NOT NULL,\
low float NOT NULL,\
close float NOT NULL,\
adj_close float NULL,\
volume int NOT NULL,\
adj_volume int NuLL)'
cursor.execute(sql)
sql = "INSERT INTO `eq_symbol_ohlc_"+str(year)+\
"` (`symbol`,`bbid`, `mcid`,`trading_date`, `open`, `high`, `low`, `close`, `adj_close`, `volume`, `adj_volume`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "+\
"on duplicate key update `symbol`=`symbol`;"
cursor.execute(
sql,
(str(data[item]['symbol']), str(result['bbid']), str(mcid),
str(data[item]['date']), str(data[item]['open']),
str(data[item]['high']), str(data[item]['low']),
str(data[item]['close']), str(data[item]['close']),
str(data[item]['unadjustedVolume']), str(
data[item]['volume'])))
# end with
connection.commit()
# end for data loop
connection.close()
def mainfunc():
pd_cre = pd.read_csv('../../credential/credential.csv', index_col='item')
code = pd_cre.loc['mysql', 'cred']
crypto_obj = AESCipher('Always Love You')
passwd = crypto_obj.decrypt(code)
connection = pymysql.connect(host='localhost',
user='******',
password=passwd,
db='mercuriusdb',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
files = os.listdir(eq_path)
pos = 0
for item0 in files: # loop the market
df = pd.read_csv(eq_path + '/' + item0, parse_dates=True)
itm_arr = item0.split('.')
with connection.cursor() as cursor:
sql = "SELECT bbid FROM `eq_symbol_id_name` WHERE `symbol`=%s"
cursor.execute(sql, itm_arr[0])
result = cursor.fetchone()
if result is None:
pos = pos + 1
print(itm_arr[0] + ' failed!')
continue
print(result['bbid'] + '-->' + itm_arr[0])
for idx, row in df.iterrows():
year = row['Date'][0:4]
mcid = str(md5_unique(itm_arr[0] + row['Date']))
with connection.cursor() as cursor:
sql = 'CREATE TABLE IF NOT EXISTS eq_symbol_ohlc_' + str(
year) + '(symbol varchar(10) NOT NULL,\
bbid varchar(30) NOT NULL,\
mcid char(32) NOT NULL primary key,\
trading_date date NULL,\
open float NOT NULL,\
high float NOT NULL,\
low float NOT NULL,\
close float NOT NULL,\
adj_close float NULL,\
volume int NOT NULL,\
adj_volume int NuLL)'
cursor.execute(sql)
sql = "INSERT INTO `eq_symbol_ohlc_"+str(year)+\
"` (`symbol`,`bbid`, `mcid`,`trading_date`, `open`, `high`, `low`, `close`, `adj_close`, `volume`, `adj_volume`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "+\
"on duplicate key update `symbol`=`symbol`;"
cursor.execute(
sql,
(str(itm_arr[0]), str(result['bbid']), str(mcid),
str(row['Date']), str(row['Open']), str(row['High']),
str(row['Low']), str(row['Close']), str(row['Adj Close']),
str(row['Volume']), str(row['Volume'])))
connection.commit()
connection.close()