PYODBC--Data source name not found and no default driver specified

python sql pyodbc
user8560985 picture user8560985 · Sep 5, 2017 · Viewed 100.2k times · Source 
import pyodbc
connection = pyodbc.connect('Driver = {SQL Server};Server=SIWSQL43A\SIMSSPROD43A;'
                            'Database=CSM_reporting;Trusted_Connection=yes;')

Error:

connection = pyodbc.connect('Driver = {SQL Server};Server=SIWSQL43A\SIMSSPROD43A;'
    pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Answer

Gord Thompson picture Gord Thompson · Sep 5, 2017

Do not put a space after the Driver keyword in the connection string.

This fails on Windows ...

conn_str = (
    r'DRIVER = {SQL Server};'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

... but this works:

conn_str = (
    r'DRIVER={SQL Server};'
    r'SERVER=(local)\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

Related questions

Retrieving Data from SQL Using pyodbc

I am trying to retrieve data from an SQL server using pyodbc and print it in a table using Python. However, I can only seem to retrieve the column name and the data type and stuff like that, not the …

Read More
Get data from pandas into a SQL server with PYODBC

I am trying to understand how python could pull data from an FTP server into pandas then move this into SQL server. My code here is very rudimentary to say the least and I am looking for any advice or …

Read More
pyodbc insert into sql

I use a MS SQL express db. I can connect and fetch data. But inserting data does not work: cursor.execute("insert into [mydb].[dbo].[ConvertToolLog] ([Message]) values('test')") I get no error but nothing is inserted into the table. …

Read More