Python sqlalchemy.types.Boolean() Examples

def test_implicitly_boolean(self):
        # test for expressions that the database always considers as boolean
        # even if there is no boolean datatype.
        assert not self.table1.c.myid._is_implicitly_boolean
        assert (self.table1.c.myid == 5)._is_implicitly_boolean
        assert (self.table1.c.myid == 5).self_group()._is_implicitly_boolean
        assert (self.table1.c.myid == 5).label("x")._is_implicitly_boolean
        assert not_(self.table1.c.myid == 5)._is_implicitly_boolean
        assert or_(
            self.table1.c.myid == 5, self.table1.c.myid == 7
        )._is_implicitly_boolean
        assert not column("x", Boolean)._is_implicitly_boolean
        assert not (self.table1.c.myid + 5)._is_implicitly_boolean
        assert not not_(column("x", Boolean))._is_implicitly_boolean
        assert (
            not select([self.table1.c.myid])
            .scalar_subquery()
            ._is_implicitly_boolean
        )
        assert not text("x = y")._is_implicitly_boolean
        assert not literal_column("x = y")._is_implicitly_boolean 

def fields_map(self, field_type):
        if field_type == "primary":
            return ID(stored=True, unique=True)
        type_map = {
            'date': types.Date,
            'datetime': types.DateTime,
            'boolean': types.Boolean,
            'integer': types.Integer,
            'float': types.Float
        }
        if isinstance(field_type, str):
            field_type = type_map.get(field_type, types.Text)

        if not isinstance(field_type, type):
            field_type = field_type.__class__

        if issubclass(field_type, (types.DateTime, types.Date)):
            return DATETIME(stored=True, sortable=True)
        elif issubclass(field_type, types.Integer):
            return NUMERIC(stored=True, numtype=int)
        elif issubclass(field_type, types.Float):
            return NUMERIC(stored=True, numtype=float)
        elif issubclass(field_type, types.Boolean):
            return BOOLEAN(stored=True)
        return TEXT(stored=True, analyzer=self.analyzer, sortable=False) 

def _add_annotation_docs_to_sqlite(dbcon, annotation_docs, item):
    # add full item path for convenience
    annotation_docs.loc[:, "item_name"] = item['name']

    # save tables to sqlite
    annotation_docs.to_sql(
        name='annotation_docs', con=dbcon, if_exists='append',
        dtype={
            'annotation_girder_id': String(),
            '_modelType': String(),
            '_version': Integer(),
            'itemId': String(),
            'item_name': String(),
            'created': String(),
            'creatorId': String(),
            'public': Boolean(),
            'updated': String(),
            'updatedId': String(),
            'groups': String(),
            'element_count': Integer(),
            'element_details': Integer(), },
        index=False,
    ) 

def test_five_b(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c], having=c),
            "SELECT x HAVING x = 1",
            dialect=self._dialect(False),
        ) 

def test_column_defaults_ddl(self):

        t = Table(
            "t",
            MetaData(),
            Column(
                "x",
                Boolean(create_constraint=True),
                server_default=sql.false(),
            ),
        )

        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE t (x BOOLEAN DEFAULT (0), CHECK (x IN (0, 1)))",
        )

        t = Table(
            "t",
            MetaData(),
            Column("x", String(), server_default=func.sqlite_version()),
        )
        self.assert_compile(
            CreateTable(t),
            "CREATE TABLE t (x VARCHAR DEFAULT (sqlite_version()))",
        )

        t = Table(
            "t",
            MetaData(),
            Column("x", Integer(), server_default=func.abs(-5) + 17),
        )
        self.assert_compile(
            CreateTable(t), "CREATE TABLE t (x INTEGER DEFAULT (abs(-5) + 17))"
        ) 

def test_one(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(c),
            "SELECT x WHERE x",
            dialect=self._dialect(True),
        ) 

def test_two_a(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(c),
            "SELECT x WHERE x = 1",
            dialect=self._dialect(False),
        ) 

def test_two_b(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c], whereclause=c),
            "SELECT x WHERE x = 1",
            dialect=self._dialect(False),
        ) 

