Before creating python connection to SQL database from windows computer we need to ensure we have correct driver installed. The quickest ways to do this is by opening ODBC Data Source Administrator and selecting Drivers tab as per screenshot below.

In my case I will be connecting to SQL server 2017 therefore I will be using ODBC Driver 17 for SQL Server.

If you are connecting to other databases such as Oracle, MySQL, etc you need to make sure you have correct driver and version (32bit or 64bit) installed otherwise it will not work.

I have seen many connection issues is due to incorrect driver version. For example if you have python 32-bit version installed, you need to make sure you have 32-bit ODBC driver installed otherwise your connection will not work.

If you’re not sure if your python executing 32-bit or 64-bit then you can run the code below and the output will how if it is 32bit or 64 bit version.

Now you know which version of ODBC driver (32-bit or 64-bit) you need to install it, the next step is to install pyodbc package.

pyodbc package will be communicating directly with the ODBC driver.

pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0specification but is packed with even more Pythonic convenience.

The easiest way to install is to use pip:

If you’re using IDE PyCharm you can install it from:

File –> Setting –> Project: Name of Your Project –> Project Interpreter

Click on icon + and install the package, if everything is ok you will receive confirmation Package ‘pyodbc’ installed successfully.

I’m will break down connection string in more meaningful way.

Trusted_Connection=no; – Specify if you want connection string to use your windows credentials, if my case no therefore SQL credentials will be used.

DRIVER={ODBC Driver 17 for SQL Server}; – Specify name of the driver from ODBC Data Source Administrator

SERVER=.\\SQLEXPRESS01; – Dot means my local computer and SQLEXPREESS01 is a instants of your SQL server.

PORT=1433; – port number connect to SQL server

DATABASE=test; – name of the database

UID=USER1; – username of SQL server

PWD=Password; – password of the SQL server user