ms sql server python pandas insert

1#Connection to be added in to /etc/odbc.ini in non windows machine
2
3[ConnectionName]
4Driver=<DriverAddress>
5Description=<Description String>
6Trace=No
7Server=<Server/HostName>
8Port=<Port Number>
9Database=<DatabaseName>
10
11#python
12#for odbc connection and cursor
13import pyodbc
14#for mssql+pyodbc connection (mostly used for pandas) faster exectution
15import sqlalchemy as sal
16
17db_conn = pyodbc.connect('DSN=<ConnectionName>;UID=<username>;PWD=<pass>;TDS_Version=8.0;ClientCharset=UTF-8')
18db_cur = db_conn.cursor()
19
20#some dataframe
21df 
22#rearrange the df column name according to table name
23df = df[['col1', 'col2', 'col3', 'col4', 'col5', 'col6']]
24cols = ",".join([str(i) for i in df.columns.tolist()])
25#code to insert using pyodbc
26 rows = 0
27 for i,row in df.iterrows():
28   ins_sql = "INSERT INTO <TableName> (" +cols + ") VALUES (" + "?,"*(len(row)-1) + "?)"
29   db_cur.execute(ins_sql, tuple(row))
30   #to commit after each 10000 rows
31   rows += 1
32   if rows == 10000:
33       db_conn.commit()
34       print(f'commiting : {rows}')
35       rows = 0
36
37#create engine and connection using SQL Alchemy
38engine = sal.create_engine("mssql+pyodbc://<username>:<pass>@<DSNname>")
39conn = engine.connect()
40#dataframe to sql
41df.to_sql(name='<table_name>', schema = 'dbo',con=conn, if_exists='append', index=False)
42