CODEWITHSUNDEEP
postgresqlsqlalchemy
jennykwan
jennykwan

Reputation: 2701

SQLAlchemy Core - generating PostgreSQL SUBSTRING expression?

The syntax for SUBSTRING in PostgreSQL is SUBSTRING(<text_expr> FROM <i> FOR <j>). Any idea how to make SQLAlchemy core generate that? I'm trying sqlalchemy.sql.expression.func, but that expects typically comma-separated notation. I don't see a built-in Function that addresses this. I'm not quite sure if literal or text would work. Any thoughts?

Upvotes: 3

Views: 1105

Answers (1)

thejohnbackes
thejohnbackes

Reputation: 1255

Looking through the SqlAlchemy tests, I found that sqlalchemy.sql.expression.func.substring compiles to SUBSTRING for PSQL:

    def test_substring(self):
        self.assert_compile(
            func.substring("abc", 1, 2),
            "SUBSTRING(%(substring_1)s FROM %(substring_2)s "
            "FOR %(substring_3)s)",
        )
        self.assert_compile(
            func.substring("abc", 1),
            "SUBSTRING(%(substring_1)s FROM %(substring_2)s)",
        )

func.substring(str, from, [for]) is indeed what you want. It is "comma-delineated" because that's how Python methods

If you want to generate the SQL yourself, you could do something like text("SUBSTRING('foo' FROM 1 FOR 2)"), but I don't see why you would.

Upvotes: 4

Related Questions