Skip to content
Advertisement

sqlalchemy concat with more than 2 elements on Oracle DB

considering the following table definition

meta = MetaData()

tgd = Table('T_GROUP_DAT', meta,
            Column('C_ID'),
            Column('CLASS_CODE'))

I create a select-statement using the sqlalchemy.sql.functions.concat with 3 statements

tgd_q1 = select([functions.concat(tgd.c.C_ID, '-', tgd.c.CLASS_CODE)])

using

tgd_q1.compile(dialect=OracleDialect(), compile_kwargs={"literal_binds": True}))

the query

SELECT concat("T_GROUP_DAT"."C_ID", "T_GROUP_DAT"."CLASS_CODE") AS concat_1
FROM "T_GROUP_DAT"

is generated. However, when I run this, the exception ORA-00909: invalid number of arguments is thrown. This is because CONCAT (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm) only allows 2 Arguments.

My workaround for now ist to use concat inside of concat, which works

tgd_q1 = select([functions.concat(functions.concat(tgd.c.C_ID,'-'),tgd.c.CLASS_CODE)])

However, this makes the resulting sql-query far less readable. Is there a way to create a query using | oparator or to create a more readable sql query for this case using sqlalchemy

Advertisement

Answer

To create a concat with more than 2 parameters + operator can be used:

tgd_q1 = select([tgd.c.C_ID + '-' + tgd.c.CLASS_CODE])

resulting in a || connected query like this

select c_id || '-' || class_code from t
2 People found this is helpful
Advertisement