psycodict

The package root. Its __all__ is the version marker and the names psycodict re-exports for convenience, so that downstream code need not import them from a submodule or from the driver; everything else lives in the modules below.

The autodoc options here are deliberately wider than on the module pages: seven of the eight root exports are imported members (imported-members) and __version__ is a special data member (special-members), so without them Sphinx would silently render an empty page for a module whose entire public surface is re-exports. The options are local to this page — the module pages keep documenting exactly their own __all__.

This module provides an interface to Postgres supporting the kinds of queries needed by the LMFDB.

The examples in this package’s docstrings are real transcripts, run as doctests by tests/test_doctests.py against two small tables of LMFDB data that it creates: test_fields (22 selected number fields of degree at most 3) and test_curves (a dozen elliptic curves over three of those fields).

EXAMPLES:

>>> from psycodict.database import PostgresDatabase
>>> db = PostgresDatabase()  # configuration found via $PSYCODICT_CONFIG / config.ini
>>> db
Interface to Postgres database
>>> 'test_fields' in db.tablenames
True
>>> nf = db.test_fields
>>> nf
Interface to Postgres table test_fields

You can search using the methods search, lucky and lookup:

>>> nf.lookup('2.0.23.1', 'class_number')
3
>>> nf.lucky({'degree': 2, 'disc_sign': 1, 'disc_abs': 5}, projection=0)
'2.2.5.1'
>>> list(nf.search({'ramps': {'$contains': [2]}}, projection=0))
['2.0.4.1', '2.0.8.1', '2.2.8.1', '2.2.12.1', '3.1.44.1', '3.1.76.1']

psycodict.__version__ = '1.0.0rc2'

The version of psycodict, as a PEP 440 string. For an installed copy it is the same value importlib.metadata.version("psycodict") reports.

class psycodict.SQL(obj: LiteralString)

Bases: Composable

A Composable representing a snippet of SQL statement.

!SQL exposes join() and format() methods useful to create a template where to merge variable parts of a query (for instance field or table names).

The !obj string doesn’t undergo any form of escaping, so it is not suitable to represent variable identifiers or values: you should only use it to pass constant strings representing templates or snippets of SQL statements; use other objects such as Identifier or Literal to represent variable parts.

!SQL objects can be passed directly to ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() in place of the query string.

Example:

>>> query = sql.SQL("SELECT {0} FROM {1}").format(
...    sql.SQL(', ').join([sql.Identifier('foo'), sql.Identifier('bar')]),
...    sql.Identifier('table'))
>>> print(query.as_string(conn))
SELECT "foo", "bar" FROM "table"

as_string(context: AdaptContext | None = None) → str

Return the value of the object as string.

Parameters:

context (connection or cursor) – the context to evaluate the string into.

as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

format(*args: Any, **kwargs: Any) → Composed

Merge Composable objects into a template.

Parameters:

• args – parameters to replace to numbered ({0}, {1}) or auto-numbered ({}) placeholders

• kwargs – parameters to replace to named ({name}) placeholders

Returns:

the union of the !SQL string with placeholders replaced

Return type:

Composed

The method is similar to the Python str.format() method: the string template supports auto-numbered ({}), numbered ({0}, {1}…), and named placeholders ({name}), with positional arguments replacing the numbered placeholders and keywords replacing the named ones. However placeholder modifiers ({0!r}, {0:<10}) are not supported.

If a !Composable objects is passed to the template it will be merged according to its as_string() method. If any other Python object is passed, it will be wrapped in a Literal object and so escaped according to SQL rules.

Example:

>>> print(sql.SQL("SELECT * FROM {} WHERE {} = %s")
...     .format(sql.Identifier('people'), sql.Identifier('id'))
...     .as_string(conn))
SELECT * FROM "people" WHERE "id" = %s

>>> print(sql.SQL("SELECT * FROM {tbl} WHERE name = {name}")
...     .format(tbl=sql.Identifier('people'), name="O'Rourke"))
...     .as_string(conn))
SELECT * FROM "people" WHERE name = 'O''Rourke'

join(seq: Iterable[Template]) → Template

join(seq: Iterable[Any]) → Composed

Join a sequence of Composable.

Parameters:

seq – the elements to join.

Use the !SQL object’s string to separate the elements in !seq. Elements that are not Composable will be considered Literal.

If the arguments are Template instance, return a Template joining all the items. Note that arguments must either be all templates or none should be.

Note that Composed objects are iterable too, so they can be used as argument for this method.

Example:

>>> snip = sql.SQL(', ').join(
...     sql.Identifier(n) for n in ['foo', 'bar', 'baz'])
>>> print(snip.as_string(conn))
"foo", "bar", "baz"

