def update(model, item_id, data_in):
item = get_or_error(model, item_id)
data = db_process_input(data_in, item)
with db():
# Enforce referential integrity for Sqlite
if DATABASE_URL[:6] == "sqlite":
db.session.execute("PRAGMA foreign_keys = ON")
for column, value in data.items():
if value != getattr(item, column):
setattr(item, "changed", datetime.utcnow())
setattr(item, column, value)
db.session.add(item)
try:
db.session.commit()
except IntegrityError:
raise HTTPException(
status_code=409,
detail="This update would break references. Not performing.",
)
db.session.refresh(item)
return item
def cron():
with db():
# Creat a "handle habitica" job, rather than processing directly, in case there are multiple
# server instances running. Then wait some random seconds each instance, rather than everybody
# grabbing at once (race condition?). Not perfect, but will do for now.
k = 'habitica'
M.Job.create_job(k, run_on='server')
time.sleep(random.randint(0, 10))
job = M.Job.take_job(db.session, f"method='{k}'")
# Another instance grabbed it
if not job: return
logger.info(f"Running {k}")
users = db.session.execute(f"""
select id from users
where char_length(habitica_user_id) > 0 and char_length(habitica_api_token) > 0
-- stop tracking inactive users
and updated_at > {utcnow} - interval '3 days'
""").fetchall()
def fn():
errs = ""
for u in users:
try:
sync_for(db.session.query(M.User).get(u.id))
except Exception as err:
errs += "\n" + str(err)
if errs: raise Exception(errs)
return {}
M.Job.wrap_job(job.id, k, fn)
def on_after_register(user: M.FU_UserDB, request: Request):
ga(user.id, 'user', 'register')
with db():
t = M.Tag(user_id=user.id, main=True, selected=True, name='Main')
db.session.add(t)
db.session.commit()
send_mail(user.email, "welcome", {})
async def keep_database_alive():
while True:
with db():
logger.info("Database keep alive running.")
q = db.session.query(Settings).filter(Settings.key == "topic").one_or_none()
logger.info(f"Current twitch topic: {q.value}")
await asyncio.sleep(3600)
def create_tracking(start_time: float, request_datetime: datetime,
status_code: int):
with db():
tracking = models.Tracking(
request_datetime=request_datetime,
status_code=status_code,
completion_time=time.time() - start_time,
)
db.session.add(tracking)
db.session.commit()
def db_get(model, id=None, **kwargs):
with db():
q = db.session.query(model)
q = item_query(q, model, id=id, **kwargs)
item = q.one_or_none()
# Detach from the db session to support threading. Must use session.add() before running ORM operations.
if item is not None:
db.session.expunge(item)
return item
def create(model, data_in):
data = db_process_input(data_in)
item = model(**data)
with db():
try:
db.session.add(item)
db.session.commit()
db.session.refresh(item)
except IntegrityError as e:
raise HTTPException(409, "ID already exists.")
return item
def update_books(user_id):
with db():
# every x thumbs, update book recommendations
sql = """
select count(*)%8=0 as ct from bookshelf
where user_id=:uid and shelf not in ('ai', 'cosine')
"""
should_update = db.session.execute(satext(sql), {
'uid': user_id
}).fetchone().ct
if should_update:
Job.create_job(method='books',
data_in={'args': [str(user_id)]})
def delete(model, item_id):
item = get_or_error(model, item_id)
with db():
# Enforce referential integrity for Sqlite
if DATABASE_URL[:6] == "sqlite":
db.session.execute("PRAGMA foreign_keys = ON")
try:
db.session.query(model).filter(model.pk == item.pk).delete()
db.session.commit()
except IntegrityError:
raise HTTPException(
status_code=409,
detail=
"Can't delete this item because it is still being referenced.",
)
def user(data={}):
"""
Fake an user in db for testing
:return: user model object
"""
user = User(full_name=data.get('name') or fake.name(),
email=data.get('email') or fake.email(),
hashed_password=get_password_hash(data.get('password'))
or get_password_hash(fake.lexify(text='?????????')),
is_active=data.get('is_active')
if data.get('is_active') is not None else True,
role=data.get('role')
if data.get('role') is not None else UserRole.GUEST.value)
with db():
db.session.add(user)
db.session.commit()
db.session.refresh(user)
return user
def cron():
with db():
k = 'habitica'
logger.info(f"Running {k}")
users = db.session.execute(f"""
select id from users
where char_length(habitica_user_id) > 0 and char_length(habitica_api_token) > 0
-- stop tracking inactive users
and updated_at > now() - interval '3 days'
""").fetchall()
errs = ""
for u in users:
try:
sync_for(db.session.query(M.User).get(u.id))
except Exception as err:
errs += "\n" + str(err)
if errs: logger.error(errs)
return {}
from typing import Optional
from fastapi_sqlalchemy import DBSessionMiddleware, db
from fastapi import FastAPI
from config import DB_URL, Base
from teams.endpoints import teamsRouter
from players.endpoints import playersRouter
app = FastAPI()
app.add_middleware(DBSessionMiddleware,
db_url=DB_URL,
engine_args={"connect_args": {
"check_same_thread": False
}})
with db():
Base.metadata.create_all(bind=db.session.get_bind())
@app.get('/')
def check_status():
return {"status": "Ok"}
app.include_router(teamsRouter)
app.include_router(playersRouter)
def users():
with db():
user = db_session.query(User).filter_by(ref_type='partner').all()
print(user)
typer.echo("Hello users and i am video")
def drop_tables():
with db():
db.session.query(models.Streamer).delete()
db.session.query(models.Language).delete()
db.session.query(models.Topic).delete()
db.session.commit()
def test_get__model_attr_is_the_same_created_by_fixture():
crud = CRUDTopic()
with db():
result = crud.get(1)
assert result.name == 'topic_xpto'
def exists(model, id=None, **kwargs):
with db():
q = db.session.query(model)
q = item_query(q, model, id=id, **kwargs)
return db.session.query(q.exists()).scalar()
def get_or_error(model, item_id, detail="Item not found", **kwargs):
with db():
item = db_get(model, id=item_id, **kwargs)
if item is None:
raise HTTPException(status_code=404, detail=detail)
return item
def query(model, order_by="name", **kwargs):
with db():
q = db.session.query(model).order_by(order_by)
return q.all()
def test_get__model_returns_a_model_instance():
crud = CRUDTopic()
with db():
result = crud.get(1)
assert isinstance(result, Topic)
def create_topic(request):
with db():
CRUDBase(models.Topic).create({'name': 'Python'})
yield request
async def dispatch(self, request: Request,
call_next: RequestResponseEndpoint):
with db():
Base.set_session(db.session)
return await super().dispatch(request, call_next)
def create_topic_dummy():
topic = TopicSchema(name='topic_xpto')
with db():
CRUDTopic().create(topic)
