Differences to SQL and Python

SPyQL is the result of joining Python and SQL in the same language. We have tried to make SPyQL as faithful as possible to the two but, still, there are differences that should be highlighted.

Differences to SQL

In SPyQL:

• there is guarantee that the order of the output rows is the same as in the input (if no reordering is done)

• the AS keyword must precede a column alias definition (it is not optional as in SQL)

• you can always access the nth input column by using the default column names colN (e.g. col1 for the first column)

• currently only a small subset of SQL is supported, namely SELECT statements without: sub-queries, joins, set operations, etc (check the Syntax section)

• sub-queries are achieved by piping and joins by dictionary lookups (see the Command line examples section)

• comments follow Python’s syntax (# line comment) instead of the SQL standard (-- line comment, /* multi-line comment */)

• in SQL SELECT count(1) WHERE False returns 1 row with 1 column with value 0, while in SPyQL an equivalent query would not return any row

• counting the number of distinct values is done using SELECT count_distinct_agg(x) instead of SELECT count(DISTINCT x)

• aggregation functions have the suffix _agg to avoid conflicts with python’s built-in functions (e.g. SPyQL uses sum_agg instead of sum to avoid conflicts with Python’s built-in function):

Operation	PostgreSQL	SPyQL
Sum all values of a column	SELECT sum(col_name)	SELECT sum_agg(col_name)
Sum an array	SELECT sum(a) FROM (SELECT unnest(array[1,2,3]) AS a) AS t	SELECT sum([1,2,3])

• expressions are pure Python:

SQL	SpySQL
x = y	x == y
x BETWEEN a AND b	a <= x <= b
CAST(x AS INTEGER)	int(x)
CASE WHEN x > 0 THEN 1 ELSE -1 END	1 if x > 0 else -1
upper('hello')	'hello'.upper()

Differences to Python

Additional syntax

We added additional syntax for making querying easier (the original python syntax is supported):

Python	SpySQL shortcut	Purpose
row['hello']['world']	.hello.world	Easy access of elements in dicts (e.g. JSONs)
row['hello']['planet earth']	.hello['planet earth']	Easy access of elements in dicts (e.g. JSONs)

NULL datatype

Python’s None generates exceptions when making operations on missing data, breaking query execution (e.g. None + 1 throws a TypeError). To overcome this, we created a NULL type that has the same behavior as in SQL (e.g. NULL + 1 returns NULL), allowing for queries to continue processing data.

Operation	Native Python throws	SpySQL returns	SpySQL warning
NULL + 1	NameError	NULL
a_dict['inexistent_key']	KeyError	NULL	yes
int('')	ValueError	NULL	yes
int('abc')	ValueError	NULL	yes

The above dictionary key access only returns NULL if the dict is an instance of qdict. SPyQL adds qdict, which extends python’s native dict. JSONs are automatically loaded as qdict. Unless you are creating dictionaries on the fly you do not need to worry about this.