Step 3: Proof of concept connecting to SQL using pyodbc
This sample proof of concept uses pyodbc
to connect to an SQL database. This sample assumes that you're using the AdventureWorksLT sample database.
Note
This example should be considered a proof of concept only. The sample code is simplified for clarity, and does not necessarily represent best practices recommended by Microsoft.
Prerequisites
- Python 3
- If you don't already have Python, install the Python runtime and Python Package Index (PyPI) package manager from python.org.
- Prefer to not use your own environment? Open as a devcontainer using GitHub Codespaces.
pyodbc
package from PyPI.- Install the Microsoft ODBC Driver 18 for SQL Server
- An SQL database and credentials.
Connect and query data
Connect to a database using your credentials.
Create a new file named app.py.
Add a module docstring.
""" Connects to a SQL database using pyodbc """
Import the
pyodbc
package.import pyodbc
Create variables for your connection credentials.
SERVER = '<server-address>' DATABASE = '<database-name>' USERNAME = '<username>' PASSWORD = '<password>'
Create a connection string variable using string interpolation.
connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
Use the
pyodbc.connect
function to connect to an SQL database.conn = pyodbc.connect(connectionString)
Execute a query
Use an SQL query string to execute a query and parse the results.
Create a variable for the SQL query string.
SQL_QUERY = """ SELECT TOP 5 c.CustomerID, c.CompanyName, COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC; """
Use
cursor.execute
to retrieve a result set from a query against the database.cursor = conn.cursor() cursor.execute(SQL_QUERY)
Note
This function essentially accepts any query and returns a result set, which can be iterated over with the use of cursor.fetchone().
Use
cursor.fetchall
with aforeach
loop to get all the records from the database. Then print the records.records = cursor.fetchall() for r in records: print(f"{r.CustomerID}\t{r.OrderCount}\t{r.CompanyName}")
Save the app.py file.
Open a terminal and test the application.
python app.py
29485 1 Professional Sales and Service 29531 1 Remarkable Bike Store 29546 1 Bulk Discount Store 29568 1 Coalition Bike Company 29584 1 Futuristic Bikes
Insert a row as a transaction
In this example, you execute an INSERT
statement safely and pass parameters. Passing parameters as values protects your application from SQL injection attacks.
Import
randrange
from therandom
library.from random import randrange
Generate a random product number.
productNumber = randrange(1000)
Tip
Generating a random product number here ensures that you can run this sample multiple times.
Create a SQL statement string.
SQL_STATEMENT = """ INSERT SalesLT.Product ( Name, ProductNumber, StandardCost, ListPrice, SellStartDate ) OUTPUT INSERTED.ProductID VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP) """
Execute the statement using
cursor.execute
.cursor.execute( SQL_STATEMENT, f'Example Product {productNumber}', f'EXAMPLE-{productNumber}', 100, 200 )
Fetch the first column of the single result using
cursor.fetchval
, print the result's unique identifier, and then commit the operation as a transaction usingconnection.commit
.resultId = cursor.fetchval() print(f"Inserted Product ID : {resultId}") conn.commit()
Tip
Optionally, you can use
connection.rollback
to rollback the transaction.Close the cursor and connection using
cursor.close
andconnection.close
.cursor.close() conn.close()
Save the app.py file and test the application again
python app.py
Inserted Product ID : 1001