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