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(datavalues)=values(datavalues)

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, opts=Dict())

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
  • opts::Dict : Dictionary containing MySQL connection options

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 debugging 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 debugging 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