Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- posts = db.execute(
- 'SELECT p.id, author_id, interlocutor_id, msg, created, u.username'
- ' FROM post p JOIN user u'
- ' ON author_id = ?'
- ' AND interlocutor_id = ?'
- ' OR interlocutor_id = ?'
- ' AND author_id = ?'
- ' ORDER BY p.created DESC',
- (main_id, user_id, main_id, user_id,)
- ).fetchall()
- for i in posts:
- pst[i['id']] = i['username']
- import sqlite3
- import pandas as pd # pip install pandas
- conn = sqlite3.connect(r'D:downloadflaskr.sqlite')
- qry = """
- SELECT p.id, author_id, interlocutor_id, msg, created, u.username
- FROM post p
- JOIN user u
- ON u.id = p.author_id
- WHERE (p.author_id = ? AND p.interlocutor_id = ?)
- OR
- (p.interlocutor_id = ? AND p.author_id = ?)
- ORDER BY p.created DESC"""
- data = pd.read_sql(qry, conn, params=(1,3,1,3))
- In [100]: data
- Out[100]:
- id author_id interlocutor_id msg created username
- 0 19 1 3 Привет! 2018-12-15 15:33:53 root
- 1 18 1 3 Привет! 2018-12-15 15:33:48 root
- 2 15 3 1 ddd 2018-12-15 15:31:10 hamidmi
- In [101]: data.to_dict('record')
- Out[101]:
- [{'id': 19,
- 'author_id': 1,
- 'interlocutor_id': 3,
- 'msg': 'Привет!',
- 'created': '2018-12-15 15:33:53',
- 'username': 'root'},
- {'id': 18,
- 'author_id': 1,
- 'interlocutor_id': 3,
- 'msg': 'Привет!',
- 'created': '2018-12-15 15:33:48',
- 'username': 'root'},
- {'id': 15,
- 'author_id': 3,
- 'interlocutor_id': 1,
- 'msg': 'ddd',
- 'created': '2018-12-15 15:31:10',
- 'username': 'hamidmi'}]
Add Comment
Please, Sign In to add comment