def fetch_hourly(self, page, rows, sidx, sord='asc', _search='false', searchOper=None, searchField=None, searchString=None, **kw): ''' Function called on AJAX request made by FlexGrid Fetch data from DB, return the list of rows + total + current page ''' if not in_any_group('admin','STATS'): return dict(page=0, total=0, rows=[]) try: page = int(page) rows = int(rows) offset = (page-1) * rows except: page = 1 rows = 24 offset = 0 log.info('fetch_hourly : page=%d, rows=%d, offset=%d, sidx=%s, sord=%s' % ( page, rows, offset, sidx, sord)) # Initialize data, in case no data is available for that time slice data = [{'id': x, 'cell': ['%d h 00 < %d h 00' % (x, x+1), 0, None]} for x in range(24)] # Count calls by hour if db_engine=='oracle': req = func.to_char(CDR.calldate, 'HH24') else: # PostgreSql req = func.date_trunc('hour', cast(CDR.calldate, TIME)) cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec)) if self.stats_type: # Monthly stats d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y') if db_engine=='oracle': cdrs = cdrs.filter(func.trunc(CDR.calldate, 'month') == \ func.trunc(d, 'month')) else: # PostgreSql cdrs = cdrs.filter(func.date_trunc('month', CDR.calldate) == \ func.date_trunc('month', d)) cdrs = cdrs.group_by(req) # cdrs = cdrs.order_by(func.sum(CDR.billsec)) for i, c in enumerate(cdrs): if db_engine=='oracle': j = int(c[0]) else: # PostgreSql j = c[0].seconds / 3600 data[j] = {'id': j, 'cell': ['%d h 00 < %d h 00' % (j,j+1), c[1], hms(c[2])]} return dict(page=page, total=24, rows=data[offset:offset+page*rows])
def get_series_items_df(cls, session, from_date=None, until_date=None, market_type_code=None, division_codes=None, item_freq_type_code=None, min_market_total_volume=None, min_market_pre_off_volume=None, max_mins_from_off_time=None, market_location_code=None): query = session.query(cls).join(ExchangeOddsSeries) # Event data filters if from_date: query = safe_join(query, Market) query = query.filter(Market.off_time >= from_date) if until_date: query = safe_join(query, Market) query = query.filter(Market.off_time < until_date) # Market filters if market_type_code: query = safe_join(query, Market) query = query.filter(Market.market_type_code == market_type_code) if market_location_code: query = safe_join(query, Market) query = query.filter( Market.market_location_code == market_location_code) if min_market_pre_off_volume: query = safe_join(query, Market).filter( Market.pre_off_volume >= min_market_pre_off_volume) if min_market_total_volume: query = safe_join( query, Market).filter(Market.total_volume >= min_market_total_volume) # Division filters if division_codes: query = query.join(Event).filter( Event.division_code.in_(division_codes)) # Time and frequency filters if max_mins_from_off_time: query = safe_join(query, Market) query = query.filter( func.trunc((extract('epoch', Market.off_time) - extract('epoch', cls.published_datetime)) / 60) < max_mins_from_off_time) if item_freq_type_code: query = query.filter( ExchangeOddsSeries.item_freq_type_code == item_freq_type_code) return pd.read_sql(query.statement, query.session.bind)
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql', 'maxdb') is_oracle = testing.against('oracle') # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([ func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today') ])) assert isinstance( ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('maxdb'): def2 = sa.text("curdate") elif testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table( 'default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl')) t.create()
def define_tables(cls, metadata): default_generator = cls.default_generator = {"x": 50} def mydefault(): default_generator["x"] += 1 return default_generator["x"] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text("13")])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text("12")])).scalar() use_function_defaults = testing.against("postgresql", "mssql") is_oracle = testing.against("oracle") class MyClass(object): @classmethod def gen_default(cls, ctx): return "hi" class MyType(TypeDecorator): impl = String(50) def process_bind_param(self, value, dialect): if value is not None: value = "BIND" + value return value cls.f = 6 cls.f2 = 11 with testing.db.connect() as conn: currenttime = cls.currenttime = func.current_date(type_=sa.Date) if is_oracle: ts = conn.scalar( sa.select([ func.trunc( func.current_timestamp(), sa.literal_column("'DAY'"), type_=sa.Date, ) ])) currenttime = cls.currenttime = func.trunc( currenttime, sa.literal_column("'DAY'"), type_=sa.Date) def1 = currenttime def2 = func.trunc( sa.text("current_timestamp"), sa.literal_column("'DAY'"), type_=sa.Date, ) deftype = sa.Date elif use_function_defaults: def1 = currenttime deftype = sa.Date if testing.against("mssql"): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = conn.scalar(func.current_date()) else: def1 = def2 = "3" ts = 3 deftype = Integer cls.ts = ts Table( "default_test", metadata, # python function Column("col1", Integer, primary_key=True, default=mydefault), # python literal Column( "col2", String(20), default="imthedefault", onupdate="im the update", ), # preexecute expression Column( "col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk"), ), # SQL-side default from sql expression Column("col4", deftype, server_default=def1), # SQL-side default from literal expression Column("col5", deftype, server_default=def2), # preexecute + update timestamp Column("col6", sa.Date, default=currenttime, onupdate=currenttime), Column("boolcol1", sa.Boolean, default=True), Column("boolcol2", sa.Boolean, default=False), # python function which uses ExecutionContext Column( "col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx, ), # python builtin Column( "col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today, ), # combo Column("col9", String(20), default="py", server_default="ddl"), # python method w/ context Column("col10", String(20), default=MyClass.gen_default), # fixed default w/ type that has bound processor Column("col11", MyType(), default="foo"), )
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql') is_oracle = testing.against('oracle') class MyClass(object): @classmethod def gen_default(cls, ctx): return "hi" # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')])) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table('default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl'), # python method w/ context Column('col10', String(20), default=MyClass.gen_default) ) t.create()
def csv(self, **kw): if not in_any_group('admin', 'STATS'): flash(u'Accès interdit') redirect('/') today = datetime.datetime.today() filename = 'statistiques-' + today.strftime('%Y%m%d') + '.csv' import StringIO import csv csvdata = StringIO.StringIO() writer = csv.writer(csvdata) # Global headers if self.stats_type: rt = 'quotidien ' + self.stats_type[0:2] + self.stats_type[5:] col1 = 'Jour' else: rt = 'mensuel' col1 = 'Mois' writer.writerow(['Statistiques SVI']) writer.writerow(['Date', today.strftime('%d/%m/%Y')]) writer.writerow(['Type de rapport', rt]) writer.writerow([]) cols = [col1] if self.stats_type: # Daily stats d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y') if db_engine=='oracle': req = func.trunc(CDR.calldate, 'J') else: # PostgreSql req = func.date_trunc('day', CDR.calldate) else: # Monthly stats if db_engine=='oracle': req = func.trunc(CDR.calldate, 'month') else: # PostgreSql req = func.date_trunc('month', CDR.calldate) # Order by date cdrs = self.stats_req cdrs = cdrs.order_by(req) cols.append( (u'Appels\n(nombre)').encode('utf-8') ) cols.append( (u'Appels\n(durée)').encode('utf-8') ) writer.writerow(cols) for c in cdrs.all(): row = [] if self.stats_type: row.append(c[0].strftime('%d')) else: row.append(c[0].strftime('%m/%Y')) row.append(c[1]) row.append(hms(c[2])) writer.writerow(row) rh = response.headers rh['Content-Type'] = 'text/csv; charset=utf-8' rh['Content-Disposition'] = str( (u'attachment; filename="%s"' % ( filename)).encode('utf-8') ) rh['Pragma'] = 'public' # for IE rh['Cache-control'] = 'max-age=0' #for IE return csvdata.getvalue()
def fetch(self, page, rows, sidx, sord='desc', _search='false', searchOper=None, searchField=None, searchString=None, **kw): ''' Function called on AJAX request made by FlexGrid Fetch data from DB, return the list of rows + total + current page ''' if not in_any_group('admin','STATS'): flash(u'Accès interdit !', 'error') redirect('/') try: page = int(page) rows = int(rows) offset = (page-1) * rows except: page = 1 rows = 12 offset = 0 log.info('fetch : page=%d, rows=%d, offset=%d' % (page, rows, offset)) if self.stats_type: # Daily stats d = datetime.datetime.strptime(self.stats_type, '%m/%d/%Y') if db_engine=='oracle': req = func.trunc(CDR.calldate, 'J') else: # PostgreSql req = func.date_trunc('day', CDR.calldate) cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec)) if db_engine=='oracle': cdrs = cdrs.filter(func.trunc(CDR.calldate, 'month') == \ func.trunc(d, 'month')) else: # PostgreSql cdrs = cdrs.filter(func.date_trunc('month', CDR.calldate) == \ func.date_trunc('month', d)) cdrs = cdrs.group_by(req) else: # Monthly stats if db_engine=='oracle': req = func.trunc(CDR.calldate, 'month') else: # PostgreSql req = func.date_trunc('month', CDR.calldate) cdrs = DBSession.query(req, func.count(req), func.sum(CDR.billsec)) cdrs = cdrs.group_by(req) self.stats_req = cdrs if sidx=='calls': cdrs = cdrs.order_by(getattr(func.count(req),sord)()) elif sidx=='billsec': cdrs = cdrs.order_by(getattr(func.sum(CDR.billsec),sord)()) else: cdrs = cdrs.order_by(getattr(req,sord)()) cdrs = cdrs.offset(offset).limit(rows) total = cdrs.count()/rows + 1 data = [{ 'id' : i, 'cell': cell(self.stats_type, i, c) } for i, c in enumerate(cdrs.all()) ] return dict(page=page, total=total, rows=data)
def precision_check(sess,longitude: float , latitude: float): ress = sess.query(models.Pincode.pin).filter(func.trunc(models.Pincode.longitude,1) == func.trunc(longitude,1), func.trunc(models.Pincode.latitude,1)==func.trunc(latitude,1)).first() sess.close() return ress
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {"x": 50} def mydefault(): default_generator["x"] += 1 return default_generator["x"] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text("13")])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text("12")])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against("postgresql", "mssql", "maxdb") is_oracle = testing.against("oracle") # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label("today")]) ) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against("maxdb"): def2 = sa.text("curdate") elif testing.against("mssql"): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len("abcdef") f2 = len("abcdefghijk") def1 = def2 = "3" ts = 3 deftype = Integer t = Table( "default_test1", metadata, # python function Column("col1", Integer, primary_key=True, default=mydefault), # python literal Column("col2", String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column("col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk")), # SQL-side default from sql expression Column("col4", deftype, server_default=def1), # SQL-side default from literal expression Column("col5", deftype, server_default=def2), # preexecute + update timestamp Column("col6", sa.Date, default=currenttime, onupdate=currenttime), Column("boolcol1", sa.Boolean, default=True), Column("boolcol2", sa.Boolean, default=False), # python function which uses ExecutionContext Column("col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column("col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column("col9", String(20), default="py", server_default="ddl"), ) t.create()
def age_in_minutes(self, now=datetime.utcnow()): return func.trunc( extract('epoch', now) - extract('epoch', self.creation_datetime) / 60)