Querying data

Sqlite bedrock packs package provide functions to build queries more easily.

class Left(value)[source]

A helper class to indicate that a table should be joined using LEFT join in the query of EasyQuery class.

Parameters

value (str) –

value: str

The name of the table

class EasyQuery(connection, sql_code)[source]

EasyQuery is a class which allows quick and easy way of building and executing queries on the database.

In most cases queries should be build using the EasyQuery.build() method. Creating them manually is possible and can be useful if you want to use benefits of some of the methods of this class. In this case make sure that the results of the query are pimary keys of the tables and that the columns are named after the tables.

Parameters

  • connection (Connection) –

  • sql_code (str) –

static build(db, root, *tables, blacklist=('BehaviorPack', 'ResourcePack'), accept_non_pk=True, distinct=True, where=None, group_by=None, having=None, order_by=None)[source]

Creates an instance of EasyQuery from the given properties. This is a go-to method for creating an instance of EasyQuery.

This function automatically finds the relations in the database to find the connections between provided tables. The rows of the results of the query contain primary keys of the provided tables. The query build by this function is then passed to the EasyQuery constructor.

Example:

>>> query = EasyQuery(
...     None, "Entity", Left("Geometry"), "RpAnimation",
...     accept_non_pk=True,
...     where=[
...         "EntityFile.EntityFile_pk == 1"
...     ]
... ).sql_code
>>> print(query)
SELECT DISTINCT
        Entity_pk AS Entity,
        Geometry_pk AS Geometry,
        RpAnimation_pk AS RpAnimation
FROM Entity
JOIN ClientEntity
        ON Entity.identifier = ClientEntity.identifier
JOIN ClientEntityGeometryField
        ON ClientEntity.ClientEntity_pk = ClientEntityGeometryField.ClientEntity_fk
LEFT JOIN Geometry
        ON ClientEntityGeometryField.identifier = Geometry.identifier
JOIN ClientEntityAnimationField
        ON ClientEntity.ClientEntity_pk = ClientEntityAnimationField.ClientEntity_fk
JOIN RpAnimation
        ON ClientEntityAnimationField.identifier = RpAnimation.identifier
WHERE
        EntityFile.EntityFile_pk == 1
Parameters

  • db (Connection | Database | None) – The database connection or Database object. The db can be None if you only want to get the query string and don’t care about running it.

  • root (str) – The root table to start the query from.

  • tables (str | Left) – A list of tables to join. Use Left to indicate that a table should be joined using LEFT join. The tables don’t need to have a direct connection between each other, the connections will be found automatically if necessary relations exist (this means that the query genrated can include tables that are not in the list).

  • blacklist (Iterable[str]) – A list of tables to ignore while searching for connections. By default it’s BehaviorPack and ResourcePack because otherwise in many cases the query would look for objects from the same packs instead of using different more useful connections.

  • accept_non_pk (bool) – Whether to accept non-primary key relations. By default it’s True. The primary key connections only cover the situations where it’s guaranteed that the relation is valid (like a relation between EntityFile and Entity). Most of the connections that might be useful to query are non-primary key connections (like a relation between a ClientEntity and RenderController).

  • distinct (bool) – Whether to use DISTINCT in the query. By default it’s True. It’s recommended to use it when querying for multiple tables because otherwise the query might return the same row multiple times.

  • where (list[str] | None) – A list of constraints to add to the query. This is a list of strings with raw SQL code which is inserted into the WHERE part of the query. The constraints are joined using AND.

  • group_by (list[str] | None) – A list of columns to group the results by. This is a list of strings with raw SQL code which is inserted into the GROUP BY part of the query.

  • having (list[str] | None) – A list of constraints to add to the query. This is a list of strings with raw SQL code which is inserted into the HAVING part of the query. The constraints are joined using AND.

  • order_by (list[str] | None) – A list of columns to order the results by. This is a list of strings with raw SQL code which is inserted into the ORDER BY part of the query.

Return type

EasyQuery

connection: Connection

The database connection.

run()[source]

Run the query on the database and return the cursor.

Return type

Cursor

sql_code: str

The query runnned by this instance of EasyQuery.

yield_wrappers()[source]

Returns an iterator that yields the wrapper classes from the query results. The results are tuples with wrapper classes from sqlite_bedrock_packs.wrappers module based on the tables in the query. If the query is using LEFT join, the wrapper class will be None if the row doesn’t have a value.

Return type

Iterator[tuple]