sqlalchemy: Incorrect code for sa.values on SQLite and MariaDB

but I dont see a way to select named columns from it

Apart from a UNION statement, you can also use a CTE, and include the column names in the CTE name.

The example from the report:

myvalues = sa.values(sa.column('x'), name='myvalues').data([(7,)])
stmt = sa.select(myvalues)

can be rendered as

with myvalues(x) as (values (7)) select * from myvalues;

Demo in the sqlite3 cli:

sqlite> .headers on
sqlite> .mode columns
sqlite> with myvalues(x) as (values (7)) select * from myvalues;
x
----------
7

You’d need Values to inherit from HasCTE to support .cte(), and an update to the SQLite compiler to use the Values object as a CTE with the right name and column names, of course.

oh sorry! I was working on a recipe now. We probably should include this in the docs, or in the examples, or something, and maybe even have it in the test suite for now seeing that two dfiferent datbases use the same workaround. The script below runs for all three targeted DBs:

import sqlalchemy as sa
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import literal
from sqlalchemy.sql import roles
from sqlalchemy.sql import select
from sqlalchemy.sql import union_all
from sqlalchemy.sql import Values


class SubValues(Values, roles.CompoundElementRole):
    """a Values element that can be added to a union_all()."""


@compiles(Values, "sqlite")
@compiles(Values, "mariadb")
@compiles(Values, "mysql")  # for mariadb connections w/ "mysql://"
def _render_values_w_union(element, compiler, **kw):
    all_data = [elem for chunk in element._data for elem in chunk]

    remaining_data = all_data[1:]
    stmt = select(
        *[
            literal(val).label(col.name)
            for col, val in zip(element.columns, all_data[0])
        ]
    )
    if remaining_data:
        stmt = union_all(
            stmt, SubValues(*element.columns).data(remaining_data)
        )

    stmt = stmt.subquery(element.name)

    return compiler.process(stmt, **kw)


@compiles(SubValues, "sqlite")
@compiles(SubValues, "mariadb")
@compiles(SubValues, "mysql")
def _render_subvalues_w_union(element, compiler, **kw):
    # omit rendering parenthesis, columns, "AS name", etc.
    kw.pop("asfrom", None)
    return compiler.visit_values(element, **kw)


for engine in [
    sa.create_engine("postgresql://scott:tiger@localhost/test", echo=True),
    sa.create_engine("sqlite+pysqlite:///:memory:", echo=True),
    sa.create_engine("mariadb://scott:tiger@localhost/test", echo=True),
]:

    with engine.connect() as conn:

        for values in [
            sa.values(sa.column("x"), name="myvalues").data([(7,)]),
            sa.values(sa.column("x"), name="myvalues").data([(7,), (8,)]),
            sa.values(sa.column("x"), sa.column("y"), name="myvalues").data(
                [(7, 10), (8, 12), (14, 15)]
            ),
        ]:
            print(conn.execute(select(values)).all())

Thanks for the comments! Values can be used without typing select on both sqlite and mariadb:

sqlite> values (2,4), (5,1);
2|4
5|1

I don’t know if any column names are assigned on sqlite. On MariaDB the column names appear to be the same as the values in the first row, but I don’t know if that is documented anywhere:

MariaDB [(none)]> select x, y from (values ('x','y'), (1,2), (3,8)) as anon limit 3 offset 1;
+---+---+
| x | y |
+---+---+
| 1 | 2 |
| 3 | 8 |
+---+---+

That is what I call a hack! 😃