· 8 years ago · Mar 20, 2018, 10:10 AM
1
2def create_tables():
3 TABLES = {}
4 TABLES['_ctrl_status'] = (
5 "CREATE TABLE `_ctrl_status` ("
6 " `ctrl_status_id` BIGINT NOT NULL AUTO_INCREMENT,"
7 " `ctrl_status_name` VARCHAR(20) NOT NULL,"
8 " PRIMARY KEY (`ctrl_status_id`)"
9 ") ENGINE=InnoDB")
10 TABLES['industry_rank'] = (
11 " CREATE TABLE `industry_rank` ("
12 " `rank_id` INT NOT NULL AUTO_INCREMENT,"
13 " `Industry_name` VARCHAR(100) NOT NULL,"
14 " `rank_rating` INT NOT NULL,"
15 " PRIMARY KEY (`rank_id`)"
16 ") ENGINE=InnoDB")
17 TABLES['_ctrl'] = (
18 "CREATE TABLE `_ctrl` ("
19 " `id` BIGINT NOT NULL AUTO_INCREMENT,"
20 " `report_id` BIGINT NOT NULL,"
21 " `ticker` VARCHAR(6) NOT NULL,"
22 " `report_date` DATE NOT NULL,"
23 " `ctrl_status_id` BIGINT NOT NULL,"
24 " `report_last_seen_time` DATE,"
25 " `report_last_seen_who` VARCHAR(30),"
26 " `load_folder_date` DATE NOT NULL,"
27 " `process_date` DATE NOT NULL,"
28 " `reload_date` DATE,"
29 " `reload_reason` VARCHAR(255),"
30 " `is_invalid` BIT NOT NULL,"
31 " `is_deleted` BIT NOT NULL,"
32 " `rank_id` INT NOT NULL,"
33 " `layout_id` INT NOT NULL,"
34 " `Report_file` VARCHAR(255) NOT NULL ,"
35 " PRIMARY KEY (`id`), UNIQUE KEY `report_id` (`report_id`),"
36 " CONSTRAINT `status_id_ibfk_1` FOREIGN KEY (`ctrl_status_id`) "
37 " REFERENCES `_ctrl_status`(`ctrl_status_id`) ON DELETE CASCADE ON UPDATE CASCADE,"
38 " CONSTRAINT `rank_id_ibfk_1` FOREIGN KEY (`rank_id`) "
39 " REFERENCES `industry_rank`(`rank_id`) ON DELETE CASCADE ON UPDATE CASCADE"
40 ") ENGINE=InnoDB")
41
42 TABLES['annual_rates'] = (
43 "CREATE TABLE `annual_rates` ("
44 " `id` BIGINT NOT NULL AUTO_INCREMENT,"
45 " `report_id` BIGINT NOT NULL,"
46 " `sales_past_10yrs` NUMERIC,"
47 " `sales_past_5yrs` NUMERIC,"
48 " `sales_estimation` NUMERIC,"
49 " `cashf_past_10yrs` NUMERIC,"
50 " `cashf_past_5yrs` NUMERIC,"
51 " `cashf_estimation` NUMERIC,"
52 " `earn_past_10yrs` NUMERIC,"
53 " `earn_past_5yrs` NUMERIC,"
54 " `earn_estimation` NUMERIC,"
55 " `dividents_past_10yrs` NUMERIC,"
56 " `dividents_past_5yrs` NUMERIC,"
57 " `dividents_estimation` NUMERIC,"
58 " `bookvalue_past_10yrs` NUMERIC,"
59 " `bookvalue_past_5yrs` NUMERIC,"
60 " `bookvalue_past_estimation` NUMERIC,"
61 " PRIMARY KEY (`id`), KEY `_ctrl` (`report_id`),"
62 " CONSTRAINT `report_id_ibfk_1` FOREIGN KEY (`report_id`) "
63 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
64 ") ENGINE=InnoDB")
65
66 TABLES['current_positions'] = (
67 "CREATE TABLE `current_positions` ("
68 " `id` BIGINT NOT NULL AUTO_INCREMENT,"
69 " `report_id` BIGINT NOT NULL,"
70 " `yr1` NUMERIC,"
71 " `yr2` NUMERIC,"
72 " `date1` NUMERIC,"
73 " `cash_assets_yr1` NUMERIC,"
74 " `cash_assets_yr2` NUMERIC,"
75 " `cash_assets_date1` NUMERIC,"
76 " `receivables_yr1` NUMERIC,"
77 " `receivables_yr2` NUMERIC,"
78 " `inventory_yr1` NUMERIC,"
79 " `inventory_yr2` NUMERIC,"
80 " `inventory_date1` NUMERIC,"
81 " `other1_yr1` NUMERIC,"
82 " `other1_yr2` NUMERIC,"
83 " `other1_date1` NUMERIC,"
84 " `current_assets_yr1` NUMERIC,"
85 " `current_assets_yr2` NUMERIC,"
86 " `current_assets_date1` NUMERIC,"
87 " `acct_payable_yr1` NUMERIC,"
88 " `acct_payable_yr2` NUMERIC,"
89 " `acct_payable_date1` NUMERIC,"
90 " `debt_due_yr1` NUMERIC,"
91 " `debt_due_yr2` NUMERIC,"
92 " `debt_due_date1` NUMERIC,"
93 " `other2_yr1` NUMERIC,"
94 " `other2_yr2` NUMERIC,"
95 " `other2_date1` NUMERIC,"
96 " `curr_liab_yr1` NUMERIC,"
97 " `curr_liab_yr2` NUMERIC,"
98 " `curr_liab_date1` NUMERIC,"
99 " `curr_position_scale` VARCHAR(10),"
100 " KEY `current_positions` (`id`),"
101 " CONSTRAINT `report_id_ibfk_2` FOREIGN KEY (`report_id`) "
102 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
103 ") ENGINE=InnoDB")
104
105 TABLES['high_low'] = (
106 "CREATE TABLE `high_low` ("
107 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
108 " `report_id` BIGINT NOT NULL,"
109 " `yr` INT NOT NULL,"
110 " `high` NUMERIC NOT NULL,"
111 " `low` NUMERIC NOT NULL,"
112 " PRIMARY KEY (`ID`),"
113 " CONSTRAINT `reportid_ibfk_3` FOREIGN KEY (`report_id`)"
114 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
115 ") ENGINE=InnoDB")
116
117 TABLES['projections'] = (
118 "CREATE TABLE `projections` ("
119 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
120 " `report_id` BIGINT NOT NULL,"
121 " `yr_start` INT NOT NULL,"
122 " `yr_end` INT NOT NULL,"
123 " `high_price` INT NOT NULL,"
124 " `high_gain_pct` INT NOT NULL,"
125 " `high_ann_total_return` INT NOT NULL,"
126 " `low_price` INT NOT NULL,"
127 " `low_gain_pct` INT NOT NULL,"
128 " `low_ann_total_return` INT NOT NULL,"
129 " PRIMARY KEY (`ID`),"
130 " CONSTRAINT `reportid_ibfk_4` FOREIGN KEY (`report_id`)"
131 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
132 ") ENGINE=InnoDB")
133
134 TABLES['quarterly_stats'] = (
135 "CREATE TABLE `quarterly_stats` ("
136 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
137 " `report_id` BIGINT NOT NULL,"
138 " `type` INT NOT NULL,"
139 " `yr` INT NOT NULL,"
140 " `q1` NUMERIC NOT NULL,"
141 " `q2` NUMERIC NOT NULL,"
142 " `q3` NUMERIC NOT NULL,"
143 " `q4` NUMERIC NOT NULL,"
144 " `q_total` NUMERIC NOT NULL,"
145 " PRIMARY KEY (`ID`),"
146 " CONSTRAINT `reportid_ibfk_5` FOREIGN KEY (`report_id`)"
147 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
148 ") ENGINE=InnoDB")
149
150 TABLES['single_values'] = (
151 "CREATE TABLE `single_values` ("
152 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
153 " `report_id` BIGINT NOT NULL,"
154 " `pe_ratio` NUMERIC NOT NULL,"
155 " `pe_ratio_trail` NUMERIC NOT NULL,"
156 " `pe_ratio_med` NUMERIC NOT NULL,"
157 " `pe_ratio_rel` NUMERIC NOT NULL,"
158 " `div_yld` NUMERIC NOT NULL,"
159 " `timeliness` NUMERIC NOT NULL,"
160 " `safety` NUMERIC NOT NULL,"
161 " `technical` NUMERIC NOT NULL,"
162 " `beta` NUMERIC NOT NULL,"
163 " `beta_market` NUMERIC NOT NULL,"
164 " `total_debt` NUMERIC NOT NULL,"
165 " `total_debt_txt` NUMERIC NOT NULL,"
166 " `common_stock` NUMERIC NOT NULL,"
167 " `market_cap` NUMERIC NOT NULL,"
168 " `market_cap_money_txt` NUMERIC NOT NULL,"
169 " `market_cap_txt` NUMERIC NOT NULL,"
170 " `fin_strength` NUMERIC NOT NULL,"
171 " `price_stability` NUMERIC NOT NULL,"
172 " `price_growth_persistence` NUMERIC NOT NULL,"
173 " `earnings_predictability` NUMERIC NOT NULL,"
174 " PRIMARY KEY (`ID`),"
175 " CONSTRAINT `reportid_ibfk_6` FOREIGN KEY (`report_id`)"
176 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
177 ") ENGINE=InnoDB")
178
179 TABLES['txt_desc'] = (
180 "CREATE TABLE `txt_desc` ("
181 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
182 " `report_id` BIGINT NOT NULL,"
183 " `business` BLOB NOT NULL,"
184 " `description` BLOB NOT NULL,"
185 " PRIMARY KEY (`ID`),"
186 " CONSTRAINT `reportid_ibfk_7` FOREIGN KEY (`report_id`)"
187 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
188 ") ENGINE=InnoDB")
189
190 TABLES['year_stats'] = (
191 "CREATE TABLE `year_stats` ("
192 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
193 " `report_id` BIGINT NOT NULL,"
194 " `yr` INT NOT NULL,"
195 " `position` INT NOT NULL,"
196 " `value` NUMERIC NOT NULL,"
197 " `Isvirtual_yr` BIT NOT NULL,"
198 " PRIMARY KEY (`ID`),"
199 " CONSTRAINT `reportid_ibfk_8` FOREIGN KEY (`report_id`)"
200 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
201 ") ENGINE=InnoDB")
202
203 TABLES['year_stats_captions'] = (
204 "CREATE TABLE `year_stats_captions` ("
205 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
206 " `report_id` BIGINT NOT NULL,"
207 " `position` INT NOT NULL,"
208 " `title` VARCHAR(30) NOT NULL,"
209 " PRIMARY KEY (`ID`),"
210 " CONSTRAINT `reportid_ibfk_9` FOREIGN KEY (`report_id`)"
211 " REFERENCES `_ctrl` (`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
212 ") ENGINE=InnoDB")
213
214 TABLES['year_stats_projections'] = (
215 "CREATE TABLE `year_stats_projections` ("
216 " `ID` BIGINT NOT NULL AUTO_INCREMENT,"
217 " `report_id` BIGINT NOT NULL,"
218 " `position` INT NOT NULL,"
219 " `projection` NUMERIC NOT NULL,"
220 " PRIMARY KEY (`ID`),"
221 " CONSTRAINT `reportid_ibfk_10` FOREIGN KEY (`report_id`)"
222 " REFERENCES `_ctrl`(`report_id`) ON DELETE CASCADE ON UPDATE CASCADE"
223 ") ENGINE=InnoDB")
224
225 for name, ddl in TABLES.items():
226 try:
227 print("Creating table {}: ".format(name), end='')
228 cursor.execute("DROP TABLE IF EXISTS {}".format(name))
229 cursor.execute(ddl)
230 except mysql.connector.Error as err:
231 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
232 print("already exists.")
233 else:
234 print(err.msg)
235 else:
236 print("OK")
237
238 """Inserting status options"""
239 status_value_list = ['Uploaded',
240 'Processed',
241 'Completed',
242 'Processing issue',
243 'Analysis issue']
244 for status in status_value_list:
245 sql_ctrl_status = (
246 "INSERT INTO `_ctrl_status` (`ctrl_status_name`) VALUE('{}')".format(status)
247 )
248 try:
249 cursor.execute(sql_ctrl_status)
250 cnx.commit()
251 except:
252 cnx.rollback()