import_csv_to_db

import_csv_to_db.py

# coding: utf8
"""
Goal: import csv to database.

pip install pandas, numpy
"""

import pandas
import numpy as np

todo = 'test.csv'
table = 'master_disclaimer'
fields = [
    'index',
    'us_english',
    'uk_english',
    'german',
    'italian',
    'french',
    'japanese',
]

# without header line
# df0 = pandas.read_csv(todo, encoding='utf-8', dtype=unicode, header=None)
# with header line
df0 = pandas.read_csv(todo, encoding='utf-8', dtype=unicode)
# replace NaN
df = df0.replace(np.nan, u'')
# close file?
if hasattr(df0, 'close') and callable(df0.close):
    df0.close()
# convert to python unicode
rows = [map(unicode, x) for x in df.to_records(index=False)]


#print rows[0]

def format_sql(row):
    rv = 'insert into %s (%s) values (%s);\n' % (table, ','.join(fields), ','.join(map(lambda s: "'%s'" % s.replace("'", r"''"), row)))
    rv = rv.encode('utf-8')
    print rv
    return rv

with open('db.sql', 'w') as sql:
    map(lambda x: sql.write(format_sql(x)), rows)