Answers for "pandas insert in sql server"

ms sql server python pandas insert

#Connection to be added in to /etc/odbc.ini in non windows machine

[ConnectionName]
Driver=<DriverAddress>
Description=<Description String>
Trace=No
Server=<Server/HostName>
Port=<Port Number>
Database=<DatabaseName>

#python
#for odbc connection and cursor
import pyodbc
#for mssql+pyodbc connection (mostly used for pandas) faster exectution
import sqlalchemy as sal

db_conn = pyodbc.connect('DSN=<ConnectionName>;UID=<username>;PWD=<pass>;TDS_Version=8.0;ClientCharset=UTF-8')
db_cur = db_conn.cursor()

#some dataframe
df 
#rearrange the df column name according to table name
df = df[['col1', 'col2', 'col3', 'col4', 'col5', 'col6']]
cols = ",".join([str(i) for i in df.columns.tolist()])
#code to insert using pyodbc
 rows = 0
 for i,row in df.iterrows():
   ins_sql = "INSERT INTO <TableName> (" +cols + ") VALUES (" + "?,"*(len(row)-1) + "?)"
   db_cur.execute(ins_sql, tuple(row))
   #to commit after each 10000 rows
   rows += 1
   if rows == 10000:
       db_conn.commit()
       print(f'commiting : {rows}')
       rows = 0

#create engine and connection using SQL Alchemy
engine = sal.create_engine("mssql+pyodbc://<username>:<pass>@<DSNname>")
conn = engine.connect()
#dataframe to sql
df.to_sql(name='<table_name>', schema = 'dbo',con=conn, if_exists='append', index=False)