SQLAlchemy Values and SQLite

⍝

Originally written back in 2022, while trying to add integrations tests against SQLite to an application that uses SQL Server in production.

It wasn’t my idea and it failed but was a good learning experience, so I am publishing this draft with its last section missing. I have since essentially stopped using SQLAlchemy constructs to build complex queries preferring to use raw SQL, as those complex queries are unlikely to be portable between DBMS¹.

With version 1.4, SQLAlchemy added a new Values type to represent the VALUES clause. This is very useful for those SQL implementation that have VALUES as first class citizen like Microsoft SQL Server.

The simplest statement we can build is values constructor in a select

from sqlalchemy import column, select, values

labels = ["col1", "col2"]
data = [(1, "a"), (2, "b"), (3, "c")]

select(
    values(column("col1"), column("col2"), name="test")
    .data([(1, "a"), (2, "b"), (3, "c")])
)

which compiles to²

SELECT test.col1, test.col2
FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS test (col1, col2)

But in SQLite, VALUES is not a full fledged table constructor, it is only part of other statements like SELECT or INSERT and does not allow aliasing with AS.

First attempt WITH CTE

My first attempt was to replace this statement with a Common Table Expression (CTE), the original ad-hoc table constructor.

WITH test (col1, col2)
AS (VALUES (1, 'a'), (2, 'b'), (3, 'c'))
SELECT test.col1, test.col2 FROM test

SQLAlchemy also offers a cte constructor.

from sqlalchemy import column, select, values

select(
    values(column("col1"), column("col2"), name="test")
    .data([(1, "a"), (2, "b"), (3, "c")])
    .cte()
)

But when I executed this, I only managed to raise the following exception.

AttributeError: 'Values' object has no attribute 'cte'

That’s because Values is a subclass of FromClause not SelectBase (or HasCTE). So we could wrap it in a select, but that takes us right back to the original select+values issue above…

Second attempt UNION ALL

So, second attempt, replace the statement with several UNION ALL in a subquery. While not as elegant, it is standard SQL and should also work with all DBMS implementations.

SELECT test.col1, test.col2
FROM (
	SELECT 1 AS col1, 'a' AS col2
	UNION ALL SELECT 2, 'b'
	UNION ALL SELECT 3, 'c'
) AS test

Which is again easy to build with the union_all constructor, the SelectBase.subquery method and two comprehensions (just one if you’re passing only one column)

from sqlalchemy import column, literal, select, union_all, values

labels = ["col1", "col2"]
data = [(1, "a"), (2, "b"), (3, "c")]

select(
    union_all(
        *(
            select(*(literal(v).label(l) for l, v in zip(labels, datum)))
            for datum in data
        )
    ).subquery("test")
)

and compiles to²

SELECT test.col1, test.col2
FROM (
	SELECT 1 AS col1, 'a' AS col2
	UNION ALL SELECT 2 AS col1, 'b' AS col2
	UNION ALL SELECT 3 AS col1, 'c' AS col2
) AS test

‼

It’s at this point our story stops, after poking around with this for not too long but still too long, and halfway through writing my own custom SQL construct, I stumbled on this comment by @zzzeek which proposes a custom SQL construct that solves this precise issue.