Python and SQL Bulk Insert

Question

I am using IronPython to create a data array and I need to insert this array to MS SQL Server. The .Net class I'm using is SqlBulkCopy (part of System.Data.SqlClient).

I have found this StackOverflow article quite useful SqlBulkCopy from a list

However, since I'm only starting to learn python I'm having difficulties recreating this C# example with Python code.

Here's what I have so far

import clr
clr.AddReference('System.Data')
from System.Data import *
import clr
clr.AddReference('System')
from System import *
sqlDbConnection = SqlClient.SqlConnection("<my-db-connection-string>")
sqlDbConnection.Open()
myDataArray = [
[Byte(7), Byte(8), Int32(1), Byte(15), Byte(12), Single(0.34324)],
[Byte(5), Byte(1), Int32(2), Byte(11), Byte(10), Single(0.77362)],
[Byte(9), Byte(2), Int32(3), Byte(12), Byte(9), Single(0.93394)]]
sqlDbConnection.Close()

The idea is to generate a large data array (say> 1 Million rows) and import it to my table using bulk copy method. I would really appreciate if anyone could shed a light on this and explain how I could tackle this and how I can make use of all the options that come with the bulk copy class. SqlBulkCopy msdn reference

I have been looking for useful examples in Python but did not find any so far...

Much appreciated

• Simon Opelt: Is this still relevant or did you manage to resolve it yourself? I saw stackoverflow.com/questions/14056891/… which you posted for a similar task in F#.
• Casual Pundit: I managed to work this out as well, I'm posting the answer shortly

Answer 1

Finally got my head around this one, hope this proves helpful:

import clr
clr.AddReference('System.Data')
from System.Data import SqlClient
from System.Data import *
from System.Data import DataTable
import clr
clr.AddReference('System')
from System import *
from System import Console
from System import Data
sqlConnectionString = "<sql-connection-string>"
sqlDbConnection = SqlClient.SqlConnection(sqlConnectionString)
sqlDbConnection.Open()
workTable = DataTable()
workTable.Columns.Add("Col1", Byte)
workTable.Columns.Add("Col2",Byte)
workTable.Columns.Add("Col3", Int32)
workTable.Columns.Add("Col4", Byte)
workTable.Columns.Add("Col5", Byte)
workTable.Columns.Add("Col6", Single)
sampleArray = [Byte(7), Byte(8), Int32(1), Byte(15), Byte(12), Single(0.34324)] 
for i in range (0, 189000) :
 workTable.Rows.Add(Array[object](sampleArray))
cmd = SqlClient.SqlCommand("truncate table dbo.MyTable", sqlDbConnection);
def bulkLoadEsgData (): 
 sbc = SqlClient.SqlBulkCopy(sqlConnectionString, SqlClient.SqlBulkCopyOptions.TableLock, BulkCopyTimeout=0, DestinationTableName="dbo.MyTable")
 sbc.WriteToServer(workTable)
# Start simulation
Console.WriteLine("Enter number of simulations (1 simulation = 189,000 data rows):"+"\n")
strN = Console.ReadLine()
n = int(strN)
cmd.ExecuteNonQuery()
for i in range (0, n):
 bulkLoadEsgData()
sqlDbConnection.Close()
Environment.Exit(1111)

• Simon Opelt: Would you mind improving your code sample for future reference? You should correcly free IDisposable resources (at least for the SqlBulkCopy object, for SqlConnection and SqlCommand if possible) using with as seen in stackoverflow.com/a/1757402/468244 to avoid leaks etc. I would also avoid Environment.Exit in such a code sample because this might be a rough termination and break in a hosted scenario.