How to Connect to Microsoft SQL Server Using Python

Refresher on SQL, Databases, Servers, and Ports

A database is the basic electronic information storage unit. It is a collection of information organized to provide efficient retrieval. The collected information could be in any number of formats (electronic, printed, graphic, audio, statistical, combinations). There are physical (paper/print) and electronic databases. A database could be as simple as an alphabetical arrangement of names in an address book or as complex as a database that provides information in a combination of formats.

A server is something that handles requsts over a network. It listens for requests on a port. A database server handles query requsts into a database. Th database server retrieves the requests an assembles the result in a way specified in the query.

SQL stands for structured query language. It is just a query language, not a database. It does not describe a database. It can describe a server because it can be the language in which the server reads its queries.

import pyodbc; import pandas as pd

conn = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'+
                        'Server=DESKTOP-2FIIRT5\SQLEXPRESS; ' +
                        'Database=AdventureWorks2016; ' +
                        'Trusted_Connection=yes;')

Determining Your Driver

Microsoft have written and distributed multiple ODBC drivers for SQL Server:

• {SQL Server} - released with SQL Server 2000
• {SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
• {SQL Server Native Client 10.0} - released with SQL Server 2008
• {SQL Server Native Client 11.0} - released with SQL Server 2012
• {ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014
• {ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016
• {ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016
• {ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2017

Note that the “SQL Server Native Client …” and earlier drivers are deprecated and should not be used for new development.

You can get the driver names of the drivers installed on your pc by running pyodbc.drivers()

pyodbc.drivers()

['SQL Server',
 'QB SQL Anywhere',
 'SQL Server Native Client 10.0',
 'MySQL ODBC 5.3 ANSI Driver',
 'MySQL ODBC 5.3 Unicode Driver',
 'ODBC Driver 13 for SQL Server',
 'PostgreSQL ANSI(x64)',
 'PostgreSQL Unicode(x64)',
 'Amazon Redshift (x64)',
 'SQL Server Native Client 11.0',
 'SQL Server Native Client RDA 11.0']

sql = 'select top 3 * from Production.Product'

pd.read_sql(sql, conn)

In Case We Don’t Want Our Data in a Dataframe

pyodbc.cnxn.cursor()

Returns a new Cursor object using the connection.

pyodbc supports multiple cursors per connection but your database may not.

cursor = conn.cursor()

pyodbc.cnxn.cursor.execute()

This function is not part of the Python DB API.

Creates a new Cursor object, calls its execute method, and returns the new cursor.

See Cursor.execute() for more details. This is a convenience method that is not part of the DB API. Since a new Cursor is allocated by each call, this should not be used if more than one SQL statement needs to be executed on the connection.

cursor.execute('select top 3 *' + 
                'from Production.Product ')

<pyodbc.Cursor at 0x1e42865daf8>

for row in cursor:
    print(row)

(1, 'Adjustable Race', 'AR-5381', False, False, None, 1000, 750, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 0, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '694215B7-08F7-4C0D-ACB1-D734BA44C0C8', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000))
(2, 'Bearing Ball', 'BA-8327', False, False, None, 1000, 750, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 0, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '58AE3C20-4F3A-4749-A7D4-D568806CC537', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000))
(3, 'BB Ball Bearing', 'BE-2349', True, False, None, 800, 600, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 1, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000))

pyodbc.cnxn.cursor.fetchall()

To return query results, we have to call the fetchall method, which returns a tuple of tuples back to python, i.e. query_results = ((row0), (row1), (row2), …).

Warning: There is the possibility of crashing your computer by completely filling the RAM, so be careful when using fetchall(). This is why I limit my result to three rows.

cursor.execute('select top 3 *' + 
                'from Production.Product ')
cursor.fetchall()

[(1, 'Adjustable Race', 'AR-5381', False, False, None, 1000, 750, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 0, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '694215B7-08F7-4C0D-ACB1-D734BA44C0C8', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000)),
 (2, 'Bearing Ball', 'BA-8327', False, False, None, 1000, 750, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 0, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '58AE3C20-4F3A-4749-A7D4-D568806CC537', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000)),
 (3, 'BB Ball Bearing', 'BE-2349', True, False, None, 800, 600, Decimal('0.0000'), Decimal('0.0000'), None, None, None, None, 1, None, None, None, None, None, datetime.datetime(2008, 4, 30, 0, 0), None, None, '9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E', datetime.datetime(2014, 2, 8, 10, 1, 36, 827000))]