Python – PostgreSQL self-study

When an assignment stops, I prefer to spend time time on broadening and deepening my knowledge. First of all because there’s no much time when in a full-time assignment, but also because I noticed that it’s a good way of processing what I’ve learned and experienced during the assignment. Apart from this, it’s also a good way of pulling myself away from the culture and way of working within that organisation, opening myself for the differences that exists in all organisations.

During my previous assignment, I got acquainted with programming language Python in combination with processing data coming from PostgreSQL. This showed to be a nice and easy to read combination that made me curious for the options it can bring me. It also suites an internal project that’s going through my mind for a while: My new electrical cal collects a lot of data, which is partly shown in the car’s mobile app and partly shown on the cars dashboard only. There’s a Github project though that can read data from the car, using the vendors servers. This project writes its data to csv, which is fine for analysing it in a spreadsheet solution. However, as a database specialist I obviously want this data in in PostgreSQL.

Reading data from the car into PostgreSQL

So, in Docker, I created a PostgreSQL container and a Python container that I can use for development. Using the powers of AI, it didn’t take much to place the data into a nice datamodel, make sure no duplicates are introduced and the files processed are archived nicely. Plus sufficient inline documentation.

During this process, I also learn al lot about the use of AI for this. It most certainly speeds up the development process, but it’s also in the way of the learning process. AI gives a complete solution, which doesn’t always work, gaves a new complete solution, iterates like this for a while and then it works. But it doesn’t always explain why the first iterations didn’t work. Also, being a complete set of code, it’s hard to understand what to code does. Nevertheless, when explicitly asking about the differences, AI does explain quite well, stil adding to the learning process. Hence, it’s even possible to learn from the mistakes AI makes. I still think though that when I would not have used AI, I would have learnt more but also had to spend more time on it.

Image scraping

Interestingly enough, the data coming from the car has no information about the cars trip-level energy consumption. Eventhough it is displayed on the cars dashboard on a nice time-based graph. For me, this is the main reason for initiating the project, especially for understanding the impact of towing a caravan during vacation. After searching deeper, including digging into the Open Source code of the existing projects that I use, it becomes clear that the API simply doesn’t deliver this information.

This brings up the real power of AI: When asking how hard it is to grab textual information from an image, it provides some existing Open Source projects, including their pro’s and cons, totally fine-tuned to my specific case. Not much later, I had a working process where I make some pictures of the multi-screen graph shown on the dashboard, Python code that reads the file, shapes the text from it, deduplicates where needed as some pictures may overlap each other, places it in a proper tabular order and inserts it into a database table.

Combining Data coming from two sources

Then the real challenge popped up. The dashboard only shows date and consumption, but not time. When there were multiple trips on the same day, which is quite regular, the order in which the figures appear is the only link to the other metrics that directly come from the car..

Here I ran into an interesting AI phenomenon. Since I’m working in Python, it firmly sticks to solving this quest in Python only. This results in a complex and inefficient set of shifting data back and forth between database and code. It doesn’t even consider using the database for this. As a database specialist, I know this is quite easily and efficiently solved using a simple view. This still is where the power of human thought wins from AI.

Ready, but not yet

Now the data is placed in the database, as preferred. A regular process runs to keep the database up to date and when I place new dashboard pictures in a given directory, it’s automatically processed and scraped into the database. Goal reached. Or not yet?

The database is a good start, but even for a database specialist like me, there are better ways of presenting this data. Also, an update of the car vendors app, now does show trip-level energy consumption. Hence, two new projects are in planning:

  • Visualise the data in Grafana.
    I have quite some experience in using Grafana for presenting data, but I never installed and configures a Grafana environment. Hence, this brings a nice additional learning process.
  • See if the API now can deliver the trip-level consumption as well and if not, see if I can add this to either the API or to a personal branch.

Since there are other activities with higher priority, this is on the future wishist.

Core competences

  • Python basic knowledge, acquired on the fly
  • Efficient use of Artificial Intelligence (AI) as a part of both development and learning processes
  • PostgreSQL Database Setup
  • PostgreSQL Data connectivity
  • PostgreSQL Datamodelling
  • Docker basic skills
  • Docker Compose