Contents

• Overview
• How to Query a Database in Python?
• How to Setup a Python Program for SQLite Interaction
• Example 1
• Example 2
• SQL Code or Python Code?
• Embedded SQL + Python Variables
• Dynamic SQL Example
• Practice

Overview

Interacting with a database from a programming language is a critical skill for programmers to learn. We do this in all mannger of applications, such as web pages, desktop applications, mobile device applications, enterprise and server applications, and many other categories. Programmers write code to interact with databases behind all of these types of applications. The most common action programmers write in these contexts are queries.

Here's an example, let's say we've written an application that requires users to log in through a Graphic User Interface (GUI). When a user attempts to log into the application the program needs to confirm the user's login and password are valid. The programmer does this by comparing the login and password entered by the user with a user record stored in a database. A simplified image of this process might look like this:

In this example, the user types their login and password and clicks the Login button. The program creates a query that includes the login and password provided by the user and sends that query to the database engine. We can think of this as asking the database a question, in this example we might verbalize the question as Is there a user in the database with this login and password combination? The result of the question (query) to the database is returned to the program as a result set, which may contain a result or not. In this example, if there is not a result then there is no user record that matches the login and password entered by the user. So, in that case, the program would inform the user their login and/or password are invalid. Otherwise, since there is a user with the login and password combination so the login is successful and the program would then allow the user into the system.

That example demonstrates, at a high level, how the programmer uses SQL queries to ask the database a question (query) and then respond to the response to that question.

In order to ask a question of the database, that is, send it a query, from within a Python program, we embed SQL query code in our Python program and instruct Python to pass that query to the database engine. This is often referred to as embedded SQL. The database engine processes the SQL query and returns a result set to the Python program. Once the result set is returned, we can then use Python code to work with that result as we need to depending on the context of the program.

A common approach to getting the SQL part of this process working correctly is to write the SQL in our database IDE, instead of directly in Python, at first. In this way, we can confirm that our SQL query produces the results we need and then we can copy the SQL code from the database IDE over into our Python programming environment. For example, let's take one of the queries from the DemoQueries.sql script file we looked at previously.

First, I'll open SQLite Studio and open the DemoQueries.sql file:

Note that I have highlighted one of the queries. This query asks the question Are there any students whose last name is Summers? If I click the Run button I see the following result:

The result set indicates that there is one student with the last name Summers. Also, because the query reads SELECT * it means we're asking for all columns in the students table to be part of our result set.

In the next section, we'll take a look at how to bring the SQL into Python and work with the query results.

To interact with SQLite in Python there are a few key concepts to understand that's we'll cover here before looking at a full code example. Take a look at the following partial Python code file and read the detailed notes below. You can use it as a template for interacting with a SQLite database from Python.

Notes

• Code Line 1: The first element we need is to import the sqlite3 library. This provides the functionality to then connect to databases, query them, etc.
• Code Line 3: The next element is to connect to your database. If a connection to the database is successful then a connection object is created, in this example, stored in the variable conn.
  • A: In this example, A refers to the path to your SQLite database file that ends in .db. For example, something like C:\SQLite\Data\grading_system.db.
• Code Line 5: This line of code demonstrates using the connection established on Code Line 3 above to pass a query to the database engine, using the execute function of the connection object conn. When the database engine returns a result it is stored in the cursor variable.
  • B: In this example, B is where you place the SQL code for your query. For example SELECT * FROM students WHERE last_name = 'Summers';".
• Code Lines 7 thru 10: There are many different ways to process the results of a query. This example shows a for loop that iterates through each row in a result set. The code inside the loop would be applied to each of those rows. For example, using the sample query above, we could print a report of each student who appears in the result set.
• Code Line 11: Here we use the close() function of the connection object to close the database connection. This is a best practice in coding.

Now let's take a look at a full code example. We'll use the query that we considered above: SELECT * FROM students WHERE last_name = 'Summers';". We know from testing that query in SQLite Studio that it returns at least one student record. So, using the template example above, we'll implement that query here:

Python Code

import sqlite3
connection = sqlite3.connect("c:\SQLite\Data\grading_system.db")
cursor = connection.execute("SELECT * FROM Students WHERE last_name = 'Summers';")
for row in cursor:
    print("Student ID:", row[0])
    print("First Name:", row[1])
    print("Last Name:", row[2])
    print("Class Standing:", row[3])
    print("School ID:", row[4])
    print("Cumulative GPA:", row[5])
    print("Major ID:", row[6])
connection.close()

Output

Student ID: 27
First Name: Amelia
Last Name: Summers
Class Standing: Junior
School ID: S28816323
Cumulative GPA: 3.44
Major ID: 8

Notes

