How to join two rows in sql using python-CodePudding

Table:

fk_case_id    page_number     page_content
-----------------------------------------------
     467          1             Hello
     467          2             Good morning
     468          1             No need to add

My Python code:

mycursor.execute(
        f"select page_number,page_content,fk_case_id from mt_page_file_contents ORDER BY fk_case_id")
myresult = mycursor.fetchall()
prev_case_id = ""
matchstring = ""

for x in myresult:
    page_number = x[0]
    text = x[1]
    print(text)

Is there any solution to get "Hello Good morning" by joining two rows?

CodePudding user response：

Of course, exactly how you would join any other set of strings. There's nothing special about an SQLite result set.

mycursor.execute(
        "select page_number,page_content,fk_case_id from mt_page_file_contents ORDER BY fk_case_id")
myresult = mycursor.fetchall()
prev_case_id = ""
matchstring = ""
greeting = []

for x in myresult:
    page_number = x[0]
    greeting.append( x[1] )
print(' '.join(greeting))

CodePudding user response：

You can use pandas to filter based on your query results

>>> import pandas as pd
>>> # Create your db connection, let's say it is conn
>>> df = pd.read_sql_query("select page_number,page_content,fk_case_id from mt_page_file_contents;", conn)
>>> # Now filter with fk_case_id
>>> for case_id in df.fk_case_id.unique():
        output = " ".join(df[df.fk_casr_id == case_id].page_content.values)
        print(case_id, output)

If you are looking only for specific fk_case_id then you can filter them out in the dataframe initially itself.