1

I´m having problem connecting SQL and python through pyodbc.

I´ve tried most of the drivers names and inclusions in the system, but I keep having the same issue.

CODE:

import pyodbc 
conn = pyodbc.connect(
    "Driver='{SQL Server Native Client 11.0}';"              
               "Server = server;"
               "Database = db;"
               "username = xxx;"
               "password = xxxxxxxxx;"
               "Trusted_Connection = yes;")




cursor = conn.cursor()
cursor.execute('SELECT * FROM db.table')

for row in cursor:
    print(row)

Error:

InterfaceError                            Traceback (most recent call last)
<ipython-input-36-04dae4d66996> in <module>()
      1 import pyodbc
      2 conn = pyodbc.connect(
----> 3     "Driver='{SQL Server Native Client 11.0}';"
      4                "Server = server;"
      5                "Database = db;"

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Pedro Villani
  • 13
  • 3
  • The driver name should not be enclosed in single quotes. (It doesn't really need to be in curly brackets either.) Spaces before and after the equal signs are also a bad idea. Try `DRIVER=SQL Server Native Client 11.0;SERVER=server_name; ...` – Gord Thompson Apr 25 '19 at 18:49

2 Answers2

0

try like below

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password')

 --DRIVER={ODBC Driver 17 for SQL Server} here driver name should be yours odbc version

no need --Trusted_Connection=True when you are providing user name and password

Microsoft have written and distributed multiple ODBC drivers for SQL Server:

{SQL Server} - released with SQL Server 2000
{SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
{SQL Server Native Client 10.0} - released with SQL Server 2008
{SQL Server Native Client 11.0} - released with SQL Server 2012
{ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014
{ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016
{ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016
{ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2017
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
0

ODBC Driver 17 for SQL Server is the standard driver you need to be using. It covers for most use cases. If you execute the below, it will list all the drivers available on the machine you are trying to run from.

pyodbc.drivers()

If ODBC Driver 17 for SQL Server is not in the list, you will need to install it by downloading from the microsoft site.

dpsahoo
  • 27
  • 7