I'm currently trying to convert some of my Snowflake SQL code to SQLAlchemy Core python code and I can't quite seem to figure out how to convert indexing an array within SQL to SQLAlchemy Core code.
For example if I have the following SQL query, how could I convert it to SQLAlchemy Core?
SELECT
TRIM(SPLIT(CATEGORIES, ',')[0]) AS MAIN_CATEGORY,
...
FROM EXAMPLE_TABLE
So far I have tried this:
from sqlalchemy import func, select
...
select([
func.trim(
func.split(table.c.categories, ",")[0]
).label("main_category")
])
But it doesn't look like SQLAlchemy is able to convert the [0]
python part to a SQL code.
Any way around this?
CodePudding user response:
well it seems they have support for trim, and split, so you could ether try SPLIT_PART, or GET
select 'a,b,c' as a
,split(a, ',') as b
,b[0] as c
,get(b, 0) as c1
,split_part(a, ',', 0) as c2;
A | B | C | C1 | C2 |
---|---|---|---|---|
a,b,c | [ "a", "b", "c" ] | "a" | "a" | a |
Not sure if/how these map to what SQLAlchemy exposes/supports