Python sqlalchemy.func() Examples

def _generic_pad(arg, length, pad):
    f = sa.func
    arg_length = f.length(arg)
    pad_length = f.length(pad)
    number_of_zero_bytes = (
        (length - arg_length - 1 + pad_length) / pad_length + 1
    ) / 2
    return f.substr(
        f.replace(
            f.replace(
                f.substr(f.quote(f.zeroblob(number_of_zero_bytes)), 3), "'", ''
            ),
            '0',
            pad,
        ),
        1,
        length - f.length(arg),
    ) 

def _variance_reduction(func_name):
    suffix = {'sample': 'samp', 'pop': 'pop'}

    def variance_compiler(t, expr):
        arg, how, where = expr.op().args

        if arg.type().equals(dt.boolean):
            arg = arg.cast('int32')

        func = getattr(
            sa.func, '{}_{}'.format(func_name, suffix.get(how, 'samp'))
        )

        if where is not None:
            arg = where.ifelse(arg, None)
        return func(t.translate(arg))

    return variance_compiler 

def audit_table(self, table, exclude_columns=None):
        args = [table.name]
        if exclude_columns:
            for column in exclude_columns:
                if column not in table.c:
                    raise ImproperlyConfigured(
                        "Could not configure versioning. Table '{}'' does "
                        "not have a column named '{}'.".format(
                            table.name, column
                        )
                    )
            args.append(array(exclude_columns))

        if self.schema_name is None:
            func = sa.func.audit_table
        else:
            func = getattr(getattr(sa.func, self.schema_name), 'audit_table')
        query = sa.select([func(*args)])
        if query not in cached_statements:
            cached_statements[query] = StatementExecutor(query)
        listener = (table, 'after_create', cached_statements[query])
        if not sa.event.contains(*listener):
            sa.event.listen(*listener) 

def get_alarms(self, meter=None, pagination=None, **kwargs):
        """Yields a lists of alarms that match filters."""
        pagination = pagination or {}
        session = self._engine_facade.get_session()
        query = session.query(models.Alarm)
        query = apply_filters(query, models.Alarm, **kwargs)
        query = self._get_pagination_query(
            session, query, pagination, alarm_api_models.Alarm, models.Alarm)

        alarms = self._retrieve_alarms(query)

        # TODO(cmart): improve this by using sqlalchemy.func factory
        if meter is not None:
            alarms = filter(lambda row:
                            row.rule.get('meter_name', None) == meter,
                            alarms)

        return alarms 

def postgresql_array_search(element, compiler, **kw):
    needle, haystack = element.clauses
    i = sa.func.generate_subscripts(haystack, 1).alias('i')
    c0 = sa.column('i', type_=sa.INTEGER(), _selectable=i)
    result = (
        sa.func.coalesce(
            sa.select([c0])
            .where(haystack[c0].op('IS NOT DISTINCT FROM')(needle))
            .order_by(c0)
            .limit(1)
            .as_scalar(),
            0,
        )
        - 1
    )
    string_result = compiler.process(result, **kw)
    return string_result 

def set_activity_values(self, session):
        dialect = session.bind.engine.dialect
        table = self.transaction_cls.__table__

        if not isinstance(dialect, PGDialect):
            warnings.warn(
                '"{0}" is not a PostgreSQL dialect. No versioning data will '
                'be saved.'.format(dialect.__class__),
                RuntimeWarning
            )
            return

        values = convert_callables(self.get_transaction_values())
        if values:
            values['native_transaction_id'] = sa.func.txid_current()
            values['issued_at'] = sa.text("now() AT TIME ZONE 'UTC'")
            stmt = (
                insert(table)
                .values(**values)
                .on_conflict_do_nothing(
                    constraint='transaction_unique_native_tx_id'
                )
            )
            session.execute(stmt) 

