Python Friday #90: SQL Server Connection Strings for SQLAlchemy

My first steps with SQLAlchemy and SQL Server ended in a lot of problems, mainly around the driver and the correct form of the connection string. Let us look what you need to successfully connect to SQL Server.

This post is part of my journey to learn Python. You can find the other parts of this series here.

 

The usual connection string format

If you look for a tutorial on SQLAlchemy you see a lot of connection strings using this format:

‘dialect+driver://username:password@host:port/database’

For the other databases this format may work, but SQL Server likes it a bit different. There are multiple ways you can write your connection string. I stopped to try more alternatives as I got everything working for my use cases.

 

Pre-requisites

Database and user

I try to connect to the AdventureWorks2014 database using my windows account and my user demo with the password ‘P@ssword1’. Make sure that not only your windows account but also your user demo has the correct access rights to the database. Otherwise you can connect but your queries will not get anything back or throw errors.

 

Driver

You can choose between multiple drivers to connect to SQL Server. I use PyODBC because that is what we use at work. You can install it using pip:

 

SQLAlchemy

For completeness, please remember to install SQLAlchemy first. You can do this with this command:

 

Connect with windows authentication

For once the connection with windows authentication was the simpler one. Your connection string should have this syntax:

‘mssql+pyodbc://host/dbname?driver=SQL Server?Trusted_Connection=yes’

With my database the connection string looks like this (put everything on one line):

 

Connect with username and password

As long as your username and password have no special characters, you can use the HTTP authentication syntax in your connection string:

‘mssql+pyodbc://user:password@host/dbname?driver=SQL Server’

However, most current password rules enforce special characters. If you put them there your connection string will most likely no longer work. You need to URL-encode it properly and for that you can use the urllib.parse.quote_plus() function:

 

Establish a connection and query data

With your connection string build in the proper way, you now can use it to establish a connection:

If you do not get an error, you can query your database:

 

Conclusion

It took me a while to figure out the driver part of the connection string. Without that you only get a cryptical error buried in a stack trace. Should you get no error and still be unable to retrieve data, double-check the permissions of your user. It helps when the user is allowed to access the database.

 

Next

Next week we try another way to map tables to SQLAlchemy ORM objects that can help us when someone else maintains the database structure.