<commit_before>"""Database Toolkit tools
Functions to create a database from a delimited text file.

Supported database engines: MySQL, PostgreSQL

Usage: python dbtk_ernest2003.py [-e engine (mysql, postgresql, etc.)] [--engine=engine]
                                 [-u username] [--user=username] 
                                 [-p password] [--password=password]
                                 [-h {hostname} (default=localhost)] [--host=hostname] 
                                 [-o {port}] [--port=port]
                                 [-d {databasename}] [--db=databasename]

db variables:
    dbname - the name to use for the new database. If it exists, it will be dropped.
    drop - if the database already exists, should it be dropped?     
    opts - list of variables supplied from command line arguments or manually input
    engine - specifies the database engine (MySQL, PostgreSQL, etc.)
    cursor - a cursor from the database connection    

table variables:
    tablename - the name to use for the new table.
    drop - if the table already exists, should it be dropped?
    pk - the name of the value to be used as primary key. If None, no primary key will
         be used. The primary key must be the first column in dbcolumns.
    source - the open file or url containing the data
    delimiter - the delimiter used in the text file. If None, whitespace will be assumed.
    header_rows - number of header rows to be skipped
    cleanup - the name of the cleanup function to be used (or no_cleanup for none)
    dbcolumns - a list of tuples, containing each column name and its data type.
                The number of values in each row of the text file must correspond with
                the number of columns defined.
                Data type is also a tuple, with the first value specifying the type.
                (The second part of the type specifies the length and is optional)
                    pk      - primary key
                    int     - integer
                    double  - double precision
                    char    - string
                    bit     - binary
                    skip    - ignore this row
                    combine - append this row's data to the data of the previous row 
"""

import getpass
import getopt
import urllib
import warnings
import sys

def no_cleanup(value):
    """Default cleanup function, returns the unchanged value"""
    return value 

class Database:
    """Information about database to be passed to dbtk_tools.create_table"""
    dbname = ""
    engine = "MySQL"
    cursor = None
    drop = True
    opts = dict()
    
class Table:
    """Information about table to be passed to dbtk_tools.create_table"""
    tablename = ""
    pk = None
    lines = []
    delimiter = None
    columns = []
    drop = True
    header_rows = 1
    cleanup = no_cleanup

def create_table(db, table):
    """Creates a table based on settings supplied in table object"""
    warnings.filterwarnings("ignore")        
    
    # Create the table
    if table.drop:
        db.cursor.execute(drop_statement(db.engine, "TABLE", db.dbname + "." + table.tablename))
        createstatement = "CREATE TABLE " + db.dbname + "." + table.tablename + "("
    else:
        createstatement = "CREATE TABLE IF NOT EXISTS " + db.dbname + "." + table.tablename + "("    
    for item in table.columns:
        if (item[1][0] != "skip") and (item[1][0] != "combine"):
            createstatement += item[0] + " " + convert_data_type(db.engine, item[1]) + ", "    
    if table.pk and db.engine == "mysql":
        createstatement += "PRIMARY KEY (" + table.pk + ")"
    else:
        createstatement = createstatement.rstrip(', ')    
    createstatement += ")"  
    
    print "Creating table " + table.tablename + " in database " + db.dbname + " . . ."
    db.cursor.execute(createstatement)
    
    print "Inserting rows: "
    species_id = 0    
    for line in table.source:
        
        line = line.strip()
        if line:
            # If there is a primary key specified, add an auto-incrementing integer            
            if table.pk:
                species_id += 1
                linevalues = [species_id]
                column = 0
            else:
                linevalues = []
                column = -1 
            for value in line.split(table.delimiter):
                column += 1
                thiscolumn = table.columns[column][1][0]
                # If data type is "skip" ignore the value
                if thiscolumn == "skip":
                    pass
                elif thiscolumn == "combine":
                    # If "combine" append value to end of previous column
                    linevalues[len(linevalues) - 1] += " " + value 
                else:
                    # Otherwise, add new value
                    linevalues.append(value) 
                        
            # Build insert statement with the correct # of values
            insertstatement = "INSERT INTO " + db.dbname + "." + table.tablename + " VALUES ("
            for value in linevalues:
                insertstatement += "%s, "
            insertstatement = insertstatement.rstrip(", ") + ");"

            sys.stdout.write(str(species_id) + "\b" * len(str(species_id)))
            db.cursor.execute(insertstatement, 
                           # Run correct_invalid_value on each value before insertion
                           [table.cleanup(value) for value in linevalues])
            
    print "\n Done!"
    table.source.close()
    
