basic pyodbc bulk insert

Here's a function that can do the bulk insert into SQL Server database.

import pyodbc
import contextlib

def bulk_insert(table_name, file_path):
    string = "BULK INSERT {} FROM '{}' (WITH FORMAT = 'CSV');"
    with contextlib.closing(pyodbc.connect("MYCONN")) as conn:
        with contextlib.closing(conn.cursor()) as cursor:
            cursor.execute(string.format(table_name, file_path))
        conn.commit()

This definitely works.

UPDATE: I've noticed at the comments, as well as coding regularly, that pyodbc is better supported than pypyodbc.

NEW UPDATE: remove conn.close() since the with statement handles that automatically.

The best way to handle this is to use the pyodbc function executemany.

ds1Cursor.execute(selectSql)
result = ds1Cursor.fetchall()


ds2Cursor.executemany('INSERT INTO [TableName] (Col1, Col2, Col3) VALUES (?, ?, ?)', result)
ds2Cursor.commit()