Python and SQL Server
Setting up Python to connect to SQL Server was relatively easy. First, you select a DB API driver. I chose pyodbc because I saw a Python article on Simple-Talk. There are two simple steps:
- Install Pywin32. Get the latest. It’s a dependency for pyodbc.
- Install pyodbc. Get it for the version of Python you’re using.
Once you’ve done this, you can query your SQL Server db as so:
import pyodbc connection = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.5;DATABASE=MyAwesomeDB;UID=sa;PWD=password') cursor = connection.cursor() cursor.execute("select * from states") for row in cursor: print row.StateID, row.Abbreviation, row.Name
For more snippets and a tutorial, check out the documentation.
Now let’s try something more interesting. Let’s try doing some inserts and see how long it takes.
import win32api import uuid import pyodbc connection = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.0.5;DATABASE=MrSkittles;UID=sa;PWD=password') cursor = connection.cursor() _start = win32api.GetTickCount() for i in range( 0, 10000 ): # Let's insert two pieces of data, both random UUIDs. sql = "INSERT INTO Manager VALUES( '" + str( uuid.uuid4() ) + "', '" + str( uuid.uuid4() ) + "' )" cursor.execute( sql ) connection.commit() _end = win32api.GetTickCount() _total = _end - _start print "\n\nProcess took", _total * .001, "seconds"
After some tests, 10,000 records took roughly 20-30 seconds. 1,000,000 records took 30 to 40 minutes. A bit slow, but it’s not a server machine. My machine is a Core Duo, 1.8Ghz x 2, at ~4GB with PAE on WindowsXP, but I ran this on a VMware VM with 1GB and SQL Server 2005 w/Windows Server 2003. The table was a two column table both varchar(50). On a server machine, it should be a helluva lot faster.
Categories