SELECT Queries

Python Code Template

# Python + SQLite Lesson 1

# Step 1: import SQLite3 Module
import sqlite3

# Step 2: create a connection with a SQL database file
connection = sqlite3.connect("pokemon.db")

# Step 3: create a cursor that helps us to communicate with a the database
cursor = connection.cursor()

# Step 4: execute SQL queries with a method called ".execute()" from the cursor

# QUERIES GO HERE

# Step 5: when finished with the database, close your cursor and connection
cursor.close()
connection.close()

This Tutorial's Database: pokemon.db

Table Name: pkmn

name	pkmn_type1	pkmn_type2	number
Bulbasaur	Grass	Poison	1
Charmander	Fire		4
Squirtle	Water		7

Aside: What is a Pokemon?

In the table above, we have an example snapshot of a table that exists within pokemon.db file that is included in the lesson1 folder.

Database Explained

• Columns (Fields, Properties) contain the following data's information

  • name -> TEXT ; represents the names of the pokemon

  • pkmn_type1 -> TEXT ; represents the pokemon's type from the game's type structure

  • pkmn_type2 -> TEXT; represents the pokemon's second type if applicable

  • number -> INTEGER; represents the number assigned to each pokemon based on the Pokedex order

• Rows (Records, Instances) contain the actual data

  • Each row within a table will have all, some or none of the column data available for the given data

  • When inserted, the data must follow the datatype rule set by the column

SELECT Query

Structure

SELECT column1_name, column2_name, ... column100_name FROM table_name

To retrieve (see -or- lookup) data from a SQL database, we can use a SELECT statement.

In SQL, commands that are reserved for SQL are to be capitalized.

Get all data from a table

SELECT * FROM pkmn

The following statement will generate:

name	pkmn_type1	pkmn_type2	number
Bulbasaur	Grass	Poison	1
Charmander	Fire		4
Squirtle	Water		7

In SQL, the asterisk (*) is used in a SELECT statement to indicate that you want to retrieve all columns from the specified table(s). This is a shorthand way of selecting every column without having to list each one individually.

Python Code

# Between Step 4 & Step 5.
# 1. Define your query in a multi-line string
query = ''' 
SELECT * FROM pkmn
'''

# 2. Run the query using cursor's execute method
result = cursor.execute(query)

# 3. See all the data provided by .fetchall() method from 
#    the cursor object called result
print(f"Our Query Result:\n{result.fetchall()}")

Python Output:

Our Query Result:
[('Bulbasaur', 'Grass', 'Poison', 1), ('Charmander', 'Fire', None, 4), ('Squirtle', 'Water', None, 7)]

Understanding .fetchall()

When you use fetchall() after executing a SELECT query in SQLite using Python's sqlite3 module, the method returns a list of tuples, where each tuple represents a row from the result set.

• If you select multiple columns, each tuple will contain multiple values, corresponding to the selected columns.

• If you select a single column, each tuple will contain a single value.

However, even if only one column is selected, the result will still be returned as a list of single-value tuples (often referred to as "singleton tuples").

This is because the database cursor's fetchall() method is designed to consistently return a list of tuples, ensuring a uniform structure regardless of the number of columns selected.

Get data from a single column

SELECT name FROM pkmn

name
Bulbasaur
Charmander
Squirtle

You can specify which columns are you are interested by explicitly stating the columns of interest.

Python Code

# Between Step 4 & Step 5.
# 1. Define your query in a multi-line string
query = ''' 
SELECT name FROM pkmn
'''

# 2. Run the query using cursor's execute method
result = cursor.execute(query)

# 3. See all the data provided by .fetchall() method from 
#    the cursor object called result
print(f"Our Query Result:\n{result.fetchall()}")

Python Output:

Our Query Result:
[('Bulbasaur',), ('Charmander',), ('Squirtle',)]

Selecting multiple columns

SELECT number, name FROM pkmn

number	name
1	Bulbasaur
4	Charmander
7	Squirtle

# Between Step 4 & Step 5.
# 1. Define your query in a multi-line string
query = ''' 
SELECT number, name FROM pkmn
'''

# 2. Run the query using cursor's execute method
result = cursor.execute(query)

# 3. See all the data provided by .fetchall() method from 
#    the cursor object called result
print(f"Our Query Result:\n{result.fetchall()}")

Python Output:

Our Query Result:
[(1, 'Bulbasaur'), (4, 'Charmander'), (7, 'Squirtle')]

As you can see the column order does not matter as long as your choose column names that exists.

Exercises

• Query from the pkmn table such that you output a set (or a list) with all the types that can be found in the columns of pkmn_type1 and pkmn_type2

  • Expected Output: {'Grass', 'Fire', 'Water', 'Poison'}

• Query from the pkmn table then create and print a list in decreasing order based on the column number

  • Expected Output: ['Squirtle', 'Charmander', 'Bulbasaur']

Connected Reading

• SQLite Tutorial Site (Link)
• SQL Tutorial by w3School (Link)