SQLAlchemy Tips

2019 June 3

FILTER BY NULL:

faturas_all = Fatura.query.filter(
	Fatura.cliente_id==cliente.id,
	Fatura.data_quitacao == None).order_by(
	Fatura.periodo_referencia_ano.desc(),
	Fatura.periodo_referencia_mes.desc()
)

FILTER BY NOT NULL:

faturas_all = Fatura.query.filter(
	Fatura.cliente_id==cliente.id,
	Fatura.data_quitacao != None).order_by(
	Fatura.periodo_referencia_ano.desc(),
	Fatura.periodo_referencia_mes.desc()
)

Filter by “ContaCorrente.cliente.codigo” (Filtering by Relationship Attribute):

ContaCorrente.query.join(ContaCorrente.cliente).filter(Cliente.codigo == '100')

Filter by “ContaCorrente.cliente.apelido” (Filtering by Relationship Attribute):

ContaCorrente.query.join(ContaCorrente.cliente).filter(Cliente.apelido.ilike("%da%"))

Complex query (filter + order_by relationship attribute):

PreFatura.query.join(PreFatura.cliente).filter(
	PreFatura.periodo_referencia_mes==mes,
	PreFatura.periodo_referencia_ano==ano,
	PreFatura.status=='A').order_by(
	PreFatura.periodo_referencia_ano.desc(),
	PreFatura.periodo_referencia_mes.desc(),
	Cliente.codigo)

Unique constraint with multiple fields:

from app.models.config import Config

class Task(BaseModel):
	id = db.Column(db.Integer, primary_key=True, autoincrement=True)
	entity_id = db.Column(db.Integer, nullable=False)
	status = db.Column(db.Enum(*Status.all()), nullable=False, index=True)
	config_id = db.Column(db.Integer, db.ForeignKey('config.id'), nullable=False)
	config = relationship(Config)
	__table_args__ = (db.UniqueConstraint('config_id', 'entity_id', name='_unique_config_entity'), )

## Filter by boolean attribute value :
product = Product.query.join(Vendor).filter(
Vendor.name == self.vendor.name,
Package.is_receipt_confirmed_at(datetime.utcnow()).is_(False),
Package.is_cancelled.is_(False)
)

Bring only some fields on the query (equivalent to SELECT field1 FROM table) / query by boolean field:

The with_entities method can be used for that purpose, with the list of fields you want back. In the example below, Table2.unique_identifier. For the boolean field, you can use the is_ method from sqlalchemy boolean fields.

results = Table1.query \
.join(Table2) \
.join(Table3) \
.with_entities(Table2.unique_identifier) \
.filter(
Table3.typefield == Constants.TYPE,
Table1.booleanfield.is_(True),
Table1.value == value
) \
.all()

SQLALCHEMY TIPS