def populate_items(items: List[ItemInput], database: Database) -> None:
'''Populate names
On conflict, just do nothing
Parameters
----------
items: List[ItemInput]
list of structured items
database: Database
db instance
'''
sql = '''INSERT INTO item (name, description)
VALUES (:name, :description)
ON CONFLICT (name) DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for item in items:
conn.query(sql,
name=item.name,
description=item.description)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
sql = '''INSERT INTO linkitemtheme (id_theme, id_item)
VALUES (
(SELECT id FROM theme t
WHERE t.name=:theme_name),
(SELECT id FROM item i
WHERE i.name=:item_name)
)
ON CONFLICT DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for item in items:
for theme in item.themes:
conn.query(sql,
theme_name=theme,
item_name=item.name)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
def _retrieve_nearest_vertex(db: Database, uic_ref: int) -> Optional[int]:
transaction = db.transaction()
row = db.query("""SELECT nearest_vertex_id FROM stop_vertex_mapping
WHERE stop_uic_ref = :uic_ref;""",
uic_ref=uic_ref).first()
transaction.commit()
if row:
return row['nearest_vertex_id']
return None
def _create_database(self, db_name):
""" Manages database creation.
Connects to MySQL via Database class from records library.
Creates a new MySQL local database.
"""
connection = Database(f'mysql+pymysql://{MYSQL_ID}:\
{MYSQL_PW}@localhost/?charset=utf8')
connection.query(f'CREATE DATABASE {db_name} CHARACTER SET "utf8"')
def get_db() -> Database:
'''Return database object for queries'''
if settings.TESTING:
if settings.TEST_DB_URL is None: # pragma: no cover
logger.critical('Test database not set')
sys.exit(1)
database = Database(settings.TEST_DB_URL)
else: # pragma: no cover
database = Database(settings.DB_URL)
return database
def calc_isochrones(db: Database, uic_ref: int, boundaries: List[int]) -> List[Isochrone]:
logger.info(f"Calculate isochrones for {uic_ref}")
nearest_vertex_id = _retrieve_nearest_vertex(db, uic_ref)
if not nearest_vertex_id:
return list()
transaction = db.transaction()
rows = db.query("""SELECT distance, polygon FROM isochrones(:node_id, :boundaries)""",
node_id=nearest_vertex_id,
boundaries=boundaries
).all()
isochrones = _map_isochrones(rows)
transaction.commit()
return isochrones
def get_count_of_distinct_next_stops(db: Database, relevant_stops: List[str]) -> Dict[int, int]:
rows = db.query("""WITH relevant_stops AS (
SELECT unnest(:relevant_stops) AS uic_ref
),
next_station_mapping AS (
SELECT DISTINCT
s.stop_name,
t.trip_id,
st.stop_sequence,
s.uic_ref
FROM stops s
INNER JOIN stop_times st ON s.stop_id = st.stop_id
INNER JOIN trips t ON st.trip_id = t.trip_id
INNER JOIN routes r ON t.route_id = r.route_id
WHERE r.route_type = 2 OR r.route_type = 1
)
SELECT distinct
nsm1.uic_ref,
COUNT(nsm2.stop_name)
OVER (PARTITION BY nsm1.uic_ref )
FROM relevant_stops
LEFT JOIN next_station_mapping nsm1 ON relevant_stops.uic_ref = nsm1.uic_ref
INNER JOIN next_station_mapping nsm2 ON nsm1.trip_id = nsm2.trip_id
WHERE nsm1.stop_sequence = (nsm2.stop_sequence - 1)
GROUP BY nsm1.uic_ref, nsm2.stop_name;""",
relevant_stops=relevant_stops).all()
return {int(row['uic_ref']): row['count'] for row in rows}
def populate_themes(themes: List[str], database: Database) -> None:
'''Populate database for testing purposes
On conflict, do nothing
Parameters
----------
themes: List[str]
list of themes names
database: Database
db instance
'''
sql = '''INSERT INTO theme (name) VALUES (:name)
ON CONFLICT (name) DO NOTHING;'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for name in themes:
conn.query(sql, name=name)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
def optimize_stop_vertex_mapping(db: Database):
logger.info("Locate public transport stops on the routing graph")
transaction = db.transaction()
db.query("""DROP TABLE IF EXISTS edge_preselection""")
db.query("""CREATE UNLOGGED TABLE edge_preselection (
id INTEGER,
source INTEGER,
target INTEGER,
cost DOUBLE PRECISION
)""")
db.query("SELECT optimize_stop_vertex_mapping()")
db.query("DROP TABLE edge_preselection;")
transaction.commit()
def cli():
cli_docs = """Box Exporter: Take that data and put it in a box.
Usage:
boxex <filepath> <filename> [--url=<url>]
boxex (-h | --help)
Options:
-h --help Show this screen
--url=<url> The database URL to use. Defaults to $DATABASE_URL
Notes:
- While you may specify a database connection string with --url, box-exporter
will automatically default to the value of $DATABASE_URL, if available.
- filepath is intended to be the path of a SQL file.
- All box credentials are set via environmental variables. Make sure you have
the following environment variables set or a KeyError will occur:
$BOX_CLIENT_ID
$BOX_CLIENT_SECRET
$BOX_ENTERPRISE_ID
$BOX_RSA_PRIVATE_KEY_PASS
$BOX_RSA_PRIVATE_KEY_PATH
$BOX_JWT_KEY_ID
$BOX_FOLDER_ID
"""
arguments = docopt(cli_docs)
# Create the database object
db = Database(arguments['--url'])
# Authenticate the box client
client = BoxClient()
queryfile = arguments['<filepath>']
filename = arguments['<filename>']
# Execute the query, if it is found.
if os.path.isfile(queryfile):
rows = db.query_file(queryfile).all()
if rows:
# grab the first row and use keys as fieldnames
fieldnames = rows[0].as_dict().keys()
client.upload(to_csv(fieldnames, rows), filename)
else:
print('There was no query file that was found')
def test_db_populate_themes(self, database: Database,
mock_data: LoadedDbItemsJson) -> None:
'''Test populating themes'''
themes = database.query('SELECT * FROM theme').as_dict()
assert len(themes) == len(mock_data.themes) # nosec
db_themes = [t['name'] for t in themes]
assert set(mock_data.themes) - set(db_themes) == set() # nosec
def test_db_populate_items(self, database: Database,
mock_data: LoadedDbItemsJson) -> None:
'''Test populating items'''
items = database.query('SELECT * FROM item').as_dict()
assert len(items) == len(mock_data.items) # nosec
db_items = [i['name'] for i in items]
mock_items = [i.name for i in mock_data.items]
assert set(mock_items) - set(db_items) == set() # nosec
def __init__(self):
self.configuration = Configuration()
self.database = Database(self.configuration.database.url)
self.game_repository: GameRepository = MySQLGameRepository(
self.database)
self.create_game_command_handler = CreateGameCommandHandler(
GameCreator(self.game_repository))
self.search_game_query_handler = SearchGameQueryHandler(
self.game_repository)
self.query_bus: QueryBus = SimpleQueryBus(
{SearchGameQuery.__name__: self.search_game_query_handler})
self.guess_repository: GuessRepository = MySQLGuessRepository(
self.database)
self.create_guess_command_handler = \
CreateGuessCommandHandler(GuessCreator(
self.guess_repository, self.query_bus))
def database_connection():
""" Manages connection to the database.
Opens 'db_name.txt' to retrieve db_name.
Connects to MySQL local database via records.Database
Returns 'database' object containing connection with database.
"""
with open('db_name.txt', "r") as f:
db_name = f.read()
database = Database(f'''mysql+pymysql://{MYSQL_ID}:{MYSQL_PW}@localhost/\
{db_name}?charset=utf8''')
return database
def test_db_populate_link_theme_items(
self, database: Database, mock_data: LoadedDbItemsJson) -> None:
'''Test links between names and themes'''
sql = '''
SELECT theme.name
FROM item, linkitemtheme, theme
WHERE item.id=linkitemtheme.id_item
AND theme.id=linkitemtheme.id_theme
AND item.name=:iname
'''
for item in mock_data.items:
links = database.query(sql, iname=item.name).as_dict()
link_themes = [l['name'] for l in links]
assert set(link_themes) - set(item.themes) == set() # nosec
def test_db_populate_names(self, database: Database,
mock_data: LoadedDbItemsJson) -> None:
'''Test populating themes'''
names_from_db = database.query('SELECT * FROM name').as_dict()
assert len(names_from_db) == len(mock_data.names) # nosec
mock_full_names = [
f'{n.firstname} {n.lastname}' for n in mock_data.names
]
db_full_names = [
f'{n["firstname"]} {n["lastname"]}' for n in names_from_db
]
assert set(mock_full_names) - set(db_full_names) == set() # nosec
def _query_transport_stop_rows(db: Database):
return db.query("""SELECT DISTINCT
s.uic_ref,
s.stop_name,
s.stop_lat,
s.stop_lon,
array_agg(r.route_type)
OVER (PARTITION BY s.uic_ref) AS route_types,
(s.uic_ref IN (SELECT uic_ref FROM intercity_stations)) AS is_intercity_station
FROM stops s
INNER JOIN stop_times st ON s.stop_id = st.stop_id
INNER JOIN trips t ON st.trip_id = t.trip_id
INNER JOIN routes r ON t.route_id = r.route_id
WHERE s.stop_id LIKE '85%'
GROUP BY s.uic_ref, s.stop_name, s.stop_lat, s.stop_lon, r.route_type;
""").all()
def _query_frequency_departure_times(db: Database, due_date: datetime) -> Dict[int, List[datetime]]:
"""Get departure times for stops which have trips that are modeled in the frequencies table"""
due_date_gtfs: str = _format_gtfs_date(due_date)
rows = db.query("""SELECT
s.uic_ref,
array_agg(st.departure_time + (INTERVAL '1s' * intervals)) AS departure_times
FROM stop_times st
INNER JOIN frequencies f on st.trip_id = f.trip_id
INNER JOIN trips t on f.trip_id = t.trip_id
INNER JOIN stops s on st.stop_id = s.stop_id
LEFT JOIN calendar_dates c ON t.service_id = c.service_id,
generate_series(0, 86400, f.headway_secs) intervals
WHERE (st.departure_time + (INTERVAL '1s' * intervals)) <= f.end_time
AND (c.date = :date OR t.service_id = '000000')
GROUP BY s.uic_ref""",
date=due_date_gtfs).all()
return {row['uic_ref']: _combine_departure_time(row, due_date) for row in rows}
def test_db_populate_link_theme_features(
self, database: Database, mock_data: LoadedDbItemsJson) -> None:
'''Test links between names and themes'''
sql = '''
SELECT theme.name
FROM feature, linkfeaturetheme, theme
WHERE feature.id=linkfeaturetheme.id_feature
AND theme.id=linkfeaturetheme.id_theme
AND feature.text_masc=:tmasc
AND feature.text_fem=:tfem
'''
for feat in mock_data.features:
links = database.query(sql,
tmasc=feat.text_masc,
tfem=feat.text_fem).as_dict()
link_themes = [l['name'] for l in links]
assert set(link_themes) - set(feat.themes) == set() # nosec
def test_db_populate_link_theme_names(
self, database: Database, mock_data: LoadedDbItemsJson) -> None:
'''Test links between names and themes'''
sql = '''
SELECT theme.name
FROM name, linknametheme, theme
WHERE name.id=linknametheme.id_name
AND theme.id=linknametheme.id_theme
AND name.firstname=:fname
AND name.lastname=:lname
'''
for name in mock_data.names:
links = database.query(sql,
fname=name.firstname,
lname=name.lastname).as_dict()
link_themes = [l['name'] for l in links]
assert set(link_themes) - set(name.themes) == set() # nosec
def _query_stop_times_departures(db: Database, due_date: datetime) -> Dict[int, List[datetime]]:
due_date_gtfs: str = _format_gtfs_date(due_date)
rows = db.query("""WITH calendar_trip_mapping AS (
SELECT
st.departure_time,
s.uic_ref
FROM stop_times st
INNER JOIN stops s ON st.stop_id = s.stop_id
INNER JOIN trips t ON st.trip_id = t.trip_id
LEFT JOIN calendar_dates c ON t.service_id = c.service_id
WHERE NOT EXISTS(SELECT 1
FROM frequencies f
WHERE f.trip_id = t.trip_id)
AND (c.date = :date OR t.service_id = '000000')
)
SELECT
uic_ref,
array_agg(departure_time) AS departure_times
FROM calendar_trip_mapping
GROUP BY uic_ref""",
date=due_date_gtfs).all()
# service_id 000000 represents the whole schedule
return {row['uic_ref']: _combine_departure_time(row, due_date) for row in rows}
import os from records import Database from flask import Flask app = Flask(__name__) db = Database(os.environ["DATABASE_URL"]) from app import routes
from urllib.parse import quote_plus
from records import Database
from common.config import DATABASE
def _build_database_url(
database=DATABASE,
server='.\SQLEXPRESS',
driver='SQL Server',
):
params = quote_plus(
f'DRIVER={driver};SERVER={server};DATABASE={database};')
database_url = f'mssql+pyodbc:///?odbc_connect={params}'
return database_url
database_url = _build_database_url()
db = Database(database_url)
from records import Database
from json import load
reviews = load(open('./reviews.json'))
db = Database('postgres:///pitchfork-reviews')
i = 0
for review in reviews:
album = review['album']
album_id = db.query_file('./insert_album.sql', **album).first().id
reviewer = review['author']
reviewer_id = db.query_file('./insert_reviewer.sql', **reviewer).first().id
idn = review['id']
url = review['url']
date = review['date']
year = date['year']
month = date['month']
day = date['day']
release_date = f'{year}-{month}-{day}'
score = review['score']
ibm = review['is_best_new_music']
i += 1
print(i)
db.query_file("insert_review.sql",
url=url,
reviewerId=reviewer_id,
albumId=album_id,
release_date=release_date,
score=score,
def mark_relevant_roads(db: Database, max_relevant_distance: float):
logger.info(f"Mark nodes that are reachable in {max_relevant_distance} metres")
transaction = db.transaction()
db.query("""SELECT mark_relevant_ways(:max_relevant_distance);""", max_relevant_distance=max_relevant_distance)
transaction.commit()
def db_connection(db_config: dict):
connection = Database(db_config['public-transport-stops'])
yield connection
connection.close()
from records import Database
from json import load
reviews = load(open('./reviews.json'))
db = Database('postgres:///pitchfork-reviews')
for review in reviews:
album = review['album']
album_id = db.query_file('./insert_album.sql', **album).first().id
db.query_file("insert_review.sql", album_id=album_id)
import os
import responder
from records import Database
DATABASE_URL = os.environ["DATABASE_URL"]
db = Database()
api = responder.API()
def migrate(db):
db.query_file("../contacts.sql")
@api.route("/")
async def greet_world(req, resp):
contacts = db.query("SELECT * FROM contacts")
resp.text = api.template("index.html", contacts=contacts)
if __name__ == "__main__":
migrate(db=db)
api.run()
def populate_names(names: List[NameInput], database: Database) -> None:
'''Populate names
On conflict, just do nothing
Parameters
----------
names: List[NameInput]
list of structured names
database: Database
db instance
'''
sql = '''INSERT INTO name (firstname, lastname, gender)
VALUES (:firstname, :lastname, :gender)
ON CONFLICT ON CONSTRAINT name_firstname_lastname_key
DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for name in names:
conn.query(sql,
firstname=name.firstname,
lastname=name.lastname,
gender=name.gender)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
sql = '''INSERT INTO linknametheme (id_theme, id_name)
VALUES (
(SELECT id FROM theme t
WHERE t.name=:theme_name),
(SELECT id FROM name n
WHERE n.firstname=:fname
AND n.lastname=:lname)
)
ON CONFLICT DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for name in names:
for theme in name.themes:
conn.query(sql,
theme_name=theme,
fname=name.firstname,
lname=name.lastname)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
def populate_features(features: List[FeatureInput], database: Database) -> None:
'''Populate names
On conflict, just do nothing
Parameters
----------
features: List[FeatureInput]
list of structured features
database: Database
db instance
'''
sql = '''INSERT INTO feature (text_masc, text_fem, description, is_good)
VALUES (:text_masc, :text_fem, :description, :is_good)
ON CONFLICT DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for feature in features:
conn.query(sql,
text_masc=feature.text_masc,
text_fem=feature.text_fem,
description=feature.description,
is_good=feature.is_good)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
sql = '''INSERT INTO linkfeaturetheme (id_theme, id_feature)
VALUES (
(SELECT id FROM theme t
WHERE t.name=:theme_name),
(SELECT id FROM feature f
WHERE f.text_masc=:tmasc
AND f.text_fem=:tfem)
)
ON CONFLICT DO NOTHING;
'''
conn = database.get_connection()
transaction = conn.transaction()
try:
for feature in features:
for theme in feature.themes:
conn.query(sql,
theme_name=theme,
tmasc=feature.text_masc,
tfem=feature.text_fem)
transaction.commit()
except IntegrityError as ierror: # pragma: no cover
logger.error(ierror)
transaction.rollback()
raise
finally:
conn.close()
file_name = '__'.join(row['github'].split('/'))
return os.path.exists('data/readmes/' + file_name)
def path(github):
file_name = '__'.join(github.split('/'))
return 'data/readmes/' + file_name
def has_file(row):
return os.path.exists(path(row.github))
if not os.path.isfile('data/data.pkl'):
db = Database(os.environ['DATABASE'])
rows = db.query('select github, stars, time_alive from libs')
filtered = filter(has_file, rows)
data = {
'y': [],
'x1': [],
'x2': [],
}
for lib in filtered:
data['y'].append(lib.stars)
data['x1'].append(parse_readme(lib.github))
data['x2'].append(int(lib.time_alive / 1000))