def train(currency='USD'):
sql_command = ''' select date, rate_nb from exchange_rates
where currency_pair_id = (select id from currency_pairs where currency = '{}')
order by date;'''.format(currency)
data = pd.read_sql(sql_command, conn)
lendf = len(data)
train_df = data[:int(lendf * 0.995)]
test_df = data[int(lendf * 0.995):]
arima = ARIMAC(data, train_df, test_df, 'rate_nb')
arima.getBestParamsARIMA()
rmse, results = arima.modelTrain()
print(results)
print('Best order = {}'.format(arima.order))
print('RMSE = {}'.format(rmse))
cursor = conn.cursor()
cursor.execute('''insert into arima_params (p, d, q, currency, date, rmse)
values ({}, {}, {}, '{}', '{}', {}) on conflict do nothing'''
.format(arima.order[0], arima.order[1], arima.order[2],
currency, str(date.today()), rmse))
conn.commit()
cursor.close()
plt.figure(figsize=(14, 5))
plt.plot(data['date'], data['rate_nb'])
plt.plot(results['date'], results['predicted'])
plt.plot(results['date'], results['rate_nb'])
plt.show()
def approve_place(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE places SET approved = 1 WHERE placeID = %s "
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def delete_review(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "DELETE FROM review WHERE placeID = %s"
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def signup(username, email, password):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO users (userName, email, password) VALUES(%s, %s, %s)"
cur.execute(sql_string, (username, email, password))
conn.commit()
cur.close()
conn.close()
def prune_temp_request():
""" delete temporary requests tables. Should be called at app startup """
c = conn.cursor()
res = c.execute("""
DELETE FROM temp_request(userid,reqsec)
""")
conn.commit()
return True
def prune_temp_request():
""" delete temporary requests tables. Should be called at app startup """
c = conn.cursor()
res = c.execute("""
DELETE FROM temp_request(userid,reqsec)
""")
conn.commit()
return True
def block_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE users SET isBlock = 1 WHERE userID = %s "
cur.execute(sql_string, (user_id, ))
conn.commit()
cur.close()
conn.close()
def post_review(place_id, user_id, review, rating):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO review (placeID, userID, reviewDate, review,rating) VALUES(%s, %s,now(), %s, %s)"
cur.execute(sql_string, (place_id, user_id, review, rating))
conn.commit()
cur.close()
conn.close()
def new_place(user_id, name, address, website, phone, image_loc, description):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO places (userID, name, address, website, phone, image_location, description, postedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, now())"
cur.execute(
sql_string,
(user_id, name, address, website, phone, image_loc, description))
conn.commit()
cur.close()
conn.close()
def subsidies_info(url, api_fp_id, fp_id, s_start_id, pages):
# субсидии фeдпроекта
subs_id_f = s_start_id
for i in range(1, pages + 1):
print(url + str(i), '\t', 'время отправки запроса: ',
datetime.datetime.now())
resp_subsidies = requests.get(url + str(i)).text
data_subsidies = json.loads(resp_subsidies)
print(url + str(i), '\t', 'время получения данных: ',
datetime.datetime.now())
for item_s in data_subsidies['items']:
regnum = item_s['regnum'] # уникальный рег. номер субсидии
grbs_name = item_s['grbs_name'] # имя ГРБС
date_agreem = item_s['year'] # дата соглашения
full_amount = item_s['amount'] # сумма субсидии
subs_url = item_s['spending_subs_url'] # ссылка на субсидию
reciever = item_s['receivers'][0]['receiver_name'] # получатель
reciever = reciever.replace(
"'", '') # замена кавычек для корректных sql запросов
if grbs_name == None: # в апи почти всегда не данных по ГРБС /не костыль/
grbs_svr_code = item_s[
'grbs_svr_code'] # код по сводному регистру
resp_grbs = requests.get(
"https://spending.gov.ru/subsidies/grbs/%s/" %
grbs_svr_code)
soup = BeautifulSoup(resp_grbs.text, 'lxml')
data_grbs = soup.find_all(
'p', {'class': "h3 text-center font-weight-semibold mb-0"})
grbs_name = data_grbs[0].text
try:
cursor.execute(
"insert into subsidies(id_subsidy, id_federal_project, subsidy_sum, manager, recipient, release_date, reg_num, subs_url)"
"values (%i, %i, %f, '%s', '%s', %i, '%s', '%s')" %
(subs_id_f, fp_id, full_amount, grbs_name, reciever,
date_agreem, regnum, subs_url))
conn.commit()
except Exception as e:
print('Error!', e)
subs_id_f += 1
print('Успешно записано 50 субсидий, всего: %i' % (subs_id_f - 1))
def get_national_and_federal_projects_info(url):
resp = requests.get(url).text
data = json.loads(resp)
proj_id = 1
subs_id = 1
try:
cursor.execute("truncate table national_projects")
cursor.execute("truncate table federal_projects")
cursor.execute("truncate table subsidies")
conn.commit()
except Exception as e:
print("Error!", e)
# нацпроекты
for index, item in enumerate(data['items']):
national_project_name = item['np_short_name']
np_budget_released = item['total_sum']
np_id = item['np_id'] # id нацпроекта
try:
cursor.execute(
"insert into national_projects("
" id_national_project, project_name, total_np_budget, released_budget, np_api_id)"
" values (%i, '%s', %f, %f, '%s')" %
(index + 1, national_project_name, np_budget_released,
np_budget_released, np_id))
conn.commit()
except Exception as e:
print("Error!", e)
# федеральные проекты по нацпроекту
for item2 in item['fedprojects']:
api_fp_id = item2['fp_id'] # id федпроекта
federal_project_name = item2['fp_fullname'] # название
fp_budget = item2['total_sum'] # сумма контрактов и субсидий
subsidies_count = item2['subsidies_count'] # кол-во субсидий
subsidies_sum = item2['subsidies_sum'] # сумма субсидий
contracts_count = item2['contracts_count'] # кол-во контрактов
contracts_sum = item2['contracts_sum'] # сумма контрактов
print('Феделарьный проект №', proj_id)
try:
cursor.execute(
"insert into federal_projects(id_federal_project, id_national_project, project_name,"
" project_budget, fp_api_id, subsidies_count, contracts_count, contracts_sum, subsidies_sum)"
"values (%i, %i, '%s', %f, '%s', %i, %i, %f, %f)" %
(proj_id, index + 1, federal_project_name, fp_budget,
api_fp_id, subsidies_count, contracts_count,
contracts_sum, subsidies_sum))
conn.commit()
except Exception as e:
print("Error!", e)
if subsidies_count > 0:
pages = subsidies_count // 50 + 1
subsidies_info(
'https://api.spending.gov.ru/v1/natprojects/fpsubsidies?fp_id=%s&page='
% (api_fp_id), api_fp_id, proj_id, subs_id, pages)
subs_id += subsidies_count
proj_id += 1 # инкремент id федерального провекта
def save_access_tokens(userid, req_secret, acc_key, acc_secret):
c = conn.cursor()
c.execute(SAVE_ACCESS_TOKENS,
(userid, req_secret, acc_key, acc_secret, userid))
conn.commit()
return c.rowcount == 1
def delete_unverified_request(userid):
""" Delete an user temporary request returns always True """
c = conn.cursor()
res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid,))
conn.commit()
return True
def save_unverified_request(userid, req_secret):
""" save unverified request keys """
c = conn.cursor()
res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid))
conn.commit()
return True
def delete_token(userid):
""" delete token with provided userid """
c = conn.cursor()
c.execute(DELETE_TOKEN, (userid,))
conn.commit()
return c.rowcount == 1
def save_unverified_request( userid, req_secret ):
""" save unverified request keys """
c = conn.cursor()
res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid))
conn.commit()
return True
def save_access_tokens(userid, req_secret, acc_key, acc_secret):
c = conn.cursor()
c.execute(SAVE_ACCESS_TOKENS, (userid, req_secret, acc_key, acc_secret, userid))
conn.commit()
return c.rowcount == 1
for row in data:
achid = str(row[0])
stamp = str(row[1])
URL = Host + "/wow/achievement/" + achid + "?fields=news&locale=en_US&apikey=" + key
r = requests.get(URL)
c = r.json()
achname = c["title"]
POST = (
"Wiping with Style has completed: %s \n http://www.wowhead.com/achievement=%s #worldofwarcraft #achievement"
% (achname, achid)
)
print POST
logging.warning("Posting: %s" % POST)
try:
os.system('python /var/www/wwsguild/twitter-scripts/posttwit.py -m "%s"' % POST)
except:
print "Error"
raise
fixql = "UPDATE wwspost.news SET posted='1' WHERE stamp =" + stamp
try:
x.execute(fixql)
logging.warning("Updating: %s" % fixql)
conn.commit()
except mdb.Error, e:
try:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
except IndexError:
print "MySQL Error: %s" % str(e)
conn.rollback()
conn.close()
def delete_unverified_request(userid):
""" Delete an user temporary request returns always True """
c = conn.cursor()
res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid, ))
conn.commit()
return True
#!/usr/bin/env python3
from connection import conn
cursor = conn.cursor()
with open('./sql/create_tables.sql', 'r') as fsql:
sql = fsql.read()
cursor.execute(sql)
conn.commit()
conn.close()
def delete_token(userid):
""" delete token with provided userid """
c = conn.cursor()
c.execute(DELETE_TOKEN, (userid, ))
conn.commit()
return c.rowcount == 1
