SQL Server to Python


How would you connect Microsoft SQL Server to Python so that you could pull data into Python? Perhaps you are simply designing simple summary reports or perhaps you are analyzing the data.

Let’s do a simple example in Jupyter Notebook, using Anaconda Navigator. I will name the project the same as this post’s name. Here are some of the parameters I need to know about this connection. The server name is simply localhost. I am using SQL Server Management Studio version 20.2. My database is called MikeTest. The table is called Names.

import pandas as pd
import pyodbc
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=localhost;"
                      "Database=MikeTest;"
                      "Trusted_Connection=yes;")
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Names')
for row in cursor:
    print('row = %r' % (row,))
row = ('Bob', 7)
row = ('Sally Smith', 2)
row = ('Jackie', 43)
row = ('Suzie', 76)
row = ('Bob', 7)
row = ('Sally Smith', 2)
# Another way - puts the data into a pandas DataFrame
df = pd.read_sql_query('select * from Names', cnxn)
df

When you do this you get a Warning that says that “…Please consider using SQLAlchemy.”

Leave a comment

Your email address will not be published. Required fields are marked *