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.
26 Examples
4
Example 1
Project: sqlalchemy Source File: test_compiler.py
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)
4
Example 2
Project: autonomie Source File: invoice.py
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_
3
Example 3
Project: sqlalchemy Source File: test_types.py
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)
3
Example 4
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")
3
Example 5
Project: sqlalchemy Source File: test_dialect.py
@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 6
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)
3
Example 7
Project: sqlalchemy Source File: test_functions.py
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
3
Example 8
Project: sqlalchemy Source File: test_functions.py
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()
3
Example 9
Project: blaze Source File: sql.py
@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 10
def _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 11
Project: autonomie Source File: commercial.py
def estimations(self):
"""
Query for estimations
"""
return self.request.context.get_estimations(valid=True).filter(
extract('year', Estimation.date) == self.year
)
3
Example 12
Project: autonomie Source File: estimation.py
def get_years(dbsession):
"""
We consider that all docuements 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
Project: autonomie Source File: estimation.py
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
3
Example 14
Project: Flexget Source File: database.py
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)
3
Example 15
Project: JARR Source File: article.py
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
3
Example 16
Project: JARR Source File: utils.py
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
3
Example 17
Project: MailingListStats Source File: report.py
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()
3
Example 18
Project: newspipe Source File: article.py
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
3
Example 19
Project: newspipe Source File: misc_utils.py
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
0
Example 20
Project: sqlalchemy Source File: test_query.py
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])
0
Example 21
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)
0
Example 22
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
0
Example 23
Project: ibis Source File: compiler.py
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
0
Example 24
Project: autonomie Source File: cache.py
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))
0
Example 25
Project: autonomie Source File: commercial.py
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
0
Example 26
Project: MailingListStats Source File: report.py
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()