<commit_before>#!/usr/bin/env python
import pyodbc
import json
from toolbox import process_data_row
from files import (get_schema_file, loop_delimited_file)
from toolbox import _defaultencode

def connect(server, database, username, password):
    """Build pyodbc connection to SQL Server from file, assuming driver name is "ODBC Driver 11 for SQL Server"

        Args:
            server: string, name or ip address for SQL Server
            database: string, database name
            username: string, useranme for the database
            password: string, password for the database
    
        Returns:
            pyodbc connection object
    """
    try:
        connect_string = 'DRIVER={FreeTDS};SERVER=' + server.encode('utf-8') + ';PORT=1433;DATABASE=' + database.encode(
            'utf-8') + ';UID=' + username.encode('utf-8') + ';PWD=' + password.encode('<PASSWORD>')
        connection = pyodbc.connect(connect_string)
    except (ValueError) as e:
        print "Error creating database connection", e
        raise e

    return connection


def insert_list_to_sql(connection,lst,tableName):
    """Inserts from a list to a SQL table.  List must have the same format and item order as the table columns.
        Args:
            list: list, Values to insert to table
            tableName: string, Fully qualified SQL table name

        Returns:
            None
    """ 
    sorted_column_values_list = []
    for items in lst:
        sorted_column_values_list.append(items)

    for val in sorted_column_values_list:
        valstring = '('
        for colval in val:
            try:
                valstring += "'" + colval + "',"
            except TypeError:
                valstring += str(colval) +','
        valstring = valstring[0:-1] + ')' #remove trailing comma
        query = "INSERT INTO {0} VALUES {1}".format(tableName, valstring)

        c = run_sql(connection,query)
        #print type(c)
    return

def insert_list_to_sql_batch(connection,lst,tableName,batchsize=1000):
    """Inserts from a list to a SQL table.  List must have the same format and item order as the table columns.
        Args:
            list: list, Values to insert to table
            tableName: string, Fully qualified SQL table name
            batchsize: specifies what size you'd want the batches to run as
            connection: sql server connection

        Returns:
            None
    """ 
    insertvals = ''
    batchcnt = 0
    lstcnt = 0
    lstsize = len(lst)
    rowstr = 'SELECT '
    for row in lst:
        if batchcnt == batchsize or lstcnt == lstsize:
            for val in row:
                if type(val) == int or val == 'null':
                    rowstr += str(val) +','
                else:
                    rowstr += "'" + str(val) + "',"
            insertvals = insertvals + rowstr[:-1] + ' UNION ALL '
            c = run_sql(connection,"INSERT INTO {0} {1}".format(tableName, insertvals[:-11]))
            insertvals = ''
            rowstr = 'SELECT '
            batchcnt = 0
        else:
            for val in row:
                    if type(val) == int or val == 'null':
                        rowstr += str(val) +','
                    else:
                        rowstr += "'" + str(val) + "',"
            insertvals = insertvals + rowstr[:-1] + ' UNION ALL '
            rowstr = 'SELECT '
            batchcnt += 1
            lstcnt += 1

    if batchcnt > 0:
        c = run_sql(connection,"INSERT INTO {0} {1}".format(tableName, insertvals[:-11]))

    return


def run_sql(connection,query): #courseTagDict
    """Runs SQL statement and commits changes to database.
        
        Args:
            connection: pyodbc.connect() object, Connection to use when running Sql 
            query: string, Valid query string

        Returns:
            cursor object, Results of the call to pyodb.connection().cursor().execute(query)
    """ 
    cursor=connection.cursor()
    cursor.execute(query.encode('utf-8'))
    connection.commit()

    return cursor


def truncate_sql_table(connection,table_name):
    """Runs truncate table SQL command and commits changes to database.
        
        Args:
            connection: pyodbc.connect() object, Connection to use for truncate
            tableName: string, Fully qualified SQL table name (make sure this is the table you want to clear!)

        Returns:
            None
    """ 
    sql = "truncate table " + table_name
    cursor=connection.cursor()
    cursor.execute(sql.encode('utf-8'))
    connection.commit()

    return


