Dominic Pham
03/26/2022, 12:22 AMKevin Kho
03/26/2022, 1:45 AMAnna Geller
03/26/2022, 9:00 AMDominic Pham
03/28/2022, 5:25 PMAnna Geller
03/28/2022, 6:13 PM@task
def load_data_to_sql_server(dataframe, some_table_name):
your_conn_str = None # define it
create_tmp_tbl_stmt = f"""select top 0 * into #temp_table from {some_table_name}"""
# OR: never true so that it always returns 0 rows, just to get the table structure
# create_tmp_tbl_stmt = f"""select * into #temp_table from some_table_name where 1=2"""
insert_to_tmp_tbl_stmt = f"""insert into #temp_table VALUES (?,?,?,?,?,?,?,?,?,?)"""
tuples = dataframe.values.tolist()
with pyodbc.connect(your_conn_str, autocommit=True) as conn:
cursor = conn.cursor()
cursor.fast_executemany = True
cursor.execute(create_tmp_tbl_stmt)
cursor.executemany(insert_to_tmp_tbl_stmt, tuples)
print(f"{len(dataframe)} rows inserted to the Temp table")
cursor.commit()
cursor.close()
conn.close()
the above code works with both local and global temp table, because everything runs in one session
check this if you need more info on that https://stackoverflow.com/a/39278050/9509388Dominic Pham
03/28/2022, 6:39 PMAnna Geller
03/28/2022, 6:43 PM