Python multiple database results in one update

Question

I would like to update several database results in one row.

My Code:

sql_client = "SELECT pd.prj_id, pd.dl_id, pd.name, pd.email FROM projects_doc pd JOIN reminder rm ON pd.prj_id = rm.prj_id WHERE doc_typ = 'assignment' ORDER BY pd.id ASC;"
result = cursor.execute(sql_client)
result = cursor.fetchall()

for row in result:
       sql_prj_id = row['prj_id']
       sql_dl_id = row['dl_id']
       sql_dl_name = row['name']
       sql_dl_email = row['email']

Example Output:

1, 544, TestName1, [email protected]
2, 255, TestName4, [email protected]
2, 256, TestName5, [email protected]
2, 257, TestName6, [email protected]
3, 188, TestName7, [email protected]

How can I update my table so that the following result comes to an end?

Table structure

prj_id, dl_id, dl_name, dl_email

End result (example)

1; 544; TestName1; [email protected]
2; 255, 256, 258; TestName4, TestName5, TestName6; [email protected], [email protected], [email protected]
3; 188; TestName7; [email protected]

What do you mean update? Do you want that output in Python or to overwrite existing MySQL table data? If the latter, how to handle different rows? — Parfait
– Parfait, Commented Jan 2, 2018 at 21:52

Parfait · Accepted Answer · 2018-01-02 21:49:50Z

Consider MySQL's dialect-specific aggregate function group_concat:

SELECT pd.prj_id, 
       GROUP_CONCAT(pd.dl_id) AS grp_ids, 
       GROUP_CONCAT(pd.name) AS grp_name, 
       GROUP_CONCAT(pd.email) AS grp_email 
FROM projects_doc pd 
JOIN reminder rm ON pd.prj_id = rm.prj_id 
WHERE doc_typ = 'assignment' 
GROUP BY pd.prj_id
ORDER BY pd.id ASC;

However, it is not clear how you want to run update since more rows would be in original table than above aggregate query. Consider creating a new table from above query:

CREATE TABLE new_table AS <above SELECT query>

Or better yet keep your original long format which provides more efficient storage, scalability, query maintainability, and referential integrity as nested lists in columns are usually for reporting than table structure. And simply create a view from this query. Views are stored queries for use elsewhere even in application level like Python and can even be queried themselves.

CREATE VIEW new_view AS <above SELECT query>

SELECT * FROM new_view WHERE prj_id = 1;

Collectives™ on Stack Overflow

Python multiple database results in one update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related