How to Run the SQL Server BULK INSERT Command from Within a Python Program - Article on SQLNetHub

How to Run the SQL Server BULK INSERT Command from Within a Python Program

by

Hi friends. In this article, we will see step by step, how to run the SQL Server BULK INSERT command from within a Python Program.

This is an approach, that indeed helps importing the contents of large data files into SQL Server faster, since logging is minimal.

Note that this article, is from our online course on Udemy, titled “Working with Python on Windows and SQL Server Databases“.

If you want to learn more on how to work with SQL Server from within your Python program, you should check the course.

 

This Example’s Scenario and Sample File

For this article’s example, we will be importing into SQL Server, a CSV file, for which we will be calling the BULK INSERT command, from our Python code.

The full path for the csv file in this example is: C:\Demos\sample-csv-file-for-demo.csv

Also, we use a semicolon (;) for separating the fields of each record in the file.

Let’s take a look at the sample csv file:

How to Run the SQL Server BULK INSERT Command from Within a Python Program – Article on SQLNetHub

 

High Level Process for Using BULK INSERT in a Python Program

In order to implement this approach from within your Python program, you would need to follow the below high level steps:

  1. Assemble the CREATE TABLE command for the table into which the data will be imported
  2. Execute the CREATE TABLE command from within your Python program using a cursor
  3. Assemble the BULK INSERT command for the file to be imported
  4. Execute the BULK INSERT command using a cursor

Below, we analytically explain each step.

 

Step 1: Assemble the CREATE TABLE SQL Command

The first step, is to assemble the proper CREATE TABLE SQL command, from within your Python code, which will be used for creating the table in SQL Server, that will be used for importing the file.

Based on the above sample file, I’m defining the CREATE TABLE command, using the below string, in my Python code:

# Create the table tblCustomersFromCSV
createTableTSQL="""CREATE TABLE [dbo].[tblCustomersFromCSV](
[id] [int] NOT NULL,
[code] [varchar](50) NULL,
[firstName] [varchar](50) NULL,
[lastName] [varchar](50) NULL,
)"""

 

Step 2: Execute the CREATE TABLE Command with a Cursor in Python

The next step, is to execute the above command from within your Python code, in order to indeed create the table in the corresponding SQL Server database. To do this, you need a cursor.

So, let’s say, if the instance to connect to is the local named instance “.\SQL2K19”, the process would be:

  1. Create the connection
  2. Create the cursor based on the connection
  3. Define the CREATE TABLE SQL command string
  4. Execute the CREATE TABLE command using the cursor
  5. Commit the changes (I’m not closing the cursor and the connection since I will be using them later for the BULK INSERT command)

 

Here’s the code block for all the above:

# Trusted Connection to Named Instance 
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQL2K19;DATABASE=SampleDB;Trusted_Connection=yes;')

cursor=connection.cursor()

# Create the table tblCustomersFromCSV
createTableTSQL="""CREATE TABLE [dbo].[tblCustomersFromCSV](
[id] [int] NOT NULL,
[code] [varchar](50) NULL,
[firstName] [varchar](50) NULL,
[lastName] [varchar](50) NULL,
)"""

cursor.execute(createTableTSQL)
connection.commit()

 

Step 3: Assemble the BULK INSERT Command for the File to be Imported

The next step, is to assemble the BULK INSERT command for the file to be imported.

So, based on this example’s scenario and sample file (see the beginning of the article), this is how we construct the BULK INSER Command string:

bulkInsertCommand="""BULK INSERT tblCustomersFromCSV
FROM 'C:\Demos\sample-csv-file-for-demo.csv'  
WITH   
  (  
     FIELDTERMINATOR =';',  
     ROWTERMINATOR ='\n'  
  );"""

 

Step 4: Execute the BULK INSERT Command Using a Cursor

The fourth and final step, is to execute the BULK INSERT command using a cursor, from within your Python code.

To this end, based on our example, this is how we implement this:

cursor.execute(bulkInsertCommand)
connection.commit()

 

Full Code Example: Import File in SQL Server Table Using BULK INSERT from within a Python Program

Below, you can check the full code example, for importing a file in a SQL Server table, using the BULK INSERT command, from within a Python program.

# BULK_INSERT_CSV_SQLServer.py
#
# Created for the online course on Udemy: "Working with Python® on Windows® and SQL Server® Databases"
#
# Course URL: 
# https://www.udemy.com/course/python-windows-sql-server
#
# Author/Instructor: Artemakis Artemiou
#
# Disclaimer: This source code which is part of the online course on Udemy "Working with Python® on Windows® 
# and SQL Server® Databases", is intended to be used only for demo purposes. Do not 
# use it for Production systems as it is simplified for demo purposes.
#

# BULK INSERT CSV File Contents to SQL Server Table
# Note 1: The data file to be imported via the BULK INSERT command, must be located on the database server.
# Note 2: For this example, I'm using C:\Demos\sample-csv-file-for-demo.csv and I use semicolon for separating the

# Import modules
import pyodbc

print()

# Exception Handling
try:

    # Trusted Connection to Named Instance 
    connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQL2K19;DATABASE=SampleDB;Trusted_Connection=yes;')

    cursor=connection.cursor()

    # Create the table tblCustomersFromCSV
    createTableTSQL="""CREATE TABLE [dbo].[tblCustomersFromCSV](
  [id] [int] NOT NULL,
  [code] [varchar](50) NULL,
  [firstName] [varchar](50) NULL,
  [lastName] [varchar](50) NULL,
    )"""
    
    cursor.execute(createTableTSQL)
    connection.commit()

    # SELECT Query Before BULK INSERT
    cursor.execute("SELECT * FROM tblCustomersFromCSV")

    print("[Before BULK INSERT...]")
    while 1:
        row = cursor.fetchone()
        if not row:
            break
        print(row.id,row.code,row.firstName,row.lastName)

    print()

    #Read CSV File and Insert Rows to SQL Server Table via the BULK INSERT COMMAND
    bulkInsertCommand="""BULK INSERT tblCustomersFromCSV
    FROM 'C:\Demos\sample-csv-file-for-demo.csv'  
    WITH   
      (  
         FIELDTERMINATOR =';',  
         ROWTERMINATOR ='\n'  
      );"""
    print("Running BULK INSERT command...")    
    cursor.execute(bulkInsertCommand)
    connection.commit()
    print("BULK INSERT command executed.")    

            
    print()

    # SELECT Query After INSERT
    cursor.execute("SELECT * FROM tblCustomersFromCSV")

    print("[After BULK INSERT...]")
    while 1:
        row = cursor.fetchone()
        if not row:
            break
        print(row.id,row.code,row.firstName,row.lastName)


    cursor.close()
    connection.close()

except pyodbc.Error as ex:
    print("Exception: ",ex)
    cursor.close()
    connection.close()
    print("Closing program...")
    print()
    exit()

print()

 

Learn more about SQL Server Data Access from Python – Enroll to our Course!

Enroll to our online course  “Working with Python on Windows and SQL Server Databases” with an exclusive discount, and get started with Python data access programming for SQL Server databases, fast and easy!

Working with Python on Windows and SQL Server Databases - Online Course
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Check our online courses!

Check our eBooks!

Subscribe to our YouTube channel!

Subscribe to our newsletter and stay up to date!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 6,709