Connecting Python to SQL Server using trusted and login credentials

PythonGary Hutson0

The pypyodbc is the library you need to drive the connection to a SQL server database. If, like me, you struggle with connection strings, then please navigate to this site for help with all types of connection string: https://www.connectionstrings.com/.

Installing with pip install

To install the relevant packages needed you will need to use pip install the pypyodbc package. Alternatively, for Anconda you will need to prefix with conda install. For more advice on how to install Python packages via pip install, see the relevant guidance.

Connecting via a Trusted connection

To connect to Python by a trusted connection using the following syntax:

import pypyodbc as odbc
import pandas as pd
import numpy as np

driver = 'Driver={ODBC Driver 17 for SQL Server};'
server = 'Server=localhost;Database=master;Trusted_Connection=True;'

print(driver + server)
conn = odbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'Server=localhost;'
                      'Database=master;'
                     'Trusted_Connection=yes;')
print(conn)

For those new to Python, this imports the relevant libraries needed for the project and then sets an alias using the as keyword. The driver and server are string variables and then the variables get concatenated together using the concatenation (+) operator. This will print the driver and server and then the conn variable uses the connection string to the relevant server, database and then specifies that the user details are to be those used when authenticating the user against SQL server. This then prints the connection.

Connecting via User Credential Account

To undertake this, we change the connections string slightly to facilitate this:

conn = odbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=master;"                  "uid=garyhutson;pwd=password")

The difference here to the conn variable is the addition of the userid and the password of the user.

Testing the connection

Utilising the trusted connection string we can execute the code to check that the connection has been successful:

  • The connection will return a connection object that has been instanced
  • There will be an integer of 0 or 1 to indicate whether the connection has been successful.

Querying a table in the database

The following example will query from a test table that has been created for this demo in a SQL instance. 

df = pd.read_sql_query('SELECT * FROM [master].[dbo].[RockStars]', conn)
print(df.head(20))
print(df.describe())

This query returns a SELECT statement for the whole table into the df variable to indicate data frame. Then, I use the head method to select the top 10 records and the df.describe() shows all the summary statistics.

The below is returned, using VS Code:

As you can see the querying of the data frame has been successful. In the next post I will teach you how to use Pandas, and coming at this from a R to Python conversion perspective.

Summary

I hope this has been useful and I aim to submit many more articles on how to use Python with VS Code in the future.

Leave a Reply