PyBloom coding project part 9: Working with the database

Over the previous parts, I've shown how I set up my coding environments, and how I wrote the main program. In this part 9, I look at how I set up my external database, including learning SQL.

Database utilities

The database is set up automatically on first connection, but the tables within need to be instantiated.

Global libraries and constants

import sqlite3

DEFAULT_DB = 'database.sqlite3'

All will become clear later!

con = db_connect()  # connect to the database

cur = con.cursor()  # instantiate a cursor obj

Let’s connect to the database and start creating our two tables.

Table of Bloom colours

colours_sql = """

CREATE TABLE IF NOT EXISTS colours (

We create here the colours table, using the modifier IF NOT EXISTS to make sure we don’t delete a load of data if we accidentally run this script after the system is live.

id integer PRIMARY KEY,

temperature integer UNIQUE,

hex_value text NOT NULL

The three columns created are:

• The primary key, id, constrained to be an integer

• The temperature, constrained to be unique (as we should never have one temperature associated with more than one hex value)

• The corresponding hex_value, which is constrained to be text since SQLite doesn’t have a hex data type, and can’t not exist if the temperature exists

The constraints will prevent the code from breaking as I’ve not written much error case handling.

cur.execute(colours_sql)

We need to pre-populate this colours table.

temp_colours = {  

    40: 'ff0000',  # colour value if temperature is >= than key

    35: 'ff4000',  # note: value is string, not hex

    30: 'ff8000',

    25: 'ffbf00',

    20: 'ffff00',

    15: 'bfff00',

    10: '00ff80',

     5: '00ffbf',

     0: '00ffff',

    -5: '00bfff',

   -10: '0080ff',

   -15: '0040ff'

}

These values are taken from https://www.w3schools.com/colors/colors_picker.asp. The hex values are actually strings, even though Python can very happily handle hex natively. This is because the value is intended to be used by the Python RGBxy library, the Python PyGal library, the database and the web page CSS; and neither RGBxy nor SQL can understand hex. String is therefore the common format across all of them.

colours_sql = '''INSERT OR IGNORE INTO colours (temperature, hex_value)

                 VALUES (?, ?)'''

By using the OR IGNORE modifier, we can be sure that we’re not overwriting any value if the table already exists and the script has been run in error. (I haven’t written all CRUD operations as this script is for create only; I’ll be writing the update and delete operations in the main code.) 

for temperature, hex_value in temp_colours.items():

    cur.execute(colours_sql, (temperature, hex_value))

The SQLite query above has spaces for two parameters, which we’ll pass as we iterate through the temp_colours dictionary. Every key, value pair gets written as a new row.

Table of observations

observations_sql = """

