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)
> On Thursday, 15 October 2020 at 23:55:53 UTC+7 Mike Bayer wrote:
>>
>>
>> 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
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/c0be052d-1c20-49d1-a73d-875b4a7afef9n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/35fe8997-a400-46cf-9dc7-b4dd86441787n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/35fe8997-a400-46cf-9dc7-b4dd86441787n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/18ad93d2-73de-4118-b9b4-43c4fc2c8100%40www.fastmail.com.
