Guest User

Untitled

a guest
Nov 23rd, 2017
360
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.75 KB | None | 0 0
  1. # -*- coding: utf-8 -*-
  2.  
  3. """This is a 10-minute script to load a csv file into a
  4. MySQL database.
  5.  
  6. """
  7.  
  8. import sys
  9. import os
  10. import MySQLdb
  11. from optparse import OptionParser
  12.  
  13. class Credentials:
  14. """ ADT to make life cushier. """
  15.  
  16. def __init__(self, hostname, username, password):
  17. self.hostname = hostname
  18. self.username = username
  19. self.password = password
  20.  
  21.  
  22. def get_db(db_credentials):
  23. """ Returns the db cursor. """
  24.  
  25. db = MySQLdb.Connect(host=db_credentials.hostname,
  26. user=db_credentials.username,
  27. passwd=db_credentials.password)
  28. cursor = db.cursor()
  29. return cursor
  30.  
  31. def create_table(table, db_credentials):
  32. """ Create a table for tick data """
  33.  
  34. db = get_db(db_credentials)
  35. sql = """
  36. CREATE TABLE contract_history.tick_data
  37. (
  38. symbol VARCHAR(8) NOT NULL,
  39. time_stamp TIMESTAMP,
  40. open FLOAT,
  41. high FLOAT,
  42. low FLOAT,
  43. close FLOAT,
  44. volume INTEGER,
  45. PRIMARY KEY (symbol, time_stamp))
  46. """
  47. db.execute(sql)
  48.  
  49. def load_csv_files_into_db(path, table, db_credentials):
  50. """ Load all the csv files in a given directory
  51. into a MySQL table.
  52.  
  53. """
  54.  
  55. files = os.listdir(path)
  56. db = get_db(db_credentials)
  57. for file in files:
  58. print file
  59. db.execute("""load data local infile '%s\\%s'
  60. into table %s
  61. fields terminated by ','
  62. enclosed by ''
  63. lines terminated by '\r\n';""" % (path, file, table))
  64. #fuck windows... ugh
  65.  
  66. def main(options):
  67. db_credentials = Credentials(options.hostname,
  68. options.username,
  69. options.password)
  70. if options.create_table:
  71. create_table(options.table, db_credentials)
  72. load_csv_files_into_db(options.path, options.table, db_credentials)
  73.  
  74. if __name__ == '__main__':
  75. usage = "usage: %prog [options]"
  76. parser = OptionParser(usage=usage)
  77. parser.add_option('-n', '--hostname', metavar='[hostname]', dest='hostname')
  78. parser.add_option('-u', '--username', metavar='[username]', dest='username')
  79. parser.add_option('-p', '--password', metavar='[password]', dest='password')
  80. parser.add_option('-t', '--table', metavar='[table]', dest='table')
  81. parser.add_option('-l', '--path', metavar='[path]', dest='path')
  82. parser.add_option('-c', '--create-table', dest='create_table', default=False)
  83. (options, args) = parser.parse_args()
  84. try:
  85. main(options)
  86. except:
  87. sys.exit('ERROR: Check Settings')
Add Comment
Please, Sign In to add comment