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 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
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