<commit_before># db_importer.py

import click
import os
import fnmatch
import re

'''
USAGE:
python db_importer.py --idir=data/hedu/2012/ --name=hedu
'''

pattern = re.compile('(\w+).csv(.bz2)*')


def parse_table(t, dbname):
    m = pattern.search(t)
    if m:
        return dbname + "_" + m.group(1)

# via http://stackoverflow.com/questions/13299731/python-need-to-loop-through-directories-looking-for-txt-files


def findFiles(path, filter):
    for root, dirs, files in os.walk(path):
        for file in fnmatch.filter(files, filter):
            yield os.path.join(root, file)


@click.command()
@click.option('--idir', '-i', default='.', type=click.Path(exists=True), prompt=False, help='Directory for files.')
@click.option('--separator', '-s', default=',', prompt=False, help='Fields separator.')
@click.option('--name', '-n', prompt=True, help='Name of database eg rais or secex.')
@click.option('--host', '-h', prompt=True, help='Database host ip.')
@click.option('--user', '-u', prompt=True, help='Database user.')
@click.option('--password', '-p', prompt=True, help='Database password.')
@click.option('--database', '-d', prompt=True, help='Database name.')
def main(idir, separator, name, host, user, password, database):
    file_type = 'csv' if separator == ',' else 'tsv'
    for f in findFiles(idir, '*.' + file_type + '*'):
        bzipped = False
        # print f, "Processing"
        if f.endswith("bz2"):
            bzipped = True
            os.system("bunzip2 -k " + f)
            f = f[:-4]
        handle = open(f)
        tablename = parse_table(f, name)
        print "importing", f, "into", tablename
        header = handle.readline().strip()
        fields = header.split(separator)
        fields_null = ["{0} = nullif(@v{0},'')".format(fi) for fi in fields]
        # print "fields", fields

        fields = ["@v{}".format(x) for x in fields]

        fields = ",".join(fields)
        fields_null = ",".join(fields_null)

        cmd = '''mysql -h %s -u%s -p%s %s --local-infile=1 -e "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s
                 FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\n' IGNORE 1 LINES (%s) SET %s;" ''' % (
            host, user, password, database, f, tablename, separator, fields, fields_null)
        # print cmd
        os.system(cmd)

        # delete bunzipped file
        if bzipped:
            os.remove(f)

if __name__ == '__main__':
    main()<commit_msg>Fixes tab fields termination<commit_after># db_importer.py

import click
import os
import fnmatch
import re

'''
USAGE:
python db_importer.py --idir=data/hedu/2012/ --name=hedu
'''

pattern = re.compile('(\w+).csv(.bz2)*')


def parse_table(t, dbname):
    m = pattern.search(t)
    if m:
        return dbname + "_" + m.group(1)

# via http://stackoverflow.com/questions/13299731/python-need-to-loop-through-directories-looking-for-txt-files


def findFiles(path, filter):
    for root, dirs, files in os.walk(path):
        for file in fnmatch.filter(files, filter):
            yield os.path.join(root, file)


@click.command()
@click.option('--idir', '-i', default='.', type=click.Path(exists=True), prompt=False, help='Directory for files.')
@click.option('--separator', '-s', default=',', prompt=False, help='Fields separator.')
@click.option('--name', '-n', prompt=True, help='Name of database eg rais or secex.')
@click.option('--host', '-h', prompt=True, help='Database host ip.')
@click.option('--user', '-u', prompt=True, help='Database user.')
@click.option('--password', '-p', prompt=True, help='Database password.')
@click.option('--database', '-d', prompt=True, help='Database name.')
def main(idir, separator, name, host, user, password, database):
    file_type = 'csv' if separator == ',' else 'tsv'
    for f in findFiles(idir, '*.' + file_type + '*'):
        bzipped = False
        # print f, "Processing"
        if f.endswith("bz2"):
            bzipped = True
            os.system("bunzip2 -k " + f)
            f = f[:-4]
        handle = open(f)
        tablename = parse_table(f, name)
        print "importing", f, "into", tablename
        header = handle.readline().strip()
        fields = header.split(separator)
        fields_null = ["{0} = nullif(@v{0},'')".format(fi) for fi in fields]
        # print "fields", fields

        fields = ["@v{}".format(x) for x in fields]

        fields = ",".join(fields)
        fields_null = ",".join(fields_null)

        fields_termination = ',' if separator == ',' else '\\t'
        cmd = '''mysql -h %s -u%s -p%s %s --local-infile=1 -e "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s
                 FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\n' IGNORE 1 LINES (%s) SET %s;" ''' % (
            host, user, password, database, f, tablename, fields_termination, fields, fields_null)
        print cmd
        os.system(cmd)

        # delete bunzipped file
        if bzipped:
            os.remove(f)

if __name__ == '__main__':
    main()
{'fraction_non_alphanumeric': 0.17803794913201454, 'fraction_numerical': 0.008477997577714978, 'mean_word_length': 2.8590342679127727, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4201938', 'n_tokens_mistral': 1721, 'n_tokens_neox': 1643, 'n_words': 431}