SQL Server integration with Python

Something very important to me in particular as I utilize SQL Server quite a bit. Utilizing pyodbc and Pandas you can bring your data in.

Required Libraries

conda install pyodbc #Anaconda installation
#pip install pyodbc #Alternative

Here we can try altering to read from the AdventureWorks2017 database in SQL Server, as an example (replacing your server_name):

import pyodbc 
import pandas as pd
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=AdventureWorks2017;'
                      'Trusted_Connection=yes;')

sql_query = pd.read_sql_query('SELECT * FROM Person.person',conn)
sql_query.head()

Notice this gets thrown out as a dataframe:

Similarly – we could also write back to SQL Server if we wanted to utilizing slightly altered logic (abbreviated insert given how many fields actually exist in the table).

import pyodbc 
import pandas as pd
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=AdventureWorks2017;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
#cursor.execute('SELECT * FROM Person.person')
cursor.execute('''
    INSERT INTO [Person].[Person]
    ([PersonType]
    ,[FirstName])
    VALUES
    ('EM','Mike')
''')
conn.commit()

Lastly, if we did not want to throw this to a dataframe, but simply look at the logic as a list – we could run a cursor to throw back the values. (can run a bit slow)

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=server_name;'
                      'Database=AdventureWorks2017;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT top 2 * FROM Person.person')

for row in cursor:
    print(row)

Results:

(1, ‘EM’, False, None, ‘Ken’, ‘J’, ‘Sánchez’, None, 0, None, ‘0’, ’92C4279F-1207-48A3-8448-4636514EB7E2′, datetime.datetime(2009, 1, 7, 0, 0))
(2, ‘EM’, False, None, ‘Terri’, ‘Lee’, ‘Duffy’, None, 1, None, ‘0’, ‘D8763459-8AA8-47CC-AFF7-C9079AF79033’, datetime.datetime(2008, 1, 24, 0, 0))