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)')
The answer is
Try below:
import pyodbc
server = 'servername'
database = 'DB'
username = 'UserName'
password = 'Password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Tbl')
for row in cursor:
print('row = %r' % (row,))
Below connection string is working
import pandas as pd
import pyodbc as odbc
sql_conn = odbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=SERVER_NAME;DATABASE=DATABASE_NAME;UID=USERNAME;PWD=PASSWORD;')
query = "SELECT * FROM admin.TABLE_NAME"
df = pd.read_sql(query, sql_conn)
df.head()
Apart from the other answers, that considered the connection string itself, it might simply be necessary to download the correct odbc driver. My client just faced this issue when executing a python app, that required it. you can check this by pressing windows + typing "odbc". the correct driver should appear in the drivers tab.
I'm using
Django 2.2
and got the same error while connecting to sql-server 2012. Spent lot of time to solve this issue and finally this worked.
I changed
'driver': 'ODBC Driver 13 for SQL Server'
to
'driver': 'SQL Server Native Client 11.0'
and it worked.
I was facing the same issue whole day wasted and I tried all possible ODBC Driver values
import pyodbc
connection = pyodbc.connect('Driver = {SQL Server};Server=ServerName;'
'Database=Database_Name;Trusted_Connection=yes;')
In place of Driver = {SQL Server} we can try these option one by one or just you can use with you corresponding setting, somehow in my case the last one works :)
Driver={ODBC Driver 11 for SQL Server} for SQL Server 2005 - 2014
Driver={ODBC Driver 13 for SQL Server} for SQL Server 2005 - 2016
Driver={ODBC Driver 13.1 for SQL Server} for SQL Server 2008 - 2016
Driver={ODBC Driver 17 for SQL Server} for SQL Server 2008 - 2017
Driver={SQL Server} for SQL Server 2000
Driver={SQL Native Client} for SQL Server 2005
Driver={SQL Server Native Client 10.0} for SQL Server 2008
Driver={SQL Server Native Client 11.0} for SQL Server 2012
Have you installed any product of SQL in your system machine ? You can download and install "ODBC Driver 13(or any version) for SQL Server" and try to run if you havent alerady done.
I have had the same error on python3 and this help me:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
'SERVER=YourServerName;'
'DATABASE=YourDatabaseName;UID=USER_NAME;PWD=PASS_WORD;')
remember python is case-sensitive so you have to mention DRIVER,SERVER,... in upper case. and you can visit this link for more information:
Create a DSN something like this (ASEDEV) for your connection and try to use DSN instead of DRIVER like below:
enter code here
import pyodbc
cnxn = pyodbc.connect('DSN=ASEDEV;User ID=sa;Password=sybase123')
mycur = cnxn.cursor()
mycur.execute("select * from master..sysdatabases")
row = mycur.fetchone()
while row:
print(row)
row = mycur.fetchone()`
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)
You need to download Microsoft ODBC Driver 13 for SQL Server from Microsoft ODBC Driver 13
I am also getting same error. Finally i have found the solution.
We can search odbc in our local program and check for version of odbc. In my case i have version 17 and 11 so. i have used 17 in connection string
'DRIVER={ODBC Driver 17 for SQL Server}'
The below code works magic.
SQLALCHEMY_DATABASE_URI = "mssql+pyodbc://<servername>/<dbname>?driver=SQL Server Native Client 11.0?trusted_connection=yes?UID" \
"=<db_name>?PWD=<pass>"
I've met same problem and fixed it changing connection string like below. Write
'DRIVER={ODBC Driver 13 for SQL Server}'
instead of
'DRIVER={SQL Server}'
for error : pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
No space between the driver and event
connection = Driver={SQL Server Native Client 11.0};
"Server=servername;"
"Database=dbname;"
"Trusted_Connection=yes;"
You could try:
import pyodbc
# Using a DSN
cnxn = pyodbc.connect('DSN=odbc_datasource_name;UID=db_user_id;PWD=db_password')
Note: You will need to know the "odbc_datasource_name". In Windows you can search for ODBC Data Sources. The name will look something like this:
if any one are trying to access the database which is hosted in azure then try to give the driver as ODBC Driver 17 for SQL Server
I faced this issue and was looking for the solution. Finally I was trying all the options from the https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows , and for my MSSQL 12 only "{ODBC Driver 11 for SQL Server}" works. Just try it one by one. And the second important thing you have to get correct server name, because I thought preciously that I need to set \SQLEXPRESS in all of the cases, but found out that you have to set EXACTLY what you see in the server properties. Example on the screenshot: 
Source: Stackoverflow.com