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.
30 Examples
3
Example 1
View licensedef 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)
3
Example 2
View licensedef 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)
3
Example 3
View licensedef 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")
3
Example 4
View license@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)
3
Example 5
View licensedef 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)
3
Example 6
View licensedef 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
3
Example 7
View licensedef 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()
3
Example 8
View license@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)
3
Example 9
View licensedef _extract(fmt): def translator(t, expr): arg, = expr.op().args sa_arg = t.translate(arg) return sa.extract(fmt, sa_arg) return translator
3
Example 10
View licensedef 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_
3
Example 11
View licensedef estimations(self): """ Query for estimations """ return self.request.context.get_estimations(valid=True).filter( extract('year', Estimation.date) == self.year )
3
Example 12
View licensedef 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()
3
Example 13
View licensedef 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
3
Example 14
View licensedef 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)
3
Example 15
View licensedef 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
3
Example 16
View licensedef 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
3
Example 17
View licensedef 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()
3
Example 18
View licensedef 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()
3
Example 19
View licensedef 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
3
Example 20
View licensedef 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
3
Example 21
View licensedef 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
3
Example 22
View licensedef 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
0
Example 23
View licensedef _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])
0
Example 24
View licensedef _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)
0
Example 25
View licensedef _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
0
Example 26
View licensedef _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
0
Example 27
View licensedef 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))
0
Example 28
View licensedef 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
0
Example 29
View licensedef 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()
0
Example 30
View licensedef 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()