SHARE
TWEET

Untitled

a guest Dec 16th, 2018 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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'}]
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top