-
Basic Functionality 6
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
Pandas & Indices 4
-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
-
Joins, Unions & Order By 3
-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
-
Group By & Views 3
-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Select
The select statement in SQL is the way in which we try to pull data. Begin by connecting again.
#Let's connect again
conn = sqlite3.connect('Basics.db')
cur = conn.cursor()
SELECT * is the way to query all columns, followed by FROM and the table to pull from. Let’s try executing.
#The way we view all data in a database is by using SELECT * FROM followed by our table name
query = """SELECT * FROM students;"""
cur.execute(query)
You will notice nothing actually is returned. To get the results, we need to iterate over the cursor which will return each row. For example, to get the database rows….
#But we do not actually see the result, to do that we iterate over the return
for row in cur.execute('SELECT * FROM students;'):
print(row)
Replacing * with one column name returns only that column for the pulled data.
#We can also select only one or more column by giving columns instead of *
for row in cur.execute('SELECT name FROM students;'):
print(row)
Adding WHERE after followed by comparisons allows us to filter results. For example, we can pull all records where the grade is greater than 87 with the following added on to our select statement.
#You can also select rows based on a condition
for row in cur.execute('SELECT * FROM students WHERE grade>87;'):
print(row)
Combining the last two queries, we can pull only the names for students who have grades greater than 87.
#If we want only names we can combine the last two queries
for row in cur.execute('SELECT name FROM students WHERE grade>87;'):
print(row)
More than one comparison can be combined through the use of AND/OR, like so:
#There are also clauses we can use such as AND/OR
for row in cur.execute('SELECT name FROM students WHERE grade>87 AND grade<93;'):
print(row)
print()
print()
for row in cur.execute('SELECT name FROM students WHERE grade<87 OR grade>93;'):
print(row)
Adding NOT in front of logic will reverse the truth. Such as the following. Notice that we put the AND statement in parantheses so that we are reversing the combined AND statement.
#If you use NOT you can reverse logic
#But if you have a null value it will not be included in either
for row in cur.execute('SELECT name FROM students WHERE grade>87 AND grade<93;'):
print(row)
print()
print()
for row in cur.execute('SELECT name FROM students WHERE NOT (grade>87 AND grade<93);'):
print(row)
You might notice that 1 of the 4 records is not being shown in either of the two results. This is because it has a null value which is not included in either query. We can, hoever, query for records that have null values or ones that do not with the following as an example.
#To find NULL records....
for row in cur.execute('SELECT name FROM students WHERE grade IS NULL;'):
print(row)
print()
print()
#And non-null
for row in cur.execute('SELECT name FROM students WHERE grade IS NOT NULL;'):
print(row)