def get_daily_ave(self):
MetaData = sql.schema.MetaData(self.Connection)
viewAveMinutes_Table = Table('viewAveMinutes', MetaData, autoload=True)
Select = sql.Select([viewAveMinutes_Table.c.Minutos])
ResultProxy = self.Connection.execute(Select)
Result_Tuple = ResultProxy.fetchone()
return Result_Tuple[0]
def __init__(self,
source,
tbl,
columns=None,
ops=None,
group_by=tuple(),
order_by=tuple(),
funcs=None,
rm_attr=('str', 'dt'),
call_sub_attr=('dt', 'str'),
dispatch_cls=None,
result_cls=sql.elements.ClauseElement):
"""Create a representation of a SQL table.
Args:
source: a sqlalchemy.Engine or sqlalchemy.Connection instance.
tbl: table of form 'schema_name.table_name', 'table_name', or sqlalchemy.Table.
columns: if specified, a listlike of column names.
Examples
--------
::
from sqlalchemy import create_engine
from siuba.data import mtcars
# create database and table
engine = create_engine("sqlite:///:memory:")
mtcars.to_sql('mtcars', engine)
tbl_mtcars = LazyTbl(engine, 'mtcars')
"""
# connection and dialect specific functions
self.source = sqlalchemy.create_engine(source) if isinstance(
source, str) else source
dialect = self.source.dialect.name
self.funcs = get_dialect_funcs(dialect) if funcs is None else funcs
self.dispatch_cls = get_sql_classes(
dialect) if dispatch_cls is None else dispatch_cls
self.result_cls = result_cls
self.tbl = self._create_table(tbl, columns, self.source)
# important states the query can be in (e.g. grouped)
self.ops = [sql.Select([self.tbl])] if ops is None else ops
self.group_by = group_by
self.order_by = order_by
# customizations to allow interop with pandas (e.g. handle dt methods)
self.rm_attr = rm_attr
self.call_sub_attr = call_sub_attr
self.result_cls = result_cls
def get_last_scrape(self):
MetaData = sql.schema.MetaData(self.Connection)
tblLog_Table = Table('tblLog', MetaData, autoload=True)
Select = sql.Select([tblLog_Table.c.TimeStamp]).order_by(
tblLog_Table.c.TimeStamp.desc()).limit(1)
ResultProxy = self.Connection.execute(Select)
Result_Tuple = ResultProxy.fetchone()
if Result_Tuple == None:
return "Never"
else:
return Result_Tuple[0].strftime("%Y-%m-%d %H:%M:%S")
def album_forecast(self):
Select = sql.Select([sql.func.count()]).select_from(
self.Table).where(self.Table.c.Year == datetime.date.today().year)
ResultProxy = self.Connection.execute(Select)
CountAlbumsCurrentYear_Int = ResultProxy.fetchone()[0]
PrimerDiaDelAnio_Date = datetime.date(year=datetime.date.today().year,
month=1,
day=1)
Hoy_Date = datetime.date.today()
YearToDate_TimeDelta = Hoy_Date - PrimerDiaDelAnio_Date
FracYearPassed_Float = YearToDate_TimeDelta.days / 365
return round(CountAlbumsCurrentYear_Int / FracYearPassed_Float, 2)
def get_days_of_playback(self):
Select = sql.Select([self.Table.c.Duration])
ResultProxy = self.Connection.execute(Select)
Durations_List = [Tuple[0] for Tuple in ResultProxy]
TimeDeltas_List = [
datetime.timedelta(hours=Time.hour,
minutes=Time.minute,
seconds=Time.second) for Time in Durations_List
]
Sum_TimeDelta = sum(TimeDeltas_List, datetime.timedelta())
TotalDays_Int = Sum_TimeDelta.days
Seconds_Float = Sum_TimeDelta.seconds / (24 * 60 * 60)
return TotalDays_Int + Seconds_Float
def get_sex_list(self):
Select = sql.Select([self.Table.c.Sex]).group_by(self.Table.c.Sex)
ResultProxy = self.Connection.execute(Select)
Sex_List = [Tuple[0] for Tuple in ResultProxy]
return Sex_List
def get_type_list(self):
Select = sql.Select([self.Table.c.Type]).group_by(self.Table.c.Type)
ResultProxy = self.Connection.execute(Select)
Types_List = [Tuple[0] for Tuple in ResultProxy]
return Types_List
def get_genre_list(self):
Select = sql.Select([self.Table.c.Genre]).group_by(self.Table.c.Genre)
ResultProxy = self.Connection.execute(Select)
Genres_List = [Tuple[0] for Tuple in ResultProxy]
return Genres_List
def count_records(self):
Select = sql.Select([sql.func.count()]).select_from(self.Table)
ResultProxy = self.Connection.execute(Select)
Result_Tuple = ResultProxy.fetchone()
return Result_Tuple[0]
