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)
