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