sqlalchemy.extract

Here are the examples of the python api sqlalchemy.extract taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

    def test_extract(self):
        t = table('t', column('col1'))

        for field in 'day', 'month', 'year':
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                'SELECT DATEPART(%s, t.col1) AS anon_1 FROM t' % field)

    def test_extract(self):
        from sqlalchemy import extract
        fivedaysago = datetime.datetime.now() \
            - datetime.timedelta(days=5)
        for field, exp in ('year', fivedaysago.year), \
                ('month', fivedaysago.month), ('day', fivedaysago.day):
            r = testing.db.execute(
                select([
                    extract(field, fivedaysago)])
            ).scalar()
            eq_(r, exp)

    def test_extract(self):
        t = sql.table('t', sql.column('col1'))

        for field in 'year', 'month', 'day':
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                "SELECT EXTRACT(%s FROM t.col1) AS anon_1 FROM t" % field)

        # millsecondS to millisecond
        self.assert_compile(
            select([extract('milliseconds', t.c.col1)]),
            "SELECT EXTRACT(millisecond FROM t.col1) AS anon_1 FROM t")

    @testing.fails_on('+zxjdbc',
                      "Can't infer the SQL type to use for an instance "
                      "of org.python.core.PyObjectDerived.")
    def test_extract(self):
        fivedaysago = datetime.datetime.now() \
            - datetime.timedelta(days=5)
        for field, exp in ('year', fivedaysago.year), \
                ('month', fivedaysago.month), ('day', fivedaysago.day):
            r = testing.db.execute(
                select([
                    extract(field, func.now() + datetime.timedelta(days=-5))])
            ).scalar()
            eq_(r, exp)

    def test_extract(self):
        t = sql.table('t', sql.column('col1'))

        mapping = {
            'day': 'day',
            'doy': 'dayofyear',
            'dow': 'weekday',
            'milliseconds': 'millisecond',
            'millisecond': 'millisecond',
            'year': 'year',
        }

        for field, subst in list(mapping.items()):
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                'SELECT DATEPART("%s", t.col1) AS anon_1 FROM t' % subst)

    def test_extract_bind(self):
        """Basic common denominator execution tests for extract()"""

        date = datetime.date(2010, 5, 1)

        def execute(field):
            return testing.db.execute(select([extract(field, date)])).scalar()

        assert execute('year') == 2010
        assert execute('month') == 5
        assert execute('day') == 1

        date = datetime.datetime(2010, 5, 1, 12, 11, 10)

        assert execute('year') == 2010
        assert execute('month') == 5
        assert execute('day') == 1

    def test_extract_expression(self):
        meta = MetaData(testing.db)
        table = Table('test', meta,
                      Column('dt', DateTime),
                      Column('d', Date))
        meta.create_all()
        try:
            table.insert().execute(
                {'dt': datetime.datetime(2010, 5, 1, 12, 11, 10),
                 'd': datetime.date(2010, 5, 1)})
            rs = select([extract('year', table.c.dt),
                         extract('month', table.c.d)]).execute()
            row = rs.first()
            assert row[0] == 2010
            assert row[1] == 5
            rs.close()
        finally:
            meta.drop_all()

@dispatch((std, var), sql.elements.ColumnElement)
def compute_up(t, s, **kwargs):
    measure = t.schema.measure
    is_timedelta = isinstance(getattr(measure, 'ty', measure), TimeDelta)
    if is_timedelta:
        # part 1 of 2 to work around the fact that postgres does not have
        # timedelta var or std: cast to a double which is seconds
        s = sa.extract('epoch', s)
    if t.axis != (0,):
        raise ValueError('axis not equal to 0 not defined for SQL reductions')
    funcname = 'samp' if t.unbiased else 'pop'
    full_funcname = '%s_%s' % (prefixes[type(t)], funcname)
    ret = getattr(sa.func, full_funcname)(s)
    if is_timedelta:
        # part 2 of 2 to work around the fact that postgres does not have
        # timedelta var or std: cast back from seconds by
        # multiplying by a 1 second timedelta
        ret = ret * datetime.timedelta(seconds=1)
    return ret.label(t._name)

def _extract(fmt):
    def translator(t, expr):
        arg, = expr.op().args
        sa_arg = t.translate(arg)
        return sa.extract(fmt, sa_arg)
    return translator

