Text subquery column names in the results

[Kotofos online]

Hi,
Could you shed some light on what I might be doing incorrectly? I have this text() SELECT * query on top of a one-column sub-query and in the result, I am not getting that column name.

```
stmt = text('select "FirstName", from "Customer"')
stmt = select('*').select_from(stmt.columns().alias())
print(stmt)

SELECT *
FROM (select "FirstName",  from "Customer") AS anon_1
```

This is works, but produces incorrect column name in the output:
```
res = engine.execute(stmt)
keys = res.fetchall()[0].keys()
print(keys)

['*']
```

But when the subquery has two columns, then it works as expected:
```
stmt = text('select "FirstName", "LastName" from "Customer"')
stmt = select('*').select_from(stmt.columns().alias())
res = engine.execute(stmt)
keys = res.fetchall()[0].keys()
print(keys)

['FirstName', 'LastName']
```
So, is there a better way to wrap text query? Why column name is lost in the first case? Is it a bug?

Tested on 1.3.20 and 1.2.19

[Mike Bayer]

On Thu, Oct 15, 2020, at 2:52 AM, Kotofos online wrote:

Hi,

Could you shed some light on what I might be doing incorrectly? I have this text() SELECT * query on top of a one-column sub-query and in the result, I am not getting that column name.

```

stmt = text('select "FirstName", from "Customer"')

stmt = select('*').select_from(stmt.columns().alias())

print(stmt)

SELECT *

FROM (select "FirstName",  from "Customer") AS anon_1

```

This is works, but produces incorrect column name in the output:

```

res = engine.execute(stmt)

keys = res.fetchall()[0].keys()

print(keys)

When using select(), the names in the result rows come from those which were provided to the select() method.  they are matched to those of the actual cursor if possible but not if they don't match.

['*']

```

But when the subquery has two columns, then it works as expected:

```

stmt = text('select "FirstName", "LastName" from "Customer"')

stmt = select('*').select_from(stmt.columns().alias())

res = engine.execute(stmt)

keys = res.fetchall()[0].keys()

print(keys)

['FirstName', 'LastName']

```

So, is there a better way to wrap text query? Why column name is lost in the first case? Is it a bug?

it's basically undefined behavior you're dealing with.  you wouldn't want to pass a "*" to select().   I'm sure there's more context to what you actually need to do, but in the example above, there's no need to use a subquery, just invoke the text() construct directly.

Tested on 1.3.20 and 1.2.19

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c0be052d-1c20-49d1-a73d-875b4a7afef9n%40googlegroups.com.

[Kotofos online]

I'm working on raw SQL feature, and it has to support distinct and sort. Instead of parsing and modifying user query, I'm wrapping it as subquery and then do distinct and sort.

```

user_query = 'select "FirstName", from "Customer"'

stmt = text(user_query)

stmt = select('*').select_from(stmt.columns().alias())

stmt = stmt.distinct() # and order_by(user_columns)

print(stmt)

SELECT DISTINCT *

FROM (select "FirstName",  from "Customer") AS anon_1

```

So, any better way to implement it? I'm thinking about extracting column names from a query and using them in select(). Is it possible in sqlalchemy?

[Mike Bayer]

On Fri, Oct 16, 2020, at 2:30 AM, Kotofos online wrote:

I'm working on raw SQL feature, and it has to support distinct and sort. Instead of parsing and modifying user query, I'm wrapping it as subquery and then do distinct and sort.

```

user_query = 'select "FirstName", from "Customer"'

stmt = text(user_query)

stmt = select('*').select_from(stmt.columns().alias())

stmt = stmt.distinct() # and order_by(user_columns)

print(stmt)

SELECT DISTINCT *

FROM (select "FirstName",  from "Customer") AS anon_1

```

So, any better way to implement it? I'm thinking about extracting column names from a query and using them in select(). Is it possible in sqlalchemy?

you have to extract the column names from the text if you want to perform further manipulations with them.

from sqlalchemy import text, select, column

user_query = 'select "FirstName", from "Customer"'

stmt = text(user_query).columns(column("FirstName"))

subq = stmt.alias("subq")

stmt = select([subq])

stmt = stmt.distinct()

stmt = stmt.order_by(subq.c.FirstName)

print(stmt)

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/35fe8997-a400-46cf-9dc7-b4dd86441787n%40googlegroups.com.