def review(dbService):
print("Review")
with open("../OpenReferral/review.csv", "r") as review:
csv_in_review = csv.DictReader(review)
stmt = "DELETE FROM review"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO review (id, service_id, reviewer_organization_id, title, description, date, score," \
"url, widget) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_review:
args = (row["id"],
row["service_id"],
row["reviewer_organization_id"],
row["title"],
row["description"],
row["date"],
row["score"],
row["url"],
row["widget"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def funding(dbService):
print("Funding")
with open("../OpenReferral/funding.csv", "r") as funding:
csv_in_funding = csv.DictReader(funding)
stmt = "DELETE FROM funding"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO funding (id, service_id, source) VALUES (%s, %s, %s)"
i = 0
for row in csv_in_funding:
args = (row["id"],
row["service_id"],
row["source"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def holiday_schedule(dbService):
print("Holiday Schedule")
with open("../OpenReferral/holiday_schedule.csv", "r") as holidaySchedule:
csv_in_holiday_schedule = csv.DictReader(holidaySchedule)
stmt = "DELETE FROM holiday_schedule"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO holiday_schedule (id, service_id, service_at_location_id, closed, opens_at, closes_at," \
" start_date, end_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_holiday_schedule:
args = (row["id"],
row["service_id"],
row["service_at_location_id"],
row["closed"],
row["opens_at"],
row["closes_at"],
row["start_date"],
row["end_date"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def stocks_intraday(conn, res):
insert = []
print(res)
symbol = res['Meta Data']['2. Symbol']
series = res['Time Series (1min)']
for (dstr, data) in series.items():
pdt = datetime.datetime.strptime(dstr,'%Y-%m-%d %H:%M:%S')
pdt = est.localize(pdt).astimezone(pytz.utc)
insert.append([
symbol.upper(),
pdt,
data['1. open'],
data['2. high'],
data['3. low'],
data['4. close'],
data['5. volume'],
])
sql = '''INSERT INTO stocks VALUES %s
ON CONFLICT DO NOTHING'''
# attempt to commit changes
db.execute(conn, sql, insert)
def contact(dbService):
print("Contact")
with open("../OpenReferral/contact.csv", "r") as contact:
csv_in_contact = csv.DictReader(contact)
stmt = "DELETE FROM contact"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO contact (id, service_id, name, title) values (%s, %s, %s, %s)"
i = 0
for row in csv_in_contact:
args = (row["id"],
row["service_id"],
row["name"],
row["title"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def location(dbService):
print("Location")
with open("../OpenReferral/location.csv", "r") as location:
csv_in_location = csv.DictReader(location)
stmt = "DELETE FROM location"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO location (id, name, description, latitude, longitude) VALUES (%s, %s, %s, %s, %s)"
i = 0
for row in csv_in_location:
args = (row["id"],
row["name"],
row["description"],
row["latitude"],
row["longitude"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def physical_address(dbService):
print("Physical address")
with open("../OpenReferral/physical_address.csv", "r") as physicalAddress:
csv_in_physical_address = csv.DictReader(physicalAddress)
stmt = "DELETE FROM physical_address"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO physical_address (id, location_id, address_1, city, state_province, " \
"postal_code, country, attention) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_physical_address:
args = (row["id"],
row["location_id"],
row["address_1"],
row["city"],
row["state_province"],
row["postal_code"],
row["country"],
row["attention"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def service_at_location(dbService):
print("Service at location")
with open("../OpenReferral/service_at_location.csv", "r") as service_at_location:
csv_in_location = csv.DictReader(service_at_location)
stmt = "DELETE FROM service_at_location"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO service_at_location (id, service_id, location_id) VALUES (%s, %s, %s))"
i = 0
for row in csv_in_location:
args = (row["id"],
row["service_id"],
row["location_id"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def organization(dbService):
print("Organization")
with open("../OpenReferral/organization.csv", "r") as organization:
csv_in_service = csv.DictReader(organization)
stmt = "DELETE FROM organization"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO organization (id, name, description, url, logo, uri) VALUES (%s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_service:
args = (row["id"],
row["name"],
row["description"],
row["url"],
row["logo"],
row["uri"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def stocks_depracated(conn, res):
insert = []
for symbol in res.keys():
chart = res[symbol]['chart']
for item in chart:
# process time
dstr = item['date']+item['minute']
pdt = datetime.datetime.strptime(dstr,'%Y%m%d%H:%M')
pdt = est.localize(pdt).astimezone(pytz.utc)
insert.append([
symbol.lower(),
item['open'] if 'open' in item else None,
item['close'] if 'close' in item else None,
item['high'] if 'high' in item else None,
item['low'] if 'low' in item else None,
item['volume'],
pdt,
])
sql = '''INSERT INTO stocks VALUES %s
ON CONFLICT DO NOTHING'''
# attempt to commit changes
db.execute(conn, sql, insert)
def link_taxonomy(dbService):
print("Link Taxonomy")
with open("../OpenReferral/link_taxonomy.csv", "r") as link_taxonomy:
csv_in_link_taxonomy = csv.DictReader(link_taxonomy)
stmt = "DELETE FROM link_taxonomy"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO link_taxonomy (id, link_type, link_id, taxonomy_id) VALUES (%s, %s, %s, %s)"
i = 0
for row in csv_in_link_taxonomy:
args = (row["id"],
row["link_type"],
row["link_id"],
row["taxonomy_id"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def eligibility(dbService):
print("Eligibility")
with open("../OpenReferral/eligibility.csv", "r") as eligibility:
csv_in_eligibility = csv.DictReader(eligibility)
stmt = "DELETE FROM eligibility"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO eligibility (id, service_id, eligibility) VALUES (%s, %s, %s)"
i = 0
for row in csv_in_eligibility:
args = (row["id"],
row["service_id"],
row["eligibility"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def phone(dbService):
print("Phone")
with open("../OpenReferral/phone.csv", "r") as phone:
csv_in_phone_schedule = csv.DictReader(phone)
stmt = "DELETE FROM phone"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO phone (id, contact_id, number, language) VALUES (%s, %s, %s, %s)"
i = 0
for row in csv_in_phone_schedule:
args = (row["id"],
row["contact_id"],
row["number"],
row["language"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def accessibility_for_disabilities(dbService):
print("Accessibility for disabilities")
with open("../OpenReferral/accessibility_for_disabilities", "r") as accessibility_for_disabilities:
csv_in_accessibility_for_disabilities = csv.DictReader(accessibility_for_disabilities)
stmt = "DELETE FROM accessibility_for_disabilities"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO accessibility_for_disabilities (id, location_id, accessibility) VALUES (%s, %s, %s)"
i = 0
for row in csv_in_accessibility_for_disabilities:
args = (row["id"],
row["location_id"],
row["accessibility"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def taxonomy(dbService):
print("Taxonomy")
with open("../OpenReferral/taxonomy", "r") as taxonomy:
csv_in_taxonomy = csv.DictReader(taxonomy)
stmt = "DELETE FROM taxonomy"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO taxonomy (id, name, vocabulary, parent_id) VALUES (%s, %s, %s, %s)"
i = 0
for row in csv_in_taxonomy:
args = (row["id"],
row["location_id"],
row["accessibility"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def funding(self):
print("Funding")
with open(self.path + "funding.csv", "r") as funding:
dbService = self.openDb()
try:
csv_in_funding = csv.DictReader(funding)
stmt = "INSERT INTO funding (id, service_id, source) VALUES (%s, %s, %s) " \
"ON DUPLICATE KEY UPDATE " \
"source = VALUES(source)"
i = 0
for row in csv_in_funding:
args = (row["id"], row["service_id"], row["source"])
try:
db.execute(dbService, stmt, args, "prepared", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
finally:
self.closeDb([dbService])
def cost_option(dbService):
print("Cost Option")
with open("../OpenReferral/cost_option.csv", "r") as costOption:
csv_in_cost_option_schedule = csv.DictReader(costOption)
stmt = "DELETE FROM cost_option"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO cost_option (id, service_id, valid_from, valid_to, `option`, amount)" \
" VALUES (%s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_cost_option_schedule:
args = (row["id"],
row["service_id"],
row["valid_from"],
row["valid_to"],
row["option"],
row["amount"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def clear_db():
query1 = """delete from users"""
query2 = """delete from authors"""
query3 = """delete from books"""
query4 = """delete from personel"""
loop.run_until_complete(execute(query1, False))
loop.run_until_complete(execute(query2, False))
loop.run_until_complete(execute(query3, False))
loop.run_until_complete(execute(query4, False))
def insert_user(username, password):
query = """
insert into users(username, password)
values(:username, :password)
"""
hashed_password = get_hashed_password(password)
values = {"username": username, "password": hashed_password}
loop.run_until_complete(execute(query, False, values))
def weather(conn, res):
data = res['currently']
insert = []
insert.append([
datetime.datetime.utcnow().replace(microsecond=0).isoformat(),
data['summary'],
data['temperature'],
data['apparentTemperature'],
data['precipType'] if 'precipType' in data else None,
data['precipProbability'],
data['humidity'],
data['pressure'],
data['windSpeed'],
])
sql = 'INSERT INTO weather VALUES %s'
# attempt to commit changes
db.execute(conn, sql, insert)
def db_check(app_log):
if not os.path.exists('backup.db'):
# create empty backup file
backup = sqlite3.connect('backup.db', timeout=1)
backup.text_factory = str
b = backup.cursor()
db.execute(
b,
"CREATE TABLE IF NOT EXISTS transactions (block_height, timestamp, address, recipient, amount, signature, public_key, block_hash, fee, reward, operation, openfield)",
app_log)
db.commit(backup, app_log)
db.execute(
b, "CREATE TABLE IF NOT EXISTS misc (block_height, difficulty)",
app_log)
db.commit(backup, app_log)
app_log.warning("Created backup file")
backup.close()
# create empty backup file
"""
def crypto(conn, res):
data = res['RAW']
insert = []
for key in data.keys():
asset = data[key]['USD']
insert.append([
datetime.datetime.utcnow().replace(microsecond=0).isoformat(),
asset['FROMSYMBOL'],
asset['PRICE'],
asset['MKTCAP'],
asset['TOTALVOLUME24HTO'],
asset['CHANGE24HOUR'],
asset['CHANGEPCT24HOUR'],
])
sql = 'INSERT INTO crypto VALUES %s'
# attempt to commit changes
db.execute(conn, sql, insert)
def regular_schedule(dbService):
print("Regular Schedule")
with open("../OpenReferral/regular_schedule.csv", "r") as regularSchedule:
csv_in_regular_schedule = csv.DictReader(regularSchedule)
stmt = "DELETE FROM regular_schedule"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO regular_schedule (id, service_id, service_at_location_id, opens_at, closes_at, " \
"valid_from, valid_to, dtstart, freq, `interval`, byday, bymonthday, description) " \
"VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_regular_schedule:
args = (row["id"],
row["service_id"],
row["service_at_location_id"],
row["opens_at"],
row["closes_at"],
row["valid_from"],
row["valid_to"],
row["dtstart"],
row["freq"],
row["interval"],
row["byday"],
row["bymonthday"],
row["description"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def service(dbService):
print("Service")
with open("../OpenReferral/service.csv", "r") as service:
csv_in_service = csv.DictReader(service)
stmt = "DELETE FROM service"
db.execute(dbService, stmt, (), "", False)
dbService.commit()
stmt = "INSERT INTO service (id, organization_id, name, description, url, email, status, fees," \
" accreditations, deliverable_type, attending_type, attending_access)" \
" VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
i = 0
for row in csv_in_service:
args = (row["id"],
row["organization_id"],
row["name"],
row["description"],
row["url"],
row["email"],
row["status"],
row["fees"],
row["accreditations"],
row["deliverable_type"],
row["attending_type"],
row["attending_access"])
try:
db.execute(dbService, stmt, args, "", False)
except mysql.connector.Error as err:
print(row)
print(err)
if ((i % 100) == 0) and i != 0:
dbService.commit()
print(i)
i += 1
dbService.commit()
def news(conn, res):
insert = []
article_list = res['articles']
for article in article_list:
insert.append([
article['title'],
article['author'],
article['source']['name'],
article['description'],
article['url'],
article['publishedAt'],
'top-headlines',
'',
datetime.datetime.utcnow().replace(microsecond=0).isoformat(),
])
sql = '''INSERT INTO news VALUES %s
ON CONFLICT (title, source)
DO UPDATE SET query_time = EXCLUDED.query_time'''
# attempt to commit changes
db.execute(conn, sql, insert)
def do_failover(self, connection_string_to_local_db_node):
"""Execute promote command for performing DB failover."""
self.logger.critical(
f"Execute PROMOTE command for node {self.local_node_host_name}")
shell.execute_cmd(self.promote_command)
self.logger.warning(
f"Execute CHECKPOINT command for node {self.local_node_host_name}")
db.execute(connection_string_to_local_db_node, "CHECKPOINT;")
db.alter_postgre_sql_config(connection_string_to_local_db_node,
'synchronous_standby_names', '')
db.execute(
connection_string_to_local_db_node,
f"SELECT * FROM pg_create_physical_replication_slot('{self.replication_slot_name}')"
)
db.execute(connection_string_to_local_db_node,
"SELECT pg_reload_conf();")
elif ident == dst:
possibleDests.append(element)
def matchICAOCodesAndPrep(element):
ident = element['ident']
if ident == src:
possibleSources.append(element)
elif ident == dst:
possibleDests.append(element)
element['dist'] = 99999
element['prev'] = None
Q.append(element)
db.execute('airports.csv', matchICAOCodes)
db.execute('navaids.csv', matchICAOCodesAndPrep)
if len(possibleSources) == 0 or len(possibleDests) == 0:
if len(possibleSources) == 0:
print("Can't find " + src + "!")
if len(possibleDests) == 0:
print("Can't find " + dst + "!")
sys.exit()
refSource = None
refDest = None
if len(possibleSources) > 1:
for source in possibleSources:
country = source['iso_country']
