Google+ Pieces o' Eight: March 2012

Monday, 26 March 2012

Using Python to connect to a SQL Server 2012 LocalDb instance

One of the new features of SQL Server 2012 Express is the LocalDB installation. This is a standalone install of SQL Server Express designed to support local databases which, importantly, requires practically no configuration . Although the documentation hints that developers were Microsoft's primary consideration behind LocalDB, there are many scenarios where having a zero configuration install of SQL Server could be very useful, such as offering offline access to a LoB application without needing to port the data to different database engine or necessitating a full blown SQL Server Express install on a user's machine.

Today I was experimenting with getting Python to talk to SQL Server LocalDB's - which proved to be very easy indeed!

First up, you'll need to install SQL Server Express 2012 LocalDB: http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx

Next up you'll want to install the latest Native Client Drivers (SQLNCLI11): http://www.microsoft.com/Download/en/details.aspx?id=29065 (it's about halfway down this page)

Once both of those are installed, you're pretty much ready to go.

My Python environment is 2.7.2 on Windows 32-Bit. Into this I've installed pyodbc 3.03. From there, fire up IDLE and enter:

import pyodbc
con = pyodbc.connect('Driver={SQL Server Native Client 11.0};Server=(localdb)\\v11.0;integrated security = true')


And that should be you connected and ready to use a LocalDB from Python!