Python sqlalchemy.sql.sqltypes.Integer() Examples

def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

def sql_func_astype(col, _type):
    mappings = {
            str: types.Text,
            'str': types.Text,
            int: types.Integer,
            'int': types.Integer,
            float: types.Numeric,
            'float': types.Numeric,
            bool: types.Boolean,
            'bool': types.Boolean
            }
    try:
        sa_type = mappings[_type]
    except KeyError:
        raise ValueError("sql astype currently only supports type objects: str, int, float, bool")
    return sql.cast(col, sa_type)


# Base translations =========================================================== 

def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 

def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 

def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 

def test_datatype(self, table, column):
        """Tests that database column datatype matches the one defined in the
        models.
        """
        database_column = self.find_database_column(table, column)

        if isinstance(column.type, sqltypes.String):
            expected_type = sqltypes.VARCHAR
        elif isinstance(column.type, sqltypes.Integer):
            expected_type = sqltypes.INTEGER
        elif isinstance(column.type, sqltypes.Boolean):
            expected_type = sqltypes.BOOLEAN
        elif isinstance(column.type, sqltypes.DateTime):
            expected_type = sqltypes.DATETIME

        if not isinstance(database_column['type'], expected_type):
            self.errors.append(
                DatatypeMismatch(table, database_column, expected_type,
                                 parent=self)
            ) 

def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 

def test_temporary_table(self):
        test_data = u'Hello, World!'
        expected = DataFrame({'spam': [test_data]})
        Base = declarative.declarative_base()

        class Temporary(Base):
            __tablename__ = 'temp_test'
            __table_args__ = {'prefixes': ['TEMPORARY']}
            id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
            spam = sqlalchemy.Column(sqlalchemy.Unicode(30), nullable=False)

        Session = sa_session.sessionmaker(bind=self.conn)
        session = Session()
        with session.transaction:
            conn = session.connection()
            Temporary.__table__.create(conn)
            session.add(Temporary(spam=test_data))
            session.flush()
            df = sql.read_sql_query(
                sql=sqlalchemy.select([Temporary.spam]),
                con=conn,
            )

        tm.assert_frame_equal(df, expected) 

def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

def sql_func_extract_dow_monday(col):
    # make monday = 0 rather than sunday
    monday0 = sql.cast(sql.func.extract('dow', col) + 6, types.Integer) % 7
    # cast to numeric, since that's what extract('dow') returns
    return sql.cast(monday0, types.Numeric) 

def test_double_precision(self):
        V = 1.23456789101112131415

        df = DataFrame({'f32': Series([V, ], dtype='float32'),
                        'f64': Series([V, ], dtype='float64'),
                        'f64_as_f32': Series([V, ], dtype='float64'),
                        'i32': Series([5, ], dtype='int32'),
                        'i64': Series([5, ], dtype='int64'),
                        })

        df.to_sql('test_dtypes', self.conn, index=False, if_exists='replace',
                  dtype={'f64_as_f32': sqlalchemy.Float(precision=23)})
        res = sql.read_sql_table('test_dtypes', self.conn)

        # check precision of float64
        assert (np.round(df['f64'].iloc[0], 14) ==
                np.round(res['f64'].iloc[0], 14))

        # check sql types
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        col_dict = meta.tables['test_dtypes'].columns
        assert str(col_dict['f32'].type) == str(col_dict['f64_as_f32'].type)
        assert isinstance(col_dict['f32'].type, sqltypes.Float)
        assert isinstance(col_dict['f64'].type, sqltypes.Float)
        assert isinstance(col_dict['i32'].type, sqltypes.Integer)
        assert isinstance(col_dict['i64'].type, sqltypes.BigInteger) 

def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

def test_double_precision(self):
        V = 1.23456789101112131415

        df = DataFrame({'f32': Series([V, ], dtype='float32'),
                        'f64': Series([V, ], dtype='float64'),
                        'f64_as_f32': Series([V, ], dtype='float64'),
                        'i32': Series([5, ], dtype='int32'),
                        'i64': Series([5, ], dtype='int64'),
                        })

        df.to_sql('test_dtypes', self.conn, index=False, if_exists='replace',
                  dtype={'f64_as_f32': sqlalchemy.Float(precision=23)})
        res = sql.read_sql_table('test_dtypes', self.conn)

        # check precision of float64
        assert (np.round(df['f64'].iloc[0], 14) ==
                np.round(res['f64'].iloc[0], 14))

        # check sql types
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        col_dict = meta.tables['test_dtypes'].columns
        assert str(col_dict['f32'].type) == str(col_dict['f64_as_f32'].type)
        assert isinstance(col_dict['f32'].type, sqltypes.Float)
        assert isinstance(col_dict['f64'].type, sqltypes.Float)
        assert isinstance(col_dict['i32'].type, sqltypes.Integer)
        assert isinstance(col_dict['i64'].type, sqltypes.BigInteger) 

