public_IB_data_ac/easyPostgresConnection.py at master · jstr045329/public_IB_data_ac · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
from psycopg2 import *
from ibapi.common import BarData
import getpass
from credentials import credentials

def barTableName(whichTable):
    assert(whichTable=="5_sec" or whichTable=="15_min" or whichTable=="daily")
    return "bars_" + whichTable

def connect2IbData(credentialDict=None):
    """IMPORTANT: You must create credentials.py, which must contain a dict named credentials.
    You should add credentials.py to .gitignore because you don't want to push your login info
    to the repo where everybody can see it. Alternatively you can pass in another dictionary
    with the same name."""
    if credentialDict is None:
        # In this case, use default credentials:
        return connect(host=credentials["host"],
                       database=credentials["database"],
                       user=credentials["username"],
                       password=credentials["password"])
    else:
        # In this case, use credentials that were passed in:
        return connect(host=credentialDict["host"],
                           database=credentialDict["database"],
                           user=credentialDict["username"],
                           password=credentialDict["password"])


def mkBarLen(barLenStr):
    if barLenStr == "1 day":
        return 3600*7.5
    if barLenStr == "1 hour":
        return 3600
    if barLenStr == "15 min":
        return 60*15
    if barLenStr == "5 min":
        return 60*5
    if barLenStr == "1 min":
        return 60
    if barLenStr == "5 sec":
        return 5
    if barLenStr == "tick":
        return 0
    raise ValueError("Unrecognized bar length: " + str(barLenStr))

def insertOneBar(connection, bar:BarData, eTime=None, ticker=None, whichTable="bars_5_sec"):
    assert(hasattr(bar, 'epoch') or hasattr(bar, 'eTime') or eTime is not None)
    assert(hasattr(bar, 'ticker') or isinstance(ticker, str))

    oneCmd = "INSERT INTO " + whichTable + " (ticker, epoch, open, close, high, low, volume) VALUES ("

    if hasattr(bar, 'ticker'):
        oneCmd += "'" + bar.ticker.upper() + "', "
    else:
        oneCmd += "'" + ticker.upper() + "', "

    if hasattr(bar, 'epoch'):
        oneCmd += str(bar.epoch) + ', '
    elif hasattr(bar, 'eTime'):
        oneCmd += str(bar.eTime) + ', '
    else:
        oneCmd += str(eTime) + ', '

    oneCmd += str(bar.open) + ', '
    oneCmd += str(bar.close) + ', '
    oneCmd += str(bar.high) + ', '
    oneCmd += str(bar.low) + ', '
    oneCmd += str(bar.volume) + ');'
    connection.cursor().execute(oneCmd)
    connection.commit()

# def updateOneBar(connection, bar:BarData, kind, eTime=None, ticker=None):
#     """UPDATE bars SET bid_open = 21 WHERE ticker='AAPL' and epoch=12092;
#        UPDATE bars SET bid_close = 21, bid_high = 89 WHERE ticker='AAPL';"""
#     assert(hasattr(bar, 'epoch') or hasattr(bar, 'eTime') or eTime is not None)
#     assert(hasattr(bar, 'ticker') or isinstance(ticker, str))
#     assert(kind=="bid" or kind=="ask")
#
#     if hasattr(bar, 'ticker'):
#         ticker = bar.ticker.upper()
#
#     if hasattr(bar, 'epoch'):
#         epoch = bar.epoch
#     elif hasattr(bar, 'eTime'):
#         epoch = bar.eTime
#
#     if kind=="ask":
#         oneCmd = "UPDATE bars SET ask_open=" + str(bar.open) + ", ask_close=" + str(bar.close) +\
#             ", ask_high=" + str(bar.high) + ", ask_low=" + str(bar.low) + " WHERE TICKER='" + ticker + "' and " + \
#             " epoch=" + str(epoch) + ";"
#     else:
#         oneCmd = "UPDATE bars SET bid_open=" + str(bar.open) + ", bid_close=" + str(bar.close) +\
#             ", bid_high=" + str(bar.high) + ", bid_low=" + str(bar.low) + " WHERE TICKER='" + ticker + "' and " + \
#             " epoch=" + str(epoch) + ";"
#     print(oneCmd)
#     connection.cursor().execute(oneCmd)
#     connection.commit()

def easyQueryDemo():
    connection = connect2IbData()
    curs = connection.cursor()
    curs.execute("""SELECT * FROM bars;""")
    rows = curs.fetchall()
    for m in rows:
        print(m)

def queryOneTicker(ticker, minEpoch):
    connection = connect2IbData()
    curs = connection.cursor()
    curs.execute("""SELECT ticker, epoch, bid_high, bid_low, bid_close FROM bars WHERE ticker='""" + ticker.upper() + """' and epoch > """ + str(minEpoch) + ";")
    return curs.fetchall()

def easyInsertDemo():
    connection = connect2IbData()
    oneBar = BarData()
    oneBar.open = 100.15
    oneBar.close = 100.25
    oneBar.high = 100.55
    oneBar.low = 99.99
    oneBar.volume = 1000000
    setattr(oneBar, 'epoch', 12097)
    setattr(oneBar, 'ticker', 'AAPL')
    insertOneBar(connection, oneBar)

if __name__=="__main__":
    easyInsertDemo()
    # easyQueryDemo()
    queryOneTicker('AAPL', 12091)