Connecting to ODBC using pyODBC

jenryb picture jenryb · Jun 15, 2015 · Viewed 23.2k times · Source

I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code:

cnxn = pyodbc.connect("DSN=DSNNAME")
cursor = cnxn.cursor()
cursor.tables()
rows = cursor.fetchall()
for row in rows:
    print row.table_name

but for everything else I keep getting this error:

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I know that I can pull up my data using Microsoft Access by going through the following steps: Creating a new database, clicking the external data tab, Click More and select ODBC database, use the Link to the data source by creating a linked table, in the Select data source window choosing Machine Data source and select NAME2 which has a System type, press okay and choose the table acr.Table_one_hh, then select the fields in the table that I want to look at like City, State, Country, Region, etc. When I hover over the table name it shows the DSN name, Description, Trusted Connection = Yes, APP, Database name and the table name.

I've attempted two methods, first

cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=mycomputername;DATABASE=mydatabase;Trusted_Connection=yes;')
cursor = cnxn.cursor()

which gives an error:

Error: ('08001', '[08001] [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].  (2) (SQLDriverConnect); [HYT00] [Microsoft][SQL Server Native Client 10.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (2)')

I tried

cnxn = pyodbc.connect("DSN=DSNNAME, DATABASE=mydatabase")
cursor = cnxn.cursor()
cursor.execute("""SELECT 1 AS "test column1" from acr.Table_one_hh""")
cursor.tables()
rows = cursor.fetchall()
for row in rows:
    print row.table_name

which gave an error

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Answer

jenryb picture jenryb · Jun 17, 2015

I managed to solve my issue. My code did not really change.

cnxn = pyodbc.connect("DSN=BCTHEAT")
cursor = cnxn.cursor()
cursor.execute("select * from acr.Table_one_hh")
row = cursor.fetchall()

then I wrote the results into a csv file.