Unable to connect to SQL Server using SQLALchemy

Question

I am using a Windows 10 machine trying to connect to an SQL Serve (SQL Developer 2017) using Python. My code is as follows:

from sqlalchemy import create_engine engine = create_engine('mssql+pymssql://Sonwabo:helpdeskNo1@localhost:1433/dbname') connect = engine.connect()

I get the following error: pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (localhost:1433)\nNet-Lib error during Unknown error (10060)\n')

Ben Dickson · Accepted Answer · 2020-02-06 15:46:39Z

1

I remember my sqlalchemy connection being very finicky when connecting to SQL Server too. I had to do some experimenting with drivers and switching between pymssql and pyodbc. This is what ended up working for me.

server = 'server'
username = 'user'
password = 'pass'
driver = 'ODBC+DRIVER+17+for+SQL+Server'
engine_stmt = 'mssql+pyodbc://{}:{}@{}/{}?driver={}'.format(username,
                                                            password,
                                                            server,
                                                            database,
                                                            driver)
engine = sqlalchemy.create_engine(engine_stmt)

answered Feb 6, 2020 at 15:46

Ben Dickson

3162 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Khandakhulu Over a year ago

I am now getting the following error: pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Khandakhulu Over a year ago

Can I use localhost for my server name?

Khandakhulu Over a year ago

As I am using Windows authentication, should I supply values for username and password?

kia y · Accepted Answer · 2021-01-19 10:07:48Z

0

I think you should follow as the format below.

server = ‘ServerAddress' 
database = 'DataBaseName' 
username = 'UserName' 
password = 'Password'
driver = 'SQL Server Native Client 11.0'
DataBase_connection = f'mssql://{username}:{password}@{server}/{database}?driver={driver}'


engine = create_engine(DataBase_connection)
connection = engine.connect()

answered Jan 19, 2021 at 10:07

kia y

113 bronze badges

Collectives™ on Stack Overflow

Unable to connect to SQL Server using SQLALchemy

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related