Missing data in databases can cause bugs in applications or incorrect calculations. Recently, while working on a RETS application, I needed to ensure that not many missing values were encountered in one of the MySQL tables. Although one could easily write a SQL query to find the percentage of missing values, I many times find it easier to first get a visual representation of the amount of missing data there is in the table, and then drill-down further if required. One library that I found that lets you easily get a visual representation of missing data in your database tables is missingno – a Python library.
(more…)
database
Combinatorial row pairing in databases
While working on a database design I came across the problem of finding combinations or ‘cartesian product’ between all the rows between one table to another table, but not repeating the same permutations.
Take the ‘Teams’ table below which lists four cricket teams. We want to create a combination list of matches between each teams.
(more…)
Semantic Normalization of data
Every database programmer knows about database normalization – the process of organizing the columns and tables a relational database to minimize data redundancy. Semantic Normalization is the process of reshaping free form entered data. I first encountered the concept in Nathan Marzs Big Data: Principles and best practices of scalable realtime data systems
(more…)
MySQL transactions in NodeJS
In a previous post we saw how to access and query MySQL from NodeJS. In this post we will see how we can use MySQL transactions in NodeJS. We will be using the same library as used before, so if you have not installed it go ahead and install it. The library supports simple transaction at the connection level.
(more…)
How to check when a MySQL table was last updated
I recently had to update a MySQL schema and import new data into the table. But before I could do that I needed to check that no one had updated the table during the last 7 days and no new data had been stored. As the table itself did not have any update field of itself the only other option was to look into the MySQL ‘information_schema’ database.
The ‘information_schema’ database contains a ‘tables’ table which contain the update information for each database and its tables. So all you have to do is grab that information.
(more…)
Migrating Access MDB to MySQL
A recent task needed me to import a large amount of data from a Access MDB database to MySQL. My first choice for the job was the mdb-tools set of utilities. mdb-tools provides a set of tools and applications to export MDB data and schema to other databases such as MySQL, Oracle, Sybase, PostgreSQL, and others.
mdb-tools is available for Linux systems, and as I use Ubuntu, installation was a breeze using the package manager. To install is from the shell use the following.
(more…)