Preql Modules

__builtins__

PY(code_expr, code_setup)

Evaluate the given Python expression and convert the result to a Preql object

Parameters:

• code_expr (string) – The Python expression to evaluate
• code_setup (string?) – Setup code to prepare for the evaluation (default=null)

Note:

This function is still experemental, and should be used with caution.

Example:

>> PY("sys.version", "import sys")
"3.8.2 (tags/v3.8.2:7b3ab59, Feb 25 2020, 23:03:10)"

SQL(result_type, sql_code)

Create an object with the given SQL evaluation code, and given result type. The object will only be evaluated when required by the program flow. Using $var_name in the code will embed it in the query. Both primitives and tables are supported. A special $self variable allows to perform recursion, if supported by the dialect.

Parameters:

• result_type (union[table, type]) – The expected type of the result of the SQL query
• sql_code (string) – The SQL code to be evaluated

Example:

>> ["a", "b"]{item: SQL(string, "$item || '!'")}
table  =2
┏━━━━━━━┓
┃ item  ┃
┡━━━━━━━┩
│ a!    │
│ b!    │
└───────┘
>> x = ["a", "b", "c"]
>> SQL(type(x), "SELECT item || '!' FROM $x")
table  =3
┏━━━━━━━┓
┃ item  ┃
┡━━━━━━━┩
│ a!    │
│ b!    │
│ c!    │
└───────┘

approx_product(col)

Returns the approximate product of an aggregated column. It does so using logarithmic math. See product for an accurate version of this function.

Parameters:col (aggregated[item: number]) –

cast(obj, target_type)

Attempt to cast an object to a specified type The resulting object will be of type target_type, or a TypeError exception will be thrown.

Parameters:

• obj (any) – The object to cast
• target_type (type) – The type to cast to

char(n)

Returns the character with the given ASCII code

Parameters:n (int) –

char_ord(n)

Returns the ascii code of the given character

Parameters:n (string) –

char_range(start, end)

Produce a list of all characters from ‘start’ to ‘stop’

Parameters:

• start (string) –
• end (string) –

Example:

>> char_range('a', 'z')

columns(obj)

Returns a dictionary {column_name: column_type} for the given table

Parameters:

obj (container) –

Example:

>> columns([0])
{item: int}

commit()

Commit the current transaction This is necessary for changes to the tables to become persistent.

connect(uri, load_all_tables, auto_create)

Connect to a new database, specified by the uri

Parameters:

