String aggregation ordering in SQLAlchemy for both SQLite and PostgreSQL - Stack Overflow

admin管理员组

文章数量:1355559

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?

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?

• sqlalchemy

Share Improve this question Follow asked Mar 31 at 14:02 Nicolas DelvauxNicolas Delvaux 1771111 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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() 

本文标签： String aggregation ordering in SQLAlchemy for both SQLite and PostgreSQLStack Overflow