def test_three_a(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~c),
            "SELECT x WHERE x = 0",
            dialect=self._dialect(False),
        ) 

def test_three_a_double(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~~c),
            "SELECT x WHERE x = 1",
            dialect=self._dialect(False),
        ) 

def test_four(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~c),
            "SELECT x WHERE NOT x",
            dialect=self._dialect(True),
        ) 

def test_four_double(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).where(~~c),
            "SELECT x WHERE x",
            dialect=self._dialect(True),
        ) 

def test_five_a(self):
        c = column("x", Boolean)
        self.assert_compile(
            select([c]).having(c),
            "SELECT x HAVING x = 1",
            dialect=self._dialect(False),
        ) 

def test_boolean_default(self):
        t = Table(
            "t",
            self.metadata,
            Column("x", Boolean, server_default=sql.false()),
        )
        t.create(testing.db)
        with testing.db.connect() as conn:
            conn.execute(t.insert())
            conn.execute(t.insert().values(x=True))
            eq_(
                conn.execute(t.select().order_by(t.c.x)).fetchall(),
                [(False,), (True,)],
            ) 

def test_ten(self):
        c = column("x", Boolean)
        self.assert_compile(c == 1, "x = :x_1", dialect=self._dialect(False)) 

def test_twelve(self):
        c = column("x", Boolean)
        # I don't have a solution for this one yet,
        # other than adding some heavy-handed conditionals
        # into compiler
        self.assert_compile(
            c.is_(true()), "x IS 1", dialect=self._dialect(False)
        ) 

def test_is_true_literal(self):
        c = column("x", Boolean)
        self.assert_compile(c.is_(True), "x IS true") 

def test_is_false_literal(self):
        c = column("x", Boolean)
        self.assert_compile(c.is_(False), "x IS false") 

def _raises(self, expr):
        assert_raises_message(
            TypeError,
            "Boolean value of this clause is not defined",
            bool,
            expr,
        ) 

def __init__(self):
        self._types = {
            # Django internal type => SQLAlchemy type
            'ArrayField': SA_ARRAY,
            'AutoField': sa_types.Integer,
            'BigAutoField': sa_types.BigInteger,
            'BigIntegerField': sa_types.BigInteger,
            'BooleanField': sa_types.Boolean,
            'CharField': sa_types.String,
            'DateField': sa_types.Date,
            'DateTimeField': sa_types.DateTime,
            'DecimalField': sa_types.Numeric,
            'DurationField': sa_types.Interval,
            'FileField': sa_types.String,
            'FilePathField': sa_types.String,
            'FloatField': sa_types.Float,
            'GenericIPAddressField': sa_types.String,
            'IntegerField': sa_types.Integer,
            'JSONField': SA_JSONB,
            'NullBooleanField': sa_types.Boolean,
            'PointField': Geometry,
            'PositiveIntegerField': sa_types.Integer,
            'PositiveSmallIntegerField': sa_types.SmallInteger,
            'SlugField': sa_types.String,
            'SmallIntegerField': sa_types.SmallInteger,
            'TextField': sa_types.Text,
            'TimeField': sa_types.Time,
            'UUIDField': SA_UUID,
            # TODO: Add missing GIS fields
        } 

def get_type(data_type):
    type_map = {
        "bytes": types.LargeBinary,
        "boolean": types.Boolean,
        "date": types.Date,
        "datetime": types.DateTime,
        "double": types.Numeric,
        "text": types.String,
        "keyword": types.String,
        "integer": types.Integer,
        "half_float": types.Float,
        "geo_point": types.String,
        # TODO get a solution for nested type
        "nested": types.String,
        # TODO get a solution for object
        "object": types.BLOB,
        "long": types.BigInteger,
        "float": types.Float,
        "ip": types.String,
    }
    type_ = type_map.get(data_type)
    if not type_:
        logger.warning(f"Unknown type found {data_type} reverting to string")
        type_ = types.String
    return type_ 