• uri (string) – A string specifying which database to connect to (e.g. “sqlite:///test.db”)
• load_all_tables (bool) – If true, loads all the tables in the database into the global namespace. (default=false)
• auto_create (bool) – If true, creates the database if it doesn’t already exist (Sqlite only) (default=false)

Example:

>> connect("sqlite://:memory:")     // Connect to a database in memory

count(obj)

Count how many rows are in the given table, or in the projected column. If no argument is given, count all the rows in the current projection.

Parameters:

obj (container?) –

Examples:

>> count([0..10])
10
>> [0..10]{ => count() }
table  =1
┏━━━━━━━┓
┃ count ┃
┡━━━━━━━┩
│    10 │
└───────┘
>> [0..10]{ => count(item) }
table  =1
┏━━━━━━━┓
┃ count ┃
┡━━━━━━━┩
│    10 │
└───────┘

count_false(field)

Count how many values in the field are false or zero

Parameters:

field (aggregated[any]) –

Example:

>> [0,1,2,0,3]{ => count_false(item) }
table  =1
┏━━━━━━━━━━━━━┓
┃ count_false ┃
┡━━━━━━━━━━━━━┩
│           2 │
└─────────────┘

See Also:

• count_true

count_true(field)

Count how many values in the field are true (non-zero)

Parameters:

field (aggregated[any]) –

Example:

>> [0,1,2,0,3]{ => count_true(item) }
table  =1
┏━━━━━━━━━━━━┓
┃ count_true ┃
┡━━━━━━━━━━━━┩
│          3 │
└────────────┘

See Also:

• count_false

debug()

Breaks the execution of the interpreter, and enters into a debug session using the REPL environment. Use c() to continue the execution.

dict(...x)

Constructs a dictionary

dir(obj)

List all names in the namespace of the given object. If no object is given, lists the names in the current namespace.

Parameters:obj (any) –

distinct(t)

Removes identical rows from the given table

Parameters:

t (table) –

Example:

>> distinct(["a","b","b","c"])
table  =3
┏━━━━━━━┓
┃ item  ┃
┡━━━━━━━┩
│ a     │
│ b     │
│ c     │
└───────┘

env_vars()

Returns a table of all the environment variables. The resulting table has two columns: name, and value.

exit(value)

Exit the current interpreter instance. Can be used from running code, or the REPL. If the current interpreter is nested within another Preql interpreter (e.g. by using debug()), exit() will return to the parent interpreter.

Parameters:value (any?) –

first(obj)

Returns the first member of a column or a list

Parameters:

obj (union[table, aggregated[any]]) –

Examples:

>> first([1,2,3])
1
>> [1,2,3]{ => first(item) }
table  =1
┏━━━━━━━┓
┃ first ┃
┡━━━━━━━┩
│     1 │
└───────┘

first_or_null(obj)

Returns the first member of a column or a list, or null if it’s empty See Also first().

Parameters:obj (union[table, projected[any]]) –

fmt(s)

Format the given string using interpolation on variables marked as $var

Parameters:

s (string) –

Example:

>> ["a", "b", "c"]{item: fmt("$item!")}
table  =3
┏━━━━━━━┓
┃ item  ┃
┡━━━━━━━┩
│ a!    │
│ b!    │
│ c!    │
└───────┘

force_eval(expr)

Forces the evaluation of the given expression. Executes any db queries necessary.

Parameters:expr (object) –

get_db_type()

Returns a string representing the type of the active database.

Example:

>> get_db_type()
"sqlite"

help(inst)

Provides a brief summary for the given object

Parameters:inst (any) –

import_csv(table, filename, header)

Import a csv file into an existing table

Parameters:

• table (table) – A table into which to add the rows.
• filename (string) – A path to the csv file
• header (bool) – If true, skips the first line (default=false)

import_json(table_name, uri)

Imports a json file into a new table. Returns the newly created table.

Parameters:

• table_name (string) – The name of the table to create
• uri (string) – A path or URI to the JSON file

Note:

This function requires the pandas Python package.

import_table(name, columns)

Import an existing table from the database, and fill in the types automatically.

Parameters:

• name (string) – The name of the table to import
• columns (list?[item: string]) – If this argument is provided, only these columns will be imported. (default=null)

Example:

>> import_table("my_sql_table", ["some_column", "another_column])

inspect_sql(obj)

Returns the SQL code that would be executed to evaluate the given object

Parameters:obj (object) –

is_empty(tbl)

Efficiently tests whether the table expression tbl is empty or not

Parameters:tbl –

isa(obj, type)

Checks if the give object is an instance of the given type

Parameters:

• obj (any) –
• type (type) –

Examples:

>> isa(1, int)
true
>> isa(1, string)
false
>> isa(1.2, number)
true
>> isa([1], table)
true

issubclass(a, b)

Checks if type ‘a’ is a subclass of type ‘b’

Parameters:

• a (type) –
• b (type) –

Examples:

>> issubclass(int, number)
true
>> issubclass(int, table)
false
>> issubclass(list, table)
true

join($on, ...tables)

Inner-join any number of tables. Each argument is expected to be one of - (1) A column to join on. Columns are attached to specific tables. or (2) A table to join on. The column will be chosen automatically, if there is no ambiguity. Connections are made according to the relationships in the declaration of the table.

Parameters:

• $on – Optional special keyword argument for specifying join condition. When specified, auto-join will be skipped. (default=null)
• tables – Provided as keyword arguments, in the form of <name>: <table>. Each keyword argument must be either a column, or a table.

Returns:

A new table, where each column is a struct representing one of the joined tables.

Examples:

>> join(a: [0].item, b: [0].item)
table join46 =1
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ a           ┃ b           ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ {'item': 0} │ {'item': 0} │
└─────────────┴─────────────┘
>> join(a: [1..5].item, b: [3..8].item) {...a}
table  =2
┏━━━━━━━┓
┃  item ┃
┡━━━━━━━┩
│     3 │
│     4 │
└───────┘
>> leftjoin(a: [1,3], b: [1,2], $on: a.item > b.item)
table join78 =3
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ a           ┃ b              ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ {'item': 1} │ {'item': None} │
│ {'item': 3} │ {'item': 1}    │
│ {'item': 3} │ {'item': 2}    │
└─────────────┴────────────────┘
>> join(c: Country, l: Language) {...c, language: l.name}

joinall($on, ...tables)

Cartesian product of any number of tables See join

Parameters:

$on –

Example:

>> joinall(a: [0,1], b: ["a", "b"])
table joinall14 =4
┏━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ a           ┃ b             ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ {'item': 0} │ {'item': 'a'} │
│ {'item': 0} │ {'item': 'b'} │
│ {'item': 1} │ {'item': 'a'} │
│ {'item': 1} │ {'item': 'b'} │
└─────────────┴───────────────┘

leftjoin($on, ...tables)

Left-join any number of tables See join

Parameters:$on –

length(s)

Returns the length of the string For tables or lists, use count()

Parameters:s (string) –

limit(tbl, n)

Returns the first ‘n’ rows in the table.

Parameters:

• tbl (table) –
• n (int) –

limit_offset(tbl, lim, offset)

Returns the first ‘n’ rows in the table at the given offset.

Parameters:

• tbl (table) –
• lim (int) –
• offset (int) –

list_median(x)

Find the median of a list Cannot be used inside a projection.

Parameters:x (list[item: any]) –

lower(s)

Return a copy of the string converted to lowercase.

Parameters:s (string) –

map_range(tbl, start, end)

For each row in the table, assigns numbers out of a range, and produces (end-start) new rows instead, each attached to a number. If start or end are functions, the index is the result of the function, per row.

Parameters:

• tbl (table) – Table to map the range onto
• start (union[int, function]) – The starting index, or a function producing the starting index
• end (union[int, function]) – The ending index, or a function producing the ending index

Examples:

>> map_range(["a", "b"], 0, 3)
table  =6
┏━━━┳━━━━━━┓
┃ i ┃ item ┃
┡━━━╇━━━━━━┩
│ 0 │ a    │
│ 1 │ a    │
│ 2 │ a    │
│ 0 │ b    │
│ 1 │ b    │
│ 2 │ b    │
└───┴──────┘
>> map_range(["a", "ab"], 1, length)
table  =3
┏━━━┳━━━━━━┓
┃ i ┃ item ┃
┡━━━╇━━━━━━┩
│ 1 │ a    │
│ 1 │ ab   │
│ 2 │ ab   │
└───┴──────┘

max(col)

Finds the maximum of a column or a list See Also sum.

Parameters:col (union[table[item: number], aggregated[item: number]]) –

mean(col)

Returns the mean average of a column or a list See Also sum.

Parameters:col (union[table[item: number], aggregated[item: number]]) –

min(col)

Finds the minimum of a column or a list See Also sum.

Parameters:col (union[table[item: number], aggregated[item: number]]) –

names(obj)

List all names in the namespace of the given object. If no object is given, lists the names in the current namespace.

Parameters:obj (any) –

now()

Returns the current timestamp

outerjoin($on, ...tables)

Outer-join any number of tables See join

Parameters:$on –

page(table, index, page_size)

Pagination utility function for tables

Parameters:

• table –
• index –
• page_size –

product(col)

Returns the product of a column or a list See Also sum.

Parameters:col (union[table[item: number], aggregated[item: number]]) – Note:This function is only available in sqlite3 by default. To make it available in postgres, users must call the install_polyfills() function. For databases that don’t support product, see approx_product().

random()

Returns a random float number between 0 to 1

remove_table(table_name)

Remove table from database (drop table)

Parameters:table_name –

remove_table_if_exists(table_name)

Remove table from database (drop table). Ignore if it doesn’t exist.

Parameters:table_name –

repeat(s, num)

Repeats the string num times.

Parameters:

• s (string) –
• num (int) –

Example:

>> _repeat("ha", 3)
"hahaha"

repr(obj)

Returns the representation text of the given object

Parameters:obj (any) –

rollback()

Rollback the current transaction This reverts the data in all the tables to the last commit. Local variables will remain unaffected.

round(n, precision)

Returns a rounded float at the given precision (i.e. at the given digit index)

Parameters:

• n (number) –
• precision (int) –

Example:

>> round(3.14)
3.0
>> round(3.14, 1)
3.1

sample_fast(tbl, n, bias)

Returns a random sample of n rows from the table in one query (or at worst two queries)

Parameters:

• tbl (table) – The table to sample from
• n (int) – The number of items to sample
• bias (number) – Add bias (reduce randomness) to gain performance. Higher values of ‘bias’ increase the chance of success in a single query, but may introduce a higher bias in the randomness of the chosen rows, especially in sorted tables. (default=0.05)

sample_ratio_fast(tbl, ratio)

Returns a random sample of rows from the table, at the approximate amount of (ratio*count(tbl)).

Parameters:

• tbl –
• ratio –

serve_rest(endpoints, port)

Start a starlette server (HTTP) that exposes the current namespace as REST API

Parameters:

• endpoints (struct) – A struct of type (string => function), mapping names to the functions.
• port (int) – A port from which to serve the API (default=8080)

Note:

Requires the starlette package for Python. Run pip install starlette.

Example:

>> func index() = "Hello World!"
>> serve_rest({index: index})
INFO     Started server process [85728]
INFO     Waiting for application startup.
INFO     Application startup complete.
INFO     Uvicorn running on http://127.0.0.1:8080 (Press CTRL+C to quit)

stddev(col)

Finds the standard deviation of a column or a list See Also sum.

Parameters:col (union[table[item: number], aggregated[item: number]]) –

str_contains(substr, s)

Tests whether string substr is contained in s

Parameters:

• substr (string) –
• s (string) –

Example:

>> str_contains("i", "tim")
true
>> str_contains("i", "team")
false

str_index(substr, s)

Finds in which index does substr appear in s.

Parameters:

• substr (string) – The substring to find
• s (string) – The string to search in

Returns:

A 0-based index (int) if found the substring, or -1 if not found.

Example:

>> str_index("re", "preql")
1
>> str_index("x", "preql")
-1

str_notcontains(substr, s)

Tests whether string substr is not contained in s Equivalent to not str_contains(substr, s).

Parameters:

• substr (string) –
• s (string) –

sum(col)

Sums up a column or a list.

Parameters:

col (union[table[item: number], aggregated[item: number]]) –

Examples:

>> sum([1,2,3])
6
>> [1,2,3]{ => sum(item) }
table  =1
┏━━━━━━━┓
┃   sum ┃
┡━━━━━━━┩
│     6 │
└───────┘

table_concat(t1, t2)

Concatenate two tables (union all). Used for t1 + t2

Parameters:

• t1 (table) –
• t2 (table) –

table_intersect(t1, t2)

Intersect two tables. Used for t1 & t2

Parameters:

• t1 (table) –
• t2 (table) –

table_subtract(t1, t2)

Substract two tables (except). Used for t1 - t2

Parameters:

• t1 (table) –
• t2 (table) –

table_union(t1, t2)

Union two tables. Used for t1 | t2

Parameters:

• t1 (table) –
• t2 (table) –

tables()

Returns a table of all the persistent tables in the database. The resulting table has two columns: name, and type.

temptable(expr, const)

Generate a temporary table with the contents of the given table It will remain available until the database session ends, unless manually removed.

Parameters:

• expr (table) – the table expression to create the table from
• const (bool?) – whether the resulting table may be changed or not. (default=null)

Note:

A non-const table creates its own id field. Trying to copy an existing id field into it will cause a collision

type(obj)

Returns the type of the given object

Parameters:

obj (any) –

Example:

>> type(1)
int
>> type([1])
list[item: int]
>> type(int)
type

upper(s)

Return a copy of the string converted to uppercase.

Parameters:s (string) –

zipjoin(a, b)

Joins two tables on their row index. Column names are always a and b. Result is as long as the shortest table. Similar to Python’s zip() function.

Parameters:

• a (table) –
• b (table) –

Example:

>> zipjoin(["a", "b"], [1, 2])
table  =2
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ a             ┃ b           ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ {'item': 'a'} │ {'item': 1} │
│ {'item': 'b'} │ {'item': 2} │
└───────────────┴─────────────┘

zipjoin_left(a, b)

Similar to zipjoin, but the result is as long as the first parameter. Missing rows will be assigned null.

Parameters:

• a (table) –
• b (table) –

Example:

>> zipjoin_left(["a", "b"], [1])
table  =2
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ a             ┃ b              ┃
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━┩
│ {'item': 'a'} │ {'item': 1}    │
│ {'item': 'b'} │ {'item': null} │
└───────────────┴────────────────┘

zipjoin_longest(a, b)

Similar to zipjoin, but the result is as long as the longest table. Missing rows will be assigned null.

Parameters:

• a (table) –
• b (table) –

graph

bfs(edges, initial)

Performs a breadth-first search on a graph.

Parameters:

• edges (table) – a table of type {src: int, dst: int}, defining the edges of the graph
• initial (table) – list[int], specifies from which nodes to start

walk_tree(edges, initial, max_rank)

Walks a tree and keeps track of the rank. Doesn’t test for uniqueness. Nodes may be visited more than once. Cycles will repeat until max_rank.

Parameters:

• edges (table) – a table of type {src: int, dst: int}, defining the edges of the graph
• initial (table) – list[int], specifies from which nodes to start
• max_rank (int) – integer limiting how far to search