pyworkflow.mapper.sqlite module

class pyworkflow.mapper.sqlite.SqliteFlatDb(dbName, tablePrefix='', timeout=1000, pragmas=None, indexes=None)[source]

Bases: SqliteDb

Class to handle a Sqlite database. It will create connection, execute queries and commands

CLASS_MAP = {'Boolean': 'INTEGER', 'Float': 'REAL', 'Integer': 'INTEGER'}
VERSION = 1
addCommonFieldsToMap()[source]
aggregate(operations, operationLabel, groupByLabels=None)[source]
Parameters

operations – string or LIST of operations: MIN, MAX, AVG, COUNT, SUM, TOTAL, GROUP_CONCAT. Any single argument function

defined for sqlite at https://www.sqlite.org/lang_aggfunc.html :param operationLabel: string or LIST of attributes to apply the functions on :param groupByLabels: (Optional) attribute or list of attributes to group by the data :return:

clear()[source]
count()[source]

Return the number of element in the table.

createTables(objDict)[source]

Create the Classes and Object table to store items of a Set. Each object will be stored in a single row. Each nested property of the object will be stored as a column value.

deleteAll()[source]

Delete all objects from the db.

deleteObject(objId)[source]

Delete an existing object

deleteProperty(key)[source]
doesRowExist(objId)[source]

Return True if a row with a given id exists

getClassRows()[source]

Create a dictionary with names of the attributes of the columns.

getProperty(key, defaultValue=None)[source]

Return the value of a given property with this key. If not found, the defaultValue will be returned.

getPropertyKeys()[source]

Return all properties stored of this object.

getSelfClassName()[source]

Return the class name of the attribute named ‘self’. This is the class of the items stored in a Set.

hasProperty(key)[source]

Return true if a property with this value is registered.

insertObject(*args)[source]

Insert a new object as a row. *args: id, label, comment, … where … is the values of the objDict from which the tables where created.

maxId()[source]

Return the maximum id from the Objects table.

missingTables()[source]

Return True is the needed Objects and Classes table are not created yet.

selectAll(iterate=True, orderBy='id', direction='ASC', where=None, limit=None)[source]
selectCmd(whereStr, orderByStr=' ORDER BY id')[source]
selectObjectById(objId)[source]

Select an object give its id

selectObjectsBy(iterate=False, **args)[source]

More flexible select where the constrains can be passed as a dictionary, the concatenation is done by an AND

selectObjectsWhere(whereStr, iterate=False)[source]
setProperty(key, value)[source]

Insert or update the property with a value.

setupCommands(objDict)[source]

Setup the INSERT and UPDATE commands base on the object dictionary.

unique(labels, where=None)[source]

Returns the results of the execution of a UNIQUE query

Parameters
  • labels – list of attributes which you want unique values from

  • where – condition to match in the form: attrName=value

Returns

updateObject(*args)[source]

Update object data

class pyworkflow.mapper.sqlite.SqliteFlatMapper(dbName, dictClasses=None, tablePrefix='', indexes=None)[source]

Bases: Mapper

Specific Flat Mapper implementation using Sqlite database

aggregate(operations, operationLabel, groupByLabels=None)[source]
clear()[source]
close()[source]
commit()[source]

Commit changes made to the storage

count()[source]
delete(obj)[source]

Delete an object and all its childs

deleteAll()[source]

Delete all objects stored

deleteProperty(key)[source]
enableAppend()[source]

This will allow to append items to existing db. This is by default not allow, since most sets are not modified after creation.

exists(objId)[source]

Return True if the id is in the database

static fmtDate(date)[source]

Formats a python date into a valid string to be used in a where term Currently creation files is stored in utc time and is has no microseconds.

Parameters

date – python date un utc. use datetime.datetime.utcnow() instead of now()

getAttributes2Store(item)[source]
getInstance()[source]
getProperty(key, defaultValue=None)[source]
getPropertyKeys()[source]
hasProperty(key)[source]
insert(obj)[source]

Insert a new object into the system, the id will be set

maxId()[source]
selectAll(iterate=True, objectFilter=None, orderBy='id', direction='ASC', where='1', limit=None)[source]

Return all object from storage

selectBy(iterate=False, objectFilter=None, **args)[source]

Select object meetings some criteria

selectById(objId)[source]

Build the object which id is objId

setProperty(key, value)[source]
unique(labels, where=None)[source]

Returns a list (for a single label) or a dictionary with unique values for the passed labels. If more than one label is passed it will be unique rows similar ti SQL unique clause.

:param labels (string or list) item attribute/s to retrieve unique row values :param where (string) condition to filter the results

updateTo(obj, level=1)[source]

Update database entry with new object values.

exception pyworkflow.mapper.sqlite.SqliteFlatMapperException[source]

Bases: Exception

class pyworkflow.mapper.sqlite.SqliteMapper(dbName, dictClasses=None)[source]

Bases: Mapper

Specific Mapper implementation using Sqlite database

close()[source]
commit()[source]

Commit changes made to the storage

delete(obj)[source]

Delete an object and all its childs

deleteAll()[source]

Delete all objects stored

deleteChilds(obj)[source]
deleteRelations(creatorObj)[source]

Delete all relations created by object creatorObj