CREATE TABLE IF NOT EXISTS observations (

    id integer PRIMARY KEY,

    timestamp text UNIQUE,

    temperature real NOT NULL,

    detailed_status text NOT NULL)"""

cur.execute(observations_sql)

The rows for the table of observations are:

• The primary key, id, constrained to be an integer

• The timestamp, constrained to be unique (as we should never be able to make more than one observation simultaneously)

• The corresponding observation values: temperature (constrained to be real since we want to keep the decimals) and detailed_status (constrained to be text) 

con.commit()

con.close()  # close connection

Finally, we commit the changes and close the connection.

Utility function to connect to the database

def db_connect(db_file=DEFAULT_DB):

    # create connection to SQLite database

    connection = sqlite3.connect(db_file)

    return connection

Because we only have one place where we declare the database filename, it means all the code remains coherent.

Utility function to get rows from a table

def get_rows(table, columns='*', **kwargs):

To make this utility flexible, it’s meant to simplify 3 use cases: 

1. Getting all the rows from the table, in which case you don’t have to call this function with any further parameters beyond the table name

2. Getting only the rows that fulfil a condition described in an optional dictionary of keyword arguments (**kwargs) (defaults to no filters)

3. Getting only the named columns (defaults to all columns)

if (table == 'colours') or (table == 'observations'):

Some rudimentary logic to prevent calling for a table that doesn’t actually exist, as the code doesn’t handle SQLite errors.

con = db_connect()  # connect to the database

Here we reuse the first utility function to connect to the database. We don’t have to remember which database file and where because we’re using the utility. 

con.row_factory = sqlite3.Row

The next statement refactors the output from the queries into a list of dictionaries, which makes it a little easier to access the content. In this list, each item is a row, and each item consists of a dictionary of column name and value pairs. Each row is accessed directly by its index, but each column can be accessed by index or label. So to access the hex_value for the first row we use result[0][2] or result[0]['hex_value'].

cur = con.cursor()  # instantiate a cursor obj

Next we instantiate a cursor object, which is SQLite’s accessor object. 

rows_sql = ''

args = ()

If there aren’t any keywords passed, then we just want all the rows from the named table, and we don’t have to modify the SQLite query further.

for key, value in kwargs.items():

    if key == 'rows_sql':

        rows_sql = ' ' + value

    elif key == 'args':

        args = value

If we do have a rows filter, then we need to insert it into the SQLite query. We expect a simple dictionary of {row_sql: sql, args: (arg1_value, …)}. This little loop unpacks them. Note: I’m relying on the SQL string to be well-formed; the only parsing I’m doing is to add a space so that the final SQL query will be well-formed too.

if rows_sql.count('?') != len(args):

    results = 'Unexpected number of arguments in row modifier'

Then a simple check to see if we have the same number of arguments as places to put them.

cur.execute('SELECT ' + columns + ' FROM ' + table + rows_sql, args)

Now we have everything we need to build our .execute command. The first argument is a simple string concatenation to build the full SQL query. The second argument is the tuple of values to be inserted in place of any (?).

results = cur.fetchall()

con.close()  # close connection

return results

Once we get the results (as a list of dictionaries), we close the connection to the database and return the results.

Putting it together

import sqlite3

DEFAULT_DB = 'database.sqlite3'

def db_connect(db_file=DEFAULT_DB):

    # create connection to SQLite database

    connection = sqlite3.connect(db_file)

    return connection

# utility function, handles opening and closing the database connection

def get_rows(table, columns='*', **kwargs):

    # columns_names should be a string enclosing a tuple of selected columns

    if (table == 'colours') or (table == 'observations'):

        con = db_connect()  # connect to the database

        cur = con.cursor()  # instantiate a cursor obj

        con.row_factory = sqlite3.Row

        rows_sql = ''

        args = ()

        for key, value in kwargs.items():

            if key == 'rows_sql':

                rows_sql = ' ' + value

            elif key == 'args':

                args = value

        if rows_sql.count('?') != len(args):

            results = 'Unexpected number of arguments in row modifier'

        cur.execute('SELECT ' + columns + ' FROM ' + table + rows_sql, args)

        results = cur.fetchall()

    else:

        results = 'invalid table name'

    con.close()  # close connection

    return results

con = db_connect()  # connect to the database

cur = con.cursor()  # instantiate a cursor obj

# create table of Hue Bloom colours if one doesn't already exist

colours_sql = """

CREATE TABLE IF NOT EXISTS colours (

    id integer PRIMARY KEY,

    temperature integer UNIQUE,

    hex_value text NOT NULL)"""

cur.execute(colours_sql)

# create table of weather observations if one doesn't already exist

observations_sql = """