def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

def register_model(cls, admin=None):
    """Register *cls* to be included in the API service

    :param cls: Class deriving from :class:`sandman2.models.Model`
    """
    cls.__url__ = '/{}'.format(cls.__name__.lower())
    service_class = type(
        cls.__name__ + 'Service',
        (Service,),
        {
            '__model__': cls,
        })

    # inspect primary key
    cols = list(cls().__table__.primary_key.columns)

    # composite keys not supported (yet)
    primary_key_type = 'string'
    if len(cols) == 1:
        col_type = cols[0].type
        # types defined at http://flask.pocoo.org/docs/0.10/api/#url-route-registrations
        if isinstance(col_type, sqltypes.String):
            primary_key_type = 'string'
        elif isinstance(col_type, sqltypes.Integer):
            primary_key_type = 'int'
        elif isinstance(col_type, sqltypes.Numeric):
            primary_key_type = 'float'

    # registration
    register_service(service_class, primary_key_type)
    if admin is not None:
        admin.add_view(CustomAdminView(cls, db.session)) 

def test_notna_dtype(self):
        cols = {'Bool': Series([True, None]),
                'Date': Series([datetime(2012, 5, 1), None]),
                'Int': Series([1, None], dtype='object'),
                'Float': Series([1.1, None])
                }
        df = DataFrame(cols)

        tbl = 'notna_dtype_test'
        df.to_sql(tbl, self.conn)
        returned_df = sql.read_sql_table(tbl, self.conn)  # noqa
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        if self.flavor == 'mysql':
            my_type = sqltypes.Integer
        else:
            my_type = sqltypes.Boolean

        col_dict = meta.tables[tbl].columns

        assert isinstance(col_dict['Bool'].type, my_type)
        assert isinstance(col_dict['Date'].type, sqltypes.DateTime)
        assert isinstance(col_dict['Int'].type, sqltypes.Integer)
        assert isinstance(col_dict['Float'].type, sqltypes.Float) 

def test_double_precision(self):
        V = 1.23456789101112131415

        df = DataFrame({'f32': Series([V, ], dtype='float32'),
                        'f64': Series([V, ], dtype='float64'),
                        'f64_as_f32': Series([V, ], dtype='float64'),
                        'i32': Series([5, ], dtype='int32'),
                        'i64': Series([5, ], dtype='int64'),
                        })

        df.to_sql('test_dtypes', self.conn, index=False, if_exists='replace',
                  dtype={'f64_as_f32': sqlalchemy.Float(precision=23)})
        res = sql.read_sql_table('test_dtypes', self.conn)

        # check precision of float64
        assert (np.round(df['f64'].iloc[0], 14) ==
                np.round(res['f64'].iloc[0], 14))

        # check sql types
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        col_dict = meta.tables['test_dtypes'].columns
        assert str(col_dict['f32'].type) == str(col_dict['f64_as_f32'].type)
        assert isinstance(col_dict['f32'].type, sqltypes.Float)
        assert isinstance(col_dict['f64'].type, sqltypes.Float)
        assert isinstance(col_dict['i32'].type, sqltypes.Integer)
        assert isinstance(col_dict['i64'].type, sqltypes.BigInteger) 

def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

def test_get_schema_dtypes(self):
        float_frame = DataFrame({'a': [1.1, 1.2], 'b': [2.1, 2.2]})
        dtype = sqlalchemy.Integer if self.mode == 'sqlalchemy' else 'INTEGER'
        create_sql = sql.get_schema(float_frame, 'test',
                                    con=self.conn, dtype={'b': dtype})
        assert 'CREATE' in create_sql
        assert 'INTEGER' in create_sql 

