Return a tuple based off a subquery using MySQL and Python

Question

This works before I put the CONCAT statement in. I was hoping to return a tuple for the uploads field based off of the subquery.

def my_query():
    conn = create_connection() 
    cur = conn[0]
    cur.execute("""SELECT d.id, s.val1, d.val2, s.val3, d.val4,
                          r.val5, r.val6, r.val7,
                          CONCAT(SELECT u.id, u.file_name, u.file_path FROM related_docs u WHERE u.icd_id = d.id)
                          AS uploads
                   FROM icd_d d, icd_s s, icd_r r
                   WHERE d.id = s.icd_id
                   AND d.id = r.icd_id
                   ORDER BY id ASC
                """)
    data = cur.fetchall()    
    return data

Does the STRUCTURE GUARANTEE that the subquery returns not more than ONE record? — Akina
– Akina, Commented Jan 9, 2020 at 18:36

forpas · Accepted Answer · 2020-01-09 18:43:50Z

2

I think you want this:

(SELECT CONCAT(u.id, u.file_name, u.file_path) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

but it is better to add spaces between each value:

(SELECT CONCAT(u.id, ' ', u.file_name, ' ', u.file_path) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

This will work only if the subquery returns only 1 row.
If there is a case of multiple rows, use GROUP_CONCAT() also to get a comma separated list of each row:

(SELECT GROUP_CONCAT(CONCAT(u.id, ' ', u.file_name, ' ', u.file_path)) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

edited Jan 9, 2020 at 18:43

answered Jan 9, 2020 at 18:34

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike C. Over a year ago

I do need multiple rows

Akina Over a year ago

@MikeC. It is impossible to return "multiple rows" in one field of single record. Either concatenate everything into one long string, or JOIN instead of correlated subquery.

forpas Over a year ago

@MikeC. see in my edited answer a solution to get the results as a comma separated list.

Mike C. Over a year ago

@forpas Perfect. Thanks.

Collectives™ on Stack Overflow

Return a tuple based off a subquery using MySQL and Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related