pyworkflow.mapper.sqlite module

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

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()

aggregate(operations, operationLabel, groupByLabels=None)

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()

count()

Return the number of element in the table.

createTables(objDict)

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()

Delete all objects from the db.

deleteObject(objId)

Delete an existing object

deleteProperty(key)

doesRowExist(objId)

Return True if a row with a given id exists

getClassRows()

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

getProperty(key, defaultValue=None)

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

getPropertyKeys()

Return all properties stored of this object.

getSelfClassName()

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

hasProperty(key)

Return true if a property with this value is registered.

insertObject(*args)

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()

Return the maximum id from the Objects table.

missingTables()

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

selectAll(iterate=True, orderBy='id', direction='ASC', where=None, limit=None)

selectCmd(whereStr, orderByStr=' ORDER BY id')

selectObjectById(objId)

Select an object give its id

selectObjectsBy(iterate=False, **args)

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

selectObjectsWhere(whereStr, iterate=False)

setProperty(key, value)

Insert or update the property with a value.

setupCommands(objDict)

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

unique(labels, where=None)

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)

Update object data

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

Bases: Mapper

Specific Flat Mapper implementation using Sqlite database

aggregate(operations, operationLabel, groupByLabels=None)

clear()

close()

commit()

Commit changes made to the storage

count()

delete(obj)

Delete an object and all its childs

deleteAll()

Delete all objects stored

deleteProperty(key)

enableAppend()

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)

Return True if the id is in the database

static fmtDate(date)

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)

getInstance()

getProperty(key, defaultValue=None)

getPropertyKeys()

hasProperty(key)

insert(obj)

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

maxId()

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

Return all object from storage

selectBy(iterate=False, objectFilter=None, **args)

Select object meetings some criteria

selectById(objId)

Build the object which id is objId

setProperty(key, value)

unique(labels, where=None)

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)

Update database entry with new object values.

exception pyworkflow.mapper.sqlite.SqliteFlatMapperException

Bases: Exception

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

Bases: Mapper

Specific Mapper implementation using Sqlite database

close()

commit()

Commit changes made to the storage

delete(obj)

Delete an object and all its childs

deleteAll()

Delete all objects stored

deleteChilds(obj)

deleteRelations(creatorObj)

Delete all relations created by object creatorObj

exists(objId)

Return True if the id is in the database

fillObject(obj, objRow, includeChildren=True)

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)

Fill the object with row data.

getParent(obj)

Retrieve the parent object of another.

getRelationChilds(relName, parentObj)

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)

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)

Return all relations created by creatorObj.

getRelationsByName(relationName)

Return all relations stored of a given type.

insert(obj)

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

insertChild(obj, key, attr, namePrefix=None)

insertChilds(obj, namePrefix=None)

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)

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)

selectAll(iterate=False, objectFilter=None)

Return all object from storage

selectAllBatch(objectFilter=None)

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)

Select object meetings some criteria

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

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)

Build the object which id is objId

updateFrom(obj)

Update object data with storage info

updateTo(obj, level=1)

Update storage with object info

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

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()

Delete all objects from the db.

deleteChildObjects(ancestor_namePrefix)

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

deleteMissingObjectsByAncestor(ancestor_namePrefix, idList)

Select all objects in the hierarchy of ancestor_id

deleteObject(objId)

Delete an existing object

deleteRelationsByCreator(parent_id)

doesRowExist(objId)

Return True if a row with a given id exists

insertObject(name, classname, value, parent_id, label, comment)

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)

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

selectAllObjects()

Select all data at once

selectCmd(whereStr, orderByStr=' ORDER BY id')

selectMissingObjectsByAncestor(ancestor_namePrefix, idList)

Select all objects in the hierarchy of ancestor_id

selectObjectById(objId)

Select an object give its id

selectObjectsBy(iterate=False, **args)

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

selectObjectsByAncestor(ancestor_namePrefix, iterate=False)

Select all objects in the hierarchy of ancestor_id

selectObjectsByParent(parent_id=None, iterate=False)

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)

selectRelationChilds(relName, object_parent_id)

selectRelationParents(relName, object_child_id)

selectRelationsByCreator(parent_id)

selectRelationsByName(relationName)

updateObject(objId, name, classname, value, parent_id, label, comment)

Update object data