Connect to mssql from python

Question

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)

tried sal.create_engine('mssql+pyodbc://@localhost/Nifty/?driver=SQL+Server+Native+client+11.0?Trusted_Connection=yes') . But still getting same — user11740857
– user11740857, Commented Oct 16, 2022 at 13:07

YUVI_1303 · Accepted Answer · 2022-10-16 13:36:37Z

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))

Collectives™ on Stack Overflow

Connect to mssql from python

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related