def create_table(connection, table_name, schema_file, index):  # courseTagDict
    """Runs SQL statement and commits changes to database.

        Args:
            connection: pyodbc.connect() object, Connection to use when running Sql
            table_name: string, Table name including db schema (ex: my_schema.my_table)
            schema_file: string, Path to csv schema file with each row as col_name, data_type
            index: string, Column name of index (can put multiple columns comma delimited if desired)
        Returns:
            cursor object, Results of the call to pyodb.connection().cursor().execute(query)
    """
    cursor = connection.cursor()
    schema_list = get_schema_file(schema_file)

    ddl = """IF NOT EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = '{0}' )'
        CREATE TABLE {0} (""".format(table_name)
    for col, dt in schema_list:
        ddl = ddl + col + ' ' + dt + ', '
    ddl = ddl[:-2] + ');'

    try:
        cursor.execute(ddl.encode('utf-8'))
    except UnicodeDecodeError:
        cursor.execute(ddl)

    if index is not None:
        idx_name = table_name + '_idx'
        exists = run_sql(connection, "SELECT to_regclass('{0}')".format(idx_name))
        if exists.fetchone()[0] != idx_name:
            index_name = table_name.split('.')[-1] + '_idx'
            ddl2 = 'CREATE INDEX {0} ON {1}({2});'.format(index_name, table_name, index)
            try:
                cursor.execute(ddl2.encode('utf-8'))
            except UnicodeDecodeError:
                cursor.execute(ddl2)

    connection.commit()
    return cursor