def open_url(table, url):
    """Returns an opened file from a URL, skipping the header lines"""
    source = urllib.urlopen(url)
    
    # Skip over the header line by reading it before processing
    if table.header_rows > 0:
        for i in range(table.header_rows):
            line = source.readline()
    return source
    
def drop_statement(engine, objecttype, objectname):
    """Returns a db engine specific drop statement"""
    dropstatement = "DROP %s IF EXISTS %s" % (objecttype, objectname)
    if engine == "postgresql":
        dropstatement += " CASCADE"
    return dropstatement
    
def convert_data_type(engine, datatype):
    """Converts DBTK generic data types to db engine specific data types"""
    datatypes = dict()
    datatypes["pk"], datatypes["int"], datatypes["double"], datatypes["char"], datatypes["bit"] = range(5)
    datatypes["combine"], datatypes["skip"] = [-1, -1]
    dbdatatypes = dict()    
    dbdatatypes["mysql"] = ["INT(5) NOT NULL AUTO_INCREMENT", 
                            "INT", 
                            "DOUBLE", 
                            "VARCHAR", 
                            "BIT"]
    dbdatatypes["postgresql"] = ["integer PRIMARY KEY", 
                                 "integer", 
                                 "double precision", 
                                 "varchar", 
                                 "bit"]<commit_msg>Split out the skipping of data file headers into a new function dbtk_tools.skip_header that is now called by dbtk_tools.open_url, so that this functionality is now available to any script/function opening a file.<commit_after>"""Database Toolkit tools
Functions to create a database from a delimited text file.

Supported database engines: MySQL, PostgreSQL

Usage: python dbtk_ernest2003.py [-e engine (mysql, postgresql, etc.)] [--engine=engine]
                                 [-u username] [--user=username] 
                                 [-p password] [--password=password]
                                 [-h {hostname} (default=localhost)] [--host=hostname] 
                                 [-o {port}] [--port=port]
                                 [-d {databasename}] [--db=databasename]

db variables:
    dbname - the name to use for the new database. If it exists, it will be dropped.
    drop - if the database already exists, should it be dropped?     
    opts - list of variables supplied from command line arguments or manually input
    engine - specifies the database engine (MySQL, PostgreSQL, etc.)
    cursor - a cursor from the database connection    

table variables:
    tablename - the name to use for the new table.
    drop - if the table already exists, should it be dropped?
    pk - the name of the value to be used as primary key. If None, no primary key will
         be used. The primary key must be the first column in dbcolumns.
    source - the open file or url containing the data
    delimiter - the delimiter used in the text file. If None, whitespace will be assumed.
    header_rows - number of header rows to be skipped
    cleanup - the name of the cleanup function to be used (or no_cleanup for none)
    dbcolumns - a list of tuples, containing each column name and its data type.
                The number of values in each row of the text file must correspond with
                the number of columns defined.
                Data type is also a tuple, with the first value specifying the type.
                (The second part of the type specifies the length and is optional)
                    pk      - primary key
                    int     - integer
                    double  - double precision
                    char    - string
                    bit     - binary
                    skip    - ignore this row
                    combine - append this row's data to the data of the previous row 
"""

import getpass
import getopt
import urllib
import warnings
import sys

def no_cleanup(value):
    """Default cleanup function, returns the unchanged value"""
    return value 

class Database:
    """Information about database to be passed to dbtk_tools.create_table"""
    dbname = ""
    engine = "MySQL"
    cursor = None
    drop = True
    opts = dict()
    
class Table:
    """Information about table to be passed to dbtk_tools.create_table"""
    tablename = ""
    pk = None
    lines = []
    delimiter = None
    columns = []
    drop = True
    header_rows = 1
    cleanup = no_cleanup

