async def coverage(ohm_area__in: str,
tags: str,
session: Session = Depends(get_session)):
engine = create_engine(sqlite_url, echo=True)
area = ohm_area__in.split(',')
top_s = list(topics.keys())
area_filter = None
if len(area) == 1 and len(area[0]) == 0:
area_filter = None
else:
area_filter = []
for gid in area:
area_filter.append('geonames:{}'.format(gid))
tags = tags.split('|')
combos = itertools.product(years, top_s)
dd = pd.read_pickle('tags.feather')
ret = []
for g in combos:
ndd = dd[((dd['ohm:from_time'] <= g[0][1])
& (dd['ohm:to_time'] >= g[0][0]))]
tdd = ndd[ndd['ohm:topic'] == g[1]]
if area_filter:
atdd = tdd[tdd['ohm:area'].isin(area_filter)]
tdd = atdd
ret.append({
'interval': [g[0][0], g[0][1]],
"topic": g[1],
"available": len(tdd.index),
"subs": list(set(tdd['ohm:topic:topic'].to_list()))
})
return ret
def session_fixture() -> Generator:
# use an in-memory database for testing
engine = create_engine("sqlite://",
connect_args={"check_same_thread": False},
poolclass=StaticPool)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
yield session
def session_fixture():
engine = create_engine(
"sqlite://",
connect_args={"check_same_thread": False},
poolclass=StaticPool,
)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
yield session
def refresh_db():
global engine
try:
engine = None
os.remove(sqlite_file_name)
except:
pass
engine = create_engine(sqlite_url, echo=True)
create_db_and_tables()
prepare()
def main():
engine = create_engine("sqlite://", echo=True)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
note = Note(text="The quick brown fox jumps over the lazy dog")
session.add(note)
session.commit()
# https://stackoverflow.com/a/5083472
session.refresh(note)
note_id = note.id
with Session(engine) as session:
result = session.exec(select(Note).where(Note.id == note_id))
note = result.one()
print("")
print("The note added:", note)
def session_fixture():
engine = create_engine(
"sqlite://",
connect_args={"check_same_thread": False},
poolclass=StaticPool,
)
SQLModel.metadata.create_all(bind=engine)
with Session(engine) as db:
test_player = (db.query(models.Player).filter(
models.Player.discord_id == "1234567890").first())
if not test_player:
test_player = models.Player(discord_id="1234567890",
name="TEST PLAYER 1",
discriminator="0001")
db.add(test_player)
db.commit()
db.refresh(test_player)
with Session(engine) as session:
yield session
def connect_sqlmodel(self, db_url: str):
self.engine = create_engine(db_url, echo=False)
SQLModel.metadata.create_all(self.engine)
# There doesn't appear to be a SQLModel-native way of creating those, so fall back to SQLAlchemy
id_resolved_status_authority_id_idx = Index(
"_id_resolved_status_authority_id_idx",
Thing.primary_key,
Thing.resolved_status,
Thing.authority_id,
)
# These index creations will throw if they already exist -- there's nothing to do in that case
try:
id_resolved_status_authority_id_idx.create(self.engine)
except ProgrammingError:
pass
authority_id_tcreated_idx = Index("authority_id_tcreated_idx",
Thing.authority_id, Thing.tcreated)
try:
authority_id_tcreated_idx.create(self.engine)
except ProgrammingError:
pass
item_type_status_idx = Index("item_type_status_idx", Thing.item_type,
Thing.resolved_status)
try:
item_type_status_idx.create(self.engine)
except ProgrammingError:
pass
resolved_status_authority_id_idx = Index(
"resolved_status_authority_id_idx",
Thing.resolved_status,
Thing.authority_id,
)
try:
resolved_status_authority_id_idx.create(self.engine)
except ProgrammingError:
pass
from sqlmodel import create_engine
sqlite_file_name = 'database.db'
sqlite_url = f'sqlite:///{sqlite_file_name}'
# NOTE: Remove echo on engine for prod
engine = create_engine(sqlite_url, echo=True)
from sqlmodel import SQLModel, create_engine
from config import Settings
engine = create_engine(f"sqlite:///{Settings.FILEPATH_DB}")
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
from sqlmodel import create_engine, Table, Column, Integer, ForeignKey, SQLModel, Field
from sqlalchemy.orm import sessionmaker
from typing import Any, Optional
from sqlalchemy.ext.declarative import as_declarative
from config import settings
SQLALCHEMY_DATABASE_URL = "mysql+pymysql://{username}:{password}@{server}/{db}".format(
username=settings.DATABASE_USER,
password=settings.DATABASE_PASSWORD,
server=settings.DATABASE_SERVER,
db=settings.DATABASE_NAME,
)
engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# Based on Postgres and MySQL
INTEGER_SIZE = 2147483647
@as_declarative()
class Base:
id: Any
__name__: str
# class PlayerServerLink(SQLModel, table=True):
# player_id: Optional[int] = Field(foreign_key="player.id")
# server_id: Optional[int] = Field(foreign_key="server.id")
player_server_association = Table(
material: str
quantity: float
bom_table_id: Optional[int] = Field(default=None,
foreign_key="bomtable.id")
bom_table: Optional[BOMTable] = Relationship(back_populates="items")
@property
def is_printable(self):
return self.material.upper() == "PETG"
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
# engine = create_engine(sqlite_url, echo=True)
engine = create_engine(sqlite_url)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def remove_page_bom(session, page_uid):
tables = session.exec(
select(BOMTable).where(BOMTable.name == page_uid)).all()
for table in tables:
items = session.exec(
select(BOMItem).where(BOMItem.bom_table_id == table.id)).all()
for item in items:
session.delete(item)
session.delete(table)
from sqlmodel import create_engine from config import settings engine = create_engine(settings.database_url)
from typing import Optional
from fastapi import FastAPI
from sqlmodel import Field, SQLModel, Session, create_engine, select
# Model
class Note(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
text: str
engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
# API
app = FastAPI()
@app.get("/notes/{note_id}")
async def read_note(note_id: int):
with Session(engine) as session:
results = session.exec(select(Note).where(Note.id == note_id))
note = results.one()
return note
@app.post("/notes/")
from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel import create_engine
from sqlmodel.sql.expression import Select, SelectOfScalar
from app.core.config import settings
ASYNC_ENGINE = create_async_engine(
settings.DATABASE_URL, echo=False) # echo can be True/False or 'debug'
SYNC_ENGINE = create_engine(settings.DATABASE_URL.replace('+asyncpg', ''),
echo='debug')
SelectOfScalar.inherit_cache = True # type: ignore
Select.inherit_cache = True # type: ignore
def get_session():
engine = create_engine(sqlite_url,
connect_args={"check_same_thread": False})
with Session(engine) as session:
yield session
from sqlmodel import Session, create_engine
from contextlib import contextmanager
from app.conf import DBSettings
from . import models
engine = create_engine(DBSettings.SQLALCHEMY_DATABASE_URL,
pool_size=10,
max_overflow=2,
pool_recycle=300,
pool_pre_ping=True,
pool_use_lifo=True)
models.SQLModel.metadata.create_all(engine)
@contextmanager
def session_scope() -> Session:
"""Provide a transactional scope around a series of operations."""
db = None
try:
db = Session(autocommit=False, autoflush=False,
bind=engine) # create session from SQLModel session
yield db
finally:
db.close()
# class Hero(SQLModel, table=True):
# id: Optional[int] = Field(default=None, primary_key=True)
# name: str
# secret_name: str
description: Optional[str] = None
sent: bool = Field(default=False, index=True)
def __key(self):
return (self.id, self.sha)
def __hash__(self):
return hash(self.__key())
def __eq__(self, other):
if isinstance(other, Posting):
return self.__key() == other.__key()
return NotImplemented
engine = create_engine('sqlite:///scrapdep.db')
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
class PostingRepository:
def create_posting(self, posting: Posting):
with Session(engine) as session:
session.add(posting)
session.commit()
def get_posting_by_sha(self, sha: str) -> Optional[Posting]:
with Session(engine) as session:
statement = select(Posting).where(Posting.sha == sha)
@classmethod
def update_by_id(cls, id, set):
user = cls.get_by_id(id)
with Session(engine) as session:
for k, v in set.items():
user[k] = v
session.add(user)
session.commit()
session.refresh(user)
return user
@classmethod
def delete_by_id(cls, id):
user = cls.get_by_id(id)
with Session(engine) as session:
session.delete(user)
session.commit()
return True
engine = create_engine('postgresql://*****:*****@db:5432/postgres')
def create_db():
SQLModel.metadata.create_all(engine)
if __name__ == '__main__':
SQLModel.metadata.create_all(engine)
from typing import Optional
from sqlmodel import Field, SQLModel
from sqlmodel import create_engine, Session, select
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
hero1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero2 = Hero(name="Spider-Bod", secret_name="Pedro Parqueador")
hero3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
engine = create_engine("sqlite:///database.db", echo=True)
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
session.add(hero1)
session.add(hero2)
session.add(hero3)
session.commit()
with Session(engine) as session:
stmt = select(Hero).where(Hero.name == "Spider-Bod")
hero = session.exec(stmt).first()
print(hero.name)
from typing import Optional
import strawberry
from sqlmodel import (SQLModel, Field, create_engine, select, Session)
# Criar engine do banco
engine = create_engine('sqlite:///database.db')
class Person(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
nome: str
idade: int
# Cria o banco de dados
SQLModel.metadata.create_all(engine)
def create_app(idade: int, nome: str):
person = Person(nome=nome, idade=idade)
with Session(engine) as session:
session.add(person)
session.commit()
session.refresh(person)
return person
@strawberry.type
class Pessoa:
)
class Block(SQLModel, table=True):
id: int = Field(primary_key=True)
chain_id: int
height: int
timestamp: datetime = Field(sa_column=Column(DateTime(timezone=True)))
snapshot: Optional[datetime] = Field(sa_column=Column(DateTime(timezone=True)))
snapshots: List["Snapshot"] = Relationship(back_populates="block")
class Snapshot(SQLModel, table=True):
id: int = Field(primary_key=True)
product: str
name: str
assets: float
block_id: int = Field(foreign_key="block.id")
block: Block = Relationship(back_populates="snapshots")
user = os.environ.get('PGUSER', 'postgres')
dsn = f'postgresql://{user}@localhost:5432/yearn'
engine = create_engine(dsn, echo=False)
# SQLModel.metadata.drop_all(engine)
SQLModel.metadata.create_all(engine)
class HeroRead(HeroBase):
id: int
class HeroUpdate(SQLModel):
name: Optional[str] = None
secret_name: Optional[str] = None
age: Optional[int] = None
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
app = FastAPI()
@app.on_event("startup")
def on_startup():
create_db_and_tables()
@app.post("/heroes/", response_model=HeroRead)
from sqlmodel import SQLModel, create_engine, Session
from app.core import config
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=config.ECHO_DATABASE, connect_args=connect_args)
def create_db_and_tables(): # pragma: no cover
SQLModel.metadata.create_all(engine)
def get_session(): # pragma: no cover
with Session(engine) as session:
yield session
