-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbhandler.py
146 lines (120 loc) · 6.56 KB
/
dbhandler.py
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
"""Handler class for PostgreSQL database access.
Default DB Config file is ./config/db.ini
"""
import psycopg2
import psycopg2.extras
from configparser import SafeConfigParser
# Config file to use
configfile = "./config/db.ini"
class DbHandler:
def __init__(self):
super()
def config(self, filename=configfile):
"""Load the PostgreSQL configuration file."""
section = "postgresql"
parser = SafeConfigParser()
parser.read(filename)
db = {}
if parser.has_section(section):
params = parser.items(section)
for param in params:
db[param[0]] = param[1]
else:
raise Exception(f"Section {section} not found in the {filename} file")
return db
def create_connection(self):
"""Create a database connection to a PostgreSQL database."""
conn = None
try:
params = self.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT version()")
db_version = cur.fetchone()[0].split(",")[0]
print(f"Connected to {db_version}")
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
raise Exception(error)
return conn
def execute(self, conn, sql_statement):
"""Execute a given SQL statement using the given connection."""
result = None
try:
c = conn.cursor()
c.execute(sql_statement)
result = c.fetchall()
c.close()
except Exception as e:
print(e)
finally:
return result
def execute_self_contained(self, sql_statement):
"""Create a single-use connection to execute the given SQL statement."""
conn = self.create_connection()
result = None
try:
cursor = conn.cursor()
cursor.execute(sql_statement)
result = cursor.fetchall()
except Exception as e:
print(e)
finally:
cursor.close()
conn.commit()
return result
def delete_quote(self, conn, symbol):
result = None
try:
c = conn.cursor()
sql = """DELETE FROM quote WHERE symbol=%s;"""
c.execute(sql, (symbol,))
result = c.fetchone()
c.close()
except (Exception, psycopg2.Error) as e:
print(e)
finally:
conn.commit()
return result
def insert_quote(self, conn, quote_info, timestamp):
"""Insert a TMX quote object in the db with all financial information."""
result = None
try:
c = conn.cursor()
sql = """INSERT INTO quote (symbol, name, price, priceChange, percentChange, exchangeName, exShortName, exchangeCode, marketPlace,
sector, industry, volume, openPrice, dayHigh, dayLow, MarketCap, MarketCapAllClasses, peRatio, prevClose, dividendFrequency,
dividendYield, dividendAmount, dividendCurrency, beta, eps, exDividendDate, shortDescription, longDescription, website, email,
phoneNumber, fullAddress, employees, shareOutStanding, totalDebtToEquity, totalSharesOutStanding, sharesESCROW, vwap,
dividendPayDate, weeks52high, weeks52low, alpha, averageVolume10D, averageVolume30D, averageVolume50D, priceToBook,
priceToCashFlow, returnOnEquity, returnOnAssets, day21MovingAvg, day50MovingAvg, day200MovingAvg, dividend3Years,
dividend5Years, datatype, typename, suspended, lastupdate)
VALUES
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
ON CONFLICT (symbol) DO UPDATE SET
(name, price, priceChange, percentChange, exchangeName, exShortName, exchangeCode, marketPlace,
sector, industry, volume, openPrice, dayHigh, dayLow, MarketCap, MarketCapAllClasses, peRatio, prevClose, dividendFrequency,
dividendYield, dividendAmount, dividendCurrency, beta, eps, exDividendDate, shortDescription, longDescription, website, email,
phoneNumber, fullAddress, employees, shareOutStanding, totalDebtToEquity, totalSharesOutStanding, sharesESCROW, vwap,
dividendPayDate, weeks52high, weeks52low, alpha, averageVolume10D, averageVolume30D, averageVolume50D, priceToBook,
priceToCashFlow, returnOnEquity, returnOnAssets, day21MovingAvg, day50MovingAvg, day200MovingAvg, dividend3Years,
dividend5Years, datatype, typename, suspended, lastupdate)
=
(EXCLUDED.name, EXCLUDED.price, EXCLUDED.priceChange, EXCLUDED.percentChange, EXCLUDED.exchangeName, EXCLUDED.exShortName,
EXCLUDED.exchangeCode, EXCLUDED.marketPlace, EXCLUDED.sector, EXCLUDED.industry, EXCLUDED.volume, EXCLUDED.openPrice,
EXCLUDED.dayHigh, EXCLUDED.dayLow, EXCLUDED.MarketCap, EXCLUDED.MarketCapAllClasses, EXCLUDED.peRatio, EXCLUDED.prevClose,
EXCLUDED.dividendFrequency, EXCLUDED.dividendYield, EXCLUDED.dividendAmount, EXCLUDED.dividendCurrency, EXCLUDED.beta,
EXCLUDED.eps, EXCLUDED.exDividendDate, EXCLUDED.shortDescription, EXCLUDED.longDescription, EXCLUDED.website, EXCLUDED.email,
EXCLUDED.phoneNumber, EXCLUDED.fullAddress, EXCLUDED.employees, EXCLUDED.shareOutStanding, EXCLUDED.totalDebtToEquity,
EXCLUDED.totalSharesOutStanding, EXCLUDED.sharesESCROW, EXCLUDED.vwap, EXCLUDED.dividendPayDate, EXCLUDED.weeks52high,
EXCLUDED.weeks52low, EXCLUDED.alpha, EXCLUDED.averageVolume10D, EXCLUDED.averageVolume30D, EXCLUDED.averageVolume50D,
EXCLUDED.priceToBook, EXCLUDED.priceToCashFlow, EXCLUDED.returnOnEquity, EXCLUDED.returnOnAssets, EXCLUDED.day21MovingAvg,
EXCLUDED.day50MovingAvg, EXCLUDED.day200MovingAvg, EXCLUDED.dividend3Years, EXCLUDED.dividend5Years, EXCLUDED.datatype,
EXCLUDED.typename, EXCLUDED.suspended, EXCLUDED.lastupdate)
RETURNING symbol;"""
c.execute(sql, quote_info + (timestamp,))
result = c.fetchone()
c.close()
except (Exception, psycopg2.Error) as e:
print(e)
finally:
conn.commit()
return result