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

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#. — Simon Opelt
– Simon Opelt, Commented Dec 29, 2012 at 14:02
I managed to work this out as well, I'm posting the answer shortly — Casual Pundit
– Casual Pundit, Commented Dec 31, 2012 at 9:53

Casual Pundit · Accepted Answer · 2012-12-31 09:54:55Z

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)

answered Dec 31, 2012 at 9:54

Casual Pundit

2792 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Opelt Over a year ago

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.

Collectives™ on Stack Overflow

Python and SQL Bulk Insert

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related