def create_table(db, table):
    """Creates a table based on settings supplied in table object"""
    warnings.filterwarnings("ignore")        
    
    # Create the table
    if table.drop:
        db.cursor.execute(drop_statement(db.engine, "TABLE", db.dbname + "." + table.tablename))
        createstatement = "CREATE TABLE " + db.dbname + "." + table.tablename + "("
    else:
        createstatement = "CREATE TABLE IF NOT EXISTS " + db.dbname + "." + table.tablename + "("    
    for item in table.columns:
        if (item[1][0] != "skip") and (item[1][0] != "combine"):
            createstatement += item[0] + " " + convert_data_type(db.engine, item[1]) + ", "    
    if table.pk and db.engine == "mysql":
        createstatement += "PRIMARY KEY (" + table.pk + ")"
    else:
        createstatement = createstatement.rstrip(', ')    
    createstatement += ")"  
    
    print "Creating table " + table.tablename + " in database " + db.dbname + " . . ."
    db.cursor.execute(createstatement)
    
    print "Inserting rows: "
    species_id = 0    
    for line in table.source:
        
        line = line.strip()
        if line:
            # If there is a primary key specified, add an auto-incrementing integer            
            if table.pk:
                species_id += 1
                linevalues = [species_id]
                column = 0
            else:
                linevalues = []
                column = -1 
            for value in line.split(table.delimiter):
                column += 1
                thiscolumn = table.columns[column][1][0]
                # If data type is "skip" ignore the value
                if thiscolumn == "skip":
                    pass
                elif thiscolumn == "combine":
                    # If "combine" append value to end of previous column
                    linevalues[len(linevalues) - 1] += " " + value 
                else:
                    # Otherwise, add new value
                    linevalues.append(value) 
                        
            # Build insert statement with the correct # of values
            insertstatement = "INSERT INTO " + db.dbname + "." + table.tablename + " VALUES ("
            for value in linevalues:
                insertstatement += "%s, "
            insertstatement = insertstatement.rstrip(", ") + ");"

            sys.stdout.write(str(species_id) + "\b" * len(str(species_id)))
            db.cursor.execute(insertstatement, 
                           # Run correct_invalid_value on each value before insertion
                           [table.cleanup(value) for value in linevalues])
            
    print "\n Done!"
    table.source.close()
    
def open_url(table, url):
    """Returns an opened file from a URL, skipping the header lines"""
    source = urllib.urlopen(url)
    source = skip_header(table, source)
    return source

def skip_header(table,source):
    """Skip over the header line by reading it before processing"""
    if table.header_rows > 0:
        for i in range(table.header_rows):
            line = source.readline()
    return source
    
def drop_statement(engine, objecttype, objectname):
    """Returns a db engine specific drop statement"""
    dropstatement = "DROP %s IF EXISTS %s" % (objecttype, objectname)
    if engine == "postgresql":
        dropstatement += " CASCADE"
    return dropstatement
    
def convert_data_type(engine, datatype):
    """Converts DBTK generic data types to db engine specific data types"""
    datatypes = dict()
    datatypes["pk"], datatypes["int"], datatypes["double"], datatypes["char"], datatypes["bit"] = range(5)
    datatypes["combine"], datatypes["skip"] = [-1, -1]
    dbdatatypes = dict()    
    dbdatatypes["mysql"] = ["INT(5) NOT NULL AUTO_INCREMENT", 
                            "INT", 
                            "DOUBLE", 
                            "VARCHAR", 
                            "BIT"]
    dbdatatypes["postgresql"] = ["integer PRIMARY KEY", 
                                 "integer", 
                                 "double precision", 
                                 "varchar", 
                                 "bit"]
{'fraction_non_alphanumeric': 0.09136984372800225, 'fraction_numerical': 0.0033788540053498522, 'mean_word_length': 1.7917076046374534, 'pattern_counts': {'":': 8, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7434643', 'n_tokens_mistral': 3796, 'n_tokens_neox': 3644, 'n_words': 1321}