Join | PythonScript HQ

Using Sqlite to perform a join operation under Eclipse.

In the previous article, we used connection objects to insert values into an existing database. In this article, we will cover performing some simple join operations.

Python Database Programming: A Join Query

The following script implements a simple query that performs a join on the player and team tables:

import os
import sqlite3

conn = sqlite3.connect('my_database')
cursor = conn.cursor()
cursor.execute("""
select player.firstname, player.lastname, team.teamname
from player, team
where player.team = team.teamid
order by player.lastname asc
""")
for row in cursor.fetchall():
print(row)
cursor.close()
conn.close()

When you save and run this script, you will see output like the following:

('Lucas', 'Duda', 'New York Mets')
('Bryce', 'Harper', 'Washington Nationals')
('Travis', "d'Arnaud", 'New York Mets')

This script initializes the connection and cursor in the same manner as the previous script. Then it passes a simple join query to the cursor execute method. This query selects two columns from the player table and one from the team table. This is a simple query, but you will still want to format your queries so they are readable, similar to what is shown here.

When working with user interfaces, you will often need to expand IDs stored in the database to human-readable values. In this case, for example, the query expands the team ID, querying for the team name. You can’t expect people to remember the meaning for numeric IDs.

They query also orders the results by the players’ last names in ascending order. This means that it starts at the beginning of the alphabet, which is what you would normally expect. However, you can reverse this and have them sorted in descending order. Note that it doesn’t work quite the way I wanted it to; “d’Arnaud” appears last because it was double-quoted.

After calling the execute method, the data, if any was found, is stored in the cursor object. You can use the fetchall method to extract the data. You can also use the fetchone method to fetch one row at a time from the results. Note also how the data appears as Python tuples.

In order to perform the next join operation, we must first run the following script:

import os
import sqlite3
conn=sqlite3.connect('my_database')
cursor=conn.cursor()

# Create tables
cursor.execute("""
create table user
    (idnum integer,
    username varchar)
""")

# Insert data into the table
cursor.execute("""
insert into user(idnum, username)
values(100, 'travis')
""")


conn.commit()
cursor.close()
conn.close()

This script creates a new table (user) and inserts one record: an idnum (100) with a corresponding username (travis).

Now we are ready to run the following script:

import sqlite3
conn = sqlite3.connect('my_database')
cursor = conn.cursor()
username = 'travis'
query = """
select u.username,p.firstname,p.lastname,t.teamname
from user u, player p, team t where username=?
and u.idnum = p.idnum
and p.team = t.teamid
"""
cursor.execute(query, (username,))
for row in cursor.fetchall():
    (username,firstname,lastname,teamname) = row
    name=firstname + " " + lastname
    print(username,":",name,"plays for the",teamname)
cursor.close()
conn.close()

When you run this script, you should see the following:

travis : Travis d'Arnaud plays for the New York Mets

This script performs a join on all three example tables, using table-name aliases to create a shorter query. The purpose is to find a given user in the database by searching for that user name. The script also shows an example of expanding both the player’s ID to the player’s name and the team’s ID to the team’s name. All of this makes for more readable output.

This example also shows how you can extract data from each row into Python variables. For example:

(username,firstname,lastname,teamname) = row

An important new feature of this script is the use of a question mark to enable you to build a query using dynamic data. When you call the execute method on the Cursor, you can pass a tuple of dynamic data, which the execute method will fill in for the question marks in the SQL statement. Each element in the tuple is used, in order, to replace the question marks. [Thus, you need to have as many dynamic values as you do in the SQL statement.]

query = """
select u.username,p.firstname,p.lastname,t.teamname
from user u, player p, team t where username=?
and u.idnum = p.idnum
and p.team = t.teamid
"""

cursor.execute(query, (username,))

The query used in this example is very helpful when you want to start updating rows in the tables, because users will want to enter meaningful values.