def get_next_official_number(year=None):
    """
    Return the next available official number

    :param int year: The year we'd like to query a number for
    """
    next_ = 1
    if year is None:
        year = datetime.date.today().year

    query = DBSESSION().query(func.max(Task.official_number))
    query = query.filter(extract('year', Task.date) == year)
    last = query.first()[0]
    if last:
        next_ = last + 1

    return next_

    def estimations(self):
        """
            Query for estimations
        """
        return self.request.context.get_estimations(valid=True).filter(
            extract('year', Estimation.date) == self.year
        )

def get_years(dbsession):
    """
        We consider that all documents should be dated after 2000
    """
    @cache_region("long_term", "taskyears")
    def years():
        """
            cached version
        """
        query = dbsession.query(extract('year', Estimation.date)).distinct()
        years = list(query)
        return sorted(years)
    return years()

    def filter_date(self, query, appstruct):
        period = appstruct.get('period', {})
        if period.get('start'):
            start = period.get('start')
            end = period.get('end')
            if end is None:
                end = datetime.date.today()
            query = query.filter(Task.date.between(start, end))
        else:
            year = appstruct.get('year')
            if year is not None:
                query = query.filter(extract('year', Estimation.date) == year)
        return query

def year_property(date_attr):
    def getter(self):
        date = getattr(self, date_attr)
        return date and date.year

    def expr(cls):
        return extract('year', getattr(cls, date_attr))

    return hybrid_property(getter, expr=expr)

    def get_history(self, year=None, month=None):
        "Sort articles by year and month."
        articles_counter = Counter()
        articles = self.read()
        if year is not None:
            articles = articles.filter(
                    sqlalchemy.extract('year', Article.date) == year)
            if month is not None:
                articles = articles.filter(
                        sqlalchemy.extract('month', Article.date) == month)
        articles = articles.order_by('date')
        for article in articles.all():
            if year is not None:
                articles_counter[article.date.month] += 1
            else:
                articles_counter[article.date.year] += 1
        return articles_counter, articles

def history(user_id, year=None, month=None):
    """
    Sort articles by year and month.
    """
    articles_counter = Counter()
    articles = controllers.ArticleController(user_id).read()
    if year is not None:
        articles = articles.filter(
                sqlalchemy.extract('year', Article.date) == year)
        if month is not None:
            articles = articles.filter(
                    sqlalchemy.extract('month', Article.date) == month)
    for article in articles.all():
        if year is not None:
            articles_counter[article.date.month] += 1
        else:
            articles_counter[article.date.year] += 1
    return articles_counter, articles

    def get_messages_by_year(self, session):
        '''SELECT m.mailing_list_url,
                  extract(year from m.first_date) as year,
                  count(distinct(lower(mp.email_address)))
             FROM messages m, messages_people mp
            WHERE m.message_ID = mp.message_ID
              AND type_of_recipient = 'From'
            GROUP BY m.mailing_list_url, year;'''

        ret = session.query(db.Messages.mailing_list_url,
                            extract('year', db.Messages.first_date),
                            func.count(db.Messages.mailing_list_url)) \
            .group_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date)) \
            .order_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date))
        return ret.all()

    def get_messages_by_year(self, session):
        '''SELECT m.mailing_list_url,
                  extract(year from m.first_date) as year,
                  count(distinct(lower(mp.email_address)))
             FROM messages m, messages_people mp
            WHERE m.message_ID = mp.message_ID
              AND type_of_recipient = 'From'
            GROUP BY m.mailing_list_url, year;'''

        ret = session.query(db.Messages.mailing_list_url,
                            extract('year', db.Messages.first_date),
                            func.count(db.Messages.mailing_list_url)) \
            .group_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date)) \
            .order_by(db.Messages.mailing_list_url,
                      extract('year', db.Messages.first_date))
        return ret.all()

    def get_history(self, year=None, month=None):
        """
        Sort articles by year and month.
        """
        articles_counter = Counter()
        articles = self.read()
        if year is not None:
            articles = articles.filter(
                    sqlalchemy.extract('year', Article.date) == year)
            if month is not None:
                articles = articles.filter(
                        sqlalchemy.extract('month', Article.date) == month)
        for article in articles.all():
            if year is not None:
                articles_counter[article.date.month] += 1
            else:
                articles_counter[article.date.year] += 1
        return articles_counter, articles

    def get_history(self, year=None, month=None):
        """
        Sort articles by year and month.
        """
        articles_counter = Counter()
        articles = self.read()
        if year is not None:
            articles = articles.filter(
                    sqlalchemy.extract('year', Article.date) == year)
            if month is not None:
                articles = articles.filter(
                        sqlalchemy.extract('month', Article.date) == month)
        for article in articles.all():
            if year is not None:
                articles_counter[article.date.month] += 1
            else:
                articles_counter[article.date.year] += 1
        return articles_counter, articles

