Connect to mssql from python-CodePudding

I am trying to connect mssql from python. For this I am using below code but looks like something is wrong with the connection. Can anyone help me ?

import sqlalchemy as sal
from sqlalchemy import create_engine
import pyodbc 

##conn = pyodbc.connect('Driver={SQL Server Native client 11.0};server=localhost;database=Nifty;trusted_connection=yes;')

engine = sal.create_engine('mssql pyodbc://localhost/Nifty?driver=SQL Server Native client 11.0?Trusted_Connection=yes')

engine.execute('select  top 2 * from [dbo].ABC')

I am getting below error

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)

CodePudding user response：

pls first check if you have specified driver in connection:

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources>System DSN tab>Add

and then try :

engine = sal.create_engine('mssql pyodbc://localhost/Nifty?driver=SQL Server Native client 11.0?Trusted_Connection=yes',echo = True)

official docs

or, You can use some of the Solutions below:

Solution 1

define driver like this :

Driver={ODBC Driver 17 for SQL Server};Server=serverName\instanceName;Database=myDataBase;Trusted_Connection=yes;

and then put it in pyodbc.connect(" here ") and run it using cursor, see this

Solution 2

With Windows Authentication Without using DSN's

engine = sal.create_engine('mssql pyodbc://server/db')

Solution 3 using urllib

import urllib
params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                 "SERVER=dagger;"
                                 "DATABASE=test;"
                                 "Trusted_Connection=yes")

engine = sa.create_engine("mssql pyodbc:///?odbc_connect={}".format(params))