Python and SQL Bulk Insert

.net ironpython python sqlbulkcopy sql-server

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

1
3
5/23/2017 12:31:45 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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)
2
12/31/2012 9:54:55 AM

Related Questions

How to use SQL bulk copy to retrieve the IDs of added data records

I'm currently doing something like this:...DataTable objects = new DataTable(); DataColumn keyColumn = new DataColumn("name", typeof(string)); DataColumn versionColumn = new DataColumn("version", typeof(int)); versionColumn.DefaultValue = iVersionID; objects.Columns.Add(keyColumn); objects.Columns.Add(versionColumn); foreach (KeyValuePair<string,...
ado.net database identity sqlbulkcopy sql-server
asked by Timbo

OLEDB IRowsetFastLoad vs. Ado.Net SqlBulkCopy for SQL Bulk Data Insertion

SqlBulkCopy... is the managed equivalent of ...IRowsetFastLoad..., they should perform similarly. In the client, as a general rule, OleDB is faster than ADO.Net due to the availability of bindings, which allow for a faster transfer of data in and out the API (less memcopy required because the buffers are known ahead, fixed and pre-allocated). ADO.N...
ado.net oledb sqlbulkcopy sql-server
asked by Remus Rusanu

ASP.Net to MS-Access 2007 conversion (Bulk Insert, Bulk Copy)

Access 2007 will directly import XML. You can create an XSD for your particular data, then import that to create the table definitions. The XML should cleanly import from there.
asp.net sqlbulkcopy vb.net xml
asked by Karelzarath

Is the order retained in a SQL bulk insert with parent/child relationships?

This is not an absolut bulk insert, but instead it inserts all child data at the same time with the parent data, making only 1 round trip to the DB....insert into parent(groupcode, parentdata) values(1, 'parent data'); insert into child(parentrecno, childdata) select parentrecno, childdata from ( select SCOPE_IDENTITY() as parentrecno, 'child d...
c# sqlbulkcopy sql-server-2008
asked by Fede

SQL bulk insert does not insert values properly into table

As a follow-up to my earlier comment, since you're only missing the first row, it sounds like you need to add ...HDR=No... to your connection string. ...It should look something like this:...Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'; ...--EDIT ...Please note the added apostrophes....Good luck.
.net excel sqlbulkcopy sql-server-2008-r2
asked by sgeddes

Precision Lost while Bulk Inserting records into SQL using SqlBulkCopy

The decimal precision of a double is not fixed, even if it's a 128-bit double or higher. So it's not an "rounding error". Simply said a double is not guaranteed to represent all real numbers within a limited decimal point range. This loss of precision increase even more when trying to store bigger numbers or numbers that is very close to zero. ...
.net bulkinsert sql sqlbulkcopy sql-server-2008
asked by Kaveh Hadjari

Missing Data after insert into my end sql table using Sql Bulk Copy

you need to look at the data in Access, and find/fix those bad column values, or handle them in your TSQL after the export. One option to debug - replace temp table with real table, replace datetime datatype with string, do your export, then query the table to find the bad values so you know what kind of data to look for. After that, you can swit...
c# console-application datatable sqlbulkcopy sql-server-2008
asked by Greg

Using SQL Bulk Copy to insert ~3.7 million rows throws OutOfMemory exception

Thank you all for your response....I figured out what was the problem. Firstly, it didn't occur when calling the SQL Bulk Copy, which I assumed it could have been because, in previous run, I was able to run few 1000 less records than the current one, so I thought the SQL Bulk Copy had limitations or need to settings in order to handle large number ...
c# sqlbulkcopy sql-server
asked by superachu

SQL Bulk Insert in C# not inserting values

Here is the sample for reading schema information from Excel (here we read the tablenames - sheet names with tables in them):...private IEnumerable<string> GetTablesFromExcel(string dataSource) { IEnumerable<string> tables; using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + string.Format("Data Source...
c# oledbdatareader sqlbulkcopy
asked by Cetin Basoz

Unable to insert null values in SQL Table using SQL bulk

I guess the issue is that you are trying to convert a null value to a string and then try to put it in a field that only accepts datetime. Check if there is anything (so notnull) before converting it to string.
excel sqlbulkcopy sql-server
asked by David

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow