psycodict.searchtable¶

The read half of a table: dictionary queries.

PostgresSearchTable extends PostgresTable with the query API – search, lucky, lookup, count, random and friends – translating query dictionaries into SQL SELECT statements, with projections, sorting, joins and the info contract used by search pages. The query language is specified in QueryLanguage.md and the read API in Searching.md.

class psycodict.searchtable.PostgresSearchTable(db, search_table, label_col, sort=None, count_cutoff=1000, id_ordered=False, out_of_order=False, stats_valid=True, total=None, include_nones=True, data_types=None)[source]¶

Bases: PostgresTable

A single search table, as returned by db.<tablename>: the interface for reading data with dictionary queries.

The read methods – search(), lucky(), lookup(), exists(), count(), random(), distinct() and friends – accept a query dictionary such as {"degree": 2, "disc": {"$lt": 0}}, translate it into SQL, and return rows as dictionaries (or bare values, under a string projection). The query language is specified in QueryLanguage.md and the read API in Searching.md. Writing and schema changes live on the base class PostgresTable, and precomputed statistics on PostgresStatsTable (available as self.stats).

Instances are constructed by PostgresDatabase from each table’s meta_tables row; they are not meant to be created directly.

lucky(query={}, projection=2, offset=0, sort=[], raw=None, raw_values=None, join=None)[source]¶

One of the two main public interfaces for performing SELECT queries, intended for situations where only a single result is desired.

INPUT:

query – a mongo-style dictionary specifying the query. Generally, the keys will correspond to columns, and values will either be specific numbers (specifying an equality test) or dictionaries giving more complicated constraints. The main exception is that “$or” can be a top level key, specifying a list of constraints of which at least one must be true.
projection – which columns are desired. This can be specified either as a list of columns to include; a dictionary specifying columns to include (using all True values) or exclude (using all False values); a string giving a single column (only returns the value, not a dictionary); or an integer code (0 means only return the label, 1 means return all search columns, 2 means all columns (default)).
offset – integer. allows retrieval of a later record rather than just first.
sort – The sort order, from which the first result is returned.
- None, Using the default sort order for the table
- a list of strings (which are interpreted as column names in the ascending direction) or of pairs (column name, 1 or -1). If not specified, will use the default sort order on the table.
- [] (default), unsorted, thus if there is more than one match to the query then the choice of the result is arbitrary.
raw – a string, to be used as the WHERE part of the query. DO NOT USE THIS DIRECTLY FOR INPUT FROM WEBSITE.
raw_values – a list of values to be substituted for %s entries in the raw string. Useful when strings might include quotes.
join – a list of tuples describing other search tables to join to this one, as for search. Not compatible with raw.

OUTPUT:

If projection is 0 or a string, returns the label/column value of the first record satisfying the query. Otherwise, return a dictionary with keys the column names requested by the projection.

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: nf.lucky({'degree':int(2),'disc_sign':int(1),'disc_abs':int(5)},projection=0)
'2.2.5.1'
sage: nf.lucky({'label':'6.6.409587233.1'},projection=1)
{'class_group': [],
 'class_number': 1,
 'cm': False,
 'coeffs': [2, -31, 30, 11, -13, -1, 1],
 'degree': 6,
 'disc_abs': 409587233,
 'disc_rad': 409587233,
 'disc_sign': 1,
 'galt': 16,
 'label': '6.6.409587233.1',
 'oldpolredabscoeffs': None,
 'r2': 0,
 'ramps': [11, 53, 702551],
 'used_grh': False}
sage: nf.lucky({'label':'6.6.409587233.1'},projection=['regulator'])
{'regulator':455.191694993}

search(query={}, projection=1, limit=None, offset=0, sort=None, info=None, split_ors=False, one_per=None, silent=False, raw=None, raw_values=None, join=None)[source]¶

One of the two main public interfaces for performing SELECT queries, intended for usage from search pages where multiple results may be returned.

INPUT:

