acq2sqlite

Convert db.txt into a sqlite database.

Functions

column_names()	These names match what's used by dcmmeta2tsv.py and 00_build_db.bash CSA first, normal dicom headers, and then filename.
have_pipe_data()
none_to_null(row)	Template should match get_header.

Classes

DBQuery([sql])

Convenient SQL queries for tracking dicom headers/metadata.

class acq2sqlite.DBQuery(sql=None)

Convenient SQL queries for tracking dicom headers/metadata.

This class is a poorly implemented, ad-hoc/bespoke ORM for database defined in schema.sql

CONSTS = ['Project', 'SequenceName', 'iPAT', 'Comments', 'SequenceType', 'PED_major', 'Phase', 'TR', 'TE', 'Matrix', 'PixelResol', 'BWP', 'BWPPE', 'FA', 'TA', 'FoV']

CONSTS is a list of expected aquisition-invarient parameters. The values of these attributes should be the same for every acquisition sharing a Project × SequenceName pair (across all sessions of a Project).

We consider the acquisition to have a Quallity Assurance error when the value of any of these parameters in a single acquisition fails to match the template.

For example TR for task EPI acquisition identified by SequenceName=RewardedAnti in Project=WPC-8620 should always be 1300 ms.

check_acq(d)

Is this exact acquisition (time, id, series) already in the database?

Parameters:

d (dict[str, str]) – All parameters of an acquisition

Returns:

True/False if dict params exist

Return type:

bool

dict_to_db_row(d)

insert a dicom header (representative of acquisition) into db :param d: a single acquisition to add to DB :return: True if added or already exists

Parameters:

d (dict[str, str])

Return type:

bool

find_acquisitions_since(since_date=None)

Retrieve all acquisitions with AcqDate greater than the specified date.

Parameters:

since_date (str | None) – Date string in ‘YYYY-MM-DD’ format; defaults to yesterday if None.

Returns:

List of acquisition rows with AcqDate > since_date.

get_template(pname, seqname)

Find the template from template_by_count. See make_template_by_count.sql

Parameters:

• pname (str) – protocol name

• sqname – sequence name

• seqname (str)

Returns:

template row matching prot+seq name pair

Return type:

Row

is_template(param_id)

Check if param id is the ideal template.

Parameters:

param_id (int)

Return type:

bool

most_recent(project='%')

Find a projects most recent scan in the database :param project: project name. default to all via wildcard % :return: timestamp string of most recent seen scan

Parameters:

project (str)

Return type:

Row

param_rowid(d)

Parameters:

d (dict[str, str]) – dicom headers

Returns:

acq_param (new or existing) rowid identifying unique set of CONSTS

Return type:

int | None

Find or insert the combination of parameters for an acquisition. Using CONSTS, the header parameters that should be invariant across acquisitions of the same name within a study.

>>> db = DBQuery(sqlite3.connect(':memory:'))
>>> with open('schema.sql') as f: _ = [db.sql.execute(c) for c in f.read().split(";")]
...
>>> # db.sql.execute(".read schema.sql")
>>> example = {k: 'x' for k in db.CONSTS}
>>> db.param_rowid(example)
1
>>> db.param_rowid(example)
1
>>> db.param_rowid({**example, 'Project': 'b'})
2
>>> str(db.param_rowid({}))
'None'

search_acq_param(d)

Try to find aca_param row id of CONSTS part of input d

Parameters:

d (dict[str, str]) – dictionary of tag values (keys in CONSTS)

Returns:

rowid of matching (param_id) or None

Return type:

int | None

tsv_to_dict(line)

Read a tsv line into dictionary.

Parameters:

line (str) – tab separated string. likely line from dcmmeta2tsv.py

Returns:

dictionary with taglist.txt names and acquisition values.

Return type:

dict[str, str]

acq2sqlite.column_names()

These names match what’s used by dcmmeta2tsv.py and 00_build_db.bash CSA first, normal dicom headers, and then filename.

Defaults to reading from taglist.txt This provides a language agnostic lookup for columns in schema.sql

These column names should match what is output by ./dcmmeta2tsv.bash or ./dcmmeta2tsv.py

Also see dcmmeta2tsv.read_known_tags()

>>> cn = column_names() # reads taglist.txt
>>> cn[0] # hard coded here
'Phase'
>>> cn[3] # from taglist.xt
'AcqDate'

acq2sqlite.none_to_null(row)

Template should match get_header. This changes all None``s to ``"null" :param row: single row from a sqlite query (likely get_template())

Parameters:

row (Row | None)