Learn practical skills, build real-world projects, and advance your career
from sqlalchemy import BigInteger, Column, DateTime, JSON, String, Text, inspect
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()
metadata = Base.metadata


class Recording(Base):
    __tablename__ = 'recordings'

    id = Column(BigInteger, primary_key=True)
    orderbook = Column(JSON)
    pair = Column(Text, nullable=False)
    provider = Column(String(15), nullable=False)
    timestamp = Column(DateTime, nullable=False)
    trade = Column(JSON)
    type = Column(Text)


def to_json(table):
    """

    :param table:
    :return:
    """
    return {c.key: getattr(table, c.key)
            for c in inspect(table).mapper.column_attrs}



# Usage example:
# ==============

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

base_url = 'postgresql://postgres:postgres@localhost/MarketRecordings'

# Create DB session
recording_session_maker = sessionmaker(bind=create_engine(base_url))
session = recording_session_maker()
# Work on single item
item = session.query(Recording).first()
print(item.id)
print(item.orderbook)
print(item.pair)
# Run items query
items = session\
    .query(Recording)\
    .filter(Recording.orderbook is not None)\
    .all()

for item in items[:10]:
    print(to_json(item))

1 None ETH/BTC {'id': 1, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 15, 383000), 'trade': {'id': '725304192', 'price': 0.020038, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:04:38.542', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'} {'id': 2, 'orderbook': {'asks': [{'orderType': 'ASK', 'limitPrice': 0.02005, 'currencyPair': 'ETH/BTC', 'originalAmount': 68.29405}, {'orderType': 'ASK', 'limitPrice': 0.02006, 'currencyPair': 'ETH/BTC', 'originalAmount': 143.750681}, {'orderType': 'ASK', 'limitPrice': 0.02007, 'currencyPair': 'ETH/BTC', 'originalAmount': 138.330561}, {'orderType': 'ASK', 'limitPrice': 0.02008, 'currencyPair': 'ETH/BTC', 'originalAmount': 37.042165}, {'orderType': 'ASK', 'limitPrice': 0.02009, 'currencyPair': 'ETH/BTC', 'originalAmount': 97.407627}, {'orderType': 'ASK', 'limitPrice': 0.0201, 'currencyPair': 'ETH/BTC', 'originalAmount': 64.084664}, {'orderType': 'ASK', 'limitPrice': 0.02011, 'currencyPair': 'ETH/BTC', 'originalAmount': 126.9843}, {'orderType': 'ASK', 'limitPrice': 0.02012, 'currencyPair': 'ETH/BTC', 'originalAmount': 32.115924}, {'orderType': 'ASK', 'limitPrice': 0.02013, 'currencyPair': 'ETH/BTC', 'originalAmount': 434.657033}, {'orderType': 'ASK', 'limitPrice': 0.02014, 'currencyPair': 'ETH/BTC', 'originalAmount': 8.164313}], 'bids': [{'orderType': 'BID', 'limitPrice': 0.02004, 'currencyPair': 'ETH/BTC', 'originalAmount': 76.856977}, {'orderType': 'BID', 'limitPrice': 0.02003, 'currencyPair': 'ETH/BTC', 'originalAmount': 162.526092}, {'orderType': 'BID', 'limitPrice': 0.02002, 'currencyPair': 'ETH/BTC', 'originalAmount': 174.11889}, {'orderType': 'BID', 'limitPrice': 0.02001, 'currencyPair': 'ETH/BTC', 'originalAmount': 29.117935}, {'orderType': 'BID', 'limitPrice': 0.02, 'currencyPair': 'ETH/BTC', 'originalAmount': 55.294143}, {'orderType': 'BID', 'limitPrice': 0.01999, 'currencyPair': 'ETH/BTC', 'originalAmount': 247.681617}, {'orderType': 'BID', 'limitPrice': 0.01998, 'currencyPair': 'ETH/BTC', 'originalAmount': 20.004021}, {'orderType': 'BID', 'limitPrice': 0.01997, 'currencyPair': 'ETH/BTC', 'originalAmount': 431.931293}, {'orderType': 'BID', 'limitPrice': 0.01996, 'currencyPair': 'ETH/BTC', 'originalAmount': 25.806343}, {'orderType': 'BID', 'limitPrice': 0.01995, 'currencyPair': 'ETH/BTC', 'originalAmount': 16.997689}]}, 'pair': 'ETH/BTC', 'provider': 'Okex', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 15, 619000), 'trade': None, 'type': 'Snapshot'} {'id': 3, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 20, 990000), 'trade': {'id': '725304233', 'price': 0.020038, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:04:45.507', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'} {'id': 4, 'orderbook': {'asks': [{'id': '', 'orderType': 'ASK', 'limitPrice': 0.02006989, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.35700958}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.0200699, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.63041553}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.02006998, 'currencyPair': 'ETH/BTC', 'originalAmount': 49.0}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.02007, 'currencyPair': 'ETH/BTC', 'originalAmount': 34.25}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.02007937, 'currencyPair': 'ETH/BTC', 'originalAmount': 26.40819659}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.0200893, 'currencyPair': 'ETH/BTC', 'originalAmount': 49.0}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.02008939, 'currencyPair': 'ETH/BTC', 'originalAmount': 26.42614206}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.02009774, 'currencyPair': 'ETH/BTC', 'originalAmount': 7.02}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.0200994, 'currencyPair': 'ETH/BTC', 'originalAmount': 26.42797005}, {'id': '', 'orderType': 'ASK', 'limitPrice': 0.020108, 'currencyPair': 'ETH/BTC', 'originalAmount': 44.0}], 'bids': [{'id': '', 'orderType': 'BID', 'limitPrice': 0.0200243, 'currencyPair': 'ETH/BTC', 'originalAmount': 14.0}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02002429, 'currencyPair': 'ETH/BTC', 'originalAmount': 15.0}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.0200227, 'currencyPair': 'ETH/BTC', 'originalAmount': 45.0}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02002, 'currencyPair': 'ETH/BTC', 'originalAmount': 26.4232}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000929, 'currencyPair': 'ETH/BTC', 'originalAmount': 2.61065478}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000928, 'currencyPair': 'ETH/BTC', 'originalAmount': 43.96}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000926, 'currencyPair': 'ETH/BTC', 'originalAmount': 53.01434348}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000892, 'currencyPair': 'ETH/BTC', 'originalAmount': 69.3}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000851, 'currencyPair': 'ETH/BTC', 'originalAmount': 7.01}, {'id': '', 'orderType': 'BID', 'limitPrice': 0.02000402, 'currencyPair': 'ETH/BTC', 'originalAmount': 49.0}], 'timeStamp': '2019-12-08 19:18:15.000'}, 'pair': 'ETH/BTC', 'provider': 'Bitstamp', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 15, 326000), 'trade': None, 'type': 'Snapshot'} {'id': 5, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 21, 22000), 'trade': {'id': '725304319', 'price': 0.020037, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:04:59.437', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'} {'id': 7, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 21, 39000), 'trade': {'id': '725304549', 'price': 0.020038, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:05:31.525', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'} {'id': 9, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Huobi', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 15, 575000), 'trade': {'id': '10146475446359211679109', 'price': 0.02005, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:15:26.622', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.4372}, 'type': 'Trade'} {'id': 8, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 21, 46000), 'trade': {'id': '725304572', 'price': 0.020038, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:05:34.655', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'} {'id': 6, 'orderbook': {'asks': [{'orderType': 'ASK', 'limitPrice': 0.02005, 'currencyPair': 'ETH/BTC', 'originalAmount': 4.0}, {'orderType': 'ASK', 'limitPrice': 0.020051, 'currencyPair': 'ETH/BTC', 'originalAmount': 17.0}, {'orderType': 'ASK', 'limitPrice': 0.020052, 'currencyPair': 'ETH/BTC', 'originalAmount': 4.5}, {'orderType': 'ASK', 'limitPrice': 0.020054, 'currencyPair': 'ETH/BTC', 'originalAmount': 10.0}, {'orderType': 'ASK', 'limitPrice': 0.020055, 'currencyPair': 'ETH/BTC', 'originalAmount': 9.005}, {'orderType': 'ASK', 'limitPrice': 0.020056, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.01}, {'orderType': 'ASK', 'limitPrice': 0.020057, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, {'orderType': 'ASK', 'limitPrice': 0.020058, 'currencyPair': 'ETH/BTC', 'originalAmount': 1.5015}, {'orderType': 'ASK', 'limitPrice': 0.020059, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.01}, {'orderType': 'ASK', 'limitPrice': 0.020061, 'currencyPair': 'ETH/BTC', 'originalAmount': 0.005}], 'bids': [{'orderType': 'BID', 'limitPrice': 0.020047, 'currencyPair': 'ETH/BTC', 'originalAmount': 4.5}, {'orderType': 'BID', 'limitPrice': 0.020045, 'currencyPair': 'ETH/BTC', 'originalAmount': 10.0083}, {'orderType': 'BID', 'limitPrice': 0.020044, 'currencyPair': 'ETH/BTC', 'originalAmount': 10.4964}, {'orderType': 'BID', 'limitPrice': 0.02004, 'currencyPair': 'ETH/BTC', 'originalAmount': 7.9848}, {'orderType': 'BID', 'limitPrice': 0.020039, 'currencyPair': 'ETH/BTC', 'originalAmount': 3.3486}, {'orderType': 'BID', 'limitPrice': 0.020038, 'currencyPair': 'ETH/BTC', 'originalAmount': 6.2718}, {'orderType': 'BID', 'limitPrice': 0.020037, 'currencyPair': 'ETH/BTC', 'originalAmount': 12.473}, {'orderType': 'BID', 'limitPrice': 0.020034, 'currencyPair': 'ETH/BTC', 'originalAmount': 19.9532}, {'orderType': 'BID', 'limitPrice': 0.020032, 'currencyPair': 'ETH/BTC', 'originalAmount': 12.4706}, {'orderType': 'BID', 'limitPrice': 0.02003, 'currencyPair': 'ETH/BTC', 'originalAmount': 27.4833}]}, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 15, 376000), 'trade': None, 'type': 'Snapshot'} {'id': 11, 'orderbook': None, 'pair': 'ETH/BTC', 'provider': 'Hitbtc', 'timestamp': datetime.datetime(2019, 12, 8, 19, 18, 21, 56000), 'trade': {'id': '725304626', 'price': 0.020038, 'orderType': 'ASK', 'timestamp': '2019-12-08 19:05:42.011', 'currencyPair': 'ETH/BTC', 'originalAmount': 0.0001}, 'type': 'Trade'}
import jovian
jovian.commit()
[jovian] Saving notebook..