This is a QUICK START guide for the pyobc library for those who use it to connect to a Microsoft SQL SERVER database. However, this will not go into any detail, so you can and should read the full documentation HERE
Besides the standard query stuff you would expect... there are A LOT of other things that you can do with pyodbc, with some creativity. For example, you can use it to stage an ETL process...yes, you can execute a python script with a scheduled task on your computer... pretty cool. I often run queries in a FOR loop when I want to process data over multiple periods of time, but it is more efficient to load it in one month or one year increments. The sky is the limit! Enjoy!
import pyodbc
Use the pyodbc.drivers() method to find available drivers; the connection strings for these are essentially the same and will look like this:
conx = pyodbc.connect('DRIVER={SQL Server}; SERVER=TestServer; Database=TestDatabase; UID=UserID; PWD=Password;')
If you use Windows Authentication to connect to the server, your string will look like this:
conx = pyodbc.connect('DRIVER={SQL Server}; SERVER=TestServer; Database=TestDatabase; TRUSTED_CONNECTION=yes')
Find the DRIVERS you have available by using the pyodbc.drivers()
method
pyodbc.drivers()
['SQL Server', 'SQL Server Native Client 11.0', 'ODBC Driver 13 for SQL Server', 'SQL Server Native Client RDA 11.0', 'ODBC Driver 17 for SQL Server']
Create a VARIABLE to store the connection string
conx_string = "driver={SQL SERVER}; server=GTLPF1MZF5M\IZZY_SQL_001; database=ADVENTUREWORKS2017; trusted_connection=YES;"
Create a VARIABLE for the sql query
query = "SELECT Name, CreditRating FROM Purchasing.Vendor WHERE CreditRating < 3"
Create a CONNECTION using the connection string and pyodbc.connect()
conx = pyodbc.connect(conx_string);
Create a CURSOR that we can use to work in the database
cursor = conx.cursor();
Run the QUERY using cursor.execute()
cursor.execute(query);
Store the RESULTS in a variable
data = cursor.fetchall()
Display the RESULTS to check the data (first 5 rows)
print(data[:5])
[('Australia Bike Retailer', 1), ('Allenson Cycles', 2), ('Advanced Bicycles', 1), ('Trikes, Inc.', 2), ('Morgan Bike Accessories', 1)]
Close the CONNECTION using the close()
method
conx.close()
Create a CONNECTION and import the DATA. There is no need to close the connection manually here as it will close when exiting the with statement.
with pyodbc.connect(conx_string) as conx:
cursor = conx.cursor()
cursor.execute(query)
data = cursor.fetchall()
Display the RESULTS to check the data (first 5 rows)
print(data[:5])
[('Australia Bike Retailer', 1), ('Allenson Cycles', 2), ('Advanced Bicycles', 1), ('Trikes, Inc.', 2), ('Morgan Bike Accessories', 1)]
Access the DATA as a NAMED TUPLE, which can prove to be very handy
for row in data[:5]:
print(f"VENDOR NAME: {row.Name}\nCREDIT RATING: {row.CreditRating}")
VENDOR NAME: Australia Bike Retailer CREDIT RATING: 1 VENDOR NAME: Allenson Cycles CREDIT RATING: 2 VENDOR NAME: Advanced Bicycles CREDIT RATING: 1 VENDOR NAME: Trikes, Inc. CREDIT RATING: 2 VENDOR NAME: Morgan Bike Accessories CREDIT RATING: 1
Retrieve the COLUMN NAMES for a table
columns = [row.column_name for row in cursor.columns(table='Vendor')]
print(columns)
['BusinessEntityID', 'AccountNumber', 'Name', 'CreditRating', 'PreferredVendorStatus', 'ActiveFlag', 'PurchasingWebServiceURL', 'ModifiedDate']
Retrieve a LIST OF TABLES in the database (first 5 records)
tables = [row.table_name for row in cursor.tables()]
print(tables[:5])
['AWBuildVersion', 'DatabaseLog', 'ErrorLog', 'Department', 'Employee']
SINGLE parameter query (Female employees)
with pyodbc.connect(conx_string) as conx:
cursor = conx.cursor()
cursor.execute('SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee WHERE Gender = ?', 'F')
f_emp_data = cursor.fetchall()
Retrieve a LIST of TABLES in the database (first 5 records)
for row in f_emp_data[:5]:
print(row)
('245797967', 'Vice President of Engineering') ('695256908', 'Design Engineer') ('811994146', 'Research and Development Engineer') ('658797903', 'Research and Development Engineer') ('486228782', 'Tool Designer')
MULTI parameters query (Male employees, Vacation hours < 40)
with pyodbc.connect(conx_string) as conx:
cursor = conx.cursor()
cursor.execute('SELECT NationalIDNumber, JobTitle, VacationHours FROM HumanResources.Employee WHERE Gender = ? AND VacationHours < ?' , ('M',40))
m_vac_data = cursor.fetchall()
Retrieve a LIST of TABLES in the database (first 5 records)
for row in m_vac_data[:5]:
print(row)
('509647174', 'Engineering Manager', 2) ('998320692', 'Design Engineer', 6) ('879342154', 'Research and Development Manager', 16) ('974026903', 'Senior Tool Designer', 7) ('480168528', 'Tool Designer', 9)
Create a CONNECTION STRING. This is a different database than the other I used.
conx_string_b = "driver={SQL SERVER}; server=GTLPF1MZF5M\IZZY_SQL_001; database=SANDBOX; trusted_connection=YES;"
Insert a SINGLE record
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.execute("INSERT INTO VacationExceptions (NationalIDNumber, JobTitle, VacationHours) VALUES(125467985467854, 'Head Hancho', 500)")
Verify the DATA with a SELECT query
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.execute("SELECT * FROM VacationExceptions")
data = cursor.fetchall()
print(data)
[('125467985467854', 'Head Hancho', 500)]
Insert MULTIPLE records from VALUES
exceptions = [('615389812','Sales Representative',150),('982310417','European Sales Manager',75)]
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.executemany("INSERT INTO VacationExceptions (NationalIDNumber, JobTitle, VacationHours) VALUES(?,?,?)", exceptions)
Verify the DATA with a SELECT query (1 record from single insert, and 2 records for multi insert... 3 total)
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.execute("SELECT * FROM VacationExceptions")
data = cursor.fetchall()
for row in data:
print(row)
('125467985467854', 'Head Hancho', 500) ('615389812', 'Sales Representative', 150) ('982310417', 'European Sales Manager', 75)
Insert MULTIPLE records from ANOTHER query
Load the MALE vacation exceptions dataset into the VacationExceptions table
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.executemany("INSERT INTO VacationExceptions (NationalIDNumber, JobTitle, VacationHours) VALUES(?,?,?)", m_vac_data)
Verify the DATA with a SELECT query (first 10 records)
with pyodbc.connect(conx_string_b) as conx:
cursor = conx.cursor()
cursor.execute("SELECT * FROM VacationExceptions")
data = cursor.fetchall()
for row in data[:10]:
print(row)
('125467985467854', 'Head Hancho', 500) ('615389812', 'Sales Representative', 150) ('982310417', 'European Sales Manager', 75) ('509647174', 'Engineering Manager', 2) ('998320692', 'Design Engineer', 6) ('879342154', 'Research and Development Manager', 16) ('974026903', 'Senior Tool Designer', 7) ('480168528', 'Tool Designer', 9) ('42487730', 'Senior Design Engineer', 3) ('14417807', 'Production Technician - WC60', 21)