Python sqlalchemy.func.length() Examples

def ts_locs_array(
    config: ColumnElement, text: ColumnElement, tsquery: ColumnElement,
) -> ColumnElement:
    options = f"HighlightAll = TRUE, StartSel = {TS_START}, StopSel = {TS_STOP}"
    delimited = func.ts_headline(config, text, tsquery, options)
    parts = func.unnest(func.string_to_array(delimited, TS_START)).alias()
    part = column(parts.name)
    part_len = func.length(part) - len(TS_STOP)
    match_pos = func.sum(part_len).over(rows=(None, -1)) + len(TS_STOP)
    match_len = func.strpos(part, TS_STOP) - 1
    return func.array(
        select([postgresql.array([match_pos, match_len])])
        .select_from(parts)
        .offset(1)
        .as_scalar(),
    )

# When you add a new operator to this, also update zerver/lib/narrow.py 

def test_varchar_raise(self):
        for type_ in (
            String,
            VARCHAR,
            String(),
            VARCHAR(),
            Unicode,
            Unicode(),
        ):
            type_ = sqltypes.to_instance(type_)
            assert_raises_message(
                exc.CompileError,
                "VARCHAR requires a length on dialect firebird",
                type_.compile,
                dialect=firebird.dialect(),
            )

            t1 = Table("sometable", MetaData(), Column("somecolumn", type_))
            assert_raises_message(
                exc.CompileError,
                r"\(in table 'sometable', column 'somecolumn'\)\: "
                r"(?:N)?VARCHAR requires a length on dialect firebird",
                schema.CreateTable(t1).compile,
                dialect=firebird.dialect(),
            ) 

def test_strlen(self):
        metadata = self.metadata

        # On FB the length() function is implemented by an external UDF,
        # strlen().  Various SA tests fail because they pass a parameter
        # to it, and that does not work (it always results the maximum
        # string length the UDF was declared to accept). This test
        # checks that at least it works ok in other cases.

        t = Table(
            "t1",
            metadata,
            Column("id", Integer, Sequence("t1idseq"), primary_key=True),
            Column("name", String(10)),
        )
        metadata.create_all()
        t.insert(values=dict(name="dante")).execute()
        t.insert(values=dict(name="alighieri")).execute()
        select(
            [func.count(t.c.id)], func.length(t.c.name) == 5
        ).execute().first()[0] == 1 

def get_chat_filters(chat_id):
    try:
        return SESSION.query(CustomFilters).filter(CustomFilters.chat_id == str(chat_id)).order_by(
            func.length(CustomFilters.keyword).desc()).order_by(CustomFilters.keyword.asc()).all()
    finally:
        SESSION.close() 

def get_chat_filters(chat_id):
    try:
        return SESSION.query(CustomFilters).filter(CustomFilters.chat_id == str(chat_id)).order_by(
            func.length(CustomFilters.keyword).desc()).order_by(CustomFilters.keyword.asc()).all()
    finally:
        SESSION.close() 

def test_insert_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        i = insert(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "inserted.myid, inserted.name VALUES "
            "(:name)",
        )
        i = insert(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description VALUES (:name)",
        )
        i = insert(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "LEN(inserted.name) AS length_1 VALUES "
            "(:name)",
        ) 

def test_function_overrides(self):
        self.assert_compile(func.current_date(), "GETDATE()")
        self.assert_compile(func.length(3), "LEN(:length_1)") 

def test_insert_returning(self):
        dialect = postgresql.dialect()
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )

        i = insert(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING mytable.myid, "
            "mytable.name",
            dialect=dialect,
        )
        i = insert(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING mytable.myid, "
            "mytable.name, mytable.description",
            dialect=dialect,
        )
        i = insert(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES "
            "(%(name)s) RETURNING length(mytable.name) "
            "AS length_1",
            dialect=dialect,
        ) 

def test_insert_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        i = insert(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (:name) "
            "RETURNING mytable.myid, mytable.name",
        )
        i = insert(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (:name) "
            "RETURNING mytable.myid, mytable.name, "
            "mytable.description",
        )
        i = insert(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) VALUES (:name) "
            "RETURNING char_length(mytable.name) AS "
            "length_1",
        ) 

def test_update_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        u = update(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name RETURNING "
            "mytable.myid, mytable.name",
        )
        u = update(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name RETURNING "
            "mytable.myid, mytable.name, "
            "mytable.description",
        )
        u = update(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name RETURNING "
            "char_length(mytable.name) AS length_1",
        ) 

def get_chat_filters(chat_id):
    try:
        return SESSION.query(CustomFilters).filter(CustomFilters.chat_id == str(chat_id)).order_by(
            func.length(CustomFilters.keyword).desc()).order_by(CustomFilters.keyword.asc()).all()
    finally:
        SESSION.close() 

def get_chat_filters(chat_id):
	try:
		return SESSION.query(CustomFilters).filter(CustomFilters.chat_id == str(chat_id)).order_by(
			func.length(CustomFilters.keyword).desc()).order_by(CustomFilters.keyword.asc()).all()
	finally:
		SESSION.close() 

