def test_create_prescription_error_metrics(client, mocker):
db = SessionLocal()
count_prescriptions_before = db.query(models.Prescription.id).count()
data = {
"clinic": "1",
"physician": "1",
"patient": "1",
"text": "Dipirona 1x ao dia"
}
mocker.patch.object(Metric,
'_send_metrics',
return_value={
'error': True,
'status_code': 400,
'detail': 'Bad Request'
})
response = client.post("/prescriptions/",
headers={"Content-Type": "application/json"},
json=data)
assert response.status_code == 400
count_prescriptions_after = db.query(models.Prescription.id).count()
assert count_prescriptions_before == count_prescriptions_after
db.close()
def test_create_prescription(client, mocker):
data = {
"clinic": "1",
"physician": "1",
"patient": "1",
"text": "Dipirona 1x ao dia"
}
mocker.patch.object(Metric, '_send_metrics', return_value=True)
response = client.post("/prescriptions/",
headers={"Content-Type": "application/json"},
json=data)
assert response.status_code == 200
result = response.json()
assert result['clinic'] == int(data['clinic'])
assert result['physician'] == int(data['physician'])
assert result['patient'] == int(data['patient'])
assert result['text'] == data['text']
db = SessionLocal()
prescription_db = db.query(models.Prescription).filter(
models.Prescription.id == result['id']).first()
db.close()
assert prescription_db.id == result['id']
def end_game(session: SessionLocal, game_id: int):
game = session.query(models.Game).filter(models.Game.id == game_id).first()
# Make sure game was found
if not game:
raise LookupError()
game.active = False
return game
def test_get_prescriptions(client):
prescription = schemas.PrescriptionCreate(clinic=1,
physician=1,
patient=1,
text='Dipirona 1x ao dia')
db = SessionLocal()
db_prescription = crud.create_prescription(db=db,
prescription=prescription)
db.commit()
response = client.get('/prescriptions/')
assert response.status_code == 200
assert db_prescription.id == response.json()[0]['id']
count_prescriptions = db.query(models.Prescription.id).count()
assert count_prescriptions == 1
db.query(models.Prescription).delete()
db.commit()
db.close()
def fetch_stock_data(id: int):
db = SessionLocal()
stock = db.query(Stock).filter(Stock.id == id).first()
yahoo_data = yfinance.Ticker(stock.symbol)
stock.ma200 = yahoo_data.info['twoHundredDayAverage']
stock.ma50 = yahoo_data.info['fiftyDayAverage']
stock.price = yahoo_data.info['previousClose']
stock.forward_pe = yahoo_data.info['forwardPE']
stock.forward_eps = yahoo_data.info['forwardEps']
if yahoo_data.info['dividendYield'] is not None:
stock.dividend_yield = yahoo_data.info['dividendYield'] * 100
db.add(stock)
db.commit()
def update_life(session: SessionLocal, state: ScoreUpdate):
game = session.query(
models.Game).filter(models.Game.id == state.game_id).first()
# Make sure game was found
if not game:
raise LookupError()
if state.increment:
if state.team == 'home':
game.home_life = game.home_life + state.increment
else:
game.away_life = game.away_life + state.increment
else:
if state.team == 'home':
game.home_life = state.absolute
else:
game.away_life = state.absolute
return game
def get_current_user_from_token(token: str, token_type: str):
# """tokenからユーザーを取得"""
# トークンをデコードしてペイロードを取得。有効期限と署名は自動で検証される。
payload = jwt.decode(token, "SECRET_KEY123", algorithms=["HS256"])
# トークンタイプが一致することを確認
if payload["token_type"] != token_type:
raise HTTPException(status_code=401, detail=f"トークンタイプ不一致")
# DBからユーザーを取得
# user = User.get_by_id(payload['user_id'])
db = SessionLocal()
user = db.query(User).filter(User.id == int(payload["user_id"])).first()
# リフレッシュトークンの場合、受け取ったものとDBに保存されているものが一致するか確認
if token_type == "refresh_token" and user.refresh_token != token:
print(user.refresh_token, "¥n", token)
raise HTTPException(status_code=401, detail="リフレッシュトークン不一致")
return user
def get_games(session: SessionLocal, page: int = 0, limit: int = 100):
return session.query(models.Game).offset(page).limit(limit).all()
def get_games_by_user(session: SessionLocal, user_id: int):
return session.query(models.Game).filter(
models.Game.home_id == user_id, models.Game.away_id == user_id).all()
def get_game_by_id(session: SessionLocal, game_id: int):
return session.query(models.Game).filter(models.Game.id == game_id).first()
async def handle(self, topic: str, payload: Dict):
db = SessionLocal()
try:
parts = topic.split("/")
if len(parts) < 2 or parts[0] != "moca":
return
# moca/...
if parts[1] == "via" and len(parts) >= 5:
# data from a service (e.g. moca-service-telegram)
# example: moca/via/telegram/4/contacts --> add or update contact(s)
service: str = parts[2]
connector_id: int = int(parts[3])
command: str = parts[4]
connector = crud.get_connector_by_connector_id(
db, service, connector_id)
if not connector:
return
if command == "contacts":
for contact_data in payload:
internal_contact_id = contact_data.get("contact_id")
contact = models.Contact(
contact_id=crud.get_id(connector.connector_id,
internal_contact_id),
internal_id=internal_contact_id,
service_id=service,
name=contact_data.get("name"),
username=contact_data.get("username"),
phone=contact_data.get("phone"),
avatar=None,
connector_id=connector.connector_id,
)
db.merge(contact)
db.commit()
elif command == "chats":
for chat_data in payload:
internal_chat_id = chat_data.get("chat_id")
chat = models.Chat(
chat_id=crud.get_id(connector.connector_id,
internal_chat_id),
connector_id=connector.connector_id,
internal_id=internal_chat_id,
name=chat_data.get("name"),
is_muted=False,
is_archived=False,
)
db.merge(chat)
db.commit()
chat_id = chat.chat_id
last_message = chat_data.get("last_message")
if last_message:
# Get contact of the sender, else ask for it
internal_contact_id = last_message.get(
"contact_id")
# check if contact exists, else request contact
maybe_contact = crud.get_contact_from_connector(
db,
connector.user_id,
connector.connector_id,
internal_contact_id,
)
if not maybe_contact:
contact = await self.get_contact(
connector.connector_type,
connector.connector_id,
internal_contact_id,
)
new_contact = models.Contact(
contact_id=crud.get_id(
connector.connector_id,
internal_contact_id),
internal_id=internal_contact_id,
service_id=connector.connector_type,
connector_id=connector.connector_id,
name=contact.get("name"),
username=contact.get("username"),
phone=contact.get("phone"),
avatar=contact.get("avatar"),
is_self=False,
)
db.merge(new_contact)
db.commit()
contact_id = new_contact.contact_id
else:
contact_id = maybe_contact.contact_id
# TODO: Reimplement last message
# new_last_message = models.Message(
# message_id=last_message.get("message_id"),
# contact_id=contact_id,
# internal_id=chat_id,
# message=json.dumps(last_message.get("message")),
# sent_datetime=datetime.fromisoformat(last_message.get("sent_datetime"))
# )
# db.merge(new_last_message)
# db.commit()
participants = chat_data.get("participants")
if participants:
for participant in participants:
c = crud.get_contact_from_connector(
db,
connector.user_id,
connector.connector_id,
participant,
)
if not c:
contact = await self.get_contact(
connector.connector_type,
connector.connector_id,
participant,
)
c = models.Contact(
contact_id=crud.get_id(
connector.connector_id,
participant),
internal_id=participant,
service_id=connector.connector_type,
connector_id=connector.connector_id,
name=contact.get("name"),
username=contact.get("username"),
phone=contact.get("phone"),
avatar=contact.get("avatar"),
is_self=False,
)
db.merge(c)
db.commit()
db.merge(
models.ContactsChatsRelationship(
contact_id=c.contact_id,
chat_id=chat_id))
try:
db.commit()
except sqlalchemy.exc.IntegrityError:
db.rollback()
elif command == "messages":
for message_data in payload:
# Get contact of the sender, else ask for it
internal_contact_id = message_data.get("contact_id")
# check if contact exists, else request contact
c = crud.get_contact_from_connector(
db,
connector.user_id,
connector.connector_id,
internal_contact_id,
)
if not c:
contact = await self.get_contact(
connector.connector_type,
connector.connector_id,
internal_contact_id,
)
c = models.Contact(
contact_id=crud.get_id(connector.connector_id,
internal_contact_id),
internal_id=internal_contact_id,
service_id=connector.connector_type,
connector_id=connector.connector_id,
name=contact.get("name"),
username=contact.get("username"),
phone=contact.get("phone"),
avatar=contact.get("avatar"),
is_self=False,
)
db.merge(c)
db.commit()
else:
pass
chat = (db.query(models.Chat).filter(
models.Chat.connector_id == connector_id,
models.Chat.internal_id == message_data.get(
"chat_id"),
).first())
if not chat:
chat = models.Chat(
chat_id=crud.get_id(
connector.connector_id,
message_data.get("chat_id")),
connector_id=connector_id,
internal_id=message_data.get("chat_id"),
name="Loading...",
is_muted=False,
is_archived=False,
)
db.merge(chat)
db.commit()
new_last_message = models.Message(
message_id=crud.get_message_id(
connector.connector_id,
message_data.get("message_id"), chat.chat_id),
internal_id=message_data.get("message_id"),
contact_id=crud.get_id(connector.connector_id,
internal_contact_id),
chat_id=chat.chat_id,
message=json.dumps(message_data.get("message")),
sent_datetime=datetime.fromisoformat(
message_data.get("sent_datetime").split("Z")
[0]),
)
db.merge(new_last_message)
db.commit()
finally:
db.close()
import csv
import datetime
from app import models
from app.database import SessionLocal, engine
db = SessionLocal()
models.Base.metadata.create_all(bind=engine)
# import fresh data only if the table is empty
if db.query(models.Record).count() == 0:
with open("sars_2003_complete_dataset_clean.csv", "r") as f:
csv_reader = csv.DictReader(f)
for row in csv_reader:
db_record = models.Record(
date=datetime.datetime.strptime(row["date"], "%Y-%m-%d"),
country=row["country"],
cases=row["cases"],
deaths=row["deaths"],
recoveries=row["recoveries"],
)
db.add(db_record)
db.commit()
db.close()
from sqlalchemy.orm.session import Session
from app.database import SessionLocal
from app.models.users import User
from app.dependencies import hash_password, get_db
if __name__ == '__main__':
db = SessionLocal()
admin = db.query(User).get("admin")
if admin:
print("Admin user already exists")
db.close()
quit()
user = User()
user.active = True
user.admin = True
user.username = "******"
user.hashed_password = hash_password("admin")
user.name = "Admin"
db.add(user)
db.flush()
db.commit()
print("Admin user created")
db.close()
def get_user(user_id: int):
db = SessionLocal()
return db.query(models.User).filter(models.User.id == user_id).first()
def clear_prescription_table():
db = SessionLocal()
db.query(models.Prescription).delete()
db.commit()
db.close()
class DB:
session = None
def __init__(self):
models.Base.metadata.create_all(bind=engine)
self.session = SessionLocal()
self.date_today = date.today().isoformat()
self.sources = json.load(
open(os.path.dirname(__file__) + "/../rki/sources.json", "r"))
self.data_dir = "downloads"
# create logger
self.logger = logging.getLogger("create_db")
self.logger.setLevel(logging.DEBUG)
# create formatter
formatter = logging.Formatter(
"%(asctime)s - %(name)s - %(levelname)s - %(message)s")
# create console handler and set level to debug
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
# add formatter to ch
ch.setFormatter(formatter)
# add ch to logger
self.logger.addHandler(ch)
# create file handler and set level to debug
fh = logging.FileHandler("create_db.log")
fh.setLevel(logging.DEBUG)
# add formatter to ch
fh.setFormatter(formatter)
# add ch to logger
self.logger.addHandler(fh)
"""
def bundeslaender_data_update_from_csv(self):
# bundeslaender = self.session.query(models.Bundesland).all()
bundesland_data = (
self.session.query(models.Bundesland_Daten_Taeglich)
.order_by(models.Bundesland_Daten_Taeglich.Aktualisierung.desc())
.first()
)
last_aktualisierung_timestamp = int(bundesland_data.Aktualisierung)
last_aktualisierung_date = datetime.fromtimestamp(last_aktualisierung_timestamp)
next_day = last_aktualisierung_date + timedelta(days=1)
while True:
date = next_day.date().isoformat()
file = self.data_dir + "/" + date + "_RKI_Corona_Bundeslaender.csv.gz"
if os.path.isfile(file):
self.insert_bundeslaender_data_from_csv(date)
else:
self.logger.info("file does not exist " + file + ". stopping update")
break
next_day += timedelta(days=1)
"""
def insert_bundeslaender_data_from_csv(self, date: str, filepath: str):
# filepath = self.data_dir + "/" + date + "_RKI_Corona_Bundeslaender.csv.gz"
if not os.path.isfile(filepath):
self.logger.warning("Cound not find file " + filepath)
return
self.logger.info("reading " + filepath)
rows = []
rows = read_data_from_csv(
filepath=filepath,
expected_header_line=
"OBJECTID_1,LAN_ew_AGS,LAN_ew_GEN,LAN_ew_BEZ,LAN_ew_EWZ,OBJECTID,Fallzahl,Aktualisierung,AGS_TXT,GlobalID,faelle_100000_EW,Death,cases7_bl_per_100k,cases7_bl,death7_bl,cases7_bl_per_100k_txt,AdmUnitId,SHAPE_Length,SHAPE_Area",
)
header = rows[0]
for i in range(1, len(rows)):
row = rows[i]
# if len(row) < 10:
# continue
bundesland_ID = row[header.index("OBJECTID_1")]
# get bundesland from db or create it
bundesland = (self.session.query(
models.Bundesland).filter_by(ID=bundesland_ID).one_or_none())
if bundesland is None:
bundesland = models.Bundesland(
ID=bundesland_ID,
LAN_ew_GEN=row[header.index("LAN_ew_GEN")],
LAN_ew_BEZ=row[header.index("LAN_ew_BEZ")],
LAN_ew_EWZ=row[header.index("LAN_ew_EWZ")],
)
self.session.add(bundesland)
aktualisierung_datetime = datetime.strptime(
row[header.index("Aktualisierung")], "%Y/%m/%d %H:%M:%S+00")
aktualisierung_timestamp = int(
datetime.timestamp(aktualisierung_datetime))
# check if current data is already in db by checking the timestamp
bundesland_daten_taeglich = (self.session.query(
models.Bundesland_Daten_Taeglich).filter(
(models.Bundesland_Daten_Taeglich.bundesland_id
== bundesland_ID),
(models.Bundesland_Daten_Taeglich.Aktualisierung
== aktualisierung_timestamp),
).one_or_none())
if bundesland_daten_taeglich is None:
self.logger.info("Addingd data for " + bundesland.LAN_ew_GEN)
bundesland_daten_taeglich = models.Bundesland_Daten_Taeglich(
Fallzahl=row[header.index("Fallzahl")],
Aktualisierung=aktualisierung_timestamp,
faelle_100000_EW=row[header.index("faelle_100000_EW")],
Death=row[header.index("Death")],
cases7_bl_per_100k=row[header.index("cases7_bl_per_100k")],
cases7_bl=row[header.index("cases7_bl")],
death7_bl=row[header.index("death7_bl")],
)
try:
bundesland.taegliche_daten.append(
bundesland_daten_taeglich)
except Exception as e:
print(e)
print(row)
self.logger.error("Error :" + str(e) +
" while trying to process line " + row +
"with data " + json.dumps(e))
else:
self.logger.warning("Data for " + bundesland.LAN_ew_GEN +
" for date " + date + " already in db")
Inserted_csv_File = models.Inserted_csv_File(
data_type="Bundesland",
date=date,
md5sum=md5(filepath),
file_path=filepath,
date_file_processed=datetime.now().isoformat(),
)
self.session.add(Inserted_csv_File)
self.session.commit()
"""
def landkreise_data_update_from_csv(filepath:str):
landkreise_data = (
self.session.query(models.Landkreis_Daten_Taeglich)
.order_by(models.Landkreis_Daten_Taeglich.last_update.desc())
.first()
)
last_update_timestamp = int(landkreise_data.last_update)
last_update_date = datetime.fromtimestamp(last_update_timestamp)
next_day = last_update_date + timedelta(days=1)
while True:
date = next_day.date().isoformat()
file = self.data_dir + "/" + date + "_RKI_Corona_Landkreise.csv.gz"
if os.path.isfile(file):
self.insert_landkreise_data_from_csv(date)
else:
self.logger.info("file does not exist " + file + ". stopping update")
break
next_day += timedelta(days=1)
"""
def insert_landkreise_data_from_csv(self, date: str, filepath: str):
# filepath = self.data_dir + "/" + date + "_RKI_Corona_Landkreise.csv.gz"
if not os.path.isfile(filepath):
self.logger.warning("Cound not find file " + filepath)
return
self.logger.info("reading " + filepath)
rows = []
rows = read_data_from_csv(
filepath=filepath,
expected_header_line=
"OBJECTID,ADE,GF,BSG,RS,AGS,SDV_RS,GEN,BEZ,IBZ,BEM,NBD,SN_L,SN_R,SN_K,SN_V1,SN_V2,SN_G,FK_S3,NUTS,RS_0,AGS_0,WSK,EWZ,KFL,DEBKG_ID,death_rate,cases,deaths,cases_per_100k,cases_per_population,BL,BL_ID,county,last_update,cases7_per_100k,recovered,EWZ_BL,cases7_bl_per_100k,cases7_bl,death7_bl,cases7_lk,death7_lk,cases7_per_100k_txt,AdmUnitId,SHAPE_Length,SHAPE_Area",
)
header = rows[0]
for i in range(1, len(rows)):
row = rows[i]
landkreis_ID = row[header.index("OBJECTID")]
# get landkreis from db or create it
landkreis = (self.session.query(
models.Landkreis).filter_by(ID=landkreis_ID).one_or_none())
if landkreis is None:
landkreis = models.Landkreis(
ID=landkreis_ID,
RS=row[header.index("RS")],
AGS=row[header.index("AGS")],
GEN=row[header.index("GEN")],
BEZ=row[header.index("BEZ")],
EWZ=row[header.index("EWZ")],
BL_ID=row[header.index("BL_ID")],
)
self.session.add(landkreis)
# else:
# print("TODO: update it if necessary !")
# check if current data is already in db by checking the timestamp
last_update_datetime = datetime.strptime(
row[header.index("last_update")], "%d.%m.%Y, %H:%M Uhr")
last_update_timestamp = int(
datetime.timestamp(last_update_datetime))
# check if current data is already in db by checking the timestamp
landkreis_daten_taeglich = (self.session.query(
models.Landkreis_Daten_Taeglich).filter(
(models.Landkreis_Daten_Taeglich.landkreis_id
== landkreis_ID),
(models.Landkreis_Daten_Taeglich.last_update
== last_update_timestamp),
).one_or_none())
if landkreis_daten_taeglich is None:
self.logger.info("Adding data for " + landkreis.GEN)
landkreis_daten_taeglich = models.Landkreis_Daten_Taeglich(
death_rate=row[header.index("death_rate")],
cases=row[header.index("cases")],
deaths=row[header.index("deaths")],
cases_per_100k=row[header.index("cases_per_100k")],
cases_per_population=row[header.index(
"cases_per_population")],
county=row[header.index("county")],
last_update=last_update_timestamp,
cases7_per_100k=row[header.index("cases7_per_100k")],
cases7_lk=row[header.index("cases7_lk")],
death7_lk=row[header.index("death7_lk")],
landkreis_id=landkreis_ID,
)
try:
self.session.add(landkreis_daten_taeglich)
except Exception as e:
print(e)
print(row)
self.logger.error("Error :" + str(e) +
" while trying to process line " + row +
"with data " + json.dumps(e))
else:
self.logger.warning("Data for " + landkreis.GEN +
" for date " + date + " already in db")
Inserted_csv_File = models.Inserted_csv_File(
data_type="Landkreis",
date=date,
md5sum=md5(filepath),
file_path=filepath,
date_file_processed=datetime.now().isoformat(),
)
self.session.add(Inserted_csv_File)
self.session.commit()
def insert_or_update_faelle_data_from_csv(self, date: str, filepath: str):
"""
Achtung: diese funktion nur auf leere Tabellen für Altersgruppen, Faelle anwenden !
"""
# filepath = self.data_dir + "/" + date + "_RKI_COVID19.csv.gz"
if not os.path.isfile(filepath):
self.logger.warning("Cound not find file " + filepath)
return
rows = []
self.logger.info("reading " + filepath)
rows = read_data_from_csv(
filepath=filepath,
expected_header_line=
"ObjectId,IdBundesland,Bundesland,Landkreis,Altersgruppe,Geschlecht,AnzahlFall,AnzahlTodesfall,Meldedatum,IdLandkreis,Datenstand,NeuerFall,NeuerTodesfall,Refdatum,NeuGenesen,AnzahlGenesen,IstErkrankungsbeginn,Altersgruppe2",
)
header = rows[0]
# Alle einzigartigen Altersgruppen finden
altersgruppen_set = set()
ag_index = header.index("Altersgruppe")
for i in range(1, len(rows)):
row = rows[i]
altersgruppe_string = row[ag_index]
altersgruppen_set.add(altersgruppe_string)
# alle Altersgruppen einfügen
altersgruppe_per_name = {}
for ag_name in altersgruppen_set:
altersgruppe = models.Altersgruppe(name=ag_name)
self.session.add(altersgruppe)
altersgruppe_per_name[ag_name] = altersgruppe
self.session.commit()
# alle Landkreise laden
landkreise = self.session.query(models.Landkreis).all()
landkreise_per_RS_ID = {}
for lk in landkreise:
landkreise_per_RS_ID[int(lk.RS)] = lk
# alle Bundesländer laden
bundeslaender = self.session.query(models.Bundesland).all()
bundeslaender_per_ID = {}
for bl in bundeslaender:
bundeslaender_per_ID[bl.ID] = bl
objectId_index = header.index("ObjectId")
IdBundesland = header.index("IdBundesland")
altersgruppe_index = header.index("Altersgruppe")
geschlecht_index = header.index("Geschlecht")
anzahlFall_index = header.index("AnzahlFall")
anzahlTodesFall_index = header.index("AnzahlTodesfall")
meldeDatum_index = header.index("Meldedatum")
idLandkreis_index = header.index("IdLandkreis")
datenStand_index = header.index("Datenstand")
neuerFall_index = header.index("NeuerFall")
neuerTodesFall_index = header.index("NeuerTodesfall")
refDatum_index = header.index("Refdatum")
neuGenesen_index = header.index("NeuGenesen")
anzahlGenesen_index = header.index("AnzahlGenesen")
istErkrankungsbeginn_index = header.index("IstErkrankungsbeginn")
altersgruppe2_index = header.index("Altersgruppe2")
counter = 0
self.logger.debug("getting all IDs")
db_rows = (
self.session.query(models.Fall_Daten_Taeglich)
# .options(load_only("ID"))
.all())
# all_IDs = []
db_rows_per_ID = {}
for row in db_rows:
# all_IDs.append(int(row.ID))
db_rows_per_ID[int(row.ID)] = row
# print(type(all_IDs[0]))
self.logger.debug("looping all rows ")
for i in range(1, len(rows)):
counter += 1
row = rows[i]
ID = row[objectId_index]
altersgruppe_string = row[altersgruppe_index]
meldeDatum_datetime = datetime.strptime(
row[header.index("Meldedatum")], "%Y/%m/%d %H:%M:%S+00")
meldeDatum_timestamp = int(datetime.timestamp(meldeDatum_datetime))
datenStand_datetime = datetime.strptime(
row[header.index("Datenstand")], "%d.%m.%Y, %H:%M Uhr")
datenStand_timestamp = int(datetime.timestamp(datenStand_datetime))
refDatum_datetime = datetime.strptime(
row[header.index("Refdatum")], "%Y/%m/%d %H:%M:%S+00")
refDatum_timestamp = int(datetime.timestamp(refDatum_datetime))
ID_Landkreis = row[idLandkreis_index]
ID_Bundesland = row[IdBundesland]
"""
fall_daten_taeglich = (
self.session.query(models.Fall_Daten_Taeglich)
.filter_by(ID=ID)
.one_or_none()
) """
if int(ID) not in db_rows_per_ID:
# self.logger.debug("new ID " + str(ID))
# if fall_daten_taeglich is None:
fall_daten_taeglich = models.Fall_Daten_Taeglich(
geschlecht=row[geschlecht_index],
anzahlFall=row[anzahlFall_index],
anzahlTodesFall=row[anzahlTodesFall_index],
meldeDatum=meldeDatum_datetime,
datenStand=datenStand_timestamp,
neuerFall=row[neuerFall_index],
neuerTodesFall=row[neuerTodesFall_index],
refDatum=refDatum_timestamp,
neuGenesen=row[neuGenesen_index],
anzahlGenesen=row[anzahlGenesen_index],
istErkrankungsbeginn=bool(
int(row[istErkrankungsbeginn_index])),
altersgruppe2=row[altersgruppe2_index],
)
fall_daten_taeglich.altersgruppe = altersgruppe_per_name[
altersgruppe_string]
fall_daten_taeglich.landkreis = landkreise_per_RS_ID[int(
ID_Landkreis)]
fall_daten_taeglich.bundesland = bundeslaender_per_ID[int(
ID_Bundesland)]
self.session.add(fall_daten_taeglich)
else:
self.logger.debug("updating ID " + str(ID))
data_to_update = {}
data_to_update["geschlecht"] = row[geschlecht_index]
data_to_update["anzahlFall"] = row[anzahlFall_index]
data_to_update["anzahlTodesFall"] = row[anzahlTodesFall_index]
data_to_update["meldeDatum"] = meldeDatum_datetime
data_to_update["neuerFall"] = row[neuerFall_index]
data_to_update["neuerTodesFall"] = row[neuerTodesFall_index]
data_to_update["refDatum"] = refDatum_timestamp
data_to_update["neuGenesen"] = row[neuGenesen_index]
data_to_update["anzahlGenesen"] = row[anzahlGenesen_index]
data_to_update["istErkrankungsbeginn"] = bool(
int(row[istErkrankungsbeginn_index]))
data_to_update["altersgruppe2"] = row[altersgruppe2_index]
fall_daten_taeglich = db_rows_per_ID[int(ID)]
for key in data_to_update:
value = str(data_to_update[key])
old_value = str(getattr(fall_daten_taeglich, key))
if value != old_value:
self.logger.info("updated value for key " + key + "=" +
str(value) + " in row " + str(i + 1) +
" it was " + str(old_value))
setattr(fall_daten_taeglich, key, value)
if counter > 50000:
percent = round((i + 1) / len(rows) * 100, 1)
self.logger.info("adding/updating Faelle, " + str(percent) +
"% done")
self.session.commit()
counter = 0
# free memory
rows[i] = None
self.session.commit()
Inserted_csv_File = models.Inserted_csv_File(
data_type="Fall",
date=date,
md5sum=md5(filepath),
file_path=filepath,
date_file_processed=datetime.now().isoformat(),
)
self.session.add(Inserted_csv_File)
self.session.commit()
def _clear_db(self):
print("clearing")
self.session.close()
print(os.path.dirname(__file__) + "/../database.db")
os.remove(os.path.dirname(__file__) + "/../database.db")
# print(models.Base.metadata.tables.values())
# models.Base.metadata.drop_all(bind=engine)
models.Base.metadata.create_all(bind=engine)
self.session = SessionLocal()
def create(self, date):
from sqlalchemy.engine import Engine
from sqlalchemy import event
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
# print("event")
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA journal_mode=OFF")
# cursor.execute("PRAGMA cache_size = 100000")
cursor.execute("PRAGMA cache_size = -20000")
cursor.execute("PRAGMA SYNCHRONOUS = OFF")
cursor.execute("PRAGMA LOCKING_MODE = EXCLUSIVE")
cursor.close()
self._clear_db()
self.insert_bundeslaender_data_from_csv(
date=date,
filepath=self.data_dir + "/" + date +
"_RKI_Corona_Bundeslaender.csv.gz",
)
self.insert_landkreise_data_from_csv(
date=date,
filepath=self.data_dir + "/" + date +
"_RKI_Corona_Landkreise.csv.gz",
)
self.insert_or_update_faelle_data_from_csv(
date=date,
filepath=self.data_dir + "/" + date + "_RKI_COVID19.csv.gz")
# self.update()
# get last date of data from table
def update(self):
# hier weiter
# bundesland
csv_entries_bundesland = (self.session.query(
models.Inserted_csv_File).filter_by(data_type="Bundesland").all())
csv_files_in_db = []
for row in csv_entries_bundesland:
csv_files_in_db.append(os.path.basename(row.file_path))
downloaded_files = glob.glob("downloads/*_Bundeslaender.csv.gz")
csv_files_on_disk = []
for f in downloaded_files:
csv_files_on_disk.append(os.path.basename(f))
for f in csv_files_in_db:
if not f in csv_files_on_disk:
self.logger.waring("csv file is in db but not on disk: " + f)
continue
csv_files_on_disk.remove(f)
re_date = re.compile(r"(202\d-\d+-\d+)")
for f in csv_files_on_disk:
m = re_date.search(f)
if m == None:
self.logger.error("could not determine date in filepath " + f)
date = m.group(1)
self.insert_bundeslaender_data_from_csv(date=date,
filepath="downloads/" + f)
# Landkreise
csv_entries_landkreis = (self.session.query(
models.Inserted_csv_File).filter_by(data_type="Landkreis").all())
csv_files_in_db = []
for row in csv_entries_landkreis:
csv_files_in_db.append(os.path.basename(row.file_path))
downloaded_files = glob.glob("downloads/*_Landkreise.csv.gz")
csv_files_on_disk = []
for f in downloaded_files:
csv_files_on_disk.append(os.path.basename(f))
for f in csv_files_in_db:
if not f in csv_files_on_disk:
self.logger.waring("csv file is in db but not on disk: " + f)
continue
csv_files_on_disk.remove(f)
re_date = re.compile(r"(202\d-\d+-\d+)")
for f in csv_files_on_disk:
m = re_date.search(f)
if m == None:
self.logger.error("could not determine date in filepath " + f)
date = m.group(1)
self.insert_landkreise_data_from_csv(date=date,
filepath="downloads/" + f)
# Faelle
csv_entries_fall = (self.session.query(
models.Inserted_csv_File).filter_by(data_type="Fall").all())
csv_files_in_db = []
for row in csv_entries_fall:
csv_files_in_db.append(os.path.basename(row.file_path))
downloaded_files = glob.glob("downloads/*_COVID19.csv.gz")
csv_files_on_disk = []
for f in downloaded_files:
csv_files_on_disk.append(os.path.basename(f))
for f in csv_files_in_db:
if not f in csv_files_on_disk:
self.logger.waring("csv file is in db but not on disk: " + f)
continue
csv_files_on_disk.remove(f)
re_date = re.compile(r"(202\d-\d+-\d+)")
print(csv_files_on_disk)
for f in csv_files_on_disk:
m = re_date.search(f)
if m == None:
self.logger.error("could not determine date in filepath " + f)
date = m.group(1)
self.insert_or_update_faelle_data_from_csv(date=date,
filepath="downloads/" +
f)
"""
