inklesspen

A SQLAlchemy query is a generative object that you keep obtaining a modified copy of with successive method calls. Often, the final method call executes the query and returns a value. Python's built-in line continuation can obscure the structure, so explicitly use \ here.

Instead of this:

    return db_session.query(Opportunity.id).filter(Opportunity.is_complete == sa.sql.true(),
                            Opportunity.created_by_id == flask.session.user_id,
                            Opportunity.is_staff_created == sa.sql.false()).count()

inklesspen

# https://github.com/inklesspen/mimir/blob/fee47910e149094a339a26297324d61568dbadf3/mimir/models/mallows.py
class Writeup(Schema):
    id = fields.Integer()
    author_slug = fields.String()
    writeup_slug = fields.String()
    title = fields.String()
    status = fields.String()
    published = fields.Boolean()
    offensive_content = fields.Boolean()
    triggery_content = fields.Boolean()

inklesspen

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import register
import transaction
# https://github.com/inklesspen/mimir/blob/fee47910e149094a339a26297324d61568dbadf3/mimir/models/__init__.py#L259-L278

def create_session(request):
    sessionmaker = request.registry['db_sessionmaker']
    session = sessionmaker()
    register(session, transaction_manager=request.tm)

inklesspen

# http://stackoverflow.com/questions/16172084/postgresql-join-only-most-specific-cidr-match/16172369#16172369
subselect = sa.select([
        test_systems.c.address.label('hostaddr'),
        test_networks.c.address.label('netaddr'),
        test_networks.c.description.label('description'),
        sa.func.row_number().over(partition_by=test_systems.c.address, order_by=sa.func.masklen(test_networks.c.address).desc()).label('row')
    ], from_obj=test_systems.outerjoin(test_networks, test_systems.c.address.op('<<')(test_networks.c.address))).alias('x')

query = sa.select([
        x.c.hostaddr,

inklesspen

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, array

rec_order = sa.select(
         # using the 'WITH ORDINALITY' suffix adds a column named 'ordinality'
         # the first row (from the first array value) has ordinality 1, the second has 2, and so on
         # this is useful for sorting things by the array value; simply join on the array value
         # and order by ordinality
        [sa.column('ordinality', sa.Integer),
         # The actual column generated by unnest is named 'unnest' by default,

inklesspen

select blocking_sa.query, pg_class.relname
from pg_stat_activity as blocking_sa
join pg_locks as blocking_locks on blocking_sa.pid = blocking_locks.pid
join pg_class on blocking_locks.relation = pg_class.oid
join pg_locks as blocked_locks on blocked_locks.relation = blocking_locks.relation
join pg_stat_activity as blocked_sa on blocked_locks.pid = blocked_sa.pid
where blocking_locks.granted is true and blocked_sa.waiting is true and blocking_sa.waiting is false;

inklesspen

var LocationSource = {
  fetchLocations(country) {
    return {
      remote() {
        // return a promise fetching locations for the given country
        return new Promise(/* do stuff here */).then(
          function(response) {
            return [country, response]
          },
          function(error) {

inklesspen

latest_query = session.query(Certificate.csr_id, sqlalchemy.func.max(Certificate.not_after).label('not_after'))\
    .group_by(Certificate.csr_id).subquery('latest')
session.query(Certificate.csr_id, Certificate.not_before, Certificate.not_after)\
    .join(latest_query, sqlalchemy.and_(Certificate.csr_id == latest_query.csr_id, Certificate.not_after == latest_query.not_after))

inklesspen

from sqlalchemy import DateTime
from sqlalchemy.types import TypeDecorator

class AwareDateTime(TypeDecorator):
    """
    A DateTime type which can only store tz-aware DateTimes
    """
    impl = DateTime(timezone=True)

    def process_bind_param(self, value, dialect):

inklesspen

[application.main]
filter-with = "pony"
use = "egg:webtest#debug"

[filter.pony]
use = "egg:paste#pony"

[server.main]
use = "egg:waitress"
host = "127.0.0.1"