

Database Utilities

Collection of functions that extend of simplify interactions with MySQL and SQLite databases

Import Module

using BioMedQuery.DBUtils

Index

Functions

# BioMedQuery.DBUtils.assemble_cols — Method.

assemble_cols(data_values::DataFrame)

Given a DataFrame, returns a column name string formatted for an insert/load statement

source

# BioMedQuery.DBUtils.assemble_cols_and_vals — Method.

assemble_vals(data_values)

Given a dictionary containg (:column=>value) return a single string properly formatted for a MySQL insert. E.g MySQL requires CHAR or other non-numeric values be passed with single quotes around them.

source

# BioMedQuery.DBUtils.assemble_cols_and_vals_string — Method.

assemble_vals(data_values)

Given a dictionary containg (:column=>value), return a single string properly formatted for a MySQL SELECT. E.g MySQL requires CHAR or other non-numeric values be passed with single quotes around them.

source

# BioMedQuery.DBUtils.assemble_vals — Method.

assemble_vals(data_values, column_names)

Given a Dict of values and the column names, return a single string properly formatted for a MySQL INSERT. E.g MySQL requires CHAR or other non-numeric values be passed with single quotes around them.

source

# BioMedQuery.DBUtils.col_match — Method.

col_match(con, tablename, col_names)

Checks if each column in the csv/data frame has a matching column in the table.

source

# BioMedQuery.DBUtils.col_match — Method.

col_match(con, tablename, data_values)

Checks if each column in the dataframe has a matching column in the table.

source

# BioMedQuery.DBUtils.db_query — Method.

query_mysql(con, query_code)

Execute a mysql command

source

# BioMedQuery.DBUtils.db_query — Method.

query(db, query_code)

Execute a SQLite command

source

# BioMedQuery.DBUtils.db_select — Method.

select_(con, colnames, tablename, data_values)

Perform: SELECT colnames tablename WHERE keys(data_values)=values(data_values)

source

# BioMedQuery.DBUtils.init_mysql_database — Function.

init_mysql_database(;host = "127.0.0.1", dbname="test",
username="root", pswd="", mysql_code=nothing, overwrite=false)

Create a MySQL database using the code inside mysql_code

Arguments

host, dbname, user, pswd
mysql_code::String: String with MySQL code that crates all default tables
overwrite::Bool : Flag, if true and dbname exists, drops all database and re-creates it

Output

con: Database connection and table-column names map

source

# BioMedQuery.DBUtils.insert_row! — Method.

insert_row!(db, tablename, values)

Insert a row of values into the specified table for a given a MySQL database handle

Arguments:

db::MySQLDB: Database object (connection and map)
data_values::Dict{String, Any}: Array of (string) values
verbose: Print debugginh info

source

# BioMedQuery.DBUtils.insert_row! — Method.

insert_row!(db, tablename, values)

Insert a row of values into the specified table for a given a SQLite database handle

Arguments:

db::MySQLDB: Database object (connection and map)
data_values::Dict{String, Any}: Array of (string) values
verbose: Print debugginh info

source

# BioMedQuery.DBUtils.select_all_tables — Method.

select_all_tables_mysql(con)

Return an array of all tables in a given MySQL database

source

# BioMedQuery.DBUtils.select_all_tables — Method.

select_all_tables_mysql(con)

Return an array of all tables in a given MySQL database

source

# BioMedQuery.DBUtils.set_innodb_checks! — Function.

set_innodb_checks(conn, autocommit = 1, foreign_keys = 1, unique = 1)

source

# BioMedQuery.DBUtils.colname_dict — Method.

colname_dict_(con)

Return a dictionary maping tables and their columns for a given MySQL-connection/SQLite-database

source

# BioMedQuery.DBUtils.disable_foreign_checks — Method.

disable_foreign_checks(con::MySQL.MySQLHandle)

Disables foreign checks for MySQL database

source

# BioMedQuery.DBUtils.disable_foreign_checks — Method.

disable_foreign_checks(con::SQLite.DB)

Disables foreign checks for SQLite database

source

# BioMedQuery.DBUtils.enable_foreign_checks — Method.

enable_foreign_checks(con::MySQL.MySQLHandle)

Enables foreign checks for MySQL database

source

# BioMedQuery.DBUtils.enable_foreign_checks — Method.

enable_foreign_checks(con::SQLite.DB)

Enables foreign checks for SQLite database

source

# BioMedQuery.DBUtils.select_columns — Method.

select_columns_mysql(con, table)

For a MySQL database, return an array of all columns in the given table

source

# BioMedQuery.DBUtils.select_columns — Method.

select_columns(db, table)

Return an array with names of columns in the given table

source