How to Implement the Builder Pattern in Python for Automating SQL Dashboard Analytics

sql = (
AdvancedSQLBuilder().SELECT("NAME").SELECT("COUNT(*)")
.FROM("CARS").WHERE("NAME = 'BMW'")
.ORDER_BY("COUNT(*)").HOURS("SELL_DATE", 24)
.GROUP_BY("NAME")
.BUILD()
)
print(sql)
SELECT NAME, COUNT(*)
FROM CARS
WHERE NAME = 'BMW'
AND SELL_DATE >= DATEADD(HOUR, -24, GETDATE())
GROUP BY NAME
ORDER BY COUNT(*)

Motivation

The naive way to instantiate an object of a class uses the following format:

class MyClass:
def __init__(self, item_a, item_b, item_c):
self.a = item_a
self.b = item_b
self.c = item_c

# Example
obj = MyClass(1, 2, 3)
class SQLStatement:
def __init__(self, select_properties, from_tables,
where_conditions, group_by_clause, having_conditions):
self.select_properties = select_properties
self.from_tables = from_tables
self.where_conditions = where_conditions
self.group_by_clause = group_by_clause
self.having_conditions = having_conditions
def __repr__(self):
return f"""SELECT {self.select_properties}
FROM {self.from_tables}
WHERE {self.where_conditions}
GROUP BY {self.group_by_clause}
HAVING {self.having_conditions}
"""

# Example
sql = SQLStatement("NAME", "CARS", "COLOR = 'blue'", "","")
print(sql)
SELECT NAME
FROM CARS
WHERE COLOR = 'blue'
GROUP BY
HAVING

The Builder Pattern

The builder pattern can be best described with a simple example:

class MyBuilder:
def __init__(self):
self.a = None
self.b = None
self.c = None
def a(self, arg):
self.a = arg
return self
def b(self, arg):
self.b = arg
return self
def c(self, arg):
self.c = arg
return self
def build(self):
list_of_values = [self.a, self.b, self.c]
list_of_values = [i for i in list_of_values if i is not None]
return list_of_values.join(",")

# Example
myBuilder = MyBuilder().b(1).a(2)
print(myBuilder.build())

# Outputs: 1,2
  • Functions that set a class variable should return self. This allows chaining commands such as in the example.
  • The class should have a build() function (or any other name). This function should consume the information of all the variables that were set before calling it and return the desired output while handling missing information.
  • The setter functions can be called in any order.

Let’s build the SQL builder!

Now that we know how to use the builder design pattern, we can use it to create a domain-specific SQL builder.

# SQLBuilder.py

newline = "\n"

class SQLBuilder(object):
def __init__(self):
self._DECLARE = ""
self._SELECT = ""
self._FROM_MAP = {}
self._WHERE = ""
self._ORDER_BY = ""
self._GROUP_BY = ""
self._HAVING = ""


def DECLARE(self, key, type, value = ''):
assert(key.startswith('@'))

# Handle keyword starting with declare
s = "\n"
if (self._DECLARE == ""):
s = ""

self._DECLARE += s + f"DECLARE {key} {type}{' = ' if value else ''}{value}"
return self
def SELECT(self, t):
s = ", "
if (self._SELECT == ""):
s = "SELECT "

self._SELECT += s + t
return self

def FROM(self, t, alias=''):
if (not t in self._FROM_MAP):
self._FROM_MAP[t] = ''

if (alias):
self._FROM_MAP[t] = alias

return self
def WHERE(self, t):
if (self._WHERE != ""):
return self.AND(t)
self._WHERE = "WHERE " + t
return self

def AND(self, t):
s = "\n"
if (self._WHERE == ""):
s = "WHERE 1=1\n"

self._WHERE += s + f"AND {t}"
return self

def GROUP_BY(self, t):
s = ",\n"
if (self._GROUP_BY == ""):
s = "GROUP BY "

self._GROUP_BY += s + t
return self

def ORDER_BY(self, t):
s = ",\n"
if (self._ORDER_BY == ""):
s = "ORDER BY "

self._ORDER_BY += s + t
return self

def HAVING(self, t):
s = ",\n"
if (self._HAVING == ""):
s = "HAVING "

self._HAVING += s + t
return self
def JOIN(self, table_one, key_one, table_two, key_two):
return self.FROM(table_one).FROM(table_two).AND(f"{table_one}.{key_one} = {table_two}.{key_two}")

def BUILD(self):
self._FROM = ', '.join([f"{name}{f' {alias}' if alias else ''}" for name, alias in self._FROM_MAP.items()])
return (
f"{self._DECLARE + newline if self._DECLARE else ''}"
+ f"{newline + self._SELECT if self._SELECT else ''}"
+ f"{newline + 'FROM ' + self._FROM if self._FROM else ''}"
+ f"{newline + self._WHERE if self._WHERE else ''}"
+ f"{newline + self._GROUP_BY if self._GROUP_BY else ''}"
+ f"{newline + self._HAVING if self._HAVING else ''}"
+ f"{newline + self._ORDER_BY if self._ORDER_BY else ''}"
)
from SQLBuilder import SQLBuilder

