· 6 years ago · Oct 27, 2019, 08:24 PM
1# management/custom_sql/ops.py
2
3DROP_EXISTING_TEMP_TICKERS_TABLE = "DROP TABLE IF EXISTS screener_ticker_temp"
4
5CREATE_TEMP_TICKERS_TABLE = """CREATE TABLE screener_ticker_temp (
6 id serial NOT NULL PRIMARY KEY,
7 \"table\" varchar(255) NULL,
8 permaticker varchar(255) NULL,
9 ticker varchar(255) NOT NULL UNIQUE,
10 name varchar(255) NULL,
11 exchange varchar(255) NULL,
12 isdelisted varchar(255) NULL,
13 category varchar(255) NULL,
14 cusips varchar(255) NULL,
15 siccode varchar(255) NULL,
16 sicsector varchar(255) NULL,
17 sicindustry varchar(255) NULL,
18 famasector varchar(255) NULL,
19 famaindustry varchar(255) NULL,
20 sector varchar(255) NULL,
21 industry varchar(255) NULL,
22 scalemarketcap varchar(255) NULL,
23 scalerevenue varchar(255) NULL,
24 relatedtickers varchar(255) NULL,
25 currency varchar(255) NULL,
26 location varchar(255) NULL,
27 lastupdated date NULL,
28 firstadded date NULL,
29 firstpricedate date NULL,
30 lastpricedate date NULL,
31 firstquarter date NULL,
32 lastquarter date NULL,
33 secfilings varchar(255) NULL,
34 companysite varchar(255) NULL
35)"""
36
37COPY_CSV_TO_TEMP_TICKERS_TABLE = """
38COPY screener_ticker_temp(
39 "table",
40 permaticker,
41 ticker,
42 name,
43 exchange,
44 isdelisted,
45 category,
46 cusips,
47 siccode,
48 sicsector,
49 sicindustry,
50 famasector,
51 famaindustry,
52 sector,
53 industry,
54 scalemarketcap,
55 scalerevenue,
56 relatedtickers,
57 currency,
58 location,
59 lastupdated,
60 firstadded,
61 firstpricedate,
62 lastpricedate,
63 firstquarter,
64 lastquarter,
65 secfilings,
66 companysite
67) FROM '{}' DELIMITER ',' CSV HEADER;
68"""
69
70DROP_EXISTING_DELETED_TICKERS_TABLE = "DROP TABLE IF EXISTS deleted_tickers_temp"
71
72INSERT_UPDATE_AND_CREATE_DELETED_TICKERS_TABLE = """CREATE TABLE deleted_tickers_temp AS (
73 WITH updated AS (
74 INSERT INTO screener_ticker SELECT * FROM screener_ticker_temp ON CONFLICT (ticker) DO UPDATE SET
75 \"table\" = EXCLUDED.\"table\",
76 permaticker = EXCLUDED.permaticker,
77 ticker = EXCLUDED.ticker,
78 name = EXCLUDED.name,
79 exchange = EXCLUDED.exchange,
80 isdelisted = EXCLUDED.isdelisted,
81 category = EXCLUDED.category,
82 cusips = EXCLUDED.cusips,
83 siccode = EXCLUDED.siccode,
84 sicsector = EXCLUDED.sicsector,
85 sicindustry = EXCLUDED.sicindustry,
86 famasector = EXCLUDED.famasector,
87 famaindustry = EXCLUDED.famaindustry,
88 sector = EXCLUDED.sector,
89 industry = EXCLUDED.industry,
90 scalemarketcap = EXCLUDED.scalemarketcap,
91 scalerevenue = EXCLUDED.scalerevenue,
92 relatedtickers = EXCLUDED.relatedtickers,
93 currency = EXCLUDED.currency,
94 location = EXCLUDED.location,
95 lastupdated = EXCLUDED.lastupdated,
96 firstadded = EXCLUDED.firstadded,
97 firstpricedate = EXCLUDED.firstpricedate,
98 lastpricedate = EXCLUDED.lastpricedate,
99 firstquarter = EXCLUDED.firstquarter,
100 lastquarter = EXCLUDED.lastquarter,
101 secfilings = EXCLUDED.secfilings,
102 companysite = EXCLUDED.companysite RETURNING id
103 ), deleted_tickers as (
104 SELECT id FROM screener_ticker WHERE id NOT IN (
105 SELECT id FROM updated
106 )
107 ) select * from deleted_tickers
108);
109"""
110
111
112
113
114
115
116
117
118# management/commands/csv_ticker_update.py:
119
120...
121from lfa_lite.management.custom_sql import ops
122...
123
124
125def update_all_tickers():
126
127 # Set up the Quandl API access
128 QUANDL_API_KEY = settings.QUANDL_API_KEY
129 quandl.ApiConfig.api_key = QUANDL_API_KEY
130
131 # Ensure we only get columns that are in the current model
132 ticker_headers = [
133 'table',
134 'permaticker',
135 'ticker',
136 'name',
137 'exchange',
138 'isdelisted',
139 'category',
140 'cusips',
141 'siccode',
142 'sicsector',
143 'sicindustry',
144 'famasector',
145 'famaindustry',
146 'sector',
147 'industry',
148 'scalemarketcap',
149 'scalerevenue',
150 'relatedtickers',
151 'currency',
152 'location',
153 'lastupdated',
154 'firstadded',
155 'firstpricedate',
156 'lastpricedate',
157 'firstquarter',
158 'lastquarter',
159 'secfilings',
160 'companysite',
161 ]
162
163 # Get updated ticker list given the above columns
164 all_tickers = func_to_get_new_stuff() # Pseudo
165
166 # Set the output file directory/filename to prepare for CSV writing
167 PROJECT_ROOT = settings.PROJECT_ROOT
168 filename = os.path.join(PROJECT_ROOT, 'utils/update_files/all_tickers.csv')
169
170 # Write the ticker list to a CSV
171 with open(filename, 'w', encoding='utf-8') as out:
172 out.write(all_tickers.to_csv(index=False))
173
174 with connection.cursor() as cursor:
175 cursor.execute(ops.DROP_EXISTING_TEMP_TICKERS_TABLE)
176 cursor.execute(ops.CREATE_TEMP_TICKERS_TABLE)
177 cursor.execute(ops.COPY_CSV_TO_TEMP_TICKERS_TABLE.format(filename))
178 cursor.execute(ops.DROP_EXISTING_DELETED_TICKERS_TABLE)
179 cursor.execute(ops.INSERT_UPDATE_AND_CREATE_DELETED_TICKERS_TABLE)