def _count_distinct(t, expr):
    arg, where = expr.op().args

    if where is not None:
        sa_arg = t.translate(where.ifelse(arg, None))
    else:
        sa_arg = t.translate(arg)

    return sa.func.count(sa_arg.distinct()) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.all_`

    """

    return arrexpr.all(other, operator) 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.any_`

    """

    return arrexpr.any(other, operator) 

def _ntile(t, expr):
    op = expr.op()
    args = op.args
    arg, buckets = map(t.translate, args)
    return sa.func.ntile(buckets) 

def _lead(t, expr):
    arg, offset, default = expr.op().args
    if default is not None:
        raise NotImplementedError()
    sa_arg = t.translate(arg)
    sa_offset = t.translate(offset) if offset is not None else 1
    return sa.func.lead(sa_arg, sa_offset) 

def _lag(t, expr):
    arg, offset, default = expr.op().args
    if default is not None:
        raise NotImplementedError()

    sa_arg = t.translate(arg)
    sa_offset = t.translate(offset) if offset is not None else 1
    return sa.func.lag(sa_arg, sa_offset) 

def transaction_base(Base, schema):
    class Transaction(Base):
        __abstract__ = True
        id = sa.Column(sa.BigInteger, primary_key=True)
        native_transaction_id = sa.Column(sa.BigInteger)
        issued_at = sa.Column(sa.DateTime)
        client_addr = sa.Column(INET)

        @declared_attr
        def __table_args__(cls):
            return (
                ExcludeConstraint(
                    (cls.native_transaction_id, '='),
                    (
                        sa.func.tsrange(
                            cls.issued_at - sa.text("INTERVAL '1 hour'"),
                            cls.issued_at,
                        ),
                        '&&'
                    ),
                    name='transaction_unique_native_tx_id'
                ),
                {'schema': schema}
            )

        def __repr__(self):
            return '<{cls} id={id!r} issued_at={issued_at!r}>'.format(
                cls=self.__class__.__name__,
                id=self.id,
                issued_at=self.issued_at
            )

    return Transaction 

def _floor_divide(t, expr):
    left, right = map(t.translate, expr.op().args)
    return sa.func.floor(left / right) 

def fixed_arity(sa_func, arity):
    if isinstance(sa_func, str):
        sa_func = getattr(sa.func, sa_func)

    def formatter(t, expr):
        if arity != len(expr.op().args):
            raise com.IbisError('incorrect number of args')

        return _varargs_call(sa_func, t, expr)

    return formatter 

def _repeat(t, expr):
    arg, times = map(t.translate, expr.op().args)
    f = sa.func
    return f.replace(
        f.substr(f.quote(f.zeroblob((times + 1) / 2)), 3, times), '0', arg
    ) 

def _log(t, expr):
    arg, base = expr.op().args
    sa_arg = t.translate(arg)
    if base is None:
        return sa.func._ibis_sqlite_ln(sa_arg)
    return sa.func._ibis_sqlite_log(sa_arg, t.translate(base)) 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.any_`

    """

    return arrexpr.any(other, operator) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.all_`

    """

    return arrexpr.all(other, operator) 

def _now(t, expr):
    return sa.func.datetime('now') 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`_expression.any_`

    """

    return arrexpr.any(other, operator) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`_expression.all_`

    """

    return arrexpr.all(other, operator) 

def _get_pagination_query(session, query, pagination, api_model, model):
        if not pagination.get('sort'):
            pagination['sort'] = api_model.DEFAULT_SORT
        marker = None
        if pagination.get('marker'):
            key_attr = getattr(model, api_model.PRIMARY_KEY)
            marker_query = copy.copy(query)
            marker_query = marker_query.filter(
                key_attr == pagination['marker'])
            try:
                marker = marker_query.limit(1).one()
            except exc.NoResultFound:
                raise storage.InvalidMarker(
                    'Marker %s not found.' % pagination['marker'])
        limit = pagination.get('limit')
        # we sort by "severity" by its semantic than its alphabetical
        # order when "severity" specified in sorts.
        for sort_key, sort_dir in pagination['sort'][::-1]:
            if sort_key == 'severity':
                engine = session.connection()
                if engine.dialect.name != "mysql":
                    raise aodh.NotImplementedError
                sort_dir_func = {'asc': asc, 'desc': desc}[sort_dir]
                query = query.order_by(sort_dir_func(
                    func.field(getattr(model, sort_key), 'low',
                               'moderate', 'critical')))
                pagination['sort'].remove((sort_key, sort_dir))

        sort_keys = [s[0] for s in pagination['sort']]
        sort_dirs = [s[1] for s in pagination['sort']]
        return oslo_sql_utils.paginate_query(
            query, model, limit, sort_keys, sort_dirs=sort_dirs, marker=marker) 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.any_`

    """

    return arrexpr.any(other, operator) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.all_`

    """

    return arrexpr.all(other, operator) 