def test_default_reflection(self):

        # (ask_for, roundtripped_as_if_different)

        specs = [
            (String(3), '"foo"'),
            (sqltypes.NUMERIC(10, 2), "100.50"),
            (Integer, "5"),
            (Boolean, "False"),
        ]
        columns = [
            Column("c%i" % (i + 1), t[0], server_default=text(t[1]))
            for (i, t) in enumerate(specs)
        ]
        db = testing.db
        m = MetaData(db)
        Table("t_defaults", m, *columns)
        try:
            m.create_all()
            m2 = MetaData(db)
            rt = Table("t_defaults", m2, autoload=True)
            expected = [c[1] for c in specs]
            for i, reflected in enumerate(rt.c):
                eq_(str(reflected.server_default.arg), expected[i])
        finally:
            m.drop_all() 

def test_reflect_select(table, table_using_test_dataset):
    for table in [table, table_using_test_dataset]:
        assert len(table.c) == 18

        assert isinstance(table.c.integer, Column)
        assert isinstance(table.c.integer.type, types.Integer)
        assert isinstance(table.c.timestamp.type, types.TIMESTAMP)
        assert isinstance(table.c.string.type, types.String)
        assert isinstance(table.c.float.type, types.Float)
        assert isinstance(table.c.boolean.type, types.Boolean)
        assert isinstance(table.c.date.type, types.DATE)
        assert isinstance(table.c.datetime.type, types.DATETIME)
        assert isinstance(table.c.time.type, types.TIME)
        assert isinstance(table.c.bytes.type, types.BINARY)
        assert isinstance(table.c['record.age'].type, types.Integer)
        assert isinstance(table.c['record.name'].type, types.String)
        assert isinstance(table.c['nested_record.record.age'].type, types.Integer)
        assert isinstance(table.c['nested_record.record.name'].type, types.String)
        assert isinstance(table.c.array.type, types.ARRAY)

        rows = table.select().execute().fetchall()
        assert len(rows) == 1000 

def _compare_server_default(bind, meta_col, insp_def, meta_def):
        if isinstance(meta_col.type, sqlalchemy.Boolean):
            if meta_def is None or insp_def is None:
                return meta_def != insp_def
            insp_def = insp_def.strip("'")
            return not (
                isinstance(meta_def.arg, expr.True_) and insp_def == "1" or
                isinstance(meta_def.arg, expr.False_) and insp_def == "0"
            ) 

def compare_type(self, ctxt, insp_col, meta_col, insp_type, meta_type):
        """Return True if types are different, False if not.

        Return None to allow the default implementation to compare these types.

        :param ctxt: alembic MigrationContext instance
        :param insp_col: reflected column
        :param meta_col: column from model
        :param insp_type: reflected column type
        :param meta_type: column type from model

        """

        # some backends (e.g. mysql) don't provide native boolean type
        BOOLEAN_METADATA = (types.BOOLEAN, types.Boolean)
        BOOLEAN_SQL = BOOLEAN_METADATA + (types.INTEGER, types.Integer)

        if issubclass(type(meta_type), BOOLEAN_METADATA):
            return not issubclass(type(insp_type), BOOLEAN_SQL)

        # Alembic <=0.8.4 do not contain logic of comparing Variant type with
        # others.
        if isinstance(meta_type, types.Variant):
            orig_type = meta_col.type
            impl_type = meta_type.load_dialect_impl(ctxt.dialect)
            meta_col.type = impl_type
            try:
                return self.compare_type(ctxt, insp_col, meta_col, insp_type,
                                         impl_type)
            finally:
                meta_col.type = orig_type

        return ctxt.impl.compare_type(insp_col, meta_col) 

