How to Connect SQL Server in Python | Username and Password

      No Comments on How to Connect SQL Server in Python | Username and Password

For the Python experienced Developers & DBA’s I thought I would include an article about connecting to SQL server similar to the SQL server powershell version we wrote recently. I tested this code using PyCharm and a new virtual environment that did not have pyodbc.

To connect to SQL Server we will need the PyODBC to provide the drivers for Microsoft SQL Server. You will need to install the driver first and then use the “import” command to bring it into your project. This example is to access a sql database using python and linux.

I tested this code using PyCharm and a new virtual environment that did not have pyodbc. With a new environment you will need to install the module before importing it.

Update: This article was written using an older Driver. See the ODBC Driver for Linux article on Microsoft’s website for the version 18 driver. I haven’t had time to test but you may need to change…

"Driver={SQL Server};"

TO

"Driver={ODBC Driver 18 for SQL Server};"

Python Environment Using Ubuntu

The environment may be different depending on your platform Linux, Windows or Mac. I am working with linux for this example. In the case of Ubuntu I had to install the Unix ODBC drivers using the following apt-get command (db tales com).
These drivers may be required for Mac as well. 

sudo apt-get install unixodbc unixodbc-dev

Use the following command to create a new environment for your project. I usually create a project folder and run this command inside so it is unique to each project.
This will create as hidden folder call “.venv”.

python3 -m venv .venv

To activate the environment use the following command from inside your project folder. The command line with prepend with a parenthesis around the environment name. (.venv)

source .venv/bin/activate

Now that you are inside the .venv environment we can install the needed requirements.

(.venv) pip3 install wheel
(.venv) pip install pyodbc

When the environment is ready you can save the requirements to a .txt file to be used later and recreate this environment.

pip freeze > requirements.txt

Visual Studio Code (VSCode) Notes

When using VS Code you can perform these tasks inside the terminal window but VS Code may not recognize the environment when created. To choose the environment:

  • Open the Command Palette (ctrl-shift-p)
  • Type “Python Interpreter”
  • Choose “Python: Select Interpreter”
  • Select a system Python Interpreter
  • OR select “Enter Interpreter Path” and Browse to the Hidden (.venv/bin/python) folder

Basic SQL Server Connection

With the module successfully installed you can type “import py” and the autocomplete should bring up the installed module. This is the complete sample code for running a basic SELECT statement and returning the results with a FOR EACH.
Change the Server, Database and query to test.

Note: Depending on your Login permissions you may not need the user and password fields.

import pyodbc

conn_str = ("Driver={SQL Server};"
            "Server=sqlserver\sqlinst1;"
            "Database=DBName;"
            "UID=MyUser;"
            "PWD=Pass123;")
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT TOP(1000) * FROM sometable")

for row in cursor:
    print(row)

How to Use Parameters With PyODBC

We can use dynamic parameters to provide the connection details (db tales com) instead of statically assigning them. The “%s” is used in this case to bring variables into the statement. Let’s define the variables…

driver= 'SQL Server'
server= 'server\name'
db= 'dbname'
user= 'username'
password= 'thepasswd'

We then use the “%s” to define and connect where we need these parameters. Each parameter is brought into the statement IN ORDER so their position is important here.

conn = pyodbc.connect('driver={%s};server=%s;database=%s;uid=%s;pwd=%s' % ( driver, server, db, user, password ) )

This is the full example code…

import pyodbc

driver= 'SQL Server'
server= 'server\name'
db= 'dbname'
user= 'username'
password= 'thepasswd'

conn = pyodbc.connect("driver={%s};server=%s;database=%s;uid=%s;pwd=%s" % ( driver, server, db, user, password ) 
)
cursor = conn.cursor()
cursor.execute("SELECT TOP(1000) * FROM sometable")

for row in cursor:
    print(row)

How to Install Python and Pip

Note: You may need administrator rights to run these commands. You can also install Python without Admin.

Check For Existing Versions

python --version
pip --version

In some cases you might already have Python and Pip installed. You can upgrade using:

pip install pip --upgrade

Certificate Errors

I wanted to mention an issue I ran into when using pip and installing behind a company firewall. I believe the SSL could not be verified as the firewall was blocking either the outgoing or return request. I could be wrong on the WHY but the result was to find a way to skip SSL validation.

SSLError(SSLCertVerificationError(1, ‘[SSL: CERTIFICATE_VERIFY_FAILED]

Your Security department may not like this but there is a set of command switches where you can trust the domains. I found this solution here.

  • –trusted-host=pypi.python.org
  • –trusted-host=pypi.org
  • –trusted-host=files.pythonhosted.org

To install the pyodbc driver use this command to “trust” the domains.

pip install pyodbc --upgrade --trusted-host=pypi.python.org --trusted-host=pypi.org --trusted-host=files.pythonhosted.org