neurobooth_terra.Table

class neurobooth_terra.Table(table_id, conn, cursor=None, primary_key=None)

Table class that is a wrapper around Postgres SQL table.

Parameters

table_idstr

The table ID

conninstance of psycopg2.Postgres

The connection object

primary_keystr | None

The primary key. If None, the first column name is used as primary key.

Attributes

column_nameslist of str

The column names

data_typeslist of str

The data types of the column names

primary_keylist of str

The primary key. May be more than one in case of compound primary key.

Methods

add_column(col, dtype)	Add a new column to the table.
alter_column(col[, default])	Alter a column in the table.
delete_row([condition])	Delete rows from table.
drop_column(col)	Drop a column from the table.
insert_rows(vals, cols[, on_conflict, …])	Manual insertion into tables
query([include_columns, where])	Run a query.
update_row(pk_val, vals, cols)	Update values in a row

close
drop

__init__(table_id, conn, cursor=None, primary_key=None)

Initialize self. See help(type(self)) for accurate signature.

Methods

__init__(table_id, conn[, cursor, primary_key])	Initialize self.
add_column(col, dtype)	Add a new column to the table.
alter_column(col[, default])	Alter a column in the table.
close()
delete_row([condition])	Delete rows from table.
drop()
drop_column(col)	Drop a column from the table.
insert_rows(vals, cols[, on_conflict, …])	Manual insertion into tables
query([include_columns, where])	Run a query.
update_row(pk_val, vals, cols)	Update values in a row

add_column(col, dtype)

Add a new column to the table.

Parameters

colstr

The column name.

dtypestr

The data type of the column.

alter_column(col, default=None)

Alter a column in the table.

Parameters

colstr

The column name.

defaultstr | dict | None

The default value of the column. If you want to specify a prefix that autoincrements, you can say: dict(prefix=prefix), e.g., dict(prefix=’SUBJECT’)

delete_row(condition=None)

Delete rows from table.

Parameters

conditionstr

The condition to filter rows by and delete them.

drop_column(col)

Drop a column from the table.

Parameters

colstr

The column name.

insert_rows(vals, cols, on_conflict='error', conflict_cols='auto', update_cols='all', where=None)

Manual insertion into tables

Parameters

valslist of tuple

The records to insert. Each tuple is one row.

colslist of str

The columns to insert into.

on_conflict‘nothing’ | ‘update’ | ‘error’

What to do when a conflict is encountered

conflict_cols‘auto’ | str | list

If ‘auto’, it uses primary key when on_conflict is ‘update’. If list, uses the list of columns to create a unique index to infer conflicts.

update_cols‘all’ | str | list

If ‘all’, updates all the columns with the new values. If list, updates only those columns.

wherestr | None

Condition to filter rows by. If None, keep all rows where primary key is not NULL.

Returns

pk_valstr | None

The primary keys of the row inserted into. If multiple rows are inserted, returns None.

Notes

When conflict_cols is a list, a unique index must be set for the target columns. The following SQL command is handy:

create unique index subject_identifier on subject (first_name_birth, last_name_birth, date_of_birth);

query(include_columns=None, where=None)

Run a query.

Parameters

include_columnsstr | list of str | None

If None, query all columns

wherestr | None

Condition to filter rows by. If None, keep all rows. E.g., table.query(where=‘“wearable_bool” = True’)

Returns

dfinstance of pd.Dataframe

A pandas dataframe object.

update_row(pk_val, vals, cols)

Update values in a row

Parameters

pk_valstr

The value of the primary key to match the row to replace.

valstuple

The values in the row to replace.

colslist of str

The columns to insert into.