def test_double_precision(self):
        V = 1.23456789101112131415

        df = DataFrame({'f32': Series([V, ], dtype='float32'),
                        'f64': Series([V, ], dtype='float64'),
                        'f64_as_f32': Series([V, ], dtype='float64'),
                        'i32': Series([5, ], dtype='int32'),
                        'i64': Series([5, ], dtype='int64'),
                        })

        df.to_sql('test_dtypes', self.conn, index=False, if_exists='replace',
                  dtype={'f64_as_f32': sqlalchemy.Float(precision=23)})
        res = sql.read_sql_table('test_dtypes', self.conn)

        # check precision of float64
        assert (np.round(df['f64'].iloc[0], 14) ==
                np.round(res['f64'].iloc[0], 14))

        # check sql types
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        col_dict = meta.tables['test_dtypes'].columns
        assert str(col_dict['f32'].type) == str(col_dict['f64_as_f32'].type)
        assert isinstance(col_dict['f32'].type, sqltypes.Float)
        assert isinstance(col_dict['f64'].type, sqltypes.Float)
        assert isinstance(col_dict['i32'].type, sqltypes.Integer)
        assert isinstance(col_dict['i64'].type, sqltypes.BigInteger) 

def get_descendent_subjects(cls, subject_id, of_type_id, from_date, to_date, whole_time_required):
        if whole_time_required:
            datestr = "(%(ss)s.joined_at<=:from_date AND (%(ss)s.left_at IS NULL OR %(ss)s.left_at >= :to_date))"
        else:
            datestr = "((%(ss)s.joined_at<=:from_date AND (%(ss)s.left_at IS NULL OR %(ss)s.left_at >= :from_date))" \
                      "OR (%(ss)s.joined_at >= :from_date AND %(ss)s.joined_at <= :to_date)" \
                      "OR (%(ss)s.left_at >= :from_date AND %(ss)s.left_at <= :to_date))"

        sq = text("""
            WITH RECURSIVE nodes_cte(subject_id, name, part_of_id, depth, path) AS (
                SELECT g1.id, g1.name, NULL::bigint as part_of_id, 1::INT as depth, g1.id::TEXT as path
                FROM subjects as g1
                LEFT JOIN subjects_subjects ss ON ss.subject_id=g1.id
                WHERE ss.part_of_id = :subject_id AND """+(datestr % {'ss': 'ss'})+"""
            UNION ALL
                SELECT c.subject_id, g2.name, c.part_of_id, p.depth + 1 AS depth,
                    (p.path || '->' || g2.id ::TEXT)
                FROM nodes_cte AS p, subjects_subjects AS c
                JOIN subjects AS g2 ON g2.id=c.subject_id
                WHERE c.part_of_id = p.subject_id AND """+(datestr % {'ss': 'c'})+"""
            ) SELECT * FROM nodes_cte
        """).bindparams(subject_id=subject_id, from_date=from_date, to_date=to_date).columns(subject_id=Integer, name=String, part_of_id=Integer, depth=Integer, path=String).alias()

        j = t_subjects.join(sq, sq.c.subject_id == t_subjects.c.id)

        q = select([
            sq.c.path.label("subject_path"),
            sq.c.subject_id.label("subject_id"),
            sq.c.name.label("subject_name"),
            t_subjects.c.subjecttype_id.label("subjecttype_id")
        ], from_obj=j)

        if of_type_id is not None:
            q = q.where(t_subjects.c.subjecttype_id == of_type_id)

        rows = DBSession.execute(q).fetchall()
        subjects = {r["subject_id"]: r for r in rows if r["subject_id"]}
        return subjects 

def __init__(self, config):
        ApplicationSession.__init__(self, config)
        self.count = 0
        self.engine = None


        metadata = MetaData()
        self.telemetry = Table("telemetry", metadata,
            Column("id", Integer(), primary_key=True),
            Column("MSG_ID", Integer()),
            Column("V_FC", Integer()),
            Column("V_CAP", Integer()),
            Column("A_ENG", Integer()),
            Column("A_CAP", Integer()),
            Column("T_O2_In", Integer()),
            Column("T_O2_Out", Integer()),
            Column("T_FC_H2O_Out", Integer()),
            Column("Water_In", Integer()),
            Column("Water_Out", Integer()),
            Column("Master_SW", Integer()),
            Column("CAP_Down_SW", Integer()),
            Column("Drive_SW", Integer()),
            Column("FC_state", Integer()),
            Column("Mosfet_state", Integer()),
            Column("Safety_state", Integer()),
            Column("Air_Pump_load", Numeric()),
            Column("Mosfet_load", Integer()),
            Column("Water_Pump_load", Integer()),
            Column("Fan_load", Integer()),
            Column("Acc_X", Integer()),
            Column("Acc_Y", Integer()),
            Column("Acc_Z", Integer()),
            Column("AUX", Numeric()),
            Column("GPS_X", Integer()),
            Column("GPS_Y", Integer()),
            Column("GPS_Z", Integer()),
            Column("GPS_Speed", Integer()),
            Column("V_Safety", Integer()),
            Column("H2_Level", Integer()),
            Column("O2_calc", Numeric()),
            Column("lat", Numeric()),
            Column("lng", Numeric()),
            )


