API tools faq
paste
Advertisement
Ruslaner___

Untitled

Ruslaner___
Mar 20th, 2018
259
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 10.21 KB | None | 0 0
raw download clone embed print report
  1.  
  2. def 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()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!