<commit_before>import sqlalchemy
import pyodbc
import pandas as pd
import sqlite3

import healthcareai.common.database_connection_validation as db_validation


def build_mssql_connection_string(server):
    # https://www.connectionstrings.com/
    return 'DRIVER={SQL Server Native Client 11.0}; SERVER={}; Trusted_Connection=yes;'.format(server)


def build_mysql_connection_string(server, database, userid, password):
    return 'Server={};Database={};Uid={};Pwd={}; '.format(server, database, userid, password)


def build_sqlite_connection_string(file_path):
    # https://www.connectionstrings.com/
    return 'Data Source={};Version=3;'.format(file_path)


def build_sqlite_in_memory_connection_string():
    # https://www.connectionstrings.com/
    return 'Data Source=:memory:;Version=3;New=True;'


def write_to_mssql(server, destination_db_schema_table, predicted_column_name, grain_column, output_2dlist):
    connection_string = "{}".format(server, destination_db_schema_table)
    dataframe = pd.DataFrame(output_2dlist)


def write_to_sqlite(db_filename, predicted_column_name, grain_column, output_2dlist):
    connection_string = "{}".format()
    # TODO nuke the 2d list portion - why do this if pandas is the end?
    dataframe = pd.DataFrame(output_2dlist)

    try:
        pass
    except error
        raise


def write_to_mysql(server, destination_db_schema_table, predicted_column_name, grain_column, output_2dlist):
    connection_string = "{}".format(server, destination_db_schema_table)
    # TODO nuke the 2d list portion - why do this if pandas is the end?
    dataframe = pd.DataFrame(output_2dlist)


def db_agnostic_writing(connection_string, dataframe):
    # TODO nuke the 2d list portion - why do this if pandas is the end?
    # TODO Validate inputs
    try:
        engine = sqlalchemy.create_engine(connection_string)

        dataframe.to_sql(engine)

        # Todo: count and display (via pyodbc) how many rows inserted
        print("\nSuccessfully inserted rows into {}.".
              format(destination_db_schema_table))

    # TODO need to find a good list of errors to catch here and how to test them
    except RuntimeError:
        print("\nFailed to insert values into {}.".format(destination_db_schema_table))
        print("Was your test insert successful earlier?")
        print("If so, what has changed with your entity since then?")

        # TODO make another method that takes a connection string
        db_validation.validate_destination_table_connection(server, destination_db_schema_table, grain_column,
                                                            predicted_column_name)


def write_predictions_to_database(server, destination_db_schema_table, predicted_column_name, grain_column,
                                  output_2dlist):
    db_connection = pyodbc.connect("""DRIVER={SQL Server Native Client 11.0};
                               SERVER=""" + server + """;
                               Trusted_Connection=yes;""")
    cursor = db_connection.cursor()
    try:
        cursor.executemany("""insert into """ + destination_db_schema_table + """
                           (BindingID, BindingNM, LastLoadDTS, """ +
                           grain_column + """,""" + predicted_column_name + """,
                           Factor1TXT, Factor2TXT, Factor3TXT)
                           values (?,?,?,?,?,?,?,?)""", output_2dlist)
        db_connection.commit()

        # Todo: count and display (via pyodbc) how many rows inserted
        print("\nSuccessfully inserted rows into {}.".
              format(destination_db_schema_table))

    except pyodbc.DatabaseError:
        print("\nFailed to insert values into {}.".
              format(destination_db_schema_table))
        print("Was your test insert successful earlier?")
        print("If so, what has changed with your entity since then?")

        db_validation.validate_destination_table_connection(server, destination_db_schema_table, grain_column,
                                                            predicted_column_name)

    finally:
        try:
            db_connection.close()
        except pyodbc.DatabaseError:
            print("""\nAn attempt to complete a transaction has failed.
                  No corresponding transaction found. \nPerhaps you don't
                  have permission to write to this server.""")<commit_msg>* different approach - generate connection strings to decrease dependency requirements * nuke old db writer * implement record counts<commit_after>import sqlalchemy
import pandas as pd

import healthcareai.common.database_connection_validation as db_validation


def build_mssql_connection_string(server):
    # https://www.connectionstrings.com/
    return 'DRIVER={SQL Server Native Client 11.0}; SERVER={}; Trusted_Connection=yes;'.format(server)


def build_mysql_connection_string(server, database, userid, password):
    return 'Server={};Database={};Uid={};Pwd={}; '.format(server, database, userid, password)


def build_sqlite_connection_string(file_path):
    # https://www.connectionstrings.com/
    return 'Data Source={};Version=3;'.format(file_path)


def build_sqlite_in_memory_connection_string():
    # https://www.connectionstrings.com/
    return 'Data Source=:memory:;Version=3;New=True;'


def db_agnostic_writing(connection_string, destination_db_schema_table, dataframe):
    # TODO nuke the 2d list portion - why do this if pandas is the end?
    # TODO Validate input types

    try:
        # Set up engine
        engine = sqlalchemy.create_engine(connection_string)

        # Count before
        before_count = pd.read_sql('select count(*) from {}'.format(destination_db_schema_table), engine).iloc[0][0]

        # Insert
        dataframe.to_sql(engine)

        # Count after
        after_count = pd.read_sql('select count(*) from {}'.format(destination_db_schema_table), engine).iloc[0][0]
        delta = after_count - before_count

        print('\nSuccessfully inserted {} rows. Dataframe contained {} rows'.format(delta, len(dataframe)))

    # TODO need to find a good list of errors to catch here and how to test them
    except RuntimeError:
        print("\nFailed to insert values into {}.".format(destination_db_schema_table))
        print("Was your test insert successful earlier?")
        print("If so, what has changed with your entity since then?")

        # TODO make another method that takes a connection string
        db_validation.validate_destination_table_connection(server, destination_db_schema_table, grain_column,
                                                            predicted_column_name)
{'fraction_non_alphanumeric': 0.0920006003301816, 'fraction_numerical': 0.00495272399819901, 'mean_word_length': 3.1314321140731556, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 6, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5895487', 'n_tokens_mistral': 1825, 'n_tokens_neox': 1771, 'n_words': 476}