In this guide, I’ll show you how to use SQL in Python after connecting to a database. I’ll review a simple example using MS Access, but similar concepts would apply if you connect Python to other databases, such as in the case of Oracle, or SQL Server.
The Example
To start, let’s say that you created a table in Access. For demonstration purposes, I created a table in Access, where:
- The table name is: tracking_sales
- The database name is: testdb
This is how the tracking_sales table would look like in Access:
The Goal
Suppose that you want to derive the revenue for each record by performing a simple multiplication:
revenue = (units_ordered) * (product_price_per_unit)
There are two options you can go about it:
- Either perform the calculations in MS Access; or
- Apply the calculations in Python
Let’s now review the second option of applying the calculations in Python. To accomplish this goal, we will use SQL in Python.
Steps to Apply SQL in Python
(1) First, you’ll need to connect Python to MS Access using the Pyodbc package.
You can use the code below to connect Python to Access. Note that you’ll need to change the path name to the location where your Access database is stored on your computer:
import pyodbc conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;') cursor = conn.cursor()
(2) Once you established the connection with Access, you’ll be able to start writing the SQL in Python to get your desired results. In our example, we will use SQL to calculate the revenue.
Here is the complete Python code:
import pyodbc conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;') cursor = conn.cursor() cursor.execute( '''select client_id, client_first_name, client_last_name, units_ordered, product_price_per_unit, product_name, ((units_ordered) * (product_price_per_unit)) AS revenue from tracking_sales''') for row in cursor.fetchall(): print (row)
(3) Run the code in Python. You’ll notice that the revenue is calculated for each record (at the last column created):
(4) But what if you want to get the total revenue?
In this case, the total revenue would be the sum of all revenues across all the individual records.
Here is the syntax that you can use:
import pyodbc conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;') cursor = conn.cursor() cursor.execute( '''select sum(((units_ordered) * (product_price_per_unit))) AS total_revenue from tracking_sales''') for row in cursor.fetchall(): print (row)
When you run the code in Python, you’ll get the total revenue of 4350.
Now what if you want to connect additional tables using joins?
Using Joins
Let’s say that you have a second table in Access called shipping_country:
You can then connect the tracking_sales table to the shipping_country table using the client_id field. You’ll need to make sure that the client_id field has the same format under both tables.
And this is the full Python code:
import pyodbc conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\Ron\Desktop\Test\testdb.accdb;') cursor = conn.cursor() cursor.execute( '''select ts.client_id, ts.client_first_name, ts.client_last_name, ts.units_ordered, ts.product_price_per_unit, ts.product_name, ((ts.units_ordered) * (ts.product_price_per_unit)) AS revenue, sc.country from tracking_sales ts left join shipping_country sc on ts.client_id = sc.client_id ''') for row in cursor.fetchall(): print (row)
Once you run the Python code, you’ll see the country name at the end of each record: