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"))