def initialize(database): if os.path.exists(database): dbase = lph.connect_to_db(database) else: dbase = setup.initial_setup(database) return dbase
def email_to_subscribers(countryList): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() for country in countryList: subscribers = {} subscribers = select_all_users_where_country(country) format_str = """SELECT * FROM casesWorld WHERE country="{countryName}";""" sql_command = format_str.format(countryName=country) cursor.execute(sql_command) cases = cursor.fetchall() # if subscribers and tblValues contain data if bool(subscribers): for row in subscribers: emailAddress = row['emailAdress'] name = row['Name'] send_email(emailAddress, name, country, cases[0]['new'], cases[0]['totalCases']) else: continue # close the connection conn.close() return
def insert_query(APIData): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() currDate = date.today() countryList = [] # insert each row from APIData in tbl for row in APIData: # many key errors from API. solve the problems with a try block try: format_str = """INSERT INTO casesWorld (country, active, new, deaths, totalCases, totalDeaths, totalRecovered, date) VALUES("{countryName}", "{activeCases}", "{newCases}", "{newDeaths}", "{totalCases}", "{totalDeaths}", "{totalRecovered}", "{date}");""" sql_command = format_str.format( countryName=row['Country_text'], activeCases=convert_to_int(row['Active Cases_text']), newCases=convert_to_int(row['New Cases_text']), newDeaths=convert_to_int(row['New Deaths_text']), totalCases=convert_to_int(row['Total Cases_text']), totalDeaths=convert_to_int(row['Total Deaths_text']), totalRecovered=convert_to_int(row['Total Recovered_text']), date=currDate) cursor.execute(sql_command) # append each country to the list countryList.append(row['Country_text']) except KeyError: continue conn.commit() # close the connection conn.close() # send emails only for the countries in the list email_to_subscribers(countryList)
def delete_where_userID(userID): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() format_str = """DELETE FROM subscribers WHERE subscriber_id="{userID}";""" sql_command = format_str.format(userID=userID) cursor.execute(sql_command) conn.commit() conn.close() return
def remove_user(emailAdress): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() format_str = """DELETE FROM subscribers WHERE emailAdress="{email}";""" sql_command = format_str.format(email=emailAdress) cursor.execute(sql_command) conn.commit() # close the connection conn.close() return
def select_all_users(): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() sql_command = """SELECT * FROM subscribers""" cursor.execute(sql_command) users = cursor.fetchall() # close the connection conn.close() return users
def update_name_country(userID, country, name): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() format_str = """UPDATE subscribers SET country="{country}", Name="{name}" WHERE subscriber_id="{userID}";""" sql_command = format_str.format(country=country, name=name, userID=userID) cursor.execute(sql_command) conn.commit() conn.close() return
def select_user_where_userID(tableName, userID): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT * FROM "{table}" WHERE user_id="{userID}";""" sql_command = format_str.format(table=tableName, userID=userID) cursor.execute(sql_command) user = cursor.fetchall() # close the connection conn.close() return user
def select_user(tableName, emailAdress): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT * FROM "{table}" WHERE emailAdress="{email}";""" sql_command = format_str.format(table=tableName, email=emailAdress) cursor.execute(sql_command) user = cursor.fetchall() # close the connection conn.close() return user
def select_all_users_where_country(country): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT * FROM subscribers WHERE country="{country}";""" sql_command = format_str.format(country=country) cursor.execute(sql_command) users = cursor.fetchall() # close the connection conn.close() return users
def select_distinct_data(tableName): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT DISTINCT date FROM "{table}";""" sql_command = format_str.format(table=tableName) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def select_history_for_country(tableName, country): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT * FROM "{table}" WHERE country="{country}";""" sql_command = format_str.format(table=tableName, country=country) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def select_countries(tableName): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT country FROM "{table}" ORDER BY country;""" sql_command = format_str.format(table=tableName) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def select_cases_where_country(tableName, country): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT country, active, new, deaths, totalCases, totalDeaths, totalRecovered, date FROM "{table}" WHERE country="{countryName}";""" sql_command = format_str.format(table=tableName, countryName=country) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def insert_user(emailAdress, name, country): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() format_str = """INSERT INTO subscribers (emailAdress, name, country) VALUES("{email}", "{name}", "{country}");""" sql_command = format_str.format(email=emailAdress, name=name, country=country) cursor.execute(sql_command) conn.commit() # close the connection conn.close() return
def select_maximum_cases(tableName, cases, country): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT max("{cases}") FROM "{table}" WHERE country="{country}" AND "{cases}" <>"";""" sql_command = format_str.format(table=tableName, cases=cases, country=country) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def isert_user_into_users(tableName, emailAddress, name, hashPsw, role): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() format_str = """INSERT INTO "{table}" (emailAdress, name, hash, role) VALUES("{email}", "{name}", "{hash}", "{role}");""" sql_command = format_str.format(table=tableName, email=emailAddress, name=name, hash=hashPsw, role=role) cursor.execute(sql_command) conn.commit() # close the connection conn.close() return
def select_cases_where_country_date(tableName, country, date): # Configure SQLite database conn = connect_to_db() conn.row_factory = dict_factory cursor = conn.cursor() format_str = """SELECT * FROM "{table}" WHERE country="{countryName}" AND date="{dateUnix}";""" sql_command = format_str.format(table=tableName, countryName=country, dateUnix=date) cursor.execute(sql_command) result = cursor.fetchall() # close the connection conn.close() return result
def update_query_world(APIData): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() currDate = date.today() countryList = [] # update the tbl with the data from APIData for row in APIData: try: # compare the number of cases from API with the number from table - update tbl if not equal totalCasesAPI = convert_to_int(row['Total Cases_text']) totalCasesDB = select_cases_where_country("casesWorld", row['Country_text']) if totalCasesDB[0]['totalCases'] == totalCasesAPI: continue else: format_str = """UPDATE casesWorld SET active="{activeCases}", new="{newCases}", deaths="{newDeaths}", totalCases="{totalCases}", totalDeaths="{totalDeaths}", totalRecovered="{totalRecovered}", date="{lastUpdate}" WHERE country="{countryName}";""" sql_command = format_str.format( countryName=row['Country_text'], activeCases=convert_to_int(row['Active Cases_text']), newCases=convert_to_int(row['New Cases_text']), newDeaths=convert_to_int(row['New Deaths_text']), totalCases=convert_to_int(row['Total Cases_text']), totalDeaths=convert_to_int(row['Total Deaths_text']), totalRecovered=convert_to_int(row['Total Recovered_text']), lastUpdate=currDate) cursor.execute(sql_command) # append each country to the liste countryList.append(row['Country_text']) except KeyError: continue conn.commit() # close the connection conn.close() # send emails only for the countries from the list email_to_subscribers(countryList)
def insert_into_history(APIData): # Configure SQLite database conn = connect_to_db() cursor = conn.cursor() today = date.today() tblHistory = "history" todayValues = {} # insert each row from APIData in tbl for row in APIData: # many key errors. solve the problems with try block try: # get the values from current date todayValues = select_cases_where_country_date( tblHistory, row['Country_text'], today) except KeyError: continue """ check if today date for current country is already in the database if not, insert the data of the current date """ if bool(todayValues): continue else: try: format_str = """INSERT INTO history (country, active, new, deaths, totalCases, totalDeaths, totalRecovered, date) VALUES("{countryName}", "{activeCases}", "{newCases}", "{newDeaths}", "{totalCases}", "{totalDeaths}", "{totalRecovered}", "{date}");""" sql_command = format_str.format( countryName=row['Country_text'], activeCases=convert_to_int(row['Active Cases_text']), newCases=convert_to_int(row['New Cases_text']), newDeaths=convert_to_int(row['New Deaths_text']), totalCases=convert_to_int(row['Total Cases_text']), totalDeaths=convert_to_int(row['Total Deaths_text']), totalRecovered=convert_to_int(row['Total Recovered_text']), date=today) cursor.execute(sql_command) except KeyError: continue # commit the changes conn.commit() # close the connection conn.close()
def initial_setup(dbase): dbase_conn = lph.connect_to_db(dbase) # Set up lists of plate types create_plates_us_table(dbase_conn) create_plates_mex_table(dbase_conn) create_plates_can_table(dbase_conn) create_plates_other_table(dbase_conn) # Set up abbreviation keys create_names_us_table(dbase_conn) create_names_mex_table(dbase_conn) create_names_can_table(dbase_conn) create_day_names_(dbase_conn) # Set up each trip as a row create_trip_data_table(dbase_conn) return dbase_conn
import time import json import logging import bottle from bottle import route, run, template, Bottle, request, static_file, redirect, error, hook, response, abort, auth_basic from counterpartyd.lib import (config, util, exceptions, bitcoin) from counterpartyd.lib import (send, order, btcpay, issuance, broadcast, bet, dividend, burn, cancel, callback) from helpers import set_options, init_logging, D, S, DecimalEncoder, connect_to_db, check_auth, wallet_unlock app = Bottle() set_options() init_logging() db = connect_to_db(10000) @app.route('/<filename:path>') @auth_basic(check_auth) def send_static(filename): return static_file(filename, root=config.GUI_DIR) @app.route('/') @auth_basic(check_auth) def index(): return static_file("counterpartygui.html", root=config.GUI_DIR) @app.route('/wallet')
import functools import requests as httpclient from requests.auth import HTTPBasicAuth import bottle from bottle import route, run, template, Bottle, request, static_file, redirect, error, hook, response, abort, HTTPError from counterpartyd.lib import (config, util, exceptions, bitcoin) from counterpartyd.lib import (send, order, btcpay, issuance, broadcast, bet, dividend, burn, cancel, callback) from helpers import set_options, init_logging, D, S, DecimalEncoder, connect_to_db, wallet_unlock, write_pid app = Bottle() set_options() init_logging() db = connect_to_db(10000) counterpartyd_params = { 'send': ['source', 'destination', 'quantity', 'asset'], 'order': ['source', 'give_quantity', 'give_asset', 'get_quantity', 'get_asset', 'expiration', 'fee_fraction_required', 'fee_fraction_provided'], 'btcpay': ['order_match_id'], 'cancel': ['offer_hash'], 'issuance': ['source', 'transfer_destination', 'asset_name', 'quantity', 'divisible', 'callable', 'call_date', 'call_price', 'description'], 'dividend': ['source', 'asset', 'quantity_per_share', 'dividend_asset'], 'callback': ['source', 'asset', 'fraction_per_share'], 'broadcast': ['source', 'text', 'value', 'fee_fraction'], 'bet': ['source', 'feed_address', 'bet_type', 'deadline', 'wager', 'counterwager', 'target_value', 'leverage', 'expiration'] } def getp(key, default=''):
def perform_mapping(input=None,root_path=None): """mapping list of snomed codes to icd-10 codes :param input(dict) :return result (obj) - json with mapping results""" output = helpers.get_empty_result_dict() prefs_filepath = os.path.join(root_path, 'prefs.json') prefs = helpers.read_json_file(prefs_filepath) if input == None: input = helpers.read_json_file(prefs['default_input_filepath']) db_connector, db_cursor = helpers.connect_to_db(host=prefs['mysql_host'], user=prefs['mysql_user'], passwd=prefs['mysql_pass']) # query & evaluating rules for each input code # first rule evaluating to true accepted as an answer for that code idx = 1 for el in input['snomed_codes']: # query rules values = '({0},{1})'.format(str(idx), el['snomed_code']) mapping_rules=helpers.query_mapping(db_name=prefs['mysql_db_name'], cursor=db_cursor, values=values) if len(mapping_rules) == 1 and mapping_rules[0][3] == None: # snomed code not found in mapping table result = {"num" : idx-1, "mapping_result": "snomed code not found", "snomed_code" : el['snomed_code'], "snomed_code_description" : None, "icd_10_code" : None, "icd_10_code_description" : None, "mapping_rule_evaluated_to_true": None, "all_mapping_rules_for_snomed_code" : []} output['results'].append(result) continue if 'age' in el: # convert age (dict) into days (int) el['age_in_days'] = helpers.age_in_days(el['age']) # evaluating each rule appropriate_rule = None for rule in mapping_rules: if helpers.evaluate_mapping_rule(rule=rule,input=el): appropriate_rule = rule break formatted_rule = helpers.mapping_rule_tuple_to_dict_conversion(appropriate_rule) if appropriate_rule != None and len(appropriate_rule[8]) != 0: # matching code found result = {"num": idx-1, "mapping_result": "mapped correctly", "snomed_code": el['snomed_code'], "snomed_code_description": formatted_rule['snomed_code_description'], "icd_10_code": formatted_rule['icd_10_code'], "icd_10_code_description": formatted_rule['icd_10_code_description'], "mapping_rule_evaluated_to_true": formatted_rule, "all_mapping_rules_for_snomed_code": helpers.mapping_rules_list_conversion(mapping_rules)} elif appropriate_rule != None: # empty mapping result = {"num": idx-1, "mapping_result": "rule mapped to empty code", "snomed_code": el['snomed_code'], "snomed_code_description": formatted_rule['snomed_code_description'], "icd_10_code": None, "icd_10_code_description": None, "mapping_rule_evaluated_to_true": formatted_rule, "all_mapping_rules_for_snomed_code": helpers.mapping_rules_list_conversion(mapping_rules)} else: # need additional info result = {"num": idx-1, "mapping_result": "additional info needed", "snomed_code": el['snomed_code'], "snomed_code_description": formatted_rule['snomed_code_description'], "icd_10_code": None, "icd_10_code_description": None, "mapping_rule_evaluated_to_true": None, "all_mapping_rules_for_snomed_code": helpers.mapping_rules_list_conversion(mapping_rules)} output['results'].append(result) idx += 1 if 'output' in input and input['output'] == 'short': short_output = {"results":[]} for entry in output["results"]: short_output["results"].append({"num": entry["num"], "snomed_code": entry["snomed_code"], "snomed_code_description": entry["snomed_code_description"], "icd_10_code": entry["icd_10_code"], "icd_10_code_description": entry["icd_10_code_description"]}) return short_output else: return output