Python/MySQL

MySQL Connector

The Cursor

It appears that a cursor can be passed into a function (or object) as a parameter, but cannot be returned.

So if I am to use the cursor as the building block of database manipulation then I’ll need to set it up as part of the main loop and pass it into objects for creation.

Next experiment, to see if I’m right, will be to set up a cursor for a test database and pass it into an object representing a table within that database.

So calling the object would create the table if it is not already there and add records to it or read from it.

Would also need server objects for finding databases (already got this) and a database object for finding tables and for multi-table queries (partially got this).

Closing the connection

Meter Readings

My meter readings efforts are stored in the directory 25-Python/Meter_Readings. The purpose of the meter readings programme is to keep track of the gas and electricity meter readings and make it easier to spot changes in the amount of energy being used. The key files here are:

meterReading_01.py

The first incarnation of the meter reading program, this simply gives a list of the eight most recent readings, identifies the day of the week and offers to record the latest readings either for today (if it is a Sunday) or for the previous Sunday.

• It imports the mysql.connector, os and datetime modules
• It uses the Meter_Readings database on zeroPi.
• It has no functions or classes defined.

Learning Points

Key learning points from this exercise (over and above basic ‘how to use mysql.connector’) were:

• To update a database is is best to use a ‘prepared statement’ (which is like an extension of the python .format funcion for a string) and pass the cursor a tuple.
• If there is a list of more that one tuple then the cursor.executemany has to be used rather than just cursor.execute
• Nothing gets updated until connect.commit() is called.

meterReading_02.py

Key enhancement for this version is that the list of ‘most recent readings’ is split into Gas and Electricity readings, and for each reading it includes a calculation of how many units have been used since the previous reading.

The key technical change is that this version uses function calls to add some structure to the programme. The functions defined are:

cls

Uses the clear function imported from OS in order to clear the screen.

readDatabase

Set up specifically to read the Readings table in the Meter_Reading database. Can optionally pass a value for the number of readings to return, with the default being to return them all.

Readings returned as a list of tuples (even if there is only one set of readings to return it is still a list of one tuple)

dateSuffix

Pass it an integer and it returns the appropriate date suffix as a string (one of ‘st’, ‘nd’, ‘rd’ and ‘th’)

formatReading

Very specific to the ‘index, date, type, value’ format of this meter readings project, this function expects to be passed a set of readings (as a list of tuples) and a flag for the type of reading of interest.

It returns a list of tuples, each tuple in the format:

(day(int), week(int), month(str), year(int), yearday(int), reading(float))

printReadings

Expects to have the required title of the meter readings to be listed (e.g. Gas, New Electricity) and a set of readings in the format produced by the formatReading function.

It doesn’t return anything, but prints out a tabbed list of readings identifying the year, week number, date (including the suffix returned by dateSuffix), the reading itself and a calculation of the units used per week since the last reading.

addReadings

Expects to be passed a set of readings as a list of tuples to fit the specific format of the Readings table in the zeropi Meter_Readings database i.e.:

`idReadings`, `dateOfReading`, `Gas?`, `Reading`

Updates the table cursor.executemany and commits the changes.

Learning Points

Key learning points from this exercise were:

1. Functions can be defined with optional parameters, but these have to be at the end of the parameter list and have a default value assigned.

2. When running a SQL Query you don’t get the result until you use one of

   • cursor.fetchall()
   • cursor.fetchone()
   • cursor.fetchsome()

3. Use of::

   try:

   main code

   except:

   error condition

4. Trivial, but the webbrowser module is imported and the webbrowser.open(“url”) function used to open the Octopus Energy website.

meterReading_03.py

Key enhancement for this version is that it shows the current readings before asking whether a new one should be added. It also states which server/database is being used.

The key technical change is that this version uses objects instantiated from a single class to manage database access.

DatabaseManager class

The new class is DatabaseManager, which represents a single database accessed across a specific MySQL connector.

It is initialised by passing a set of credentials for opening a connection to a MySQL server; these credentials are passed as a dictionary which defines the values for the ‘user’, ‘password’, ‘host’, ‘port’ and ‘database’ variables to be used to access an existing database on a MySQL server and the value of the ‘raise_on_warnings’ flag to be used over the connection to that database.

The class supports new table, query and update functions which operate on the database, and ‘serverInfo’ and ‘close’ functions which operate on the connection.

Each DatabaseManager object holds attributes that hold the database name and the server on which the database resides.

This class has superceded the ‘readDatabase’ and ‘addReadings’ function calls in the previous version of the programme.

DatabaseManager class functions

Any function of the DatabaseManager class with name dB is called as dB.function(). As with any function, the function can have parameters passed to it and it can return a value or values (multiple returned values are returned as a tuple). The functions currently available with this class are:

__init__

Standard function called whenever an instance of the class (object) is created. This needs to be passed the credentials for the database, stores .host and .database as instance attributes, and creates .conn as the connector to the host and .cur as the cursor.

newTable

Will create a new table in the database. The name of the new table and the SQL definition of the columns are the two strings that need to be passed to the funcion.

query

Just pass it the SQL query as a string and it will return the results as a list of tuples.

update

When the preprepared SQL update statement is passed as a string and the data to be updated as a list of tuples, it will update the table and commit the changes. Nothing is returned.

serverInfo

Nothing passed, returns details of the server as a string.

close

Nothing passed, nothing returned.

Learning Points

Key learning points from this exercise were:

1. Defining classes and creating an object using the statement object = class(). The __init__ function in the class sets up the object. Any parameters needed to set up the object should be defined as parameters to be passed to the __init__ function.
2. Can have ‘instance attributes’ for each object that are defined when the object is instantiated. Can also have class attributes that are defined as part of the class and are in effect constants for all objects within that class. This is explained here https://www.toptal.com/python/python-class-attributes-an-overly-thorough-guide

MeterReadingBackup

The MeterReadingBackup efforts are also stored in the directory 25-Python/Meter_Readings. The purpose of MeterReadingBackups is to automatically backup the main meterReadings database. The key files here are:

meterReadingBackup_01.py

A relatively simple programme that takes a copy of all the function calls from meterReading_02.py and very simply copies all of the readings from the meterReadings database on zeroPi to a database on b4Pio whose name is a concatenation of ‘testDatabase’ plus the date on which the programme was run. There is an additional function defined in this programme which is initialiseDatabase

initialiseDatabase

Cars

Import data from LibreOffice Base database into MySQL and write a Python programme

PyQt5

Pip3 install PyQt5 didn’t work, so rather had to go for: sudo apt install python3-pyqt5 python3-pyqt5-dbg python3-gdbm-dbg python3-tk-dbg pyqt5-dev-tools

First example taken from github-pyqt_examples

Ideas

• Set up database manager as a separate importable module
• Can I find the reference to the python method of keeping SQL connector credentials secure in a separate location and implement that step
• Add try/except error handling code
• Can I merge any more of the MeterReadings code into classes/objects.
• Show the units used figures for the equivalent periods of previous years
• Maybe set up some better security (users, etc) in the MySQL servers themselves
• Put the output in a GUI, using tkinter, qt or something like that
• Make sense of the pydocs/sphinx/rest stuff?
• Udemy courses – data analysis, websites and android