mssql_push_dataframe_short.py

def push_dataframe(self, data, table="raw_data", batchsize=500):
    # create execution cursor
    cursor = self.cnxn.cursor()
    # activate fast execute
    cursor.fast_executemany = True

    # create create table statement
    query = "CREATE TABLE [" + table + "] (\n"

    # iterate through each column to be included in create table statement
    for i in range(len(list(data))):
        query += "\t[{}] varchar(255)".format(list(data)[i])  # add column (everything is varchar for now)
        # append correct connection/end statement code
        if i != len(list(data))-1:
            query += ",\n"
        else:
            query += "\n);"

    cursor.execute(query)  # execute the create table statement
    self.cnxn.commit()  # commit changes

    # append query to our SQL code logger
    self.query += ("\n\n-- create table\n" + query)

    # insert the data in batches
    query = ("INSERT INTO [{}] ({})\n".format(table,
                                              '['+'], ['  # get columns
                                              .join(list(data)) + ']') +
             "VALUES\n(?{})".format(", ?"*(len(list(data))-1)))

    # insert data into target table in batches of 'batchsize'
    for i in range(0, len(data), batchsize):
        if i+batchsize > len(data):
            batch = data[i: len(data)].values.tolist()
        else:
            batch = data[i: i+batchsize].values.tolist()
        # execute batch insert
        cursor.executemany(query, batch)
        # commit insert to SQL Server
        self.cnxn.commit()