4

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

Community
  • 1
  • 1
Casual Pundit
  • 279
  • 2
  • 10
  • Is this still relevant or did you manage to resolve it yourself? I saw http://stackoverflow.com/questions/14056891/f-datatable-to-sql-using-sqlbulkcopy which you posted for a similar task in F#. – Simon Opelt Dec 29 '12 at 14:02
  • I managed to work this out as well, I'm posting the answer shortly – Casual Pundit Dec 31 '12 at 09:53

1 Answers1

4

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)
Casual Pundit
  • 279
  • 2
  • 10
  • 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 http://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. – Simon Opelt Jan 01 '13 at 18:02