Guest User

Untitled

a guest
Dec 16th, 2018
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.55 KB | None | 0 0
  1. posts = db.execute(
  2. 'SELECT p.id, author_id, interlocutor_id, msg, created, u.username'
  3. ' FROM post p JOIN user u'
  4. ' ON author_id = ?'
  5. ' AND interlocutor_id = ?'
  6. ' OR interlocutor_id = ?'
  7. ' AND author_id = ?'
  8. ' ORDER BY p.created DESC',
  9. (main_id, user_id, main_id, user_id,)
  10. ).fetchall()
  11. for i in posts:
  12. pst[i['id']] = i['username']
  13.  
  14. import sqlite3
  15. import pandas as pd # pip install pandas
  16.  
  17. conn = sqlite3.connect(r'D:downloadflaskr.sqlite')
  18.  
  19. qry = """
  20. SELECT p.id, author_id, interlocutor_id, msg, created, u.username
  21. FROM post p
  22. JOIN user u
  23. ON u.id = p.author_id
  24. WHERE (p.author_id = ? AND p.interlocutor_id = ?)
  25. OR
  26. (p.interlocutor_id = ? AND p.author_id = ?)
  27. ORDER BY p.created DESC"""
  28.  
  29. data = pd.read_sql(qry, conn, params=(1,3,1,3))
  30.  
  31. In [100]: data
  32. Out[100]:
  33. id author_id interlocutor_id msg created username
  34. 0 19 1 3 Привет! 2018-12-15 15:33:53 root
  35. 1 18 1 3 Привет! 2018-12-15 15:33:48 root
  36. 2 15 3 1 ddd 2018-12-15 15:31:10 hamidmi
  37.  
  38. In [101]: data.to_dict('record')
  39. Out[101]:
  40. [{'id': 19,
  41. 'author_id': 1,
  42. 'interlocutor_id': 3,
  43. 'msg': 'Привет!',
  44. 'created': '2018-12-15 15:33:53',
  45. 'username': 'root'},
  46. {'id': 18,
  47. 'author_id': 1,
  48. 'interlocutor_id': 3,
  49. 'msg': 'Привет!',
  50. 'created': '2018-12-15 15:33:48',
  51. 'username': 'root'},
  52. {'id': 15,
  53. 'author_id': 3,
  54. 'interlocutor_id': 1,
  55. 'msg': 'ddd',
  56. 'created': '2018-12-15 15:31:10',
  57. 'username': 'hamidmi'}]
Add Comment
Please, Sign In to add comment