How to connect to a SQL Server database in Anaconda

Question

I'm quite new to Python and programming in general, so please bear with me. At my work I have Anaconda (Python vers 2.7) installed and would like to connect to a Microsoft SQL Server database, preferably with ODBC. Related to this task I have a number of questions:

• Is it correct that I cannot connect to a SQL Server database using sqlite3 or sqlalchemy? That I need a module like pyodbc? A brief explanation of why this is the case would be much appreciated.

• EDIT: Question related to installation of pyodbc in anaconda removed, since I figured this out (by reading Cannot Install Python Modules after Installing Anaconda)

Help is much appreciated! If any of my questions/any other specifics need to be cleared up, please don't hesitate to ask. Thanks

Solution

I do not use Anaconda, but I use various databases and ODBC. At first you can try if you have odbc module installed. It is a part of pywin32 package (http://sourceforge.net/projects/pywin32/files/) and is packed with ActiveState Python distribution. Other distribution can install it separately. Simply try:

import odbc
db = odbc.odbc('dsn/user/password')

You can also try with pyodbc you mentioned in question. There is precompiled version for Windows and I think it will work with your Anaconda environment. After installing try:

import pyodbc
db = pyodbc.connect('Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;')

You can find more connection strings at http://www.connectionstrings.com/

EDIT:

It seems that you have problem with bitness of ODBC driver.

Try to run this program to see what sources are visible to ODBC manager:

import odbc
source =  odbc.SQLDataSources(odbc.SQL_FETCH_FIRST)
while source:
    print(source)
    source =  odbc.SQLDataSources(odbc.SQL_FETCH_NEXT)