print(
SQLBuilder()
.SELECT("NAME").SELECT("COUNT(*)")
.FROM("CAR_SALES", "C")
.WHERE("C.NAME = 'BMW'")
.ORDER_BY("COUNT(*)")
.BUILD()
)
SELECT NAME, COUNT(*)
FROM CAR_SALES C
WHERE C.NAME = 'BMW'
ORDER BY C.COUNT(*)

We can now add special helper functions

These helper functions can include:

  • HOURS(key, n) to limit the range of the key up to n hours ago.
  • TODAY(key) to limit the values of key to be within today.
  • START_DATE(key, start_date) to limit the lowest value of key to start_date
  • END_DATE(key, end_date) to limit the highest value of key to end_date
  • LIKE(key, s) to add a WHERE condition that key is a string like s.
  • NULL(key) to add a WHERE condition that the value of key is null.
  • NOTNULL(key) to add a WHERE condition that the value of key is NOT null.
  • etc.
# AdvancedSQLBuilder.py

from SQLBuilder import SQLBuilder

class AdvancedSQLBuilder(SQLBuilder):
def HOURS(self, key, n):
assert(n > 0)

self.AND(f"{key} >= DATEADD(HOUR, -{n}, GETDATE())")
return self

def TODAY(self, key):
self.AND(f"CONVERT(VARCHAR(10), {key}, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)")
return self

def START_DATE(self, key, start_date):
(self.DECLARE('@start_date','datetime',start_date)
.AND(f"{key} is not null")
.AND(f"{key} >= @start_date"))
return self

def END_DATE(self, key, end_date):
self.DECLARE('@end_date','datetime',end_date)
self.AND(f"{key} is not null")
self.AND(f"{key} < @end_date")
return self

def LIKE(self, key, s):
self.AND(f"{key} like '{s}'")
return self

def NULL(self, key):
self.AND(f"{key} is null")
return self

def NOTNULL(self, key):
self.AND(f"{key} is not null")
return self
from AdvancedSQLBuilder import AdvancedSQLBuilder

print(
AdvancedSQLBuilder()
.SELECT("NAME").SELECT("COUNT(*)")
.FROM("CAR_SALES")
.START_DATE("SELL_DATE", '2021-11-18 00:00:00')
.END_DATE("SELL_DATE", '2022-11-18 00:00:00')
.ORDER_BY("COUNT(*)")
.GROUP_BY("NAME")
.BUILD()
)
DECLARE @start_date datetime = 2021-11-18 00:00:00
DECLARE @end_date datetime = 2022-11-18 00:00:00

SELECT NAME, COUNT(*)
FROM CAR_SALES
WHERE 1=1
AND SELL_DATE is not null
AND SELL_DATE >= @start_date
AND SELL_DATE is not null
AND SELL_DATE < @end_date
GROUP BY NAME
ORDER BY COUNT(*)

We can achieve even more with this pattern

Going into domain-specific examples, suppose our database uses ID numbers for our car names instead of a string in the following:

# Example database table: 
# CARS
+------------+--------+----------------+
| PRIMARY_ID | CAR_ID | SOME_VALUE |
+------------+--------+----------------+
| 1 | 103112 | some_value |
| 2 | 103113 | another value |
| ... | ... | ... |
+------------+--------+----------------+
# CAR_MAP
+--------+---------------+
| ID | CARNAME |
+--------+---------------+
| 103112 | BMW |
| 103113 | Aston Martin |
| ... | ... |
+--------+---------------+
# DomainSpecificSQLBuilder.py

from AdvancedSQLBuilder import AdvancedSQLBuilder
class DomainSpecificSQLBuilder(AdvancedSQLBuilder):
def CARNAME(self, name, table_two, key_two):
self.JOIN("CAR_MAP","ID", table_two, key_two)
self.AND(f"CAR_MAP.NAME = {name}"
from DomainSpecificSQLBuilder import DomainSpecificSQLBuilder

print(
DomainSpecificSQLBuilder()
.SELECT("CARNAME").SELECT("COUNT(*)")
.FROM("CAR_SALES")
.CARNAME("BMW", "CAR_SALES", "CAR_ID")
.WHERE("COUNT(*) > 30")
.BUILD()
)
SELECT CARNAME, COUNT(*)
FROM CAR_SALES, CAR_MAP
WHERE 1=1
AND CAR_MAP.ID = CAR_SALES.CAR_ID
AND CAR_MAP.NAME = BMW
AND COUNT(*) > 30

Conclusion

In this article, I have shown how to use the builder pattern to create a domain-specific SQL builder. This can then be used to programmatically generate SQL queries for different slices of data, allowing swift gathering of data for business dashboards and general analysis.

GitHub Repository

Feel free to check out the source code here: https://github.com/justinsj/sql-builder

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Justin San Juan

Justin San Juan

48 Followers

Award-Winning Software Engineer | Business and Web Consultant