Database

Database

Base class for databases in SAYN.

Databases are implemented using sqlalchemy, and the engine attribute is available when working in python tasks without the need for calling create_engine.

Attributes:

Name	Type	Description
engine	sqlalchemy.Engine	A sqlalchemy engine referencing the database.
name	str	Name of the db as defined in required_credentials in project.yaml.
name_in_yaml	str	Name of db under credentials in settings.yaml.
db_type	str	Type of the database.
metadata	sqlalchemy.MetaData	A metadata object associated with the engine.

execute(self, script)

Executes a script in the database. Multiple statements are supported.

Parameters:

Name	Type	Description	Default
script	sql	The SQL script to execute	required

load_data(self, table, data, schema=None, batch_size=None, replace=False, **ddl)

Loads a list of values into the database

The default loading mechanism is an INSERT...VALUES, but database drivers will implement more appropriate methods.

Parameters:

Name	Type	Description	Default
table	str	The name of the target table	required
data	list	A list of dictionaries to load	required
schema	str	An optional schema to reference the table	None
batch_size	int	The max size of each load batch. Defaults to max_batch_rows in the credentials configuration (settings.yaml)	None
replace	bool	Indicates whether the target table is to be replaced (True) or new records are to be appended to the existing table (default)	False
ddl	dict	An optional ddl specification in the same format as used in autosql and copy tasks	{}

Returns:

Type	Description
int	Number of records loaded

read_data(self, query, **params)

Executes the query and returns a list of dictionaries with the data.

Parameters:

Name	Type	Description	Default
query	str	The SELECT query to execute	required
params	dict	sqlalchemy parameters to use when building the final query as per sqlalchemy.engine.Connection.execute	{}

Returns:

Type	Description
list	A list of dictionaries with the results of the query