def sql_get_schema(connection,query,include_extract_date = True):
    """Reads schema from database by running the provided query.  It's recommended to
    pass a query that is limited to 1 record to minimize the amount of rows accessed on 
    the server.

        Args:<commit_msg>Fix mssql create table<commit_after>#!/usr/bin/env python
import pyodbc
import json
from toolbox import process_data_row
from files import (get_schema_file, loop_delimited_file)
from toolbox import _defaultencode

def connect(server, database, username, password):
    """Build pyodbc connection to SQL Server from file, assuming driver name is "ODBC Driver 11 for SQL Server"

        Args:
            server: string, name or ip address for SQL Server
            database: string, database name
            username: string, useranme for the database
            password: string, password for the database
    
        Returns:
            pyodbc connection object
    """
    try:
        connect_string = 'DRIVER={FreeTDS};SERVER=' + server.encode('utf-8') + ';PORT=1433;DATABASE=' + database.encode(
            'utf-8') + ';UID=' + username.encode('utf-8') + ';PWD=' + password.encode('utf-8')
        connection = pyodbc.connect(connect_string)
    except (ValueError) as e:
        print "Error creating database connection", e
        raise e

    return connection


def insert_list_to_sql(connection,lst,tableName):
    """Inserts from a list to a SQL table.  List must have the same format and item order as the table columns.
        Args:
            list: list, Values to insert to table
            tableName: string, Fully qualified SQL table name

        Returns:
            None
    """ 
    sorted_column_values_list = []
    for items in lst:
        sorted_column_values_list.append(items)

    for val in sorted_column_values_list:
        valstring = '('
        for colval in val:
            try:
                valstring += "'" + colval + "',"
            except TypeError:
                valstring += str(colval) +','
        valstring = valstring[0:-1] + ')' #remove trailing comma
        query = "INSERT INTO {0} VALUES {1}".format(tableName, valstring)

        c = run_sql(connection,query)
        #print type(c)
    return

def insert_list_to_sql_batch(connection,lst,tableName,batchsize=1000):
    """Inserts from a list to a SQL table.  List must have the same format and item order as the table columns.
        Args:
            list: list, Values to insert to table
            tableName: string, Fully qualified SQL table name
            batchsize: specifies what size you'd want the batches to run as
            connection: sql server connection

        Returns:
            None
    """ 
    insertvals = ''
    batchcnt = 0
    lstcnt = 0
    lstsize = len(lst)
    rowstr = 'SELECT '
    for row in lst:
        if batchcnt == batchsize or lstcnt == lstsize:
            for val in row:
                if type(val) == int or val == 'null':
                    rowstr += str(val) +','
                else:
                    rowstr += "'" + str(val) + "',"
            insertvals = insertvals + rowstr[:-1] + ' UNION ALL '
            c = run_sql(connection,"INSERT INTO {0} {1}".format(tableName, insertvals[:-11]))
            insertvals = ''
            rowstr = 'SELECT '
            batchcnt = 0
        else:
            for val in row:
                    if type(val) == int or val == 'null':
                        rowstr += str(val) +','
                    else:
                        rowstr += "'" + str(val) + "',"
            insertvals = insertvals + rowstr[:-1] + ' UNION ALL '
            rowstr = 'SELECT '
            batchcnt += 1
            lstcnt += 1

    if batchcnt > 0:
        c = run_sql(connection,"INSERT INTO {0} {1}".format(tableName, insertvals[:-11]))

    return


def run_sql(connection,query): #courseTagDict
    """Runs SQL statement and commits changes to database.
        
        Args:
            connection: pyodbc.connect() object, Connection to use when running Sql 
            query: string, Valid query string

        Returns:
            cursor object, Results of the call to pyodb.connection().cursor().execute(query)
    """ 
    cursor=connection.cursor()
    cursor.execute(query.encode('utf-8'))
    connection.commit()

    return cursor


def truncate_sql_table(connection,table_name):
    """Runs truncate table SQL command and commits changes to database.
        
        Args:
            connection: pyodbc.connect() object, Connection to use for truncate
            tableName: string, Fully qualified SQL table name (make sure this is the table you want to clear!)

        Returns:
            None
    """ 
    sql = "truncate table " + table_name
    cursor=connection.cursor()
    cursor.execute(sql.encode('utf-8'))
    connection.commit()

    return


def create_table(connection, table_name, schema_file, index):  # courseTagDict
    """Runs SQL statement and commits changes to database.

        Args:
            connection: pyodbc.connect() object, Connection to use when running Sql
            table_name: string, Table name including db schema (ex: my_schema.my_table)
            schema_file: string, Path to csv schema file with each row as col_name, data_type
            index: string, Column name of index (can put multiple columns comma delimited if desired)
        Returns:
            cursor object, Results of the call to pyodb.connection().cursor().execute(query)
    """
    cursor = connection.cursor()
    schema_list = get_schema_file(schema_file)

    ddl = """IF NOT EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = '{0}' )
        CREATE TABLE {0} (""".format(table_name)
    for col, dt in schema_list:
        ddl = ddl + col + ' ' + dt + ', '
    ddl = ddl[:-2] + ');'

    try:
        cursor.execute(ddl.encode('utf-8'))
    except UnicodeDecodeError:
        cursor.execute(ddl)

    if index is not None:
        idx_name = table_name + '_idx'
        exists = run_sql(connection, "SELECT to_regclass('{0}')".format(idx_name))
        if exists.fetchone()[0] != idx_name:
            index_name = table_name.split('.')[-1] + '_idx'
            ddl2 = 'CREATE INDEX {0} ON {1}({2});'.format(index_name, table_name, index)
            try:
                cursor.execute(ddl2.encode('utf-8'))
            except UnicodeDecodeError:
                cursor.execute(ddl2)

    connection.commit()
    return cursor


def sql_get_schema(connection,query,include_extract_date = True):
    """Reads schema from database by running the provided query.  It's recommended to
    pass a query that is limited to 1 record to minimize the amount of rows accessed on 
    the server.

        Args:
{'fraction_non_alphanumeric': 0.10044175773076029, 'fraction_numerical': 0.007827636983647215, 'mean_word_length': 2.1948502104481307, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 6, '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': '1724359', 'n_tokens_mistral': 3699, 'n_tokens_neox': 3520, 'n_words': 1164}