dlatk.mysqlMethods package

Submodules

dlatk.mysqlMethods.mysqlMethods module

MySQL interface methods based on the mysqlclient package

dlatk.mysqlMethods.mysqlMethods.abstractDBConnect(db, host='127.0.0.1', user='sgiorgi')[source]
dlatk.mysqlMethods.mysqlMethods.cloneExactTable(db, sourceTableName, destinationTableName, charset='utf8mb4', use_unicode=True)[source]
dlatk.mysqlMethods.mysqlMethods.dbConnect(db, host='127.0.0.1', charset='utf8mb4', use_unicode=True)[source]

Connects to specified database. Returns tuple of (dbConn, dbCursor, dictCursor)

dlatk.mysqlMethods.mysqlMethods.disableTableKeys(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

Disable keys: good before doing a lot of inserts

dlatk.mysqlMethods.mysqlMethods.doesTableExist(db, table, charset='utf8mb4', use_unicode=True)[source]
dlatk.mysqlMethods.mysqlMethods.enableTableKeys(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

Enables the keys, for use after inserting (and with keys disabled)

dlatk.mysqlMethods.mysqlMethods.execute(db, dbCursor, sql, warnQuery=True, charset='utf8mb4', use_unicode=True)[source]

Executes a given query

dlatk.mysqlMethods.mysqlMethods.executeGetDict(db, dictCursor, sql, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

Executes a given query, returns results as a list of dicts

dlatk.mysqlMethods.mysqlMethods.executeGetList(db, dbCursor, sql, warnQuery=True, charset='utf8mb4', use_unicode=True)[source]

Executes a given query, returns results as a list of lists

dlatk.mysqlMethods.mysqlMethods.executeGetList1(db, dbCursor, sql, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

Executes a given query, expecting one resulting column. Returns results as a list

dlatk.mysqlMethods.mysqlMethods.executeGetSSCursor(db, sql, warnMsg=True, charset='utf8mb4', use_unicode=True, host='127.0.0.1')[source]

Executes a given query (ss cursor is good to iterate over for large returns)

dlatk.mysqlMethods.mysqlMethods.executeWrite(db, dbConn, sql, row, writeCursor=None, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

Executes a write query

dlatk.mysqlMethods.mysqlMethods.executeWriteMany(db, dbConn, sql, rows, writeCursor=None, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

Executes a write query

dlatk.mysqlMethods.mysqlMethods.gen_clone_query(conn, src_tbl, dst_tbl)[source]
dlatk.mysqlMethods.mysqlMethods.getColumnNamesAndTypes(db, table, charset='utf8mb4', use_unicode=True)[source]

returns a dict of column names mapped to types

dlatk.mysqlMethods.mysqlMethods.getTableColumnNameIndices(db, table, colNamesOfNote)[source]

Returns a list of column indices pertaining to the colNames specified

dlatk.mysqlMethods.mysqlMethods.getTableColumnNameList(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

returns a dict of column names mapped to types

dlatk.mysqlMethods.mysqlMethods.getTableColumnNameTypes(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

returns a dict of column names mapped to types

dlatk.mysqlMethods.mysqlMethods.getTableColumnNames(db, table, charset='utf8mb4', use_unicode=True)[source]

Returns a list of column names from a db table

dlatk.mysqlMethods.mysqlMethods.getTableColumnNamesTypes(db, table, charset='utf8mb4', use_unicode=True)[source]

Returns a list of column names and types from a db table

dlatk.mysqlMethods.mysqlMethods.getTableDataLength(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

Returns the data length for the given table

dlatk.mysqlMethods.mysqlMethods.getTableEncoding(db, table=None, charset='utf8mb4', use_unicode=True)[source]

returns the encoding of a given table as a string

dlatk.mysqlMethods.mysqlMethods.getTableIndexLength(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

Returns the data length for the given table

dlatk.mysqlMethods.mysqlMethods.optimizeTable(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]

Optimizes the table -- good after a lot of deletes

dlatk.mysqlMethods.mysqlMethods.qExecute(db, sql, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

performs the db connect and execute in the same call

dlatk.mysqlMethods.mysqlMethods.qExecuteGetList(db, sql, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

performs the db connect and execute in the same call

dlatk.mysqlMethods.mysqlMethods.qExecuteGetList1(db, sql, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

performs the db connect and execute in the same call, equivalent to executeGetList1

dlatk.mysqlMethods.mysqlMethods.qExecuteWriteMany(db, sql, rows, writeCursor=None, warnQuery=False, charset='utf8mb4', use_unicode=True)[source]

Executes a write query

dlatk.mysqlMethods.mysqlMethods.randomSubsetTable(db, sourceTableName, destinationTableName, keyField, percentToSubset=0.1, distinct=True, charset='utf8mb4', use_unicode=True)[source]
dlatk.mysqlMethods.mysqlMethods.standardizeTable(db, dbCursor, table, collate='', engine='MYISAM', charset='utf8mb4', use_unicode=True)[source]

Sets character set, collate and engine

dlatk.mysqlMethods.mysqlMethods.tableExists(db, dbCursor, table, charset='utf8mb4', use_unicode=True)[source]
dlatk.mysqlMethods.mysqlMethods.writeTableToCSV(db, tableName, outputfile, sql_extra=None, charset='utf8mb4', use_unicode=True)[source]

dlatk.mysqlMethods.mysql_iter_funcs module

MySQL interface methods based on the SQLAlchemy package

dlatk.mysqlMethods.mysql_iter_funcs.create_table(db_eng, table_name, columns, more_definitions=[], table_options='', if_exists='error')[source]
Parameters:
  • db_eng -- sqlalchemy.Engine
  • table_name -- string
  • columns -- OrderedDict or dict - where keys are column names and values are column type definitions
  • more_details -- list
  • table_options -- string
  • if_exists -- string ('error' or 'replace' or 'skip')
Returns:

dlatk.mysqlMethods.mysql_iter_funcs.dictify(my_iter, columns)[source]
dlatk.mysqlMethods.mysql_iter_funcs.extend_table(db_eng, table_name, new_columns, if_exists='skip')[source]

Add columns to a table if they don't already exist :param new_columns: OrderedDict or dict - where keys are column names and values are column type definitions :param if_exists: string('skip') #no other options implemented yet

dlatk.mysqlMethods.mysql_iter_funcs.get_db_engine(db_schema, db_host='127.0.0.1', charset='utf8mb4', db_config='~/.my.cnf', port=3306)[source]
dlatk.mysqlMethods.mysql_iter_funcs.list_iter_to_dict_iter(list_iter, dict_keys)[source]

Take list iter + keys, return dict iter

dlatk.mysqlMethods.mysql_iter_funcs.mysql_insert(db_eng, db_table, dict_iterator)[source]
Parameters:
  • dict_iterator -- iterator where next() yields dict that corresponds to a row of data
  • db_eng -- SQLAlchemy engine
  • db_table -- expects this to already exist

steps through iterator, and inserts each yielded item into a mysql database

dlatk.mysqlMethods.mysql_iter_funcs.mysql_multitable(db_eng, dict_iter, table_prefix, table_column, table_column_transform, batch_size=1000, template_table=None)[source]

Takes an iterator and outputs into multiple tables based on data from the iterator :param db_eng: SQLAlchemy db engine :param dict_iter: an iterator of dicts :param table_prefix: for the naming of the tables :param table_column: column to use for deciding which table to insert into :param table_column_transform: function to generate table name suffix using 'table_column' value :param batch_size: once a target table is identified, assume the next n rows go here

dlatk.mysqlMethods.mysql_iter_funcs.mysql_update(db_eng, db_table, dict_iterator, unique_column='id', log_every=10000)[source]

Updates a database table with values defined in dict iterator, finds rows using unique column Other fields are assumed to have the same name as their dict key :param unique_column: the column name for unique column, probably the id field

dlatk.mysqlMethods.mysql_iter_funcs.rename_keys(dict_iter, renamings)[source]
dlatk.mysqlMethods.mysql_iter_funcs.resultset_to_dict_iter(result_set)[source]
Parameters:result_set -- SQLAlchemy results set from db_eng.execute(sql)
Returns:iterator of dicts!
dlatk.mysqlMethods.mysql_iter_funcs.select_columns(dict_iter, keys_to_keep)[source]

Take a dict iter, return a dict iter with fewer columns

Module contents