def history(user_id, year=None, month=None):
    """
    Sort articles by year and month.
    """
    articles_counter = Counter()
    articles = controllers.ArticleController(user_id).read()
    if None != year:
        articles = articles.filter(sqlalchemy.extract('year', Article.date) == year)
        if None != month:
            articles = articles.filter(sqlalchemy.extract('month', Article.date) == month)
    for article in articles.all():
        if None != year:
            articles_counter[article.date.month] += 1
        else:
            articles_counter[article.date.year] += 1
    return articles_counter, articles

def history(user_id, year=None, month=None):
    """
    Sort articles by year and month.
    """
    articles_counter = Counter()
    articles = controllers.ArticleController(user_id).read()
    if None != year:
        articles = articles.filter(sqlalchemy.extract('year', Article.date) == year)
        if None != month:
            articles = articles.filter(sqlalchemy.extract('month', Article.date) == month)
    for article in articles.all():
        if None != year:
            articles_counter[article.date.month] += 1
        else:
            articles_counter[article.date.year] += 1
    return articles_counter, articles

    def _test(self, expr, field="all", overrides=None):
        t = self.tables.t

        if field == "all":
            fields = {"year": 2012, "month": 5, "day": 10,
                      "epoch": 1336652125.0,
                      "hour": 12, "minute": 15}
        elif field == "time":
            fields = {"hour": 12, "minute": 15, "second": 25}
        elif field == 'date':
            fields = {"year": 2012, "month": 5, "day": 10}
        elif field == 'all+tz':
            fields = {"year": 2012, "month": 5, "day": 10,
                      "epoch": 1336637725.0,
                      "hour": 8,
                      "timezone": 0
                      }
        else:
            fields = field

        if overrides:
            fields.update(overrides)

        for field in fields:
            result = self.bind.scalar(
                select([extract(field, expr)]).select_from(t))
            eq_(result, fields[field])

def _second(t, expr):
    # extracting the second gives us the fractional part as well, so smash that
    # with a cast to SMALLINT
    sa_arg, = map(t.translate, expr.op().args)
    return sa.cast(sa.extract('second', sa_arg), sa.SMALLINT)

def _millisecond(t, expr):
    # we get total number of milliseconds including seconds with extract so we
    # mod 1000
    sa_arg, = map(t.translate, expr.op().args)
    return sa.cast(sa.extract('millisecond', sa_arg), sa.SMALLINT) % 1000

def _reduce_tokens(tokens, arg):
    # current list of tokens
    curtokens = []

    # reduced list of tokens that accounts for blacklisted values
    reduced = []

    non_special_tokens = (
        frozenset(_strftime_to_postgresql_rules) - _strftime_blacklist
    )

    # TODO: how much of a hack is this?
    for token in tokens:

        # we are a non-special token %A, %d, etc.
        if token in non_special_tokens:
            curtokens.append(_strftime_to_postgresql_rules[token])

        # we have a string like DD, to escape this we
        # surround it with double quotes
        elif token in _lexicon_values:
            curtokens.append('"%s"' % token)

        # we have a token that needs special treatment
        elif token in _strftime_blacklist:
            if token == '%w':
                value = sa.extract('dow', arg)  # 0 based day of week
            elif token == '%U':
                value = sa.cast(sa.func.to_char(arg, 'WW'), sa.SMALLINT) - 1
            elif token == '%c' or token == '%x' or token == '%X':
                # re scan and tokenize this pattern
                try:
                    new_pattern = _strftime_to_postgresql_rules[token]
                except KeyError:
                    raise ValueError(
                        'locale specific date formats (%%c, %%x, %%X) are '
                        'not yet implemented for %s' % platform.system()
                    )

                new_tokens, _ = _scanner.scan(new_pattern)
                value = reduce(
                    sa.sql.ColumnElement.concat,
                    _reduce_tokens(new_tokens, arg)
                )
            elif token == '%e':
                # pad with spaces instead of zeros
                value = sa.func.replace(sa.func.to_char(arg, 'DD'), '0', ' ')

            reduced += [
                sa.func.to_char(arg, ''.join(curtokens)),
                sa.cast(value, sa.TEXT)
            ]

            # empty current token list in case there are more tokens
            del curtokens[:]

        # uninteresting text
        else:
            curtokens.append(token)
    else:
        # append result to r if we had more tokens or if we have no
        # blacklisted tokens
        if curtokens:
            reduced.append(sa.func.to_char(arg, ''.join(curtokens)))
    return reduced