query – a mongo-style dictionary specifying the query. Generally, the keys will correspond to columns, and values will either be specific numbers (specifying an equality test) or dictionaries giving more complicated constraints. The main exception is that “$or” can be a top level key, specifying a list of constraints of which at least one must be true.
projection – which columns are desired. This can be specified either as a list of columns to include; a dictionary specifying columns to include (using all True values) or exclude (using all False values); a string giving a single column (only returns the value, not a dictionary); or an integer code (0 means only return the label, 1 means return all search columns (default), 2 means all columns).
limit – an integer or None (default), giving the maximum number of records to return.
offset – a nonnegative integer (default 0), where to start in the list of results.
sort – a sort order. Either None or a list of strings (which are interpreted as column names in the ascending direction) or of pairs (column name, 1 or -1). If not specified, will use the default sort order on the table. If you want the result unsorted, use [].
info – a dictionary, which is updated with values of ‘query’, ‘count’, ‘start’, ‘exact_count’ and ‘number’. Optional.
split_ors – a boolean. If true, executes one query per clause in the $or list, combining the results. Only used when a limit is provided.
one_per – a list of columns. If provided, only one result will be included with each given set of values for those columns (the first according to the provided sort order).
silent – a boolean. If True, slow query warnings will be suppressed.
raw – a string, to be used as the WHERE part of the query. DO NOT USE THIS DIRECTLY FOR INPUT FROM WEBSITE.
raw_values – a list of values to be substituted for %s entries in the raw string. Useful when strings might include quotes.
join – a list of tuples (col1, col2) or (col1, col2, jointype) describing other search tables to join to this one. In each tuple, col2 must be qualified as "table.column" and names the table being joined; col1 is a column of this table, or of a previously joined table if qualified. jointype is "inner" (the default), "left", "right" or "full". When join is given, query keys, projection entries, sort entries, $col names and names in $raw expressions may be qualified as "table.column" to refer to columns of the joined tables (unqualified names refer to this table, with periods keeping their usual path meaning), and the keys in the result dictionaries match the projection entries as given. Counts are never cached, and split_ors, one_per and raw are not supported. See the Joined queries section of QueryLanguage.md for details.

OUTPUT:

If limit is None, returns an iterator over the results, yielding dictionaries with keys the columns requested by the projection (or labels/column values if the projection is 0 or a string)

Otherwise, returns a list with the same data.

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: info = {}
sage: nf.search({'degree':int(2),'class_number':int(1),'disc_sign':int(-1)}, projection=0, limit=4, info=info)
['2.0.3.1', '2.0.4.1', '2.0.7.1', '2.0.8.1']
sage: info['number'], info['exact_count']
(9, True)
sage: info = {}
sage: nf.search({'degree':int(6)}, projection=['label','class_number','galt'], limit=4, info=info)
[{'class_number': 1, 'galt': 5, 'label': '6.0.9747.1'},
 {'class_number': 1, 'galt': 11, 'label': '6.0.10051.1'},
 {'class_number': 1, 'galt': 11, 'label': '6.0.10571.1'},
 {'class_number': 1, 'galt': 5, 'label': '6.0.10816.1'}]
sage: info['number'], info['exact_count']
(5522600, True)
sage: info = {}
sage: nf.search({'ramps':{'$contains':[int(2),int(7)]}}, limit=4, info=info)
[{'label': '2.2.28.1', 'ramps': [2, 7]},
 {'label': '2.0.56.1', 'ramps': [2, 7]},
 {'label': '2.2.56.1', 'ramps': [2, 7]},
 {'label': '2.0.84.1', 'ramps': [2, 3, 7]}]
sage: info['number'], info['exact_count']
(1000, False)

Columns of other tables can be searched on and projected onto by joining the tables:

sage: db.ec_nfcurves.search(
....:     {"rank": 1, "nf_fields.r2": 1},
....:     ["label", "nf_fields.degree"],
....:     join=[("field_label", "nf_fields.label")],
....:     limit=3)
[{'label': '2.0.1003.1-9.1-c1', 'nf_fields.degree': 2},
 {'label': '2.0.1003.1-9.1-c2', 'nf_fields.degree': 2},
 {'label': '2.0.1003.1-9.1-d1', 'nf_fields.degree': 2}]

lookup(label, projection=2, label_col=None, join=None)[source]¶

Look up a record by its label.

INPUT:

label – string, the label for the desired record.
projection – which columns are requested (default 2, meaning all columns).
See _parse_projection for more details.
label_col – which column holds the label. Most tables store a default.
join – a list of tuples describing other search tables to join to this one, as for search.

OUTPUT:

A dictionary with keys the column names requested by the projection.

Note, the example below uses loc_algebras which is no longer a column EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: rec = nf.lookup('8.0.374187008.1')
sage: rec['loc_algebras']['13']
'x^2-13,x^2-x+2,x^4+x^2-x+2'

exists(query)[source]¶

Determines whether there exists at least one record satisfying the query.

INPUT:

query – a mongo style dictionary specifying the search. See search for more details.

OUTPUT:

Boolean, whether there exists a record.

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: nf.exists({'class_number':int(7)})
True

label_exists(label, label_col=None)[source]¶

