Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # -*- coding: utf-8 -*-
- """This is a 10-minute script to load a csv file into a
- MySQL database.
- """
- import sys
- import os
- import MySQLdb
- from optparse import OptionParser
- class Credentials:
- """ ADT to make life cushier. """
- def __init__(self, hostname, username, password):
- self.hostname = hostname
- self.username = username
- self.password = password
- def get_db(db_credentials):
- """ Returns the db cursor. """
- db = MySQLdb.Connect(host=db_credentials.hostname,
- user=db_credentials.username,
- passwd=db_credentials.password)
- cursor = db.cursor()
- return cursor
- def create_table(table, db_credentials):
- """ Create a table for tick data """
- db = get_db(db_credentials)
- sql = """
- CREATE TABLE contract_history.tick_data
- (
- symbol VARCHAR(8) NOT NULL,
- time_stamp TIMESTAMP,
- open FLOAT,
- high FLOAT,
- low FLOAT,
- close FLOAT,
- volume INTEGER,
- PRIMARY KEY (symbol, time_stamp))
- """
- db.execute(sql)
- def load_csv_files_into_db(path, table, db_credentials):
- """ Load all the csv files in a given directory
- into a MySQL table.
- """
- files = os.listdir(path)
- db = get_db(db_credentials)
- for file in files:
- print file
- db.execute("""load data local infile '%s\\%s'
- into table %s
- fields terminated by ','
- enclosed by ''
- lines terminated by '\r\n';""" % (path, file, table))
- #fuck windows... ugh
- def main(options):
- db_credentials = Credentials(options.hostname,
- options.username,
- options.password)
- if options.create_table:
- create_table(options.table, db_credentials)
- load_csv_files_into_db(options.path, options.table, db_credentials)
- if __name__ == '__main__':
- usage = "usage: %prog [options]"
- parser = OptionParser(usage=usage)
- parser.add_option('-n', '--hostname', metavar='[hostname]', dest='hostname')
- parser.add_option('-u', '--username', metavar='[username]', dest='username')
- parser.add_option('-p', '--password', metavar='[password]', dest='password')
- parser.add_option('-t', '--table', metavar='[table]', dest='table')
- parser.add_option('-l', '--path', metavar='[path]', dest='path')
- parser.add_option('-c', '--create-table', dest='create_table', default=False)
- (options, args) = parser.parse_args()
- try:
- main(options)
- except:
- sys.exit('ERROR: Check Settings')
Add Comment
Please, Sign In to add comment