def prep_311_data(file): catalog = intake_civis.open_redshift_catalog() expr = catalog.public.import311.to_ibis() recent_srs = expr[(expr.createddate > (ibis.now() - ibis.interval(months=6))) & (expr.requesttype != "Homeless Encampment")] df = recent_srs.execute() df.to_csv(file, index=False)
def test_expr_list_no_table_refs(self): exlist = ibis.api.expr_list([ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c')]) result = to_sql(exlist) expected = """\ SELECT 1 AS `a`, now() AS `b`, ln(2) AS `c`""" assert result == expected
def test_timestamp_scalar_in_filter(alltypes, translate): table = alltypes expr = (table.filter([ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.week(3)), table.timestamp_col < (ibis.now() + ibis.day(10)) ]).count()) expr.execute()
def test_timestamp_scalar_in_filter(alltypes, translate): table = alltypes expr = table.filter([ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.interval(weeks=3)), table.timestamp_col < (ibis.now() + ibis.interval(days=10)), ]).count() expr.execute()
def test_execute_exprs_no_table_ref(con, expr, expected): result = con.execute(expr) assert result == expected # ExprList exlist = ibis.api.expr_list([L(1).name('a'), ibis.now().name('b'), L(2).log().name('c')]) con.execute(exlist)
def test_now(backend, con): expr = ibis.now() result = con.execute(expr) pandas_now = pd.Timestamp('now') assert isinstance(result, pd.Timestamp) # this could fail if we're testing in different timezones and we're testing # on Dec 31st assert result.year == pandas_now.year
def test_timestamp_scalar_in_filter(self): # #310 table = self.alltypes expr = (table.filter([table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.month(3)), table.timestamp_col < (ibis.now() + ibis.day(10)) ]) .count()) expr.execute()
def test_expr_list_no_table_refs(self): exlist = ibis.api.expr_list([ ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c') ]) result = to_sql(exlist) expected = """\ SELECT 1 AS `a`, now() AS `b`, ln(2) AS `c`""" assert result == expected
def test_execute_exprs_no_table_ref(self): cases = [(ibis.literal(1) + ibis.literal(2), 3)] for expr, expected in cases: result = self.con.execute(expr) assert result == expected # ExprList exlist = ibis.api.expr_list([ibis.literal(1).name("a"), ibis.now().name("b"), ibis.literal(2).log().name("c")]) self.con.execute(exlist)
def test_expr_list_no_table_refs(): exlist = ibis.api.expr_list([ ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c') ]) result = ibis.clickhouse.compile(exlist) expected = """\ SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`""" assert result == expected
def test_timestamp_scalar_in_filter(alltypes): # #310 table = alltypes expr = table.filter([ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.interval(months=3)), table.timestamp_col < (ibis.now() + ibis.interval(days=10)), ]).count() expr.execute()
def test_timestamp_scalar_in_filter(alltypes, translate): table = alltypes expr = table.filter( [ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.interval(weeks=3)), table.timestamp_col < (ibis.now() + ibis.interval(days=10)), ] ).count() expr.execute()
def test_scalar_exprs_no_table_refs(con): expr1 = ibis.now() expected1 = "SELECT now() AS `tmp`" expr2 = ibis.literal(1) + ibis.literal(2) expected2 = "SELECT 1 + 2 AS `tmp`" cases = [(expr1, expected1), (expr2, expected2)] for expr, expected in cases: result = Compiler.to_sql(expr) assert result == expected
def test_expr_list_no_table_refs(): exlist = ibis.api.expr_list( [ ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c'), ] ) result = ibis.clickhouse.compile(exlist) expected = """\ SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`""" assert result == expected
def test_decimal_timestamp_builtins(self): table = self.con.table('tpch_lineitem') dc = table.l_quantity ts = table.l_receiptdate.cast('timestamp') exprs = [ dc % 10, dc + 5, dc + dc, dc / 2, dc * 2, dc ** 2, dc.cast('double'), api.where(table.l_discount > 0, dc * table.l_discount, api.NA), dc.fillna(0), ts < (ibis.now() + ibis.month(3)), ts < (ibis.timestamp('2005-01-01') + ibis.month(3)), # hashing dc.hash(), ts.hash(), # truncate ts.truncate('y'), ts.truncate('q'), ts.truncate('month'), ts.truncate('d'), ts.truncate('w'), ts.truncate('h'), ts.truncate('minute'), ] timestamp_fields = ['year', 'month', 'day', 'hour', 'minute', 'second', 'millisecond', 'microsecond', 'week'] for field in timestamp_fields: if hasattr(ts, field): exprs.append(getattr(ts, field)()) offset = getattr(ibis, field)(2) exprs.append(ts + offset) exprs.append(ts - offset) proj_exprs = [expr.name('e%d' % i) for i, expr in enumerate(exprs)] projection = table[proj_exprs].limit(10) projection.execute()
def test_execute_exprs_no_table_ref(con): cases = [(L(1) + L(2), 3)] for expr, expected in cases: result = con.execute(expr) assert result == expected # ExprList exlist = ibis.api.expr_list( [L(1).name('a'), ibis.now().name('b'), L(2).log().name('c')] ) con.execute(exlist)
def test_execute_exprs_no_table_ref(self): cases = [(L(1) + L(2), 3)] for expr, expected in cases: result = self.con.execute(expr) assert result == expected # ExprList exlist = ibis.api.expr_list( [L(1).name('a'), ibis.now().name('b'), L(2).log().name('c')]) self.con.execute(exlist)
def test_now_from_projection(backend, con, alltypes, df): n = 5 expr = alltypes[[ibis.now().name('ts')]].limit(n) result = expr.execute() ts = result.ts assert isinstance(result, pd.DataFrame) assert isinstance(ts, pd.Series) assert issubclass(ts.dtype.type, np.datetime64) assert len(result) == n assert ts.nunique() == 1 now = pd.Timestamp('now') year_expected = pd.Series([now.year] * n, name='ts') tm.assert_series_equal(ts.dt.year, year_expected)
def test_where_analyze_scalar_op(functional_alltypes): # root cause of #310 table = functional_alltypes expr = table.filter([ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.interval(months=3)), table.timestamp_col < (ibis.now() + ibis.interval(days=10)), ]).count() result = Compiler.to_sql(expr) expected = """\ SELECT count(*) AS `count` FROM functional_alltypes WHERE (`timestamp_col` < date_add(cast({} as timestamp), INTERVAL 3 MONTH)) AND (`timestamp_col` < date_add(cast(now() as timestamp), INTERVAL 10 DAY))""" # noqa: E501 assert result == expected.format("'2010-01-01 00:00:00'")
def test_scalar_exprs_no_table_refs(self): expr1 = ibis.now() expected1 = """\ SELECT now() AS `tmp`""" expr2 = ibis.literal(1) + ibis.literal(2) expected2 = """\ SELECT 1 + 2 AS `tmp`""" cases = [ (expr1, expected1), (expr2, expected2) ] for expr, expected in cases: result = to_sql(expr) assert result == expected
def test_where_analyze_scalar_op(self): # root cause of #310 table = self.con.table('functional_alltypes') expr = (table.filter([table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.month(3)), table.timestamp_col < (ibis.now() + ibis.day(10))]) .count()) result = to_sql(expr) expected = """\ SELECT count(*) AS `tmp` FROM functional_alltypes WHERE `timestamp_col` < months_add('2010-01-01 00:00:00', 3) AND `timestamp_col` < days_add(now(), 10)""" assert result == expected
def test_where_analyze_scalar_op(self): # root cause of #310 table = self.con.table('functional_alltypes') expr = (table.filter([ table.timestamp_col < (ibis.timestamp('2010-01-01') + ibis.month(3)), table.timestamp_col < (ibis.now() + ibis.day(10)) ]).count()) result = to_sql(expr) expected = """\ SELECT count(*) AS `tmp` FROM functional_alltypes WHERE `timestamp_col` < months_add('2010-01-01 00:00:00', 3) AND `timestamp_col` < days_add(now(), 10)""" assert result == expected
def test_decimal_timestamp_builtins(self): table = self.con.table("tpch_lineitem") dc = table.l_quantity ts = table.l_receiptdate.cast("timestamp") exprs = [ dc % 10, dc + 5, dc + dc, dc / 2, dc * 2, dc ** 2, dc.cast("double"), api.where(table.l_discount > 0, dc * table.l_discount, api.NA), dc.fillna(0), ts < (ibis.now() + ibis.month(3)), ts < (ibis.timestamp("2005-01-01") + ibis.month(3)), # hashing dc.hash(), ts.hash(), # truncate ts.truncate("y"), ts.truncate("q"), ts.truncate("month"), ts.truncate("d"), ts.truncate("w"), ts.truncate("h"), ts.truncate("minute"), ] timestamp_fields = ["year", "month", "day", "hour", "minute", "second", "millisecond", "microsecond", "week"] for field in timestamp_fields: if hasattr(ts, field): exprs.append(getattr(ts, field)()) offset = getattr(ibis, field)(2) exprs.append(ts + offset) exprs.append(ts - offset) proj_exprs = [expr.name("e%d" % i) for i, expr in enumerate(exprs)] projection = table[proj_exprs].limit(10) projection.execute()
def test_timestamp_functions(self): from datetime import datetime v = L("2015-09-01 14:48:05.359").cast("timestamp") cases = [ (v.strftime("%Y%m%d"), "20150901"), (v.year(), 2015), (v.month(), 9), (v.day(), 1), (v.hour(), 14), (v.minute(), 48), (v.second(), 5), (v.millisecond(), 359), # there could be pathological failure at midnight somewhere, but # that's okay (ibis.now().strftime("%Y%m%d %H"), datetime.utcnow().strftime("%Y%m%d %H")), ] self._check_e2e_cases(cases)
def test_timestamp_functions(self): from datetime import datetime v = L('2015-09-01 14:48:05.359').cast('timestamp') cases = [ (v.strftime('%Y%m%d'), '20150901'), (v.year(), 2015), (v.month(), 9), (v.day(), 1), (v.hour(), 14), (v.minute(), 48), (v.second(), 5), (v.millisecond(), 359), # there could be pathological failure at midnight somewhere, but # that's okay (ibis.now().strftime('%Y%m%d %H'), datetime.utcnow().strftime('%Y%m%d %H')) ] self._check_e2e_cases(cases)
def test_now(): expr = ibis.now() result = cs_compile.compile(expr) expected = "SELECT CURRENT_TIMESTAMP() AS `tmp`" assert result == expected
def test_add_column_depricated(table): with pytest.deprecated_call(): table.add_column(ibis.now().cast('date'), name='date')
def test_add_column_proxies_to_mutate(table): result = table.add_column(ibis.now().cast('date'), name='date') expected = table.mutate(date=ibis.now().cast('date')) assert_equal(result, expected)
def now() -> float: """Returns the timestamp for the current time.""" return ibis.now()
def test_now(con): expr = ibis.now().strftime('%Y%m%d %H') result = con.execute(expr) expected = datetime.datetime.utcnow().strftime('%Y%m%d %H') assert result == expected
def test_timestamp_now(con, translate): expr = ibis.now() # now = datetime.now().strftime('%Y-%m-%d %H:%M:%S') assert translate(expr) == 'now()'
def test_now(): expr = ibis.now() result = ibis.bigquery.compile(expr) expected = 'SELECT CURRENT_TIMESTAMP() AS `tmp`' assert result == expected
def test_timestamp_now(self): cases = [ (ibis.now(), 'now()') ] self._check_expr_cases(cases)
def test_timestamp_now(): expr = ibis.now() result = translate(expr) assert result == "now()"
[alltypes.count().name('count')])) expr2 = expr.string_col.cast('double') query = ibis.clickhouse.compile(expr2) expected = """SELECT CAST(`string_col` AS Float64) AS `tmp` FROM ( SELECT `string_col`, count(*) AS `count` FROM {0}.`functional_alltypes` GROUP BY `string_col` ) t0""" assert query == expected.format(db.name) @pytest.mark.parametrize( ('expr', 'expected'), [(ibis.now(), 'SELECT now() AS `tmp`'), (ibis.literal(1) + ibis.literal(2), 'SELECT 1 + 2 AS `tmp`')]) def test_scalar_exprs_no_table_refs(expr, expected): assert ibis.clickhouse.compile(expr) == expected def test_expr_list_no_table_refs(): exlist = ibis.api.expr_list([ ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c') ]) result = ibis.clickhouse.compile(exlist) expected = """\ SELECT 1 AS `a`, now() AS `b`, log(2) AS `c`""" assert result == expected
expr2 = expr.string_col.cast('double') query = ibis.clickhouse.compile(expr2) expected = """SELECT CAST(`string_col` AS Float64) AS `tmp` FROM ( SELECT `string_col`, count(*) AS `count` FROM {0}.`functional_alltypes` GROUP BY `string_col` ) t0""" assert query == expected.format(db.name) @pytest.mark.parametrize( ('expr', 'expected'), [ (ibis.now(), 'SELECT now() AS `tmp`'), (ibis.literal(1) + ibis.literal(2), 'SELECT 1 + 2 AS `tmp`'), ], ) def test_scalar_exprs_no_table_refs(expr, expected): assert ibis.clickhouse.compile(expr) == expected def test_expr_list_no_table_refs(): exlist = ibis.api.expr_list( [ ibis.literal(1).name('a'), ibis.now().name('b'), ibis.literal(2).log().name('c'), ] )
def test_timestamp_now(translate): expr = ibis.now() assert translate(expr) == 'now()'