def highlight_string(text: str, locs: Iterable[Tuple[int, int]]) -> str:
    highlight_start = '<span class="highlight">'
    highlight_stop = '</span>'
    pos = 0
    result = ''
    in_tag = False

    for loc in locs:
        (offset, length) = loc

        prefix_start = pos
        prefix_end = offset
        match_start = offset
        match_end = offset + length

        prefix = text[prefix_start:prefix_end]
        match = text[match_start:match_end]

        for character in (prefix + match):
            if character == '<':
                in_tag = True
            elif character == '>':
                in_tag = False
        if in_tag:
            result += prefix
            result += match
        else:
            result += prefix
            result += highlight_start
            result += match
            result += highlight_stop
        pos = match_end

    result += text[pos:]
    return result 

def test_table_is_reflected(self):
        from sqlalchemy.types import (
            Integer,
            Text,
            BLOB,
            String,
            Date,
            Time,
            DateTime,
        )

        metadata = MetaData(testing.db)
        table = Table("testtable", metadata, autoload=True)
        eq_(
            set(table.columns.keys()),
            set(
                [
                    "question",
                    "answer",
                    "remark",
                    "photo",
                    "d",
                    "t",
                    "dt",
                    "redundant",
                ]
            ),
            "Columns of reflected table didn't equal expected " "columns",
        )
        eq_(table.c.question.primary_key, True)

        # disabled per http://www.sqlalchemy.org/trac/ticket/1660
        # eq_(table.c.question.sequence.name, 'gen_testtable_id')

        assert isinstance(table.c.question.type, Integer)
        eq_(table.c.question.server_default.arg.text, "42")
        assert isinstance(table.c.answer.type, String)
        assert table.c.answer.type.length == 255
        eq_(table.c.answer.server_default.arg.text, "'no answer'")
        assert isinstance(table.c.remark.type, Text)
        eq_(table.c.remark.server_default.arg.text, "''")
        assert isinstance(table.c.photo.type, BLOB)
        assert table.c.redundant.server_default is None

        # The following assume a Dialect 3 database

        assert isinstance(table.c.d.type, Date)
        assert isinstance(table.c.t.type, Time)
        assert isinstance(table.c.dt.type, DateTime) 

def test_update_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        u = update(table1, values=dict(name="foo")).returning(
            table1.c.myid, table1.c.name
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name",
        )
        u = update(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description",
        )
        u = (
            update(table1, values=dict(name="foo"))
            .returning(table1)
            .where(table1.c.name == "bar")
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description WHERE mytable.name = "
            ":name_1",
        )
        u = update(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name)
        )
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "LEN(inserted.name) AS length_1",
        ) 

def test_assorted(self):
        table1 = table("mytable", column("myid", Integer))

        table2 = table("myothertable", column("otherid", Integer))

        # test an expression with a function
        self.assert_compile(
            func.lala(3, 4, literal("five"), table1.c.myid) * table2.c.otherid,
            "lala(:lala_1, :lala_2, :param_1, mytable.myid) * "
            "myothertable.otherid",
        )

        # test it in a SELECT
        self.assert_compile(
            select([func.count(table1.c.myid)]),
            "SELECT count(mytable.myid) AS count_1 FROM mytable",
        )

        # test a "dotted" function name
        self.assert_compile(
            select([func.foo.bar.lala(table1.c.myid)]),
            "SELECT foo.bar.lala(mytable.myid) AS lala_1 FROM mytable",
        )

        # test the bind parameter name with a "dotted" function name is
        # only the name (limits the length of the bind param name)
        self.assert_compile(
            select([func.foo.bar.lala(12)]),
            "SELECT foo.bar.lala(:lala_2) AS lala_1",
        )

        # test a dotted func off the engine itself
        self.assert_compile(func.lala.hoho(7), "lala.hoho(:hoho_1)")

        # test None becomes NULL
        self.assert_compile(
            func.my_func(1, 2, None, 3),
            "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
        )

        f1 = func.my_func(1, 2, None, 3)
        f1._generate_cache_key()

        # test pickling
        self.assert_compile(
            util.pickle.loads(util.pickle.dumps(f1)),
            "my_func(:my_func_1, :my_func_2, NULL, :my_func_3)",
        )

        # assert func raises AttributeError for __bases__ attribute, since
        # its not a class fixes pydoc
        try:
            func.__bases__
            assert False
        except AttributeError:
            assert True 

def _test_autoincrement(self, bind):
        aitable = self.tables.aitable

        ids = set()
        rs = bind.execute(aitable.insert(), int1=1)
        last = rs.inserted_primary_key[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(), str1="row 2")
        last = rs.inserted_primary_key[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(), int1=3, str1="row 3")
        last = rs.inserted_primary_key[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        rs = bind.execute(aitable.insert(values={"int1": func.length("four")}))
        last = rs.inserted_primary_key[0]
        self.assert_(last)
        self.assert_(last not in ids)
        ids.add(last)

        eq_(
            ids,
            set(
                range(
                    testing.db.dialect.default_sequence_base,
                    testing.db.dialect.default_sequence_base + 4,
                )
            ),
        )

        eq_(
            list(bind.execute(aitable.select().order_by(aitable.c.id))),
            [
                (testing.db.dialect.default_sequence_base, 1, None),
                (testing.db.dialect.default_sequence_base + 1, None, "row 2"),
                (testing.db.dialect.default_sequence_base + 2, 3, "row 3"),
                (testing.db.dialect.default_sequence_base + 3, 4, None),
            ],
        )