#        metadata = MetaData()
#        self.telemetry = Table("telemetry", metadata,
#            Column("id", Integer(), primary_key=True),
#            Column("mma_x", Integer()),
#            Column("mma_y", Integer()),
#            Column("temp", Numeric()),
#	    Column("lat", Numeric()),
#	    Column("lng", Numeric()),
#        )

    #@inlineCallbacks 

def test_double_precision(self):
        V = 1.23456789101112131415

        df = DataFrame({'f32': Series([V, ], dtype='float32'),
                        'f64': Series([V, ], dtype='float64'),
                        'f64_as_f32': Series([V, ], dtype='float64'),
                        'i32': Series([5, ], dtype='int32'),
                        'i64': Series([5, ], dtype='int64'),
                        })

        df.to_sql('test_dtypes', self.conn, index=False, if_exists='replace',
                  dtype={'f64_as_f32': sqlalchemy.Float(precision=23)})
        res = sql.read_sql_table('test_dtypes', self.conn)

        # check precision of float64
        assert (np.round(df['f64'].iloc[0], 14) ==
                np.round(res['f64'].iloc[0], 14))

        # check sql types
        meta = sqlalchemy.schema.MetaData(bind=self.conn)
        meta.reflect()
        col_dict = meta.tables['test_dtypes'].columns
        assert str(col_dict['f32'].type) == str(col_dict['f64_as_f32'].type)
        assert isinstance(col_dict['f32'].type, sqltypes.Float)
        assert isinstance(col_dict['f64'].type, sqltypes.Float)
        assert isinstance(col_dict['i32'].type, sqltypes.Integer)
        assert isinstance(col_dict['i64'].type, sqltypes.BigInteger) 

def get_ancestor_subjects(cls, subject_id, of_type_id, from_date, to_date, whole_time_required):

        #print("Getting ancestors of %s of type %s" % (subject_id, of_type_id))
        #print("From date %s, To date %s, whole_time_required: %s" % (from_date, to_date, whole_time_required))

        if whole_time_required:
            datestr = "(%(ss)s.joined_at<=:from_date AND (%(ss)s.left_at IS NULL OR %(ss)s.left_at >= :to_date))"
        else:
            datestr = "((%(ss)s.joined_at<=:from_date AND (%(ss)s.left_at IS NULL OR %(ss)s.left_at >= :from_date))" \
                      "OR (%(ss)s.joined_at >= :from_date AND %(ss)s.joined_at <= :to_date)" \
                      "OR (%(ss)s.left_at >= :from_date AND %(ss)s.left_at <= :to_date))"

        sq = text("""
            WITH RECURSIVE nodes_cte(subject_id, name, part_of_id, depth, path) AS (
                SELECT g1.id, g1.name, g1.id::bigint as part_of_id, 1::INT as depth, g1.id::TEXT as path
                FROM subjects_subjects ss
                LEFT JOIN subjects as g1 ON ss.part_of_id=g1.id
                WHERE ss.subject_id = :subject_id AND """+(datestr % {'ss': 'ss'})+"""
            UNION ALL
                SELECT g2.id, g2.name, ss2.part_of_id, p.depth + 1 AS depth,
                    (p.path || '->' || g2.id ::TEXT)
                FROM nodes_cte AS p
                LEFT JOIN subjects_subjects AS ss2 ON ss2.subject_id=p.subject_id
                LEFT JOIN subjects AS g2 ON ss2.part_of_id = g2.id
                WHERE """+(datestr % {'ss': 'ss2'})+"""
            ) SELECT * FROM nodes_cte
        """).bindparams(subject_id=subject_id, from_date=from_date, to_date=to_date).columns(subject_id=Integer, name=String, part_of_id=Integer, depth=Integer, path=String).alias()

        j = t_subjects.join(sq, sq.c.subject_id == t_subjects.c.id)

        q = select([
            sq.c.path.label("subject_path"),
            sq.c.subject_id.label("subject_id"),
            sq.c.part_of_id.label("part_of_id"),
            sq.c.name.label("subject_name"),
            t_subjects.c.subjecttype_id.label("subjecttype_id")
        ], from_obj=j)

        if of_type_id is not None:
            q = q.where(t_subjects.c.subjecttype_id == of_type_id)

        rows = DBSession.execute(q).fetchall()
        groups = {r["part_of_id"]: r for r in rows if r["part_of_id"]}
        return groups