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)