SQL ORM Repository
Contents
SQL ORM Repository#
SQLRepo is an SQL repository that relies on
SQLAlchemy’s object-relational mapping (ORM).
The difference to SQLExprRepo
is that
SQLRepo
is more object centric and it may
be useful if you already use ORM in some places.
It is also more complex and in some cases more
error prone.
SQLRepo can be initiated numerous ways. You may initiate it with session, engine or SQLAlchemy connection string and you may optionally supply ORM model.
from sqlalchemy import create_engine
from redbird.repos import SQLRepo
engine = create_engine('sqlite://')
repo = SQLRepo(engine=engine, table="my_table")
You may also supply a session:
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from redbird.repos import SQLRepo
engine = create_engine('sqlite://')
session = Session(engine)
repo = SQLRepo(session=session, table="my_table")
Using ORM model:
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Car(Base):
__tablename__ = 'my_table'
color = Column(String, primary_key=True)
car_type = Column(String)
milage = Column(Integer)
from sqlalchemy import create_engine
from redbird.repos import SQLRepo
engine = create_engine('sqlite://')
repo = SQLRepo(model_orm=Car, engine=engine)
Using ORM model and reflect Pydantic Model:
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Car(Base):
__tablename__ = 'my_table'
color = Column(String, primary_key=True)
car_type = Column(String)
milage = Column(Integer)
from sqlalchemy import create_engine
from redbird.repos import SQLRepo
engine = create_engine('sqlite://')
repo = SQLRepo(model_orm=Car, reflect_model=True, engine=engine)
Using ORM model and Pydantic Model:
from pydantic import BaseModel
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class CarORM(Base):
__tablename__ = 'my_table'
color = Column(String, primary_key=True)
car_type = Column(String)
milage = Column(Integer)
class Car(BaseModel):
id: str
name: str
age: int
from sqlalchemy import create_engine
from redbird.repos import SQLRepo
engine = create_engine('sqlite://')
repo = SQLRepo(model=Car, model_orm=CarORM, engine=engine)
Usage#
Now you may use the repository the same way as any other repository. Please see:
Class#
- class redbird.repos.SQLRepo(*args, reflect_model=False, conn_string=None, engine=None, session=None, if_missing='raise', model=<class 'dict'>, id_field=None, query_model=<class 'redbird.base.BasicQuery'>, errors_query='raise', field_access='infer', ordered=True, model_orm=None, table=None, autocommit=True)#
SQL Repository
- Parameters
conn_string (str, optional) – Connection string to the database. Pass either conn_string, engine or session if model_orm is not defined.
engine (sqlalchemy.engine.Engine, optional) – SQLAlchemy engine to connect the database. Pass either conn_string, engine or session if model_orm is not defined.
model (Type) – Class of an item in the repository. Commonly dict or subclass of Pydantic BaseModel. By default dict
id_field (str, optional) – Attribute or key that identifies each item in the repository.
field_access ({'attr', 'key'}, optional) – How to access a field in an item. Either by attribute (‘attr’) or key (‘item’). By default guessed from the model.
query (Type, optional) – Query model of the repository.
errors_query ({'raise', 'warn', 'discard'}) – Whether to raise an exception, warn or discard the item in case of validation error in converting data to the item model from the repository. By default raise
model_orm (Type of Base, optional) – Subclass of SQL Alchemy representation of the item. This is the class that is operated behind the scenes.
table (str, optional) – Table name where the items lies. Should only be given if no model_orm specified.
session (sqlalchemy.orm.Session) – Connection session to the database. Pass either conn_string, engine or session if model_orm is not defined.
Examples
from sqlalchemy import create_engine from redbird.repos import SQLRepo engine = create_engine('sqlite://') repo = SQLRepo(engine=engine, table="my_table")
You may also supply a session:
from sqlalchemy import create_engine from sqlalchemy.orm import Session from redbird.repos import SQLRepo engine = create_engine('sqlite://') session = Session(engine) repo = SQLRepo(session=session, table="my_table")
Using ORM model:
from sqlalchemy.orm import declarative_base Base = declarative_base() class Car(Base): __tablename__ = 'my_table' color = Column(String, primary_key=True) car_type = Column(String) milage = Column(Integer) from sqlalchemy import create_engine from redbird.repos import SQLRepo engine = create_engine('sqlite://') repo = SQLRepo(model_orm=Car, engine=engine)
Using ORM model and reflect Pydantic Model:
from sqlalchemy.orm import declarative_base Base = declarative_base() class Car(Base): __tablename__ = 'my_table' color = Column(String, primary_key=True) car_type = Column(String) milage = Column(Integer) from sqlalchemy import create_engine from redbird.repos import SQLRepo engine = create_engine('sqlite://') repo = SQLRepo(model_orm=Car, reflect_model=True, engine=engine)
Using ORM model and Pydantic Model:
from pydantic import BaseModel from sqlalchemy.orm import declarative_base Base = declarative_base() class CarORM(Base): __tablename__ = 'my_table' color = Column(String, primary_key=True) car_type = Column(String) milage = Column(Integer) class Car(BaseModel): id: str name: str age: int from sqlalchemy import create_engine from redbird.repos import SQLRepo engine = create_engine('sqlite://') repo = SQLRepo(model=Car, model_orm=CarORM, engine=engine)
- insert(item)#
Insert item to the repository
- Parameters
item (instance of model) – Item to insert to the repository
Examples
repo.insert(Item(id="a", color="red"))
- filter_by(**kwargs)#
Filter the repository
- Parameters
**kwargs (dict) – Query which is used to conduct furher operation.
Examples
repo.filter_by(color="red")
- update(item)#
Update item in the repository
- Parameters
item (instance of model) – Item to update in the repository
Examples
repo.update(Item(id="a", color="red"))
- delete(item)#
Delete item from the repository
- Parameters
item (instance of model) – Item to delete from the repository
Examples
repo.delete(Item(id="a", color="red"))