mysql_commands.py

sql_multiple_query

sql_multiple_query(*args, **kwargs)

Performs a query of the identified table and returns multiple columns that matched the search criteria.

Parameters:

• database (string) – Optional “kind” of ingredients.

• searchtable (string) – REQUIRED

Raises:

lumache.InvalidKindError – If the kind is invalid.

Returns:

The ingredients list.

Return type:

list[str]

query_file

query_source_file(regexStr, *args, **kwargs)

Performs a query into the Project’s searchTable’s ‘fullpath’ column using the search criteria identified for a single file. Returns a single filepath.

query_file(regexStr, database=’CoNNECT’, searchtable=None, returncol=’fullpath’, searchcol=’filename’, progress=False, orderby=’fullpath’, inclusion=None, exclusion=None, orinclusion=None)

Parameters:

• regexStr (str) – Required initial search terms

• returncol (str) – Optional table column to return (default ‘fullpath’)

• progress (bool) – Optional operate in verbose mode

• orderby (str) – Optional column to sort results (default ‘fullpath’)

• inclusion (str or list[str]) – Optional inclusion criteria to refine results. These must also be contained within searchcol (AND operation)

• exclusion (str or list[str]) – Optional exclusion criteria to refine results. These must not be within the searchcol

• orinclusion (str or list[str]) – Optional inclusion criteria to refine results. These may also be contained within the search column (OR operation)

Raises:

Error – Any error occurs

Returns:

Fullpath to a single file if only 1 file matches the search criteria, otherwise None.

Return type:

None or str

query_source_files

query_source_file(regexStr, *args, **kwargs)

Performs a query into the Project’s searchSourceTable’s ‘fullpath’ column using the search criteria identified for a single file. Returns a single filepath.

query_source_file(regexStr, database=’CoNNECT’, searchtable=None, returncol=’fullpath’, searchcol=’filename’, progress=False, orderby=’fullpath’, inclusion=None, exclusion=None, orinclusion=None)

Parameters:

• regexStr (str) – Required initial search terms

• returncol (str) – Optional table column to return (default ‘fullpath’)

• progress (bool) – Optional operate in verbose mode

• orderby (str) – Optional column to sort results (default ‘fullpath’)

• inclusion (str or list[str]) – Optional inclusion criteria to refine results. These must also be contained within searchcol (AND operation)

• exclusion (str or list[str]) – Optional exclusion criteria to refine results. These must not be within the searchcol

• orinclusion (str or list[str]) – Optional inclusion criteria to refine results. These may also be contained within the search column (OR operation)

Raises:

Error – Any error occurs

Returns:

Fullpath to a single sourcedata file if only 1 file matches the search criteria, otherwise None.

Return type:

None or str

sql_query_dir_check

sql_query_dirs(regexStr, dirToCheck, showProgress)

Queries a directory for existing NIfTI images.

sql_query_dirs(regexStr, dirToCheck, showProgress)

Parameters:

• regexStr (str) – Required search string

• dirToCheck (str) – Required directory to perform a NIfTI image search

• showProgress (bool) – Required verbose mode

Raises:

Error – Any error occurs

Returns:

NIfTI files exist in <dirToCheck>

Return type:

bool

sql_query_dirs

sql_query_dirs(regexStr, showProgress, rawFlag, *args, **kwargs)

Performs a query into the provided table/column using the search criteria identified. Returns unique directories containing the identified files.

sql_query_dirs(regexStr, showProgress, rawFlag, inclusion=None, exclusion=None)

Parameters:

• regexStr (str) – Required search string

• showProgress (bool) – Required verbose mode

• rawFlag (bool) – Required true for project’s searchSourceTable (default false - project’s searchTable)

• inclusion (str or list[str]) – Optional inclusion criteria to refine results. These must also be contained within searchcol (AND operation)

• exclusion (str or list[str]) – Optional exclusion criteria to refine results. These must not be within the searchcol

Raises:

Error – Any error occurs

Returns:

The list of directories containing files matching search criteria

Return type:

list[str]

sql_query

sql_query(*args, **kwargs)

Performs a query into the provided table/column using the search criteria identified.

