to_sql_progress_bar.py

import sqlalchemy as sa
import urllib
import pandas as pd 
import pickle5 as pickle
from tqdm import tqdm


class PandasToSQL:
    """
    This class wrap to_sql function from pandas with tqdm progress bar
    SQL alchemy with fast_executemany parameter for quickness
    
    source: https://stackoverflow.com/a/58698842
    """

    def __init__(self, server = '' , database = '' , username = '' , password = '' ):
        self.server = server
        self.database = database
        self.username = username
        self.password = password

    def chunker(self,seq, size):
        return (seq[pos:pos + size] for pos in range(0, len(seq), size))

    def insert_with_progress(self,dataframe,dbTable):
        conn= urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+self.server+';DATABASE='+self.database+';UID='+self.username+';PWD='+ self.password)
        engine = sa.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn),fast_executemany=True)
        chunksize = int(len(dataframe) / 10)
        with tqdm(total=len(dataframe)) as pbar:
            for i, cdf in enumerate(self.chunker(dataframe, chunksize)):
                replace = "replace" if i == 0 else "append"
                cdf.to_sql(dbTable, schema='dbo', con = engine, index=False, if_exists='append')
                pbar.update(chunksize)
                tqdm._instances.clear()