def add_false_positive(context, issue):
    """Add a finding into the database as a new finding

    :param context: The Behave context
    :param response: An issue data structure (see steps.py)
    """
    dbconn = open_database(context)
    if dbconn is None:
        # There is no false positive db in use, and we cannot store the data,
        # so we will assert a failure.
        assert False, "Issues were found in scan, but no false positive database is in use."

    # Add the finding into the database

    db_insert = context.headlessscanner_issues.insert().values(
        new_issue=True,  # Boolean
        # The result from Burp Extender does not include a timestamp,
        # so we add the current time
        timestamp=datetime.datetime.utcnow(),  # DateTime
        test_runner_host=socket.gethostbyname(socket.getfqdn()),  # Text
        scenario_id=issue['scenario_id'],  # Text
        url=issue['url'],  # Text
        severity=issue['severity'],  # Text
        issuetype=issue['issuetype'],  # Text
        issuename=issue['issuename'],  # Text
        issuedetail=issue['issuedetail'],  # Text
        confidence=issue['confidence'],  # Text
        host=issue['host'],  # Text
        port=issue['port'],  # Text
        protocol=issue['protocol'],  # Text
        messages=json.dumps(issue['messages']))  # Blob

    dbconn.execute(db_insert)
    dbconn.close() 

def fields_map(self, field_type):
        if field_type == "primary":
            return {'type': 'keyword'}

        type_map = {
            'date': types.Date,
            'datetime': types.DateTime,
            'boolean': types.Boolean,
            'integer': types.Integer,
            'float': types.Float,
            'binary': types.Binary
        }
        if isinstance(field_type, str):
            field_type = type_map.get(field_type, types.Text)

        if not isinstance(field_type, type):
            field_type = field_type.__class__

        if issubclass(field_type, (types.DateTime, types.Date)):
            return {'type': 'date'}
        elif issubclass(field_type, types.Integer):
            return {'type': 'long'}
        elif issubclass(field_type, types.Float):
            return {'type': 'float'}
        elif issubclass(field_type, types.Boolean):
            return {'type': 'boolean'}
        elif issubclass(field_type, types.Binary):
            return {'type': 'binary'}
        return {'type': 'string'}


# https://medium.com/@federicopanini/elasticsearch-6-0-removal-of-mapping-types-526a67ff772 

def get_columns(self, connection, table_name, schema=None, **kw):
        # Extend types supported by PrestoDialect as defined in PyHive
        type_map = {
            'bigint': sql_types.BigInteger,
            'integer': sql_types.Integer,
            'boolean': sql_types.Boolean,
            'double': sql_types.Float,
            'varchar': sql_types.String,
            'timestamp': sql_types.TIMESTAMP,
            'date': sql_types.DATE,
            'array<bigint>': sql_types.ARRAY(sql_types.Integer),
            'array<varchar>': sql_types.ARRAY(sql_types.String)
        }

        rows = self._get_table_columns(connection, table_name, schema)
        result = []
        for row in rows:
            try:
                coltype = type_map[row.Type]
            except KeyError:
                logger.warn("Did not recognize type '%s' of column '%s'" % (row.Type, row.Column))
                coltype = sql_types.NullType
            result.append({
                'name': row.Column,
                'type': coltype,
                # newer Presto no longer includes this column
                'nullable': getattr(row, 'Null', True),
                'default': None,
            })
        return result 

def test_should_boolean_convert_boolean():
    assert get_field(types.Boolean()).type == graphene.Boolean 

def _add_folder_to_sqlite(dbcon, folder_info):
    # modify folder info to prep for appending to sqlite table
    folder_info_dtypes = {
        '_accessLevel': Integer(),
        '_id': String(),
        '_modelType': String(),
        'baseParentId': String(),
        'baseParentType': String(),
        'created': String(),
        'creatorId': String(),
        'description': String(),
        'name': String(),
        'parentCollection': String(),
        'parentId': String(),
        'public': Boolean(),
        'size': Integer(),
        'updated': String(),
        'folder_path': String(),
    }

    # in case anything is not in the schema, drop it
    folder_info = {
        k: v for k, v in folder_info.items()
        if k in folder_info_dtypes.keys()}

    # convert to df and add to items table
    folder_info_df = DataFrame.from_dict(folder_info, orient='index').T
    folder_info_df.to_sql(
        name='folders', con=dbcon, if_exists='append',
        dtype=folder_info_dtypes, index=False)