This post is not going to have much theory.
Here was my problem. Python and Pandas are excellent tools for munging data but if you want to store it long term a DataFrame is not the solution, especially if you need to do reporting. That’s why Edgar Codd discovered, and Michael Stonebreaker implemented, relational databases.
Obviously, if I had the choice I wouldn’t be using Microsoft SQL Server [MSS]. If I could, I would do this whole exercise in KDB, but KDB is expensive and not that good at storing long strings. Other relational databases might have better integration with Python, but at an enterprise MSS is the standard, and it supports all sorts of reporting.
So my task was to load a bunch of data about twenty thousand rows — in the long term we were going to load one hundred thousand rows an hour — into MSS.
Pandas is an amazing library built on top of numpy, a pretty fast C implementation of arrays.
Pandas has a built-in to_sql
method which allows anyone with a pyodbc engine to send their DataFrame into sql. Unfortunately, this method is really slow. It creates a transaction for every row. This means that every insert locks the table. This leads to poor performance (I got about 25 records a second.)
So I thought I would just use the pyodbc driver directly. After all, it has a special method for inserting many values called executemany. The MSS implementation of the pyodbc execute many also creates a transaction per row. So does pymssql.
I looked on stack overflow, but they pretty much recommended using bulk insert.Which is still the fastest way to copy data into MSS. But it has some serious drawbacks.
For one, bulk insert needs to have a way to access the created flat file. It works best if that access path is actually a local disk and not a network drive. It also is a very primitive tool, so there is very little that you can do in terms of rollback and it’s not easily monitored. Lastly, transferring flat files, means that you are doing data munging writing to disk, then copying to another remote disk then putting the data back in memory. It might be the fastest method, but all those operations have overhead and it creates a fragile pipeline.
So if I can’t do bulk insert, and I can’t use a library. I have to roll my own.
It’s actually pretty simple:
You create your connection, I did this with sqlalchemy but you can use whatever:
import sqlalchemy
import urllib
cxn_str = "DRIVER={SQL Server Native Client 11.0};SERVER=server,port;DATABASE=mydb;UID=user;PWD=pwd"
params = urllib.quote_plus(cxn_str)
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
conn = engine.connect().connection
cursor = conn.cursor()
You take your df.
I’m going to assume that all of your values in the df are strings, if they are not.
And you have longs, you are going to need to do some munging to make sure that when we stringfy the values sql knows what’s up.
(so if you have a long, then you need to chop the L off because MSS doesn’t know that 12345678910111234L is a number. here is the regex to get rid of it: re.sub(r”(?<=\d)L”,”,s ) credit to my coworker who is a regex wiz.
We need to tuplify our records, Wes Mckinney says:
records = [tuple(x) for x in df.values]
Which is good enough for me. But we also need to stringfy them as well.
So that becomes:
records = [str(tuple(x)) for x in df.values]
MSS has a batch insert mode that supports up to 1000 rows at a time. Which means 1000 rows per transaction.
We need the sql script that does batch insertion: we’ll call that insert_
insert_ = """ INSERT INTO mytable (col1 ,col2 ,col3 ...) VALUES """
Since we can only insert 1000 rows. We need to break up the rows into 1000 row batches. So here is a way of batching the records.
My favorite solution comes from here.
def chunker(seq, size): return (seq[pos:pos + size] for pos in xrange(0, len(seq), size))
Now all we need to do is have a way of creating the rows and batching.
Which becomes:
for batch in chunker(records, 1000): rows = ','.join(batch) insert_rows = insert_ + rows cursor.execute(insert_rows) conn.commit()
That’s it.
This solution got me inserting around 1300 rows a second. A 1.5 orders of magnitude increase.
There are further improvements for sure, but this will easily get me past a hundred thousand rows an hour.
iabdb 