def init_sqlalchemy():
    global db
    global UserMixin
    import sqlalchemy
    import docassemble.webapp.database
    url = docassemble.webapp.database.alchemy_connection_string()
    if url.startswith('postgresql'):
        db = sqlalchemy.create_engine(url, client_encoding='utf8', pool_pre_ping=docassemble.webapp.database.pool_pre_ping)
    else:
        db = sqlalchemy.create_engine(url, pool_pre_ping=docassemble.webapp.database.pool_pre_ping)
    #meta = sqlalchemy.MetaData(bind=con, reflect=True)
    from sqlalchemy.orm import sessionmaker, relationship, backref
    Session = sessionmaker(bind=db)
    from sqlalchemy.ext.declarative import declarative_base
    db.Model = declarative_base()
    db.Column = sqlalchemy.Column
    db.Integer = sqlalchemy.Integer
    db.String = sqlalchemy.String
    db.Boolean = sqlalchemy.Boolean
    db.Text = sqlalchemy.Text
    db.DateTime = sqlalchemy.DateTime
    db.func = sqlalchemy.func
    db.relationship = relationship
    db.backref = backref
    db.ForeignKey = sqlalchemy.ForeignKey
    db.session = Session()
    UserMixin = object
    return db 

def create_app():
    app = Flask(__name__)
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    from docassemble.base.config import daconfig
    import docassemble.webapp.database
    connect_string = docassemble.webapp.database.connection_string()
    alchemy_connect_string = docassemble.webapp.database.alchemy_connection_string()
    #app.config['SQLALCHEMY_DATABASE_URI'] = alchemy_connect_string
    app.secret_key = daconfig.get('secretkey', '38ihfiFehfoU34mcq_4clirglw3g4o87')
    #app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    #db = SQLAlchemy(app)
    db = sqlalchemy.create_engine(alchemy_connect_string, pool_pre_ping=docassemble.webapp.database.pool_pre_ping)
    Base = declarative_base()
    Base.metadata.bind = db
    #app.wsgi_app = ProxyFix(app.wsgi_app)
    db.Model = Base
    db.Column = sqlalchemy.Column
    db.Integer = sqlalchemy.Integer
    db.String = sqlalchemy.String
    db.Index = sqlalchemy.Index
    db.Boolean = sqlalchemy.Boolean
    db.Text = sqlalchemy.Text
    db.DateTime = sqlalchemy.DateTime
    db.func = sqlalchemy.func
    db.relationship = relationship
    db.backref = backref
    db.ForeignKey = sqlalchemy.ForeignKey
    docassemble.webapp.db_object.db = db
    #import flask_login
    docassemble.webapp.db_object.UserMixin = object
    socketio = SocketIO(app, async_mode='eventlet', verify=False, logger=True, engineio_logger=True, cors_allowed_origins=[daconfig.get('url root', '*')])
    return app, db, socketio 

def Any(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.any` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.any_`

    """

    return arrexpr.any(other, operator) 

def All(other, arrexpr, operator=operators.eq):
    """A synonym for the :meth:`.ARRAY.Comparator.all` method.

    This method is legacy and is here for backwards-compatibility.

    .. seealso::

        :func:`.expression.all_`

    """

    return arrexpr.all(other, operator) 

def bind(self, cube):
        """ When one column needs to match, use the key. """
        if self.measure:
            table, column = self.measure.bind(cube)
        else:
            table, column = cube.fact_table, cube.fact_pk
        # apply the SQL aggregation function:
        column = getattr(func, self.function)(column)
        column = column.label(self.ref)
        column.quote = True
        return table, column