Database

PostgresConnection

adele.database.postgres_client.PostgresConnection

Connection class that contains the sqlalchemy db connection.

Parameters:

Name	Type	Description	Default
environment	str	application environment, such as development.	required
api_name	str	Name of api using this connection. Used to load the relevant sqlalchemy table objects.	required
local	bool	Changes connection string values if True for local testing	False

Attributes:

Name	Type	Description
db	type	SQLAlchemy connection to postgreSQL database.
create_tables	type	Description of parameter create_tables.

Instantiate this class to initiate a postgres connection. If running locally, pass local=True. Pass this object to the SQLAlchemyCRUD object to instantiate a new db session. All table objects for the API as described in tables.api_name.py are stored as attributes in this object and can be passed to the SQLAlchemyCRUD object.

Examples:

>>> db = PostgresConnection('dev', 'liveml')
>>> project_info = postgres_client.SQLAlchemyCRUD(db, db.ProjectInfo)

SQLAlchemyCRUD

adele.database.postgres_client.SQLAlchemyCRUD

Postgres client to make CRUD operations for a reference table using SQLAlchemy

Parameters:

Name	Type	Description	Default
database	connect_db	A database connection per connect_db()	required
table_model	declarative_base	The declarative_base class from SQLAlchemy representing the table to operate on, e.g. ProjectInfo	required

Examples:

Import the client and whatever configs you need (SQLAlchemy tables are typically stored in configs)

>>> from adele.database import postgres_client

>>> from config import config

Initialize your crud client

>>> project_info = postgres_client.SQLAlchemyCRUD(db, db.ProjectInfo)

Create an entry in the table.

>>> project_info.create(dict(project_id="12121df",description="this one", features_id="xxx"))

Read the table. The first parameter is your 'where' clause as key values pairs in a dictionary. The second parameter is the column names in the select statement.

>>> project_info.read(dict(),[])

Update entries from the table. The first dictionary is your 'where' clause and should refer to the primary keys of the table. The second dictionary is what you want to update as key value pairs.

>>> project_info.update(
    dict(project_id="12121df"),
    dict(features_id="this", outcomes_id="that")
    )

Delete entries from the table.

>>> project_info.delete(dict(project_id="12121df"))

model property readonly

The SQL Alchemy model needs to be loaded into memory for this to work

table_columns property readonly

Extract the columns from the model

create(self, row_data)

Insert entry or entries into a table

Parameters:

Name	Type	Description	Default
row_data	dict or list	Row data that corresponds to the model definition for the table e.g. dict(project_id = "p1223", description="34234"). If dictionary is within list, supports bulk inserts.	required

Examples:

>>> from adele.database.tables.segmentation import ProjectInfo
>>> db = PostgresConnection(env.get('environment'), env.get('api'), local=True)
>>> project_info = SQLAlchemyCRUD(db, ProjectInfo)

Single row insert

>>> project_info.create(dict(project_id="R12234"))

Bulk Insert

>>> project_info.create([
        dict(project_id="R12234"),
        dict(project_id="R12235")
        ])

Exceptions:

Type	Description
DBError	Any kind on error related to the retrieval of the file data from the db.

read(self, where_info, select_info=[])

Read entries from a table

Parameters:

Name	Type	Description	Default
where_info	dict	A dictionary containing the 'where' clause, e.g. dict(project_id="P12345", filename="daffd.csv")	required
select_info	list	The list of column names in the specified table to select on. Defaults to select * from table where...	[]

Exceptions:

Type	Description
DBError	Any kind on error related to the retrieval of the file data from the db.

update(self, primary_key_info, update_values_info)

Update the entry(ies) of a single row in a table.

Parameters:

Name	Type	Description	Default
primary_key_info	dict	Dictionary that holds primary key information in key, value pairs. This acts essentially like the where clause but it has to include only primary key fields otherwise it won't work.	required
update_values_info	dict	Dictionary that holds information about fields to update in key, values pairs. Can update many fields for the row at the same time.	required

Exceptions:

Type	Description
DBError	Any kind on error related to the retrieval of the file data from the db.

delete(self, where_info)

Remove entries from a table using exact match filtering

Parameters:

Name	Type	Description	Default
where_info	dict	The where clause of the select statement as key value pairs. e.g. dict(project_id = "p1223", filename="hello.csv"). Can take any number of filters using any number of columns supported by the table model.	required

Exceptions:

Type	Description
DBError	Any kind on error related to the retrieval of the file data from the db.

---

Last update: 2022-02-04