PostgreSQL and sqlalchemy things

Anh-Thi Dinh
This is a quick & dirty draft, for me only!
Get all rows when sourcetype isn’t given (by :source). Otherwise, get only rows where sourcetype is given
2FROM your_table
3WHERE (:source IS NULL OR sourcetype = :source)
Quick ref to SQLAlchemy
Nếu DELETE mà doc ko tồn tại thì vẫn ok. Khi ấy result.rowcount ra 0 (cái này là row affected)
1result = conn.execute(text("DELETE FROM document WHERE id=:id;"), {"id": d['id']})
2result.rowcount # number of rows deleted
Could not locate column in row for column 'status’
Có nghĩa là lúc SELECT .... thiếu đi “status”. Thêm nó vào là ok.
After removing a row, the foreign IDs in other places will be automatically deleted (the tables related to the ID in this table will also be deleted)
sqlalchemy.exc.ResourceClosedError: This result object is closed
You may only call first() once on a result set. after that, it's closed!
1# wrong (call .first() twice)
2for i in list(user.first().roles):
3    user.first().roles.remove(i)
5# right
6first_user = user.first()
7for i in list(first_user.roles):
8    first_user.roles.remove(i)
Convert a result to a list of dict
1result = conn.execute(text("SELECT fromdocid, text FROM table WHERE id IN (:doc0, :doc1);"), {"doc0": 18, "doc1": 33})
2# convert result to a list of dict
3result = [{"docId": row.fromdocid, "content": row.text} for row in result]
Add a new column is_faq to the document table. If the create_date is after "2024-06-04" and the text column contains "Question: <any>, Answer: <any>", set is_faq to True; otherwise, set it to False.
1UPDATE document
2SET is_faq = 
3  CASE 
4    WHEN text ~ '(?s)Question:.*?Answer:.*?' THEN TRUE
6  END
7WHERE create_time > '2024-06-04';
Only get column text if includeContent=true. If onlyFaqs=true, get all rows, otherwise, get only rows where is_faq=TRUE
2    column1,
3    column2,
4    -- Conditionally include the "text" column
5    CASE
6        WHEN :includeContent THEN text
7        ELSE NULL
8    END AS text
9FROM your_table
10WHERE (is_faq = TRUE OR NOT :onlyFaqs);