Python Forum

Hi, I am new to Python and I started immediately from importing data I am planning to analyse in pandas(?) to get tables, mean scores etc. I know how I can import that from excel but I have issues with importing data from ODBC. I can do it in excel, in SPSS and in R using this code:

abc <- odbcConnect("asa")
Country <- sqlQuery(abc, "SELECT * FROM dbo.tblCountry")

I am trying to replicate it in Python using this:

import pandas.io.sql
import pyodbc
import sys

server = 'asa'
db = 'dbo.tblCountry'

conn = pyodbc.connect(server=server, database=db)
print(conn)

Unfortunately, I have this error:

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

What am I doing wrong?

I don't have experience with this so I might be wrong, but you say this works: abc <- odbcConnect("asa"). So what is "asa"? I guess it is a DSN. And apparently you do not have to specify username and password, so I guess this information is stored in the DSN.
Then in your python source you say:

server = 'asa'
db = 'dbo.tblCountry'

But in the previous (working) example, "dbo.tblCountry" was the name of a table. Not a database. So to do the same as in the (working) example I guess you should do:

conn = pyodbc.connect("DSN=asa")

Note: the parameter you specify is just passed through to the ODBC handler, so it is one quoted parameter. If you need to pass more parameters, it should still be one parameter in Python:

conn = pyodbc.connect("DSN=MSSQLServerDatabase;UID=myuid;PWD=mypwd")

(Don't add unnecessary spaces.)