pisces.crud

Module containing functions for in-memory and in-database table creation and in-database table dropping.

Supports: * table schema/accounts/owners/namespaces. SQLAlchemy calls them “schema.” * prefixed table names for standard table prototypes * loading arbitrary tables from existing database * creating standard prototype tables

Supported actions are: * creating in-memory canonical tables

Glossary

• table : canonical table name string, like “site” or “origin”

• tablename : the fully-qualified table name string (includes any owner, prefix)

• mapped table : a SQLAlchemy ORM table class

• table instance / row : an instance of a mapped table

pisces.crud.drop_tables(session, *tables)

Drop table classes in a database.

Parameters:

sessionsqlalchemy.orm.Session instance

tablesmapped table classes

pisces.crud.init_tables(session, *tables)

Create table classes in a database.

Parameters:

sessionsqlalchemy.orm.Session instance

tablesmapped table classes

pisces.crud.load_tables(session, *tables, **kwargs)

Load existing database tables into mapped SQLAlchemy table classes.

This function is not recommended. Instead, please subclass from an existing abstract table in the schema, or use the SQLAlchemy ORM to create a class for your table.

sessionsqlalchemy.orm.Session

Session connected to the target database.

tablesstr

Desired table names. Names must be part of the schema. If omitted, a schema must be specified, and all core tables for the schema are returned.

schemastr {‘css3’, ‘kbcore’} Default, ‘css3’

Which set of core tables are being used. This is used to associate column names to known Pisces column definitions, so that text file I/O of tables can be supported for a loaded table.

prefixstr, optional

All table names will have the provided prefix. e.g. “TA_” for “TA_origin”, “TA_wfdisc”, etc.

ownerstr, optional

e.g. “myuser” for “myuser.origin”, “myuser.wfdisc”, etc. If omitted, the database default owner/account will be used.

primary_keysdict, optional

A mapping of table names to their primary keys of the form, {‘tablename1’: [‘primary_key1’, ‘primary_key2’], …} The SQLAlchemy ORM can only reflect/load tables if they have a primary key. If a table is a view or has no primary key, these must be specified explicitly.

Returns:

dict

Corresponding dict of mapped SQLAlchemy table classes, keyed by canonical name.

Examples

# load standard tables in ‘myaccount’ table space >>> tables = load_tables(session, ‘site’, ‘sitechan’, ‘wfisc’, … schema=’kbcore’, owner=’myaccount’)

# load a standard table that has no primary key set >>> tables = load_tables(session, ‘site’, … primary_keys={‘site’, [‘sta’, ‘ondate’]})

# load all standard css3 tables in the default account >>> tables = load_tables(session, schema=’css3’)

# load a table that is outside of the schema # the table needs to have a primary key defined # this will try to use column info definitions known to Pisces >>> tables = load_tables(session, ‘ccwfdisc’, owner=’myaccount’)

pisces.crud.make_table_names(*tables, **kwargs)

Get table name strings in the format: {owner.}{prefix}tablename

This function does no checking that supplied table names are in the indicated schema.

Parameters:

tablesstr

Desired table names. If omitted, a schema must be specified, and all core tables for the schema are used.

schemastr {‘css3’, ‘kbcore’}

Which set of core tables are being used. If omitted, one or more tables must be specified.

prefixstr

All table names will have the provided prefix. e.g. “TA_” for “TA_origin”, “TA_wfdisc”, etc.

ownerstr

e.g. “myuser” for “myuser.origin”, “myuser.wfdisc”, etc.

Returns:

dict

The desired formatted table name strings, keyed by canonical name.

Raises:

ValueErrorunknown schema specified, or tablenames omitted and schema

not specified

Examples

>>> make_table_names('site', 'origin', owner='global', prefix='TA_')
{'site': 'global.TA_site', 'origin': 'global.TA_origin'}

>>> make_table_names(schema='css3', prefix='TA_')
{'affiliation': 'TA_affiliation', 'assoc': 'TA_assoc', ...}

pisces.crud.make_tables(*tables, **kwargs)

Create mapped SQLAlchemy ORM table classes.

If no owner or prefix is indicated, the returned tables are the same as those in pisces.tables.<schema> . The user is encouraged to import these classes directly. If an owner is indicated, returned tables inhererit from the same SQLA declarative base.

Parameters:

tablesstr

Desired table names. Table name must be known to the schema. If omitted, a schema must be specified, and all core tables for the schema are returned.

schemastr (“css3” or “kbcore”)

Which set of core tables are being used. Required.

prefixstr

All table names will have the provided prefix. e.g. “TA_” for “TA_origin”, “TA_wfdisc”, etc.

ownerstr

e.g. “myuser” for “myuser.origin”, “myuser.wfdisc”, etc.

Returns:

dict

Corresponding dict of mapped SQLAlchemy table classes, keyed by canonical name.

Examples

>>> # makes a dict of CSS3.0 'TA_site' and 'TA_sitechan' mapped classes
>>> tables = make_tables('site', 'sitechan', schema='css3', prefix='TA_')

>>> # make a dict of all standard kbcore tables
>>> tables = make_tables(schema='kbcore')

>>> # make a dict of some tables from the 'myowner' account
>>> tables = make_tables('origin', 'event', schema='kbcore', owner='myowner')

pisces.crud.split_table_names(*tablenames, **kwargs)

Splits full table names into a (owner, prefix, tablename) 3-tuples.

Parameters:

tablenamesstr

schemastr {‘css3’, ‘kbcore’} Default ‘css3’.

split_prefixbool, default True

If True, return (owner, prefix, tablename) tuples, otherwise return (owner, tablename) tuples.

Returns:

list

Corresponding list of (owner, tablename) or (owner, prefix, tablename) tuples strings. Empty owner or prefix are ‘’.

Examples

>>> tablenames = ['global.site', 'global.sitechan', 'TA_site',
...               'different_acct.my_origin', 'myfriend.discrim_last']
>>> split_table_names(*tablenames)
[('global', '', 'site'), ('global', '', 'sitechan'), ('', 'TA_', 'site'),
('different_acct', 'my_', 'origin'), ('myfriend', '', 'discrim_last')]