How to make a list from a query?

Question

I run a query and want to make a list from this query. I only need those select items, which I got, but I can't get rid of "," in lst.

I tried replace but it didn't work.

query = """
select
    supplier_no_and_name,
    accepted,
    rejected,
    pending,
    tdc
from edl_current.supplier_warranty_cost_recovery_warranty_alteryx 
where claim_date > "2021-01-01"
"""


lst = query.split()
lst = lst[1-len(lst):len(lst)-13]

for i in range(len(lst)):
    lst[i].replace(',',"")
print(lst)

The output is following:

['supplier_no_and_name,', 'accepted,', 'rejected,', 'pending,', 'tdc']

If you know enough about the query to say with certainty how many words there are in it, why is this even necessary? — JonSG, Mar 16 '22 at 15:34
That output is not produced by the code in your question. Please provide a [mre]. — martineau, Mar 16 '22 at 16:55

score 1 · Accepted Answer · answered Mar 16 '22 at 15:20

1

replace method has a return value which is string meaning it doesn't update the original value itself. So, you need to assign with a new value using same variable or anything.

lst[i] = lst[i].replace(',',"")

answered Mar 16 '22 at 15:20

Python learner

1,159
1
8
20

Bhavya Lodaya · Answer 2 · 2022-03-16T15:36:17.333

Try this:-

query = """
select
    supplier_no_and_name,
    accepted,
    rejected,
    pending,
    tdc
from edl_current.supplier_warranty_cost_recovery_warranty_alteryx 
where claim_date > "2021-01-01"
"""



lst = query.split()
lst = lst[1-len(lst):len(lst)-13]

for i in range(len(lst)):
    lst[i] = lst[i].replace(',', "")

d = ' '.join(lst)
print(d)

Things you should follow next time you ask questions on stack overflow.

Add the expected output in proper format.
Show the things that you've tried in the post.
Frame your question properly and always add error message if found any.
Most importantly search the question on stack overflow before posting it 85 % of time you'll get the answers without posting.

score 1 · Answer 3 · answered Mar 16 '22 at 15:32

1

str.rstrip() and list comprehension would be simpler.

# for i in range(len(lst)):
#     lst[i]=lst[i].replace(',',"")
l = [ x.rstrip(',') for x in lst ]
lst = l[:l.index('tdc')+1]  # for making same output.

answered Mar 16 '22 at 15:32

hochae

99
4

score 1 · Answer 4 · answered Mar 16 '22 at 15:51

Your current solution is super brittle in that you need to know in advance the exact number of words in your SQL statement. I think you want to try to be a little more flexible and pick out the SELECT and FROM clauses:

query = """
select
    supplier_no_and_name,
    accepted,
    rejected,
    pending,
    tdc
from edl_current.supplier_warranty_cost_recovery_warranty_alteryx 
where claim_date > "2021-01-01"
"""

query_parts = [
    p.strip(",")
    for p
    in query.replace("\n", " ").split()
    if p
]

select_index = query_parts.index("select")
from_index = query_parts.index("from")
lst = query_parts[select_index+1 : from_index]
print(lst)

This should give you:

['supplier_no_and_name', 'accepted', 'rejected', 'pending', 'tdc']

Without being sensitive to the number of words or columns.

score 1 · Answer 5 · answered Mar 16 '22 at 17:07

Since strings are immutable, their replace() method returns a new string with the modifications as opposed to doing them "in place". This means you have to explicitly replace the values in the list.

for i, value in enumerate(lst):
    lst[i] = value.replace(',', '')

How to make a list from a query?

5 Answers5