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
pokemon.db
Table Name: pkmn
Bulbasaur
Grass
Poison
1
Charmander
Fire
4
Squirtle
Water
7
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 pokemonpkmn_type1 -> TEXT
; represents the pokemon's type from the game's type structurepkmn_type2 -> TEXT
; represents the pokemon's second type if applicablenumber -> 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
SELECT
QueryStructure
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:
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()
.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
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
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 ofpkmn_type1
andpkmn_type2
Expected Output: {
'Grass', 'Fire', 'Water', 'Poison'}
Query from the
pkmn
table then create and print a list in decreasing order based on the columnnumber
Expected Output:
['Squirtle', 'Charmander', 'Bulbasaur']
Connected Reading
Last updated