Determines whether these exists a record with the given label.

INPUT:

label – a string, the label
label_col – the column holding the label (most tables have a default setting)

random(query={}, projection=0, pick_first=None)[source]¶

Return a random label or record from this table.

INPUT:

query – a query dictionary from which a result will be selected, uniformly at random
projection – which columns are requested (default 0, meaning just the label). See _parse_projection for more details.
pick_first – a column name. If provided, a value is chosen uniformly from the distinct values (subject to the given query), then a random element is chosen with that value. Note that the set of distinct values is computed and stored, so be careful not to choose a column that takes on too many values.

OUTPUT:

If projection is 0, a random label from the table. Otherwise, a dictionary with keys specified by the projection. A RuntimeError is raised if the selection fails when there are rows in the table; this can occur if the ids are not consecutive due to deletions. If there are no results satisfying the query, None is returned (analogously to the lucky method).

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: nf.random()
'2.0.294787.1'

random_sample(ratio, query={}, projection=1, mode=None, repeatable=None, silent=False)[source]¶

Returns a random sample of rows from this table. Note that ratio is not guaranteed, and different modes will have different levels of randomness.

INPUT:

ratio – a float between 0 and 1, the approximate fraction of rows satisfying the query to be returned.
query – a dictionary query, as for searching. Note that the WHERE clause is applied after the random selection except when using ‘choice’ mode
projection – a description of which columns to include in the search results
mode – one of 'system', 'bernoulli', 'choice' and None: - system – the fastest option, but will introduce clustering since random pages are selected rather than random rows. - bernoulli – rows are selected independently with probability the given ratio, then the where clause is applied - choice – all results satisfying the query are fetched, then a random subset is chosen. This will be slow if a large number of rows satisfy the query, but performs much better when only a few rows satisfy the query. This option matches ratio mostly accurately. - None – Uses bernoulli if more than self._count_cutoff results satisfy the query, otherwise uses choice.
repeatable – an integer, giving a random seed for a repeatable result.
silent – whether to suppress slow query warnings

copy_to_example(searchfile, id=None, sep='|')[source]¶

This function writes files in the format used for copy_from and reload. It writes the header and a single random row.

INPUT:

searchfile – a string, the filename to write data into for the search table
id – an id to use for the example row (random if unspecified)
sep – a character to use as a separator between columns

max(col, constraint={})[source]¶

The maximum value attained by the given column.

INPUT:

col – the name of the column
constraint – a query dictionary constraining which rows are considered

EXAMPLES:

sage: from lmfdb import db
sage: db.nf_fields.max('class_number')
1892503075117056

min(col, constraint={})[source]¶

The minimum value attained by the given column.

INPUT:

col – the name of the column
constraint – a query dictionary constraining which rows are considered

EXAMPLES:

sage: from lmfdb import db
sage: db.ec_mwbsd.min('area')
0.00000013296713869846309987200099760

distinct(col, query={})[source]¶

Returns a list of the distinct values taken on by a given column.

INPUT:

col – the name of the column
query – a query dictionary constraining which rows are considered

count(query={}, groupby=None, record=False, join=None)[source]¶

Count the number of results for a given query.

INPUT:

query – a mongo-style dictionary, as in the search method.
groupby – (default None) a list of columns
record – (default False) whether to record the number of results in the counts table. Recording is useful for queries whose counts are displayed repeatedly (search pages record theirs), but every distinct recorded query adds a row to the counts table, so it is opt-in: scripts running many one-off counts no longer clutter the table (and slow down reloads) by accident. Recording also requires saving to be enabled on the stats table; with the psycodict default saving = False nothing is written.
join – a list of tuples describing other search tables to join to this one, as for search. Counts of joined queries are computed directly and never cached, so record is ignored; groupby is not supported with join.

OUTPUT:

If groupby is None, the number of records satisfying the query. Otherwise, a dictionary with keys the distinct tuples of values taken on by the columns in groupby, and values the number of rows with those values.

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: nf.count({'degree':int(6),'galt':int(7)})
244006

count_distinct(col, query={}, record=False)[source]¶

Count the number of distinct values taken on by a given column.

The result will be the same as taking the length of the distinct values, but a bit faster and can cache the answer

INPUT:

col – the name of the column, or a list of such names
query – a query dictionary constraining which rows are considered
record – (default False) whether to record the number of results in the stats table; opt-in for the same reason as in count.

sum(col, constraint={})[source]¶

The sum of a given column.

INPUT:

col – the name of the column
constraint – a query dictionary constraining which rows are considered