Read from Database with SQLite
In the previous tutorials, we've covered creating a database and populating one, now we need to learn how to read from the database. Reading from a database is where the power of using something like SQLite over a flat file starts to make sense. While we can query the entire table, we can instead just query a single column, or even based on specific row values. Let's see what I mean:
Code up to this point:
import sqlite3
import time
import datetime
import random
conn = sqlite3.connect('tutorial.db')
c = conn.cursor()
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")
def data_entry():
c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
conn.commit()
c.close()
conn.close()
def dynamic_data_entry():
unix = int(time.time())
date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
keyword = 'Python'
value = random.randrange(0,10)
c.execute("INSERT INTO stuffToPlot (unix, datestamp, keyword, value) VALUES (?, ?, ?, ?)",
(unix, date, keyword, value))
conn.commit()
#create_table()
#data_entry()
for i in range(10):
dynamic_data_entry()
time.sleep(1)
c.close
conn.close()
Next, let's create another function, calling it read_from_db:
def read_from_db():
c.execute('SELECT * FROM stuffToPlot')
data = c.fetchall()
print(data)
for row in data:
print(row)
Notice that when we execute the query, we're executing it with the cursor as usual. Then, to access the data from the cursor, we use c.fetchall(). Think of this again, much like the computer cursor. The select is like you highlighted, then you do c.fetchall(), so this is like you are copying your highlighted information. Finally, we can either just print the data, or iterate through it, like pasting the information. Also, note that we are not needing to do a conn.commit(). There's nothing to save.
Output:
[(1452554968.0, '2016-01-11 15:29:28', 'Python', 8.0), (1452554969.0, '2016-01-11 15:29:29', 'Python', 3.0), (1452554970.0, '2016-01-11 15:29:30', 'Python', 4.0), (1452554971.0, '2016-01-11 15:29:31', 'Python', 3.0), (1452554972.0, '2016-01-11 15:29:32', 'Python', 4.0), (1452554973.0, '2016-01-11 15:29:33', 'Python', 7.0), (1452554974.0, '2016-01-11 15:29:34', 'Python', 2.0), (1452554975.0, '2016-01-11 15:29:35', 'Python', 3.0), (1452554976.0, '2016-01-11 15:29:36', 'Python', 0.0), (1452554977.0, '2016-01-11 15:29:37', 'Python', 1.0)] (1452554968.0, '2016-01-11 15:29:28', 'Python', 8.0) (1452554969.0, '2016-01-11 15:29:29', 'Python', 3.0) (1452554970.0, '2016-01-11 15:29:30', 'Python', 4.0) (1452554971.0, '2016-01-11 15:29:31', 'Python', 3.0) (1452554972.0, '2016-01-11 15:29:32', 'Python', 4.0) (1452554973.0, '2016-01-11 15:29:33', 'Python', 7.0) (1452554974.0, '2016-01-11 15:29:34', 'Python', 2.0) (1452554975.0, '2016-01-11 15:29:35', 'Python', 3.0) (1452554976.0, '2016-01-11 15:29:36', 'Python', 0.0) (1452554977.0, '2016-01-11 15:29:37', 'Python', 1.0)
In the above case, we're just outputting the entire table. We could have used a flat file for this. Let's try to add in some logic next:
c.execute('SELECT * FROM stuffToPlot WHERE value = 3')
data = c.fetchall()
print(data)
for row in data:
print(row)
Output:
[(1452554969.0, '2016-01-11 15:29:29', 'Python', 3.0), (1452554971.0, '2016-01-11 15:29:31', 'Python', 3.0), (1452554975.0, '2016-01-11 15:29:35', 'Python', 3.0)] (1452554969.0, '2016-01-11 15:29:29', 'Python', 3.0) (1452554971.0, '2016-01-11 15:29:31', 'Python', 3.0) (1452554975.0, '2016-01-11 15:29:35', 'Python', 3.0)
This time, we only referenced the data in the table where the value for the "value" column was 3.
What about comparison operators? No problem:
c.execute('SELECT * FROM stuffToPlot WHERE unix > 1452554972')
data = c.fetchall()
print(data)
for row in data:
print(row)
In this case, we're selecting all of the rows that have the value in the unix column greater than 1452554972 (you will want to consider changing that value depending on when you are doing the tutorial).
Not only can you specify which rows you want, you can also specify which columns, and even in what order:
c.execute('SELECT value, datestamp FROM stuffToPlot WHERE unix > 1452554972')
data = c.fetchall()
print(data)
for row in data:
print(row[0])
This time, we're selecting the value and datestamp columns. In the database, datestamp comes first, value second, and there are of course other columns. Here, we're selecting value and datestamp, in that order. Also note that this time we're printing out row[0]. You use indexing like this to reference specific elements. Since you can be explicit about the order of columns, coding this in should not be a challenge.
Full code to this point:
import sqlite3
import time
import datetime
import random
conn = sqlite3.connect('tutorial.db')
c = conn.cursor()
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")
def data_entry():
c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
conn.commit()
c.close()
conn.close()
def dynamic_data_entry():
unix = int(time.time())
date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
keyword = 'Python'
value = random.randrange(0,10)
c.execute("INSERT INTO stuffToPlot (unix, datestamp, keyword, value) VALUES (?, ?, ?, ?)",
(unix, date, keyword, value))
conn.commit()
time.sleep(1)
def read_from_db():
c.execute('SELECT * FROM stuffToPlot')
data = c.fetchall()
print(data)
for row in data:
print(row)
c.execute('SELECT * FROM stuffToPlot WHERE value = 3')
data = c.fetchall()
print(data)
for row in data:
print(row)
c.execute('SELECT * FROM stuffToPlot WHERE unix > 1452554972')
data = c.fetchall()
print(data)
for row in data:
print(row)
c.execute('SELECT value, datestamp FROM stuffToPlot WHERE unix > 1452554972')
data = c.fetchall()
print(data)
for row in data:
print(row[0])
read_from_db()
c.close
conn.close()
In the next tutorial, we're going to show an example of how you might graph this data from the database.
There exists 5 challenge(s) for this tutorial. for access to these, video downloads, and no ads.
There exists 9 quiz/question(s) for this tutorial. for access to these, video downloads, and no ads.
