CSV to mysql|sqlite

CSV2SQL.py

#!/usr/bin/env python
# encoding: utf-8

# only for Mysql
import MySQLdb, sqlite3
# config DataBase
import sys, os, getopt, csv, types, random
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), "../..")))
from mordor.config import DATABASES as DB
"""
DB = {
'host': '',
'name': '',
'psw': '',
'port': '',
}
"""
from optparse import OptionParser
DB = DB['default']
connect = MySQLdb.connect(host=DB['HOST'], user=DB['USER'], passwd=DB['PASSWORD'], db=DB['NAME'])
# key size
DEFAULT_SIZE = 32
DEFAULT_TYPE = 'varchar(%s)'%DEFAULT_SIZE

def main(argv = None):
    # if argv is None:
    # argv = sys.argv[1]
    # parse_file(argv)
        
    parser = OptionParser()
    parser.add_option('-f', '--file', dest='filename', help='with sqlite file')
    parser.add_option('-s', '--source', dest='source', help='The soirce path')
    parser.add_option('-o', '--out', dest='output', help='The output path')
    parser.add_option('-n', '--name', dest="name", help='the table name')
    parser.add_option('-v', '--visable', dest = 'show', help= 'show the log', action='store_true')
    options, args = parser.parse_args()
    
    source = options.source if options.source else None
    output = options.output if options.output else None
    name = options.name if options.name else None
    show = True if options.show else None
    if options.filename:
        connect = sqlite3.connect(options.filename)
    parse_file(source, name, show, connect)

def parse_file(filename, table_name, show, connect):
    cursor = connect.cursor()
    content = open(filename).read().split('\n')
    head = content[0]
    keys = head.split(',')
    body = content[1:]
    if not table_name:
        table_name = filename.split('/')[-1].split('.')[0].upper()
    sql_create_cmd = 'CREATE TABLE %s ('%table_name
    for key in keys:
        sql_create_cmd += ('%s %s,'%(key.upper(),DEFAULT_TYPE))
    sql_create_cmd = sql_create_cmd[:-1] + ');'
    if show:
        print sql_create_cmd
    try:
        cursor.execute(sql_create_cmd)
    except:
        print 'table_existing, try insert'
        
    sql_insert_cmd = 'INSERT into table_name'
    for item in body:
        _item = ''
        for key in item.split(','):
            if key.split('"')[0] is not '' or key is '':
                _item += "\"%s\","%key
            else:
                _item += "%s,"%key
            item = _item
            sql_insert_cmd = 'INSERT INTO %s '%table_name
            sql_insert_cmd += '(%s) VALUES (%s);'%(head, item[:-1])
        if show:
            num = random.random()*7+30
            print '\x1b[%sm%s\x1b[0m'%(int(num), sql_insert_cmd)
        try:
            cursor.execute(sql_insert_cmd)
            connect.commit()
        except:
            print 'error when exec %s'%sql_insert_cmd
        
if __name__ == "__main__":
  sys.exit(main())