def hash_feed(what, feed_type):
"""
what = 'news'
what = 'url'
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT short_title, url FROM feed WHERE hash = '' AND type= "+\
str(feed_type) + " LOCK IN SHARE MODE"
cursor.execute(sql)
res = cursor.fetchall()
short_title = ''
url = ''
hash_this = ''
column = ''
for row in res:
short_title = row[0]
url = row[1]
if what == 'news':
hash_this = short_title
column = 'short_title'
else:
hash_this = url
column = 'url'
cr_u = connection.cursor(pymysql.cursors.SSCursor)
sql_u = "UPDATE feed SET "+\
"hash = '"+ get_hash_string(str(hash_this)) +"' "+\
"WHERE "+ column +" = '"+ str(hash_this) +"'" + ' AND type = ' + str(feed_type)
cr_u.execute(sql_u)
connection.commit()
cursor.close()
connection.close()
def set_widgets_from_url(feed_id, connection, short_title, url, search,
sa_function):
"""
Insert into table feed widget from an url
Args:
String: id of feed type that identify a widget
String: Short title of the widget
String: URL to link to the widget
String: Search caption
Returns:
None
"""
date_today = datetime.datetime.now()
date_today = date_today.strftime("%Y%m%d")
short_description = short_title
content = short_title
ranking = '-1'
symbol = ''
feed_type = str(feed_id)
badge = ''
asset_class = '-'
market = '-'
hash_this = get_hash_string(str(url))
cr_i = connection.cursor(pymysql.cursors.SSCursor)
inserted_values = " " +\
"('"+date_today+"','"+short_title+"','"+short_description+"','"+content+"','"+url+"',"+\
"'"+ranking+"','"+symbol+"','"+feed_type+"','"+badge+"',"+\
"'"+search+"','"+asset_class+"','"+market+"','"+sa_function+"','"+hash_this+"'"+")"
sql_i = "INSERT IGNORE INTO feed"+\
"(date, short_title, short_description, content, url,"+\
" ranking, symbol, type, badge, "+\
"search, asset_class, market, sa_function, hash) VALUES " + inserted_values
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
def set_portf_feed():
"""
Import all the portfolio to table feed.
Args:
None
Returns:
None
"""
feed_id = 9
feed_type = "portfolios"
add_feed_type(feed_id, feed_type)
#Date [Today date]
date_today = datetime.datetime.now()
date_today = date_today.strftime("%Y%m%d")
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "DELETE FROM feed WHERE type= "+ str(feed_id)
cr_i.execute(sql_i)
connection.commit()
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.symbol, instruments.fullname, instruments.asset_class, "+\
"instruments.market, instruments.w_forecast_change, "+\
"instruments.w_forecast_display_info, symbol_list.uid, instruments.owner, "+\
"instruments.romad_st, instruments.stdev_st, instruments.y1, instruments.m6, "+\
"instruments.m3, instruments.m1 "+\
"FROM instruments "+\
"JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"WHERE instruments.symbol LIKE '"+ get_portf_suffix() +"%'"
cursor.execute(sql)
res = cursor.fetchall()
i = 0
inserted_value = ''
for row in res:
symbol = row[0]
fullname = row[1].replace("'", "")
asset_class = row[2]
market = row[3]
w_forecast_display_info = row[5]
uid = row[6]
owner = row[7]
romad_st = row[8]
y1_performance = row[10]
m6_performance = row[11]
m3_performance = row[12]
m1_performance = row[13]
short_title = fullname
short_description = symbol
content = get_portf_content(owner)
url = "{burl}p/?uid="+str(uid)
ranking = str(get_portf_ranking(symbol, romad_st, y1_performance,
m6_performance, m3_performance, m1_performance))
feed_type = str(feed_id)
hash_this = get_hash_string(str(url))
badge = w_forecast_display_info
search = asset_class + market + symbol + " " + fullname
debug(search +": "+ os.path.basename(__file__))
if i == 0:
sep = ''
else:
sep = ','
inserted_value = inserted_value + sep +\
"('"+date_today+"','"+short_title+"','"+short_description+"','"+content+"','"+url+"',"+\
"'"+ranking+"','"+symbol+"','"+feed_type+"','"+badge+"',"+\
"'"+search+"','"+asset_class+"','"+market +"','"+hash_this+"'" +")"
i += 1
sql_i = "INSERT IGNORE INTO temp_feed"+\
"(date, short_title, short_description, content, url,"+\
" ranking, symbol, type, badge, "+\
"search, asset_class, market, hash) VALUES " + inserted_value
debug(sql_i)
cr_i.execute('''CREATE TEMPORARY TABLE temp_feed
SELECT * FROM feed
LIMIT 0;''')
cr_i.execute(sql_i)
connection.commit()
cr_i.execute('SELECT @i := 0')
cr_i.execute('UPDATE temp_feed SET globalRank = (SELECT @i := @i +1) ORDER BY ranking DESC')
connection.commit()
cr_i.execute('INSERT INTO feed SELECT * FROM temp_feed')
connection.commit()
cr_i.close()
gc.collect()
cursor.close()
connection.close()
def set_widgets_tradingview_chart(symbol, feed_id, connection):
"""
Create tradingview chart widget for each symbol as per args
Args:
String: Instrument symbol
Integer: Id of feed type to identfy as widget
Returns:
None
"""
date_today = datetime.datetime.now()
date_today = date_today.strftime("%Y%m%d")
disabled = True
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.symbol, instruments.fullname, "+\
"instruments.asset_class, instruments.market, sectors.sector, "+\
"symbol_list.uid, symbol_list.disabled FROM instruments "+\
"JOIN sectors ON instruments.sector = sectors.id JOIN symbol_list ON "+\
"instruments.symbol = symbol_list.symbol "+\
"WHERE instruments.symbol = '"+ symbol +"' AND instruments.symbol NOT LIKE '"+\
get_portf_suffix() +"%' "
cursor.execute(sql)
res = cursor.fetchall()
i = 0
inserted_values = ''
for row in res:
symbol = row[0]
fullname = row[1].replace("'", "")
asset_class = row[2]
market = row[3]
sector = row[4]
uid = row[5]
disabled = row[6]
short_title = fullname
short_description = symbol
content = sector
url = "{burl}w/?funcname=get_tradingview_chart(" + str(uid) + ",0,0,1)"
badge = ''
ranking = '-1'
feed_type = str(feed_id)
search = set_feed_function(
'GP', symbol,
'label') + fullname + ' - Interactive Chart / Historical Graphs'
sa_function = set_feed_function('GP', symbol, 'value')
hash_this = get_hash_string(str(url))
debug(search + ": " + os.path.basename(__file__))
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "DELETE FROM feed WHERE (symbol ='"+symbol+"' AND date<='"+\
date_today+"' AND type="+ feed_type +")"
cr_i.execute(sql_i)
connection.commit()
if i == 0:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+date_today+"','"+short_title+"','"+short_description+"','"+content+"','"+url+"',"+\
"'"+ranking+"','"+symbol+"','"+feed_type+"','"+badge+"',"+\
"'"+search+"','"+asset_class+"','"+market+"','"+sa_function+"','"+hash_this+"'"+")"
cr_i.close()
cursor.close()
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "INSERT IGNORE INTO feed"+\
"(date, short_title, short_description, content, url,"+\
" ranking, symbol, type, badge, "+\
"search, asset_class, market, sa_function, hash) VALUES " + inserted_values
if not disabled:
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
def get_rss_specific(feed_id, date_d, feed_url, lang, limit):
"""
Get news in rss format specific to symbol.
Args:
Integer: Feed id for news data
String: Date in string format YYYYMMDD
String: Feed url
String: Asset class id
String: Market id
String: Langugage id
Integer: Maximum of posts to collect
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr_s = connection.cursor(pymysql.cursors.SSCursor)
sql_s = 'SELECT instruments.asset_class, instruments.market, '+\
'symbol_list.symbol, symbol_list.yahoo_finance, symbol_list.seekingalpha, '+\
'instruments.fullname, instruments.description '+\
'FROM symbol_list JOIN instruments ON symbol_list.symbol = instruments.symbol '+\
'WHERE symbol_list.disabled=0 AND (symbol_list.seekingalpha<>"" OR '+\
'symbol_list.yahoo_finance<>"") ORDER BY symbol'
cr_s.execute(sql_s)
res = cr_s.fetchall()
for row in res:
asset_class = row[0]
market = row[1]
symbol = row[2]
yahoo_finance = row[3]
seekingalpha = row[4]
feed = ''
instrument_fullname = row[5].replace(' ',
'+').replace('.',
'').replace(',', '')
instrument_description = row[5].replace(' ', '+').replace('.',
'').replace(
',', '')
feed_url_selection = feed_url.replace('{seekingalpha}', seekingalpha)
feed_url_selection = feed_url_selection.replace(
'{yahoo_finance}', yahoo_finance)
feed_url_selection = feed_url_selection.replace(
'{instrument_fullname}', instrument_fullname)
feed_url_selection = feed_url_selection.replace(
'{instrument_description}', instrument_description)
if instrument_description != '' or instrument_description is not None:
feed = feedparser.parse(feed_url_selection)
debug(feed_url_selection)
insert_line = ''
short_title = ''
short_description = ''
url = ''
search = ''
sep = ''
insert_line = ''
sentiment_score = 0
hash_str = ''
i = 1
for post in feed.entries:
short_title = str(post.title).replace("'", "`")
try:
short_description = str(post.description).replace("'", "`") +\
' '+ str(post.published)
except AttributeError as error:
debug(error)
short_description = str(post.published)
url = str(post.link)
url = url.replace("'", "'")
search = url
sentiment_score = analyze_sentiment_of_this(short_title + ' ' +
short_description)
hash_str = get_hash_string(str(short_title))
if i > 1:
sep = ','
insert_line = insert_line + sep +\
'(\''+ str(date_d)+'\',\''+str(short_title)+'\',\''+str(short_description)+'\',\''+\
str(url)+'\',\''+str(feed_id)+'\',\''+str(search)+'\',\''+\
str(asset_class)+'\',\''+str(market)+'\',\''+str(lang)+'\',\''+\
str(symbol)+'\','+ str(sentiment_score) +',\''+ str(hash_str)+'\''+ ')'
if i >= limit:
break
i += 1
if insert_line != '':
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'INSERT IGNORE INTO feed(date, short_title, short_description, '+\
'url, type, search, asset_class, market, lang, symbol, ranking, hash) '+\
'VALUES '+ insert_line
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
gc.collect()
cursor.close()
cr_s.close()
connection.close()
def get_rss_global(feed_id, date_d, feed_url, asset_class, market, lang,
limit):
"""
Get news in rss format global.
Args:
Integer: Feed id for news data
String: Date in string format YYYYMMDD
String: Feed url
String: Asset class id
String: Market id
String: Langugage id
Integer: Maximum of posts to collect
Returns:
None
"""
feed = feedparser.parse(feed_url)
insert_line = ''
short_title = ''
short_description = ''
url = ''
search = ''
sep = ''
insert_line = ''
sentiment_score = 0
hash_str = ''
i = 1
for post in feed.entries:
short_title = str(post.title).replace("'", "`")
try:
short_description = str(post.description).replace(
"'", "`") + ' ' + str(post.published)
except AttributeError as error:
debug(error)
short_description = str(post.published)
url = str(post.link)
url = url.replace("'", "'")
search = url
sentiment_score = analyze_sentiment_of_this(short_title + ' ' +
short_description)
hash_str = get_hash_string(str(short_title))
if i > 1:
sep = ','
insert_line = insert_line + sep +\
'(\''+ str(date_d)+'\',\''+str(short_title)+'\',\''+str(short_description)+'\',\''+\
str(url)+'\',\''+str(feed_id)+'\',\''+str(search)+'\',\''+str(asset_class)+'\',\''+\
str(market)+'\',\''+str(lang)+'\','+ str(sentiment_score) +',\''+ str(hash_str) + '\''+')'
if i >= limit:
break
i += 1
if insert_line != '':
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'INSERT IGNORE INTO feed(date, short_title, short_description, '+\
'url, type, search, asset_class, market, lang, ranking, hash) VALUES '+ insert_line
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
gc.collect()
cursor.close()
connection.close()