• Code Line 1: Here we import the sqlite3 library.
• Code Line 2: We then establish a connection to the grading_system database. Note that the full path is used here. In production systems it is rare to hardcode a full path, especially if the application will be used in environments where the database files may be stored in various locations.
• Code Line 3: With the conn established, we then pass the query to the database engine and wait for a result set to be returned to the Python program from the database engine.
• Code Line 4: Using the cursor object, the for loop will iterate each row of the result set and, in this example, print each attribute from the students table (Student ID, First Name, Last Name, etc.).
• Output: The output shows that each print statement in this example places an attirbute on its own line.
• Code Lines 5 thru 11: These lines use individual print statements for each result set row attribute. This example demonstrates that we can treat each attribute individually to process them in whatever way we need.
• Code Line 12: Closes the database connection.

This code example works fine and demonstrates how to use the template above to query a database table. Also, it works fine for a query that results in just one record, however what if our result set has multiple records? The output would not be ideal. When result sets contain multiple records it is more common to print them in columnar format, that is, in rows and columns. Example 2 demonstrates this approach.

In this second example, first we'll return to SQLite Studio and test a different query. This time we'll use SELECT * FROM students WHERE last_name IN ('Cooper', 'Summers'); which will give us a result set with three student records, as seen here:

In Python, we'll use the same code as Example 1, except we'll replace the query statement to the new one:

Python Code

import sqlite3
connection = sqlite3.connect("c:\SQLite\Data\grading_system.db")
cursor = connection.execute("SELECT * FROM Students WHERE last_name IN ('Cooper', 'Summers');")
for row in cursor:
    print("Student ID:", row[0])
    print("First Name:", row[1])
    print("Last Name:", row[2])
    print("Class Standing:", row[3])
    print("School ID:", row[4])
    print("Cumulative GPA:", row[5])
    print("Major ID:", row[6])
connection.close()

Notes

• Code Line 3: Note the changed query. This will result in 3 student records.
• Code Lines 4 thru 11: This code is unchanged from Example 1, it will print each row (student) with multiple print statements, one per attribute, so it will look like this ...

Output

Student ID: 27
First Name: Amelia
Last Name: Summers
Class Standing: Junior
School ID: S28816323
Cumulative GPA: 3.44
Major ID: 8
Student ID: 68
First Name: Kendra
Last Name: Cooper
Class Standing: Senior
School ID: S35743296
Cumulative GPA: 4
Major ID: 27
Student ID: 184
First Name: Patricia
Last Name: Cooper
Class Standing: Senior
School ID: S40063558
Cumulative GPA: 4
Major ID: 25

Notes

• As we can see, there are three student records printed. However, the output is not well formatted and is difficult to differentiate between students.
• It is more common, when printing results of multiple records to format the output as a columnar (rows & columns) report. So, let's change the for loop code and try again, this is called refactoring:

Refactored Python Code

import sqlite3
connection = sqlite3.connect("c:\SQLite\Data\grading_system.db")
cursor = connection.execute("SELECT * FROM Students WHERE last_name IN ('Cooper', 'Summers');")
print("-" * 50)
print("ID\t\tName\t\tClass\tGPA")
print("-" * 50)
for row in cursor:
    print(row[4] + "\t" + row[1] + " " + row[2] + "\t" + row[3] + "\t" + str(row[5]))
print("-" * 50)
connection.close()

Output

--------------------------------------------------
ID              Name            Class   GPA
--------------------------------------------------
S28816323       Amelia Summers  Junior  3.44
S35743296       Kendra Cooper   Senior  4.00
S40063558       Patricia Cooper Senior  4.00
--------------------------------------------------

Notes

