def test_select_fq(): test_schema = Schema("test") dummy_table = Table(test_schema, "dummy") columns = [Column(dummy_table, "id"), Column(dummy_table, "name")] query = Select(columns, from_=[dummy_table]) sql = query.render() eq_(sql, "SELECT test.dummy.id, test.dummy.name FROM test.dummy")
def test_select_with_where_arg(): dummy_table = Table("dummy") columns = [Column("id"), Column("name")] query = Select(columns, from_=[dummy_table], where_=Eq(Column("id"), Argument())) sql = query.render() eq_(sql, "SELECT id, name FROM dummy WHERE id = %s")
def test_select(): dummy_table = Table("dummy") columns = [Column("id"), Column("name")] query = Select(columns, from_=[dummy_table]) sql = query.render() eq_(sql, "SELECT id, name FROM dummy")
def test_select_with_alias(): dummy_table = Table("dummy") table_alias = As(dummy_table, "d") columns = [Column(table_alias, "id"), Column(table_alias, "name")] query = Select(columns, from_=[table_alias]) sql = query.render() eq_(sql, "SELECT d.id, d.name FROM dummy AS d")
def test_select_chained(): dummy_table = Table("dummy") col_id = Column("id") col_name = Column("name") columns = [col_id, col_name] query = Select(columns).from_([dummy_table]).where_(Eq(col_name, Argument())).group_by_([col_name]) sql = query.render() eq_(sql, "SELECT id, name FROM dummy WHERE name = %s GROUP BY name")
def test_select_with_group_by(): dummy_table = Table("dummy") col_id = Column("id") col_name = Column("name") columns = [col_id, col_name] query = Select(columns, from_=[dummy_table], where_=Eq(col_name, Argument()), group_by_=[col_name]) sql = query.render() eq_(sql, "SELECT id, name FROM dummy WHERE name = %s GROUP BY name")
def test_select_with_left_join(): dummy_table_a = Table("dummy_a") col_id_a = Column(dummy_table_a, "id") col_name = Column(dummy_table_a, "name") col_ref = Column(dummy_table_a, "ref_b") dummy_table_b = Table("dummy_b") col_id_b = Column(dummy_table_b, "id") col_amount = Column(dummy_table_b, "amount") columns = [col_name, col_amount] from_part = FromItem(dummy_table_a).left_join(dummy_table_b, Eq(col_id_a, col_id_b)) query = Select(columns).from_(from_part) sql = query.render() eq_(sql, "SELECT dummy_a.name, dummy_b.amount FROM dummy_a LEFT JOIN dummy_b ON dummy_a.id = dummy_b.id")
def retrieve(cursor, tables, columns, entities, start, end, subquery_filter=None, relation_table_name=None, limit=None, entitytype=None): """ Retrieve data. :param cursor: Minerva database cursor :param columns: A list of column identifiers (possibly for different datasources) :param entities: List of entity Ids :param start: The start timestamp of the range of trend values :param end: The end timestamp of the range of trend values :param subquery_filter: optional subquery for additional filtering by JOINing on field 'id' = entity_id :param relation_table_name: optional relation table name for converting entity ids to related ones """ all_rows = [] if entities is not None and len(entities) == 0: return [] columns = map(ensure_column, columns) # group tables by partition size signature to be able to JOIN them later tables_by_partition_signature = {} for table in tables: signature = table.name.split("_")[-1] tables_by_partition_signature.setdefault(signature, []).append(table) for tables in tables_by_partition_signature.values(): params = [] if start == end and start is not None and len(tables) > 1: cols = [As(Argument(), "timestamp"), Column("dn"), As(Column("id"), "entity_id")] q = Select(cols, from_=Table("directory", "entity"), where_=Eq(Column("entitytype_id"), Value(entitytype.id))) with_query = WithQuery("t", query=q) params.append(start) base_timestamp_column = Column("t", "timestamp") base_entity_id_column = Column("t", "entity_id") from_item = FromItem(Table("t")) data_tables = tables else: with_query = None base_tbl = first(tables) base_timestamp_column = Column(base_tbl, "timestamp") base_entity_id_column = Column(base_tbl, "entity_id") from_item = FromItem(base_tbl) data_tables = tables[1:] for tbl in data_tables: timestamp_comparison = Eq(Column(tbl, "timestamp"), base_timestamp_column) entity_id_comparison = Eq(Column(tbl, "entity_id"), base_entity_id_column) join_condition = And(timestamp_comparison, entity_id_comparison) from_item = from_item.join(tbl, on=join_condition, join_type="LEFT") if subquery_filter: filter_tbl = Literal("({0}) AS filter".format(subquery_filter)) from_item = from_item.join(filter_tbl, on=Eq(Column("filter", "id"), base_entity_id_column)) if relation_table_name: relation_table = Table("relation", relation_table_name) join_condition = Eq(Column("r", "source_id"), base_entity_id_column) from_item = from_item.left_join(As(relation_table, "r"), on=join_condition) entity_id_column = Column("r", "target_id") else: entity_id_column = base_entity_id_column partition_columns = [entity_id_column, base_timestamp_column] + columns where_parts = [] if not with_query: if start == end and start is not None: condition = Eq(base_timestamp_column, Argument()) where_parts.append(condition) params.append(start) else: if not start is None: condition = Gt(base_timestamp_column, Argument()) where_parts.append(condition) params.append(start) if not end is None: condition = LtEq(base_timestamp_column, Argument()) where_parts.append(condition) params.append(end) if not entities is None: condition = Literal("{0} IN ({1:s})".format(base_entity_id_column.render(), ",".join(str(entity_id) for entity_id in entities))) where_parts.append(condition) if where_parts: where_clause = ands(where_parts) else: where_clause = None select = Select(partition_columns, with_query=with_query, from_=from_item, where_=where_clause, limit=limit) query = select.render() try: cursor.execute(query, params) except psycopg2.ProgrammingError as exc: msg = "{} in query: {}".format(exc, cursor.mogrify(query, params)) raise Exception(msg) else: all_rows.extend(cursor.fetchall()) return all_rows