SELECT in SQLite and Using it in Python

SELECT is the main command that retrieves data from SQLite databases. We have encountered it before when I connected to an SQLite database. I then used SELECT to collect data from both the master table about what tables were within the database:
>>curs.execute("SELECT * FROM sqlite_master WHERE type = 'table';")
and also from another table:
>>> curs.execute("SELECT * FROM Location;")

As you can see, the SELECT commands are both part of execute commands, sent from Python via the curs object to SQLite.
In both commands, the general format is:
SELECT column_names FROM table_name (conditions/predicate);

and as we have seen, you then need to use curs.fetchall() to retrieve the data from SQLite back to Python.
The * in both commands where column names should be tells SQLite to return data from all columns in the table. If we go back to our database of historical figures:
>>> import sqlite3
>>> conn = sqlite3.connect("C:\\sqlite\\db\\history1.db")
>>> curs = conn.cursor()
>>> curs.execute("SELECT * FROM historicalfigures;")
<sqlite3.Cursor object at 0x0369B620>
>>> fulldump = curs.fetchall()
>>> for line in fulldump:
 print(line)

('Leonardo da Vinci', 1452, 1519, 'Artist', 'Italy')
('Niccolo Machiavelli', 1469, 1527, 'Politician & Author', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 1473, 1530, 'Churchman & Politician', 'England')
('William Caxton', 1422, 1491, 'Publisher', 'England')
('William Shakespeare', 1564, 1616, 'Author & Playwright', 'England')
('Francis Drake', 1540, 1596, 'Explorer', 'England')
('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
>>>
As you can see, I've added a few more records to the table.
If I want only a few columns I can adjust the SELECT command accordingly:
>>> curs.execute("SELECT name, nation FROM historicalfigures;")
<sqlite3.Cursor object at 0x0369B620>
>>> partialdump = curs.fetchall()
>>> for line in partialdump:
 print(line)

('Leonardo da Vinci', 'Italy')
('Niccolo Machiavelli', 'Italy (Florence)')
('Cardinal Thomas Wolsey', 'England')
('William Caxton', 'England')
('William Shakespeare', 'England')
('Francis Drake', 'England')
('Henry VIII', 'England')
('Elizabeth I', 'England')
>>>

What if you are not sure about what columns there are in a table, or you aren't sure of their exact names? I found a convenient SQLite command called PRAGMA.
>>> curs.execute("PRAGMA table_info('historicalfigures');")
<sqlite3.Cursor object at 0x0369B620>
>>> pragdump = curs.fetchall()
>>> for pragline in pragdump:
 print(pragline)

(0, 'Name', 'text', 0, None, 1)
(1, 'YearBirth', 'integer', 0, None, 0)
(2, 'YearDeath', 'integer', 0, None, 0)
(3, 'Role', 'text', 0, None, 0)
(4, 'Nation', 'text', 0, None, 0)
>>> 
I'm not entirely sure what the last two columns in this pragma table are, but it is clear the first one is the order of the columns, the second is the name of each column and the third is the data type.

So what if you've got a table of data and you want to find all the records that match a simple criterion - for example, all the historical figures who were monarchs. This is where the SQL conditional WHERE comes in use. I think of it a bit like an if structure in Python - SQLite checks if the WHERE condition is true for each row/record, and if so it is included in the results.

>>> curs.execute("SELECT * FROM historicalfigures WHERE Role = 'Monarch';")
<sqlite3.Cursor object at 0x0369B620>
>>> filterdump = curs.fetchall()
>>> for line in filterdump:
 print(line)

('Henry VIII', 1491, 1547, 'Monarch', 'England')
('Elizabeth I', 1533, 1603, 'Monarch', 'England')
>>>
For columns that are numbers (integers or real/floats), you can also use > (greater than) or < (less than) with WHERE to select records greater or less than a particular number. For example, which people in the table were born before 1500?

>>> curs.execute("SELECT Name, YearBirth FROM historicalfigures WHERE YearBirth < 1500;")
<sqlite3.Cursor object at 0x0369B620>
>>> filterdump = curs.fetchall()
>>> for line in filterdump:
 print(line)

('Leonardo da Vinci', 1452)
('Niccolo Machiavelli', 1469)
('Cardinal Thomas Wolsey', 1473)
('William Caxton', 1422)
('Henry VIII', 1491)
>>>
I have only scratched the surface of what SELECT can do, but it is enough for us to get started. I may come back to SELECT and expand on its capabilities later.