CREATE TABLE IF NOT EXISTS observations (

    id integer PRIMARY KEY,

    timestamp text UNIQUE,

    temperature real NOT NULL,

    detailed_status text NOT NULL)"""

cur.execute(observations_sql)

# populate Hue colours db with default values if doesn't already exist

temp_colours = {  # from https://www.w3schools.com/colors/colors_picker.asp

    40: 'ff0000',  # colour value if temperature is >= than key

    35: 'ff4000',  # note: value is string, not hex

    30: 'ff8000',

    25: 'ffbf00',

    20: 'ffff00',

    15: 'bfff00',

    10: '00ff40',

     5: '00ffbf',

     0: '00ffff',

    -5: '00bfff',

   -10: '0080ff'

}

sql = '''INSERT OR IGNORE INTO colours (temperature, hex_value) 

   VALUES (?, ?)'''

for temperature, hex_value in temp_colours.items():

    cur.execute(sql, (temperature, hex_value))

con.commit()

con.close()  # close connection

This part has introduced the second programming language of SQL. Over the course of this project I've found that you need a combination of languages in order to get things done. In the next part 10, I'll start to use another language, HTML, and introduce a scripting framework to simplify things. Also visit https://github.com/Schmoiger/pybloom for the full story.

PyBloom coding project part 8: Orchestrating the code

My hundredth post and part 8 of this series is a short one. Here I describe how I orchestrated the various Python scripts to create the final program. 

Orchestrating the code

Right at the top, I defined the structure of the code that I wanted to write, as:

1. Check the current weather

2. Set the blooms to the current temperature

3. Log the weather reading

4. Generate new graphs

Now that we’ve gone through the detail of the classes and functions, following the execution code becomes quite straightforward. I’ll leave it to you, dear reader, to put it together. See you in the next section, when we present this on a web page!

I’ve wrapped the orchestration in another function, called weather. This gives me the flexibility to call it as a module from my Flask app later, and also to run it directly from the Terminal as python pybloom.py.

Putting it together

def weather():

    # Check current weather

    observation = weather_observation()

    observation.new(HOME_LOCATION)

    # observation.set(datetime.now().strftime(DATETIME_STRING), 23,

        'dummy observation')

    print(observation)

    # Set lounge bloom to current temperature

    lounge_bloom = hue_lamp(hue_lamp_ids['lounge bloom'])

     lounge_bloom.set_colour(convert_temp_to_colour(observation.temperature))

    # Set den bloom to current temperature

    den_bloom = hue_lamp(hue_lamp_ids['den bloom'])

    den_bloom.set_colour(convert_temp_to_colour(observation.temperature))

    # Log weather observation

    observation.log()

    # generate new graphs

    generate_graphs(observation.timestamp)

    return 'Fetched weather'

weather()

Keeping secrets

Way back when I showed you how I organised the code, I mentioned that I kept sensitive secrets out of the main code. I had global constants for these secrets, which were imported from a separate credentials.py file, as structured below. I decided on a simple dictionary with self-evident but unique variable names. Note that both key and values are strings.

credentials.py

credentials = {

    'hue_ip': <IP address of your Hue Bridge>,

    'hue_username': <given by the Hue Bridge on first sync>,

    'owm_key': <generated by service on signing up>,

    'home_location': <where you live, in the format city, country code>

}

Since the hard work was done in previous sections, orchestrating the code turns out to be quite straightforward. In part 9, I'll you through how I created a web page to present these readings. Also visit https://github.com/Schmoiger/pybloom for the full story.

PyBloom coding project part 7: Plotting graphs from the data

 

Previously, I set the coding environments, and wrote the code to generate the weather observation data. Here in part 7, I create the graphs of the temperature data.

Generate graphs function

Whilst the point of this program is to look at the current temperature outside just by glancing at my Hue lamps, I also want to be able to look at the history of temperature measurements. I could’ve logged the temperature measurements into a csv file and examine them as a spreadsheet, but I wanted to present them in a more accessible way = data visualisation.

Python has many powerful visualisation libraries, and I’ve spent some time with MatPlotLib (with its Seaborn) wrapper, and find it difficult because it’s comprehensive. Looking at PyGal, it seems much simpler to work with, so I decided to use it for my program.

The technologies

• PyGal

• Custom CSS

The code

Observation sets

def generate_graphs(timestamp):

    # observation sets

First things first, let’s set the points that mark the bounds of the observation data. I’m interested in data over the last day, over the last week and over the last month.

now = datetime.strptime(timestamp, DATETIME_STRING)

If you remember from earlier, maths with dates and times is surprisingly difficult, but using the datetime module makes it all easier. However, both Python and SQLite have their own implementation of datetime, so we need to make sure both use the same data dictionary.

• DATETIME_STRING defines the format (data dictionary)

• The .strptime method parses a string into a datetime object according to the format

This statement creates a datetime object now from a timestamp string. This might seem puzzling as there’s a perfectly good datetime.now() function, but this is a different now object. It’s not the up-to-the-microsecond now() from the operating system, but the now of the most recent weather observation.

last_day = now - timedelta(days=1)

last_week = now - timedelta(weeks=1)

last_month = now - timedelta(weeks=4)

The timedelta module fulfils the promise of simpler maths, with simple-to-understand syntax.

observation_sets = {

    'last_day': last_day,

    'last_week': last_week,

    'last_month': last_month

}

Having calculated the observation points, I put them in an easy-to-access Python dictionary.

Fetching the data

rows = get_rows('colours')

There are two sets of data that we’re interested in: the colour values (which will map onto the temperatures) and the temperatures themselves. This first get_rows fetches all the columns from the colours table. It’s important to note that the results are returned as a list of tuples. This is going to be hard to manipulate later as we want a list of hex values - but hex values that PyGal understands (i.e. prefixed by #), which isn’t the same as Python type hex (i.e. prefixed by 0x). We therefore need to parse the results.

hex_list = [f'#{hex}' for hex in [row['hex_value'] for row in rows]]

We use a combination of nested list comprehension and f-strings to do this conversion:

• [row['hex_value'] for row in rows] : this list comprehension cycles through the rows, and creates a list of the hex values

• for hex in [row['hex_value'] … : an outer list comprehension cycles through the newly-created list 

• [f'#{hex}' for hex … : each item of this outer list is inserted into the output string (in effect inserting the hash sign prefix for each element), to create the final list

temps_count = {row['temperature']: 0 for row in rows}

This second command is to set up histogram bins for each of the temperature thresholds, using a bit of dictionary comprehension. Now we have the temperature data in more accessible formats, let’s move onto the observation data.

for string, then in observation_sets.items():

Since we want a similar graph for each of three different time periods, we can save a bit of effort by iterating through the observation points (then). Thanks to the dictionary, we can access both the data and the string representation, which we’ll need in a moment.

sql = 'WHERE timestamp BETWEEN datetime((?)) AND datetime((?))'

when = (then, now)

results = get_rows('observations', rows_sql=sql, args=when)

Again, our little get_rows utility makes accessing the database a little easier. The double brackets look a bit odd. The variable replaced by the underlying SQLite .execute method is represented by (?). However, they are themselves parameters into SQLite’s own datetime method, which is why they need to be enclosed in another set of brackets.

The underlying SQLite query uses its own datetime method to convert Python datetime objects into SQL datetime strings. SQL doesn’t ask for the data dictionary explicitly; it’s assumed that the string corresponds to one of the accepted formats. 

Once we have the bounds correctly interpreted into the query, we can select only the data that we’re interested in. This is the reason I chose SQLite over csv. If we’d used a csv file to store all the data, I’d have to read all the data into a dataframe into memory, then search it for the data using a library such as Pandas. Not a big overhead, but given SQLite is built into Python, it's an opportunity to import one less library.

Generating the bar graphs

times = [row['timestamp'] for row in rows]

temps = [row['temperature'] for row in rows]

The database gives us a list of dictionaries; each dictionary contains a timestamp and a measurement. We need to convert these multiple lists into two lists, one containing all the timestamps, and another containing all the measurements. This is akin to transposing rows and columns of a table. This is simply done using some list comprehension.

bar_chart = pygal.Bar(x_label_rotation=20,

                      x_labels_major_count=6,

                      show_minor_x_labels=False,

                      show_legend=False)

Now we get into using PyGal to generate the graph. This first statement instantiates the chart object. It’s at this point that we declare it’s a bar graph, and also tell it to rotate the x labels (so that they’ll fit), limit x-axis labels to 6 in total (otherwise they’ll look cluttered), and remove the legend (because there’s only one series being shown).

bar_chart.add('Temperature', [

    {'value': temp,

    'color': '#' + lookup_colour(find_temp_threshold(temp))}

    for temp in temps]

)

Now we add the data series, and its title. To make the colours meaningful, the colour of the bar matches the Bloom colour. This means adding each value to be plotted individually, as a dictionary which describes the value and the colour. The colour is calculated using our previously defined functions, remembering to prefix with a hash symbol. We loop all the temperature measurements using list comprehension.

bar_chart.x_labels = times

The final piece of formatting is to add labels for the x axis, using a nice simple syntax.

filename = string + '_bar.svg'

bar_chart.render_to_file(FILEPATH + filename)

We want the filename to reflect the data set, so this little snippet takes the string representation of the data from the dictionary key, creates a full file path prepending the FILEPATH = './app/static/' global constant, and saves the generated graph there.

Generating the pie charts

We want to generate one pie chart for every bar chart (just in case we need them). The first thing we need to do is to count the number of occurrences of temperature in the bins that were previously generated.

for temp in temps:

    temp_threshold = find_temp_threshold(temp)

    temps_count[temp_threshold] += 1

The algorithm is simplified as we’re done most of the prep work beforehand. For each temperature reading in this observation set, we first find its corresponding temperature threshold, then increment the count in its bin.

custom_style = Style(colors=(tuple(hex_list)))

We can reuse the hex values from the colours table to create a custom colour key for the pie chart. These values are in the form of a list of strings, each prefixed with a hash, which we did earlier. To be properly formatted for PyGal, we now need to convert this list into a tuple, then pass it to the Style function as one of the optional keyword arguments.

pie_chart = pygal.Pie(inner_radius=0.6,

                      style=custom_style)

Instantiating the pie chart is straightforward; the only configuration parameters are the size of its donut hole and the colours of the sections.

for temp, count in temps_count.items():

    pie_chart.add(str(temp), count)

Each pie segment needs to be added individually, so we have a little loop.

filename = string + '_pie.svg'

pie_chart.render_to_file(FILEPATH + filename)

Finally, let’s save the chart to be used by the web app later.

Wrapping up

Now we’ve done it once, we can repeat it for each of the remaining observation sets.

return 'Created graphs'

The database utility has taken care of closing the connection to the database, so all that’s left is to return an acknowledgement string for debugging purposes. The graphs themselves are already stored (or overwritten) in the predefined folder within the loop.

Putting it together

def generate_graphs(timestamp):

    # observation sets

    now = datetime.strptime(timestamp, DATETIME_STRING)

    last_day = now - timedelta(days=1)

    last_week = now - timedelta(weeks=1)

    last_month = now - timedelta(weeks=4)

    observation_sets = {

        'last_day': last_day,

        'last_week': last_week,

        'last_month': last_month

    }

    # get datapoints from database

    rows = get_rows('colours')

    hex_list = [f'#{hex}' for hex in [row['hex_value'] for row in rows]]

    temps_count = {row['temperature']: 0 for row in rows}

    # 3x graphs for every reading in last day, week, month

    for string, then in observation_sets.items():

        # fetch data

        sql = 'WHERE timestamp BETWEEN datetime((?)) AND datetime((?))'

        when = (then, now)

        results = get_rows('observations', rows_sql=sql, args=when)

        # generate bar graph

        times = [row['timestamp'] for row in rows]

        temps = [row['temperature'] for row in rows]

        bar_chart = pygal.Bar(x_label_rotation=20,

                              x_labels_major_count=6,

                              show_minor_x_labels=False,

                              show_legend=False)

        bar_chart.add('Temperature', [

            {'value': temp,

            'color': '#' + lookup_colour(find_temp_threshold(temp))}

            for temp in temps]

        )

        bar_chart.x_labels = times

        filename = string + '_bar.svg'

        bar_chart.render_to_file(FILEPATH + filename)

        # generate pie chart

        for temp in temps:

            temp_threshold = find_temp_threshold(temp)

            temps_count[temp_threshold] += 1

        custom_style = Style(colors=(tuple(hex_list)))

        pie_chart = pygal.Pie(inner_radius=0.6,

                              style=custom_style)

        for temp, count in temps_count.items():

            pie_chart.add(str(temp), count)

        filename = string + '_pie.svg'

        pie_chart.render_to_file(FILEPATH + filename)

    return 'Created graphs'

We've covered the final functional module in this part. They now need to be strung together, which is what I'll cover in part 8 when I talk about orchestrating the code. Also visit https://github.com/Schmoiger/pybloom for the full story.