Jingwen Zheng
  • Feb 19, 2019

Python: Connection to SQL Server from Windows

This blog will talk about how to connect to SQL Server, connect to a specified database and extract data with pyodbc python module.

In the last blog I presented how to connect to Teradata Database with teradata python module. Today I’ll talk about how to connect to SQL Server by python with following points:

  • Required packages
  • Connecting to SQL Server
  • Checking databases in SQL Server
  • Connecting to a specified database
  • Extracting data with SQL query

Required packages

import pandas as pd
import pyodbc

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

Connecting to SQL Server

cnxn = pyodbc.connect("DRIVER={SQL Server};"
                      "Server=sqlprod;"
                      "UID=xxx;"
                      "PWD=xxx;")

Let’s read this example line by line: the first two lines create a connection to a SQL Server named “sqlprod” using SQL Server as the connection method; then we specify usename and password with UID and PWD.

Checking databases in SQL Server

After creating the connection to SQL Server, we can check databases in it.

req_db = ("SELECT * FROM master.sys.databases")
db = pd.read_sql(req_db, cnxn)

“db” returns rich information of all databases which are contained in SQL Server, like name, database_id, source_database_id, create_date, etc.

Connecting to a specified database

By checking databases in SQL Server, we can now connect to a specified database.

cnxn_db1 = pyodbc.connect("DRIVER={SQL Server};"
                          "Server=sqlprod;"
                          "Database=db1;"
                          "UID=xxx;"
                          "PWD=xxx;")

Connecting to a database should specify the database-name in pyodbc.connect().

Extracting data with SQL query

req = ("SELECT TOP 5 * FROM TABLE")
df = pd.read_sql(req, cnxn_db1)

To connect to “TABLE” of database “db1”, we need to precise the connection “cnxn_db1” when execute SQL query with pandas.read_sql().

Reference

Search