• The first thing to notice here is the improvement of the printed output. It is now more readable and well-formatted.
• Study the refactored Python code and notice the key difference here is inside the for loop.
• Notice that in the loop, we are using a single print statement and selectively choosing which row[#] attribute to use, the order in which we want them displayed and their position in each row.
• As we see here, we can work with each row[#] element as needed as they are independently accessible, like variables.

Now that we've seen a couple of simple examples of embedded SQL in Pytyon, a key factor in deciding how to build database-driven applications is where does the logic belong? That is, what belongs in the SQL code and what belongs in the Python code?

In general, the SQL code should be used to take advantage of the database engine's strengths of working with sets of data. Take the last query we used above for example: SELECT * FROM students WHERE last_name IN ('Cooper', 'Summers');. This query takes advantage of the database engine and very quickly finds three student records that match the critieria based on last name. If we had to do that same thing with Python, it would be row by row which would be very inefficient.

Given the power of the database engine, one of the challenges for programmers is to learn how to take advantage of that power, which means learning as much about SQL coding as possible. There are many free resources available for learning SQL online.

On the other hand, our Python code handles all aspects of our application, including coordinating the embedded SQL with the database engine and then taking the appropriate action(s) on the result sets from the database. In Example 2, that means printing a well-formatted report of the results.

Let's consider a further example with the SQL Code or Python Code? question in mind. Let's say we are asked to modify Example 2 above so that our printed report is sorted last name and then first name. Looking at the output we see that it is not in that order currently. Where do we change that--in the SQL code or the Python code?

We could make this change in either SQL or Python, but which is more efficient? Sorting the result set is best accomplished in the SQL code because the database engine is very well suited to accomplish that task and the code change is minimal. We can simply add an ORDER BY clause to the embedded SQL query. Nothing else about our program would have to change, like this ...

Refactored Python Code

import sqlite3
connection = sqlite3.connect("c:\SQLite\Data\grading_system.db")
cursor = connection.execute("SELECT * FROM Students WHERE last_name IN ('Cooper', 'Summers') ORDER BY last_name, first_name;")
print("-" * 50)
print("ID\t\tName\t\tClass\tGPA")
print("-" * 50)
for row in cursor:
    print(row[4] + "\t" + row[1] + " " + row[2] + "\t" + row[3] + "\t" + str(row[5]))
print("-" * 50)
connection.close()

Output

--------------------------------------------------
ID              Name            Class   GPA
--------------------------------------------------
S35743296       Kendra Cooper   Senior  4.00
S40063558       Patricia Cooper Senior  4.00
S28816323       Amelia Summers  Junior  3.44
--------------------------------------------------

Notes

• Note that the only change to the code is the ORDER BY clause, no changes to the Python code is needed.
• The output now lists the students sorted alphabetically.

This is a simple example of deciding where to implement changes to database-driven applications.

So far the embedded SQL we've seen has been fully hardcoded, that is, it will always run the same way. However, it is more common that there are conditions that need to be considered as part of what we want to query in the database.

Let's consider or first query above SELECT * FROM students WHERE last_name = 'Summers';". This query will always look for students with the last name Summers. This is not very flexible, what if I want to see all students with other last names? We can combined our embedded SQL with Python variables to create dynamic embedded SQL. In this manner, we can make our application very flexible and more powerful.

Let's consider a version of example above that uses dynamic SQL...

In the examples above our query had the last name hardcoded in the SQL query code. It is more common that we would want the query to be dynamic in Python, that is, we want to allow the user to be able to enter a last name and the query will ask the database engine for all students with that last name.

To accomplish this, we can use the similar code as above, but with an added user prompt to enter a name and then a change to the query to be dynamic, that is, using the name entered by the user each time.

Here's the refactored code and discussion...

Refactored Python Code

import sqlite3
connection = sqlite3.connect("c:\SQLite\Data\grading_system.db")
last_name = input("Enter a Last Name: ")
cursor = connection.execute("SELECT * FROM Students WHERE last_name = '" + last_name + "' ORDER BY last_name, first_name;")
print("-" * 50)
print("ID\t\tName\t\tClass\tGPA")
print("-" * 50)
for row in cursor:
    print(row[4] + "\t" + row[1] + " " + row[2] + "\t" + row[3] + "\t" + str(row[5]))
print("-" * 50)
connection.close()

Sample Run & Output

Enter a Last Name: Jones

--------------------------------------------------
ID              Name            Class      GPA
--------------------------------------------------
S28544679       Julia Jones     Freshman   4.00
S23001736       Pablo Jones     Sophomore  3.44
--------------------------------------------------

Notes

• Code Line 3: This new line prompts the user for a last name and stores the user's entry in the last_name variable.
• Code Line 4 is then changed to use the last_name variable in place of the previously hardcoded last name value. Notice the syntax here, the variable is being concatenated into the query string. Be careful when doing this, notice, for example, that it is easy to leave out single quote marks that are needed by SQL to surround strings in a query statement. Notice the difference between the following two versions of Code Line 4:
  • cursor = conn.execute("SELECT * FROM students WHERE last_name = '" + last_name + "' ORDER BY last_name, first_name;")
  • cursor = conn.execute("SELECT * FROM students WHERE last_name = " + last_name + " ORDER BY last_name, first_name;")
  The first one is correct, it include the single quote needed in the embedded SQL ('" and "'). The second one is incorrect because it does not include the single quotes.
• Also note, the rest of the Python code is unchanged. The dynamic embedded SQL takes care of the result set and the Python code then processes it the same way in the for loop.

I recommend using the above code examples as a starting point and then practicing with the dynamic embedded SQL concepts by changing the query in different ways. You can use the DemoQueries.sql file to get some ideas to work with.