class DatabaseService:
def __init__(self, db: SessionLocal = None):
self._db = db
def __enter__(self):
""" Contextmanager to handle database connection """
self._db = SessionLocal()
return self
def __exit__(self, *args):
try:
self._db.commit()
finally:
self._db.close()
self._db = None
def run_tags():
"""
Adapter to insert default tags for the city of Amsterdam
"""
db = SessionLocal()
tags = [
{
"tag": "parkeervergunning",
"type": "default",
"description": "Parkeervergunning (PVG)"
},
{
"tag": "wonen",
"type": "default",
"description": "Wonen (W)"
},
{
"tag": "reiskostenvergoeding",
"type": "default",
"description": "Reiskostenvergoeding (RKV)"
},
{
"tag": "administratie",
"type": "default",
"description": "Administratie (A)"
},
{
"tag": "bestuur",
"type": "default",
"description": "Bestuur (B)"
},
{
"tag": "directie",
"type": "default",
"description": "Directie (D)"
},
{
"tag": "leraren",
"type": "default",
"description": "Leraren (L)"
},
]
for entry in tags:
try:
row = DbTag(
tag=entry["tag"],
type=entry["type"],
description=entry["description"],
)
db.merge(row)
db.commit()
except IntegrityError as err:
print("Error: {0}".format(err))
db.rollback()
pass
async def db_session_middleware(request: Request, call_next):
response = Response("Internal server error", status_code=500)
try:
request.state.db = SessionLocal()
response = await call_next(request)
finally:
request.state.db.close()
return response
def run():
"""
Adapter to grab parking from the API of the City of Amsterdam
via https://api.data.amsterdam.nl/dcatd/datasets/IuAYhr-__qZj9Q/purls/uB95bElRaUcD0A
"""
connect_string = settings.AMSTERDAM_PARKING
url = urllib.request.urlopen(connect_string)
if url.getcode() == 200:
data = url.read()
# Start inserting
result = json.loads(data)
db = SessionLocal()
try:
DbParking.__table__.create(engine)
except:
pass
for entry in result["parkeerplaatsen"]:
row = DbParking(
source_name="Amsterdam",
source_url=settings.AMSTERDAM_PARKING,
source_id=entry["parkeerplaats"]["title"].split(":")[0],
link_url=entry["parkeerplaats"]["linkurl"],
link_title=entry["parkeerplaats"]["linknaam"],
title=entry["parkeerplaats"]["title"],
name=entry["parkeerplaats"]["title"].split(":")[1].strip(),
description=entry["parkeerplaats"]["Bijzonderheden"],
spots_text=entry["parkeerplaats"]["Busplaatsen"],
spots=int(entry["parkeerplaats"]["Busplaatsen"].split(" ")[0]),
point=functions.ST_GeomFromGeoJSON(
entry["parkeerplaats"]["Lokatie"]))
db.merge(row)
db.commit()
def run():
"""
Adapter to grab stops from the API of the City of Amsterdam
via https://api.data.amsterdam.nl/dcatd/datasets/IuAYhr-__qZj9Q/purls/uEOyRO9EKBNIeA
"""
connect_string = settings.AMSTERDAM_STOPS
url = urllib.request.urlopen(connect_string)
if url.getcode() == 200:
data = url.read()
# Start inserting
result = json.loads(data)
db = SessionLocal()
try:
DbStop.__table__.create(engine)
except:
pass
for entry in result["in_uitstaphaltes"]:
row = DbStop(
source_url=settings.AMSTERDAM_STOPS,
source_name="Amsterdam",
source_id=entry["in_uitstaphalte"]["title"].split(":")[0],
title=entry["in_uitstaphalte"]["title"],
name=entry["in_uitstaphalte"]["title"].split(":")[1].strip(),
description=entry["in_uitstaphalte"]["Bijzonderheden"],
spots_text=entry["in_uitstaphalte"]["Busplaatsen"],
spots=int(
entry["in_uitstaphalte"]["Busplaatsen"].split(" ")[0]),
point=functions.ST_GeomFromGeoJSON(
entry["in_uitstaphalte"]["Lokatie"]))
db.merge(row)
db.commit()
def run():
"""
Adapter to grab environmental zones from some obscure esri server
"""
connect_string = settings.IPO_ENVIRONMENTAL_ZONES
url = urllib.request.urlopen(connect_string)
if url.getcode() == 200:
data = url.read()
# Start inserting
result = json.loads(data)
db = SessionLocal()
try:
DbEnvironmentalZone.__table__.create(engine)
except:
pass
for entry in result["features"]:
entry["crs"] = "EPSG:4326"
geom = json.dumps(entry["geometry"])
g1 = geojson.loads(geom)
g2 = shape(g1)
geom_wkb = g2.wkb
row = DbEnvironmentalZone(
source_url=settings.IPO_ENVIRONMENTAL_ZONES,
source_name="kE0BiyvJHb5SwQv7",
source_id=str(entry["id"]),
objectid = entry["properties"]["OBJECTID"],
gemeente = entry["properties"]["Gemeente"],
website = entry["properties"]["website"],
vrachtauto = entry["properties"]["vrachtauto"],
bestelauto = entry["properties"]["bestelauto"],
personenauto = entry["properties"]["personenauto"],
taxi = entry["properties"]["taxi"],
bromfiets = entry["properties"]["bromfiets"],
autobus = entry["properties"]["autobus"],
bron = entry["properties"]["bron"],
versie = entry["properties"]["versie"],
vrachtjaar = entry["properties"]["vrachtjaar"],
besteljaar = entry["properties"]["besteljaar"],
personenjaar = entry["properties"]["personenjaar"],
taxijaar = entry["properties"]["taxijaar"],
bromjaar = entry["properties"]["bromjaar"],
autobusjaar = entry["properties"]["autobusjaar"],
wkb_geometry = functions.ST_GeomFromWKB(geom_wkb, 4326)
)
db.merge(row)
db.commit()
def test_user_login(client):
user = {"name": "test_user", "pin": 1234}
u = User(**user)
db_session = SessionLocal()
db_session.add(u)
db_session.commit()
data = {"pin": 1234}
resp = client.post("/user/login", json=data)
print(resp.text)
assert resp.status_code == 200
resp_data = resp.json()
assert user["name"] in resp_data["name"]
def run_po():
"""
Adapter to grab schools from the API of the City of Amsterdam
See https://schoolwijzer.amsterdam.nl/nl/api/documentatie
"""
connect_string = settings.AMSTERDAM_PRIMARY_SCHOOLS
url = urllib.request.urlopen(connect_string)
if url.getcode() == 200:
data = url.read()
# Start inserting
result = json.loads(data)
db = SessionLocal()
try:
DbSchool.__table__.create(engine)
except:
pass
for entry in result["results"]:
row = DbSchool(school_id=entry["id"],
brin=entry["brin"],
vestigingsnummer=entry["vestigingsnummer"],
school_type="po",
naam=entry["naam"],
grondslag=entry["grondslag"],
schoolwijzer_url=entry["schoolwijzer_url"],
address=entry["adres"]["adres"],
suburb=entry["adres"]["stadsdeel"],
postcode=entry["adres"]["postcode"],
website=entry["adres"]["website"],
email=entry["adres"]["email"],
phone=entry["adres"]["telefoon"],
city=entry["adres"]["plaats"],
onderwijsconcept=entry["onderwijsconcept"],
heeft_voorschool=entry["heeft_voorschool"],
leerlingen=entry["leerlingen"],
point=functions.ST_GeomFromText(
"POINT(" + str(entry["coordinaten"]["lng"]) +
" " + str(entry["coordinaten"]["lat"]) + ")",
4326))
db.merge(row)
db.commit()
def run_bso():
"""
Adapter to grab schools from the API of the City of Amsterdam
See https://schoolwijzer.amsterdam.nl/nl/api/documentatie
"""
connect_string = settings.AMSTERDAM_KINDERGARTEN
url = urllib.request.urlopen(connect_string)
if url.getcode() == 200:
data = url.read()
# Start inserting
result = json.loads(data)
db = SessionLocal()
try:
DbSchool.__table__.create(engine)
except:
pass
for entry in result["results"]:
row = DbSchool(school_id=entry["id"],
lrkp_id=entry["lrkp_id"],
school_type="opvang",
naam=entry["naam"],
schoolwijzer_url=entry["schoolwijzer_url"],
address=entry["adres"]["adres"],
postcode=entry["adres"]["postcode"],
suburb=entry["adres"]["stadsdeel"],
website=entry["adres"]["website"],
email=entry["adres"]["email"],
phone=entry["adres"]["telefoon"],
city=entry["adres"]["plaats"],
point=functions.ST_GeomFromText(
"POINT(" + str(entry["coordinaten"]["lng"]) +
" " + str(entry["coordinaten"]["lat"]) + ")",
4326))
db.merge(row)
db.commit()
def __enter__(self):
""" Contextmanager to handle database connection """
self._db = SessionLocal()
return self
def _get_db(self):
db = SessionLocal()
try:
yield db
finally:
db.close()
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()