sql_query(database=’CoNNECT’, searchtable=None, returncol=’fullpath’, searchcol=’filename’, regex=None, progress=False, orderby=’fullpath’, inclusion=None, exclusion=None, orinclusion=None)

Parameters:

• database (str) – Optional MySQL database containing the project’s searchtable (default ‘CoNNECT’)

• searchtable (str) – Required MySQL table to query

• returncol (str) – Optional table column to return (default ‘fullpath’)

• searchcol (str) – Optional table column to query (default ‘filepath’)

• regex (str) – Required initial search terms

• progress (bool) – Optional operate in verbose mode

• orderby (str) – Optional column to sort results (default ‘fullpath’)

• inclusion (str or list[str]) – Optional inclusion criteria to refine results. These must also be contained within searchcol (AND operation)

• exclusion (str or list[str]) – Optional exclusion criteria to refine results. These must not be within the searchcol

• orinclusion (str or list[str]) – Optional inclusion criteria to refine results. These may also be contained within the search column (OR operation)

Raises:

Error – Any error occurs

Returns:

The list of columns matching search criteria

Return type:

list[str]

sql_multiple_query

sql_multiple_query(*args, **kwargs)

Performs a query into the provided table/column using the search criteria identified.

sql_multiple_query(database=’CoNNECT’, searchtable=None, returncol=’fullpath’, searchcol=’filename’, regex=None, progress=False, orderby=’fullpath’)

Parameters:

• database (str) – Optional MySQL database containing the project’s searchtable (default ‘CoNNECT’)

• searchtable (str) – Required MySQL table to query

• returncol (str) – Optional table column to return (default ‘fullpath’)

• searchcol (str) – Optional table column to query (default ‘filepath’)

• regex (str) – Required initial search terms

• progress (bool) – Optional operate in verbose mode

• orderby (str) – Optional column to sort results (default ‘fullpath’)

Raises:

Error – Any error occurs

Returns:

All the columns elements from each row where the input column matches the search criteria

Return type:

list[list[str]]

sql_create_project_tables

sql_create_project_tables(*args, **kwargs)

Creates the tables for a project whose credentials have been loaded into custom creds class via read_credentials().

sql_create_project_tables(progress=False)

Parameters:

progress (bool) – Optional operate in verbose mode

Raises:

Error – Any error occurs

Returns:

None

Return type:

None

sql_table_insert

sql_table_insert(table, item, *args, **kwargs)

Inserts items into a Project’s table. The project’s credentials must have been loaded into customcreds class via read_credentials().

sql_table_insert(table,item,progress=False)

Parameters:

• table (str) – Required MySQL table name

• item (dict or list) – Required elements to insert into the table

• progress (bool) – Optional operate in verbose mode

Raises:

Error – Any error occurs

Returns:

None

Return type:

None

sql_table_remove

sql_table_insert(table, item, *args, **kwargs)

Removes items into a Project’s table. The project’s credentials must have been loaded into customcreds class via read_credentials().

sql_table_remove(table,item,progress=False)

Parameters:

• table (str) – Required MySQL table name

• item (dict or list) – Required elements to remove from the table

• progress (bool) – Optional operate in verbose mode

Raises:

Error – Any error occurs

Returns:

None

Return type:

None

sql_check_table_exists

sql_check_table_exists(sqlCursor, table)

Checks if a table exists

sql_table_remove(sqlCursor, table)

Parameters:

• sqlCursor (pymysql.connect.cursor) – Required pymysql connect cursor object

• table (str) – Required MySQL table name

Raises:

Error – Any error occurs

Returns:

None

Return type:

None

sql_create_mysql_connection

sql_create_mysql_connection(host_name, user_name, user_password, db_name, progress)

Creates a connection to the MySQL database.

sql_create_mysql_connection(host_name, user_name, user_password, db_name, progress)

Parameters:

• host_name (str) – Required MySQL master hostname

• user_name (str) – Required MySQL username

• user_password (str) – Required MySQL user password

• db_name (str) – Required MySQL database

• progress (bool) – Required operate in verbose mode

Raises:

Error – Any error occurs

Returns:

Pymysql connect object

Return type:

pymysql.connect