exists(objId)[source]

Return True if the id is in the database

fillObject(obj, objRow, includeChildren=True)[source]

Fills an already instantiated object the data in a row, including children

NOTE: This, incase children are included, makes a query to the db with all its children ‘like 2.*’. At some point it calls selectById triggering the loading of other protocols and ancestors.

Parameters
  • obj – Object to fill

  • objRow – row with the values

  • includeChildren – (True). If true children are also populated

fillObjectWithRow(obj, objRow)[source]

Fill the object with row data.

getParent(obj)[source]

Retrieve the parent object of another.

getRelationChilds(relName, parentObj)[source]

Return all “child” objects for a given relation. Params:

relName: the name of the relation. parentObj: this is “parent” in the relation

Returns:

a list of “child” objects.

getRelationParents(relName, childObj)[source]

Return all “parent” objects for a given relation. Params:

relName: the name of the relation. childObj: this is “child” in the relation

Returns:

a list of “parent” objects.

getRelationsByCreator(creatorObj)[source]

Return all relations created by creatorObj.

getRelationsByName(relationName)[source]

Return all relations stored of a given type.

insert(obj)[source]

Insert a new object into the system, the id will be set

insertChild(obj, key, attr, namePrefix=None)[source]
insertChilds(obj, namePrefix=None)[source]

Insert childs of an object, if namePrefix is None, the it will be deduced from obj.

insertRelation(relName, creatorObj, parentObj, childObj, parentExt=None, childExt=None)[source]

This function will add a new relation between two objects. Params:

relName: the name of the relation to be added. creatorObj: this object will be the one who register the relation. parentObj: this is “parent” in the relation childObj: this is “child” in the relation

insertRelationData(relName, creatorId, parentId, childId, parentExtended=None, childExtended=None)[source]
selectAll(iterate=False, objectFilter=None)[source]

Return all object from storage

selectAllBatch(objectFilter=None)[source]

Select all the row at once for all the project

Returns:

all the protocols populated with the data from the DB

selectBy(iterate=False, objectFilter=None, **args)[source]

Select object meetings some criteria

selectByClass(className, includeSubclasses=True, iterate=False, objectFilter=None)[source]

Select all object of a give class. By default object of subclasses will be retrieved also. This behaviour can be changed by passing includeSubclass=False

selectById(objId)[source]

Build the object which id is objId

updateFrom(obj)[source]

Update object data with storage info

updateTo(obj, level=1)[source]

Update storage with object info

class pyworkflow.mapper.sqlite.SqliteObjectsDb(dbName, timeout=1000, pragmas=None)[source]

Bases: SqliteDb

Class to handle a Sqlite database. It will create connection, execute queries and commands

DELETE = 'DELETE FROM Objects WHERE '
DELETE_SEQUENCE = "DELETE FROM SQLITE_SEQUENCE WHERE name='Objects'"
EXISTS = 'SELECT EXISTS(SELECT 1 FROM Objects WHERE %s=? LIMIT 1)'
SELECT = "SELECT id, parent_id, name, classname, value, label, comment, datetime(creation, 'localtime') as creation FROM Objects"
SELECT_RELATION = 'SELECT object_%s_id AS id FROM Relations WHERE name=? AND object_%s_id=?'
SELECT_RELATIONS = 'SELECT * FROM Relations WHERE '
VERSION = 1
deleteAll()[source]

Delete all objects from the db.

deleteChildObjects(ancestor_namePrefix)[source]

Delete from db all objects that are childs of an ancestor, now them will have the same starting prefix

deleteMissingObjectsByAncestor(ancestor_namePrefix, idList)[source]

Select all objects in the hierarchy of ancestor_id

deleteObject(objId)[source]

Delete an existing object

deleteRelationsByCreator(parent_id)[source]
doesRowExist(objId)[source]

Return True if a row with a given id exists

insertObject(name, classname, value, parent_id, label, comment)[source]

Execute command to insert a new object. Return the inserted object id

insertRelation(relName, parent_id, object_parent_id, object_child_id, object_parent_extended=None, object_child_extended=None, **kwargs)[source]

Execute command to insert a new object. Return the inserted object id

selectAllObjects()[source]

Select all data at once

selectCmd(whereStr, orderByStr=' ORDER BY id')[source]
selectMissingObjectsByAncestor(ancestor_namePrefix, idList)[source]

Select all objects in the hierarchy of ancestor_id

selectObjectById(objId)[source]

Select an object give its id

selectObjectsBy(iterate=False, **args)[source]

More flexible select where the constrains can be passed as a dictionary, the concatenation is done by an AND

selectObjectsByAncestor(ancestor_namePrefix, iterate=False)[source]

Select all objects in the hierarchy of ancestor_id

selectObjectsByParent(parent_id=None, iterate=False)[source]

Select object with a given parent if the parent_id is None, all object with parent_id NULL will be returned

selectObjectsWhere(whereStr, iterate=False)[source]
selectRelationChilds(relName, object_parent_id)[source]
selectRelationParents(relName, object_child_id)[source]
selectRelationsByCreator(parent_id)[source]
selectRelationsByName(relationName)[source]
updateObject(objId, name, classname, value, parent_id, label, comment)[source]

Update object data