[Python] Terribly slow loading Pandas Data Frame with 150 columns & 5 millions rows into SQL...

I am trying to load data from Pandas dataframe with 150 columns & 5 million rows. On my machine or prod serverless platform it is taking 4 to 5 hours to load into sql server table.

Table has no indexes & it straight dump. If i use SSIS same the file took only 5 to 8 mins to load. No network or no I/O traffic.

I can utilize SQL Server BULK_INSERT but its against my company policy.

I have used below methods:

1. 
   pandas to_sql
   
   
2. 
   SQL Alchemy Insert()
   

Can you please suggest the best & fast way to load data.

I am using
Driver : ODBC 18 For SQL Server;UID:;PWD:;autocommit=Yes;TrustedServerCertificate=Yes
SQL Alchemy create_engine = ("mssql+pyodbc:///?odbc_connect=%s" params ,fast_executemany=True",isolation_level="AUTOCOMMIT")

Case 1 : Pandas to_sql (This is taking 4 to 5 hours to load)

Here is how i load huge data into dataframe chunk by chunk

data_chunk = pd.Data_Frame()
data_chunk = pd.read_csv('file_path, chunksize=50000,sep='I')
for chunk. in data_chunk:
finaldata = finaldata._append(str)


Loading dataframe to SQL Server table
finaldata.to_sql('Table_Name',conn=engine.connect(),if_exists='append',index=False,chunk_size=50000)

Below process works finewith chunk_size = 10, but i have load millions of records. With chunk_size = 15+, it gives below error

pyodbc.error'07002','[07002] [Microsoft][ODBC Driver 18 for SQL Server][COUNT field incorrect or syntax error (0) (SQLExecDirectw)]
​

Case 3 : SQL Alchemy insert()

Here is how i load huge data to Chunk ->DataFrame->SQL Server table using SQL Alchemy Insert()

data_chunk = pd.Data_Frame()
data_chunk = pd.read_csv('file_path, chunksize=10,sep='I')
for chunk. in data_chunk:
finaldata = finaldata._append(str)


Loading dataframe to SQL Server table

with in the same for loop

Session = sessionmaker(bind=engine)
session = Session()
data_to_insert = finaldata.to_dict(orient='records')
stmt = insert(table).values(data_to_insert)
session.execute(stmt)
session.commit()
session.close()


how to load data faster?

Continue reading...