def refresh_cache(arguments, env):
    logger = logging.getLogger(__name__)
    if not arguments['refresh']:
        logger.exception(u"Unknown error")

    logger.debug(u"Refreshing cache")
    session = db()
    index = 0
    types = get_value(arguments, '--type')
    if types is None:
        types = ['invoice', 'estimation', 'cancelinvoice']

    this_year = datetime.date.today().year

    for task in Task.query().filter(
        Task.type_.in_(types)
    ).filter(extract('year', Task.date) == this_year):
        try:
            cache_amounts(None, None, task)
            session.merge(task)
            index += 1
            if index % 200 == 0:
                logger.debug('flushing')
                session.flush()
        except:
            logger.exception(u"Error while caching total : {0}".format(task.id))

    def turnovers(self):
        """
            Return the realised turnovers
        """
        result = dict(year_total=0)
        for month in range(1, 13):

            invoices = self.request.context.get_invoices(valid=True).options(
                load_only('ht')
            )

            date_condition = and_(
                extract('year', Invoice.date) == self.year,
                extract('month', Invoice.date) == month,
                Invoice.financial_year == self.year,
            )
            if month != 12:
                invoices = invoices.filter(date_condition)
            else:
                # for december, we also like to have invoices edited in january
                # and reported to the previous comptability year
                reported_condition = and_(
                    Invoice.financial_year == self.year,
                    extract('year', Invoice.date) != self.year,
                )
                invoices = invoices.filter(
                    or_(date_condition, reported_condition)
                )

            invoice_sum = sum([invoice.ht for invoice in invoices])

            cinvoices = self.request.context.get_cancelinvoices(valid=True).options(
                load_only('ht')
            )

            date_condition = and_(
                extract('year', CancelInvoice.date) == self.year,
                extract('month', CancelInvoice.date) == month,
                CancelInvoice.financial_year == self.year,
            )
            if month != 12:
                cinvoices = cinvoices.filter(date_condition)
            else:
                reported_condition = and_(
                    CancelInvoice.financial_year == self.year,
                    extract('year', CancelInvoice.date) != self.year,
                )
                cinvoices = cinvoices.filter(
                    or_(date_condition, reported_condition)
                )

            cinvoice_sum = sum([cinvoice.ht for cinvoice in cinvoices])

            result[month] = invoice_sum + cinvoice_sum
            result['year_total'] += result[month]
        return result

    def get_people_by_year(self, session):
        '''SELECT m.mailing_list_url,
                  extract(year from m.first_date) as year,
                  count(distinct(lower(mp.email_address)))
             FROM messages m, messages_people mp
            WHERE m.message_ID = mp.message_ID
              AND type_of_recipient = 'From'
                    GROUP BY m.mailing_list_url, year;'''

        m = aliased(db.Messages)
        mp = aliased(db.MessagesPeople)
        ret = session.query(m.mailing_list_url,
                            extract('year', m.first_date),
                            func.count(func.distinct(
                                func.lower(mp.email_address)))) \
            .filter(m.message_id == mp.message_id) \
            .filter(mp.type_of_recipient == 'From') \
            .group_by(m.mailing_list_url,
                      extract('year', m.first_date))
        return ret.all()

    def get_people_by_year(self, session):
        '''SELECT m.mailing_list_url,
                  extract(year from m.first_date) as year,
                  count(distinct(lower(mp.email_address)))
             FROM messages m, messages_people mp
            WHERE m.message_ID = mp.message_ID
              AND type_of_recipient = 'From'
                    GROUP BY m.mailing_list_url, year;'''

        m = aliased(db.Messages)
        mp = aliased(db.MessagesPeople)
        ret = session.query(m.mailing_list_url,
                            extract('year', m.first_date),
                            func.count(func.distinct(
                                func.lower(mp.email_address)))) \
            .filter(m.message_id == mp.message_id) \
            .filter(mp.type_of_recipient == 'From') \
            .group_by(m.mailing_list_url,
                      extract('year', m.first_date))
        return ret.all()