class psycodict.Identifier(*strings: str)

Bases: Composable

A Composable representing an SQL identifier or a dot-separated sequence.

Identifiers usually represent names of database objects, such as tables or fields. PostgreSQL identifiers follow different rules than SQL string literals for escaping (e.g. they use double quotes instead of single).

Example:

>>> t1 = sql.Identifier("foo")
>>> t2 = sql.Identifier("ba'r")
>>> t3 = sql.Identifier('ba"z')
>>> print(sql.SQL(', ').join([t1, t2, t3]).as_string(conn))
"foo", "ba'r", "ba""z"

Multiple strings can be passed to the object to represent a qualified name, i.e. a dot-separated sequence of identifiers.

Example:

>>> query = sql.SQL("SELECT {} FROM {}").format(
...     sql.Identifier("table", "field"),
...     sql.Identifier("schema", "table"))
>>> print(query.as_string(conn))
SELECT "table"."field" FROM "schema"."table"

as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

class psycodict.Placeholder(name: str = '', format: str | PyFormat = PyFormat.AUTO)

Bases: Composable

A Composable representing a placeholder for query parameters.

If the name is specified, generate a named placeholder (e.g. %(name)s, %(name)b), otherwise generate a positional placeholder (e.g. %s, %b).

The object is useful to generate SQL queries with a variable number of arguments.

Examples:

>>> names = ['foo', 'bar', 'baz']

>>> q1 = sql.SQL("INSERT INTO my_table ({}) VALUES ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(sql.Placeholder() * len(names)))
>>> print(q1.as_string(conn))
INSERT INTO my_table ("foo", "bar", "baz") VALUES (%s, %s, %s)

>>> q2 = sql.SQL("INSERT INTO my_table ({}) VALUES ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(map(sql.Placeholder, names)))
>>> print(q2.as_string(conn))
INSERT INTO my_table ("foo", "bar", "baz") VALUES (%(foo)s, %(bar)s, %(baz)s)

as_string(context: AdaptContext | None = None) → str

Return the value of the object as string.

Parameters:

context (connection or cursor) – the context to evaluate the string into.

as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

class psycodict.Literal(obj: Any)

Bases: Composable

A Composable representing an SQL value to include in a query.

Usually you will want to include placeholders in the query and pass values as ~cursor.execute() arguments. If however you really really need to include a literal value in the query you can use this object.

The string returned by !as_string() follows the normal adaptation rules for Python objects.

Example:

>>> s1 = sql.Literal("fo'o")
>>> s2 = sql.Literal(42)
>>> s3 = sql.Literal(date(2000, 1, 1))
>>> print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'fo''o', 42, '2000-01-01'::date

as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

class psycodict.Composable(obj: Any)

Bases: ABC

Abstract base class for objects that can be used to compose an SQL string.

!Composable objects can be joined using the + operator: the result will be a Composed instance containing the objects joined. The operator * is also supported with an integer argument: the result is a !Composed instance containing the left argument repeated as many times as requested.

!SQL and !Composed objects can be passed directly to ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() in place of the query string.

abstractmethod as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

as_string(context: AdaptContext | None = None) → str

Return the value of the object as string.

Parameters:

context (connection or cursor) – the context to evaluate the string into.

class psycodict.Composed(seq: Sequence[Any])

Bases: Composable

A Composable object made of a sequence of !Composable.

The object is usually created using !Composable operators and methods (such as the SQL.format() method). !Composed objects can be passed directly to ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() in place of the query string.

It is also possible to create a !Composed directly specifying a sequence of objects as arguments: if they are not !Composable they will be wrapped in a Literal.

Example:

>>> comp = sql.Composed(
...     [sql.SQL("INSERT INTO "), sql.Identifier("table")])
>>> print(comp.as_string(conn))
INSERT INTO "table"

!Composed objects are iterable (so they can be used in SQL.join for instance).

as_bytes(context: AdaptContext | None = None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

join(joiner: SQL | LiteralString) → Composed

Return a new !Composed interposing the !joiner with the !Composed items.

The !joiner must be a SQL or a string which will be interpreted as an SQL.

Example:

>>> fields = sql.Identifier('foo') + sql.Identifier('bar')  # a Composed
>>> print(fields.join(', ').as_string(conn))
"foo", "bar"

class psycodict.DelayCommit(obj, final_commit=True, silence=None, active=True)

Bases: object

Used to set default behavior for whether to commit changes to the database connection.

Entering this context in a with statement will cause _execute calls to not commit by default. When the final DelayCommit is exited, the connection will commit.

Setting active=False disables the DelayCommit completely, which can be helpful since it’s often used in a with context and conditionally entering that context is annoying to write with if statements.