Python sqlalchemy.sql.sqltypes.String() Examples

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 get_patched_get_form(original_func):
    """
    Patch `get_form()` function, so `hidden_pk` keyword
    argument is always set to False.

    Columns with "PRIMARY KEY" constraints are represented
    as non-editable hidden input elements, not as editable
    forms, when argument `hidden_pk` is True.
    """
    def _is_pk_string(model):
        inspection = inspect(model)
        main_pk = inspection.primary_key[0]
        return isinstance(main_pk.type, String)

    def patched_func(model, converter, **kwargs):
        if _is_pk_string(model):
            kwargs['hidden_pk'] = False
        return original_func(model, converter, **kwargs)
    return patched_func 

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 visit_column_comment(element, compiler, **kw):
    ddl = "COMMENT ON COLUMN {table_name}.{column_name} IS {comment}"

    comment = compiler.sql_compiler.render_literal_value(
        (element.comment if element.comment is not None else ""),
        sqltypes.String(),
    )

    return ddl.format(
        table_name=element.table_name,
        column_name=element.column_name,
        comment=comment,
    ) 

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 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 

def test_get_columns(self):
        description = [
            ('datetime', Type.DATETIME, None, None, None, None, True),
            ('number', Type.NUMBER, None, None, None, None, True),
            ('boolean', Type.BOOLEAN, None, None, None, None, True),
            ('date', Type.DATE, None, None, None, None, True),
            ('timeofday', Type.TIMEOFDAY, None, None, None, None, True),
            ('string', Type.STRING, None, None, None, None, True),
        ]
        connection = Mock()
        connection.execute = Mock()
        result = Mock()
        result._cursor_description = Mock()
        result._cursor_description.return_value = description
        connection.execute.return_value = result

        dialect = GSheetsDialect()
        url = make_url('gsheets://docs.google.com/')
        dialect.create_connect_args(url)

        result = dialect.get_columns(connection, 'SOME TABLE')
        expected = [
            {
                'name': 'datetime',
                'type': sqltypes.DATETIME,
                'nullable': True,
                'default': None,
            },
            {
                'name': 'number',
                'type': sqltypes.Numeric,
                'nullable': True,
                'default': None,
            },
            {
                'name': 'boolean',
                'type': sqltypes.Boolean,
                'nullable': True,
                'default': None,
            },
            {
                'name': 'date',
                'type': sqltypes.DATE,
                'nullable': True,
                'default': None,
            },
            {
                'name': 'timeofday',
                'type': sqltypes.TIME,
                'nullable': True,
                'default': None,
            },
            {
                'name': 'string',
                'type': sqltypes.String,
                'nullable': True,
                'default': None,
            },
        ]
        self.assertEqual(result, expected)