I noticed that a raw SQL query like SELECT string_agg(name, ';' ORDER BY name) FROM my_table
works directly for both SQLite and PostgreSQL.
I want to write the corresponding SQLAlchemy code.
For now I have this, but this is PostgreSQL only because of aggregate_order_by
:
db_session.query(
func.aggregate_strings(
MyTable.name,
aggregate_order_by(literal_column("';'"), MyTable.name),
)
)
Since the generated SQL is supposed to be the same in the end, I don't think it should be complicated to make it works with SQLite. Maybe I'm missing something?
There doesn't seem to be a equivalent to aggregate_order_by
for SQLite, but you could use a text
fragment instead.
import sqlalchemy as sa
urls = ['postgresql+psycopg2:///so', 'sqlite://']
metadata = sa.MetaData()
tbl = sa.Table(
't79546417',
metadata,
sa.Column('name', sa.String),
)
names = ['Bob', 'Eve', 'Carol', 'Alice', 'Dave']
data = [{'name': n} for n in names]
for url in urls:
engine = sa.create_engine(url, echo=True)
tbl.drop(engine, checkfirst=True)
tbl.create(engine)
with engine.connect() as conn:
conn.execute(tbl.insert(), data)
q = sa.select(
sa.func.string_agg(tbl.c.name, sa.text("""';' ORDER BY "name" """))
)
res = conn.execute(q)
for row in res:
print(row)
engine.dispose()