コード例 #1
0
def filter_none_from_frequency_count(frequency_count):
    """
    Filter out None rows from a frequency count.

    A frequency count is a list of tuples or a list of lists, in which the first
    element is the property, and the second element is the count.

    Parameters
    ----------
    frequency_count: list of lists or tuples with length 2

    Returns
    -------
    new_frequency_count: list of lists or tuples with length 2

    Example
    -------
    >> a = [ [None, 4], [1, 2], [2, 3], [10, 1] ]
    >> UtilsContainer.filter_none_from_frequency_count(a)
    [ [1, 2], [2, 3], [10, 1] ]
    >> a = [ [None, 4], ['None', 2], ['c', 3], ['d', 1] ]
    >> UtilsContainer.filter_none_from_frequency_count(a)
    [ ['None', 2], ['c', 3], ['d', 1] ]
    """

    Assert.frequency_count(frequency_count)

    new_frequency_count = [x for x in frequency_count if x[0] is not None]
    return new_frequency_count
コード例 #2
0
    def create_database(self, db_name, force_create=False):
        """Create an empty database.

        Parameters
        ----------
        db_name: string
            Name of the to be created database
        force_create: bool
            Flag indicating if a possible existing database with the same
            name shall be overwritten or not. If force_create = False and
            there is already a database with the name db_name, nothing happens.

        Returns
        -------
        None

        Postconditions
        --------------
        Database with the given name exists.
        If force_create=True: database with the given name is empty.
        """

        Assert.py_type(db_name, str, 'db_name')
        Assert.py_type(force_create, bool, 'force_create')

        if force_create:
            self.drop_database(db_name, confirm=True)
        self.cursor.execute("CREATE DATABASE {0} DEFAULT CHARACTER SET 'utf8'".format(db_name))
        if self.info:
            print ('Database {0} created'.format(db_name))
コード例 #3
0
    def index_column(self, table, column):
        """Add an index to a specific column in a Table.

        Parameters
        ----------
        table: string
            Name of the Table to index.
        column: string
            Name of the column to index.

        Returns
        -------
        None

        Prerequisites
        -------------
        Table exists (in current database if no database is specified).
        Column exists in Table
        """

        Assert.py_type(table, str, 'table')
        Assert.py_type(column, str, 'column')
        assert self.table_exists(table)

        index_exists = self._index_exists(table, column)
        if not index_exists:
            q = '''
                ALTER TABLE {table}
                ADD INDEX `{col}_idx` (`{col}` ASC)
            '''.format(table=table, col=column)
            self.execute(q)
コード例 #4
0
def filter_array(lst):
    """
    Filter out NaN values from a list or np.array.

    If the type of the input list implements np.isnan, filter out NaN.
    Otherwise, leave the input list unaltered.

    Example
    -------
    >> L = [1, 2, 3, np.nan]
    >> UtilsContainer.filter_array(L)
    [1, 2, 3]
    >> L = np.array(['a', 'b', 'c', np.nan])
    >> UtilsContainer.filter_array(L)
    ['a', 'b', 'c', np.nan]
    """

    Assert.seq(lst)

    try:
        lst_invalid = np.isnan(lst)
    except TypeError:
        lst_invalid = np.zeros_like(lst, dtype=bool)
    lst_valid = np.logical_not(lst_invalid)

    if isinstance(lst, list):
        result = list(lst[i] for i in range(len(lst_valid)) if lst_valid[i])
    elif isinstance(lst, np.ndarray):
        result = lst[lst_valid]
    else:
        msg = 'Input shall be either list or numpy array, is now a {}'.format(type(lst))
        raise AssertionError(msg)

    assert type(lst) == type(result)
    return result
コード例 #5
0
    def column_exists(self, table, column):
        """Return whether the given column exists in the Table.

        Parameters
        ----------
        table: string
            Name of the table to look in.
        table: string
            Name of the column to look for.

        Returns
        -------
        column_exists: bool
            Flag indicating whether the Table `table` contains a column `column`.

        Prerequisites
        -------------
        The Table `table` shall exist.
        """

        Assert.py_type(table, str, 'table')
        Assert.py_type(column, str, 'column')
        assert self.table_exists(table), 'The Table `{0}` shall exist.'.format(table)

        cols = self.show_columns(table)
        return column in cols
コード例 #6
0
    def checksum_table(self, table):
        """Return the checksum of the given Table.

        Parameters
        ----------
        table: string
            Name of the Table to calculate the checksum of.

        Returns
        -------
        checksum: long int
            Checksum of the Table.

        Prerequisites
        -------------
        Table shall exist (in current database if no database is specified).

        Notes
        -----
        During the checksum operation, the table is locked with a read lock
        for InnoDB and MyISAM.
        """

        Assert.py_type(table, str, 'table')
        msg = 'Table {0} shall exist (in current database if no database is specified).'.format(table)
        assert self.table_exists(table), msg

        q = '''
            CHECKSUM TABLE {0}
        '''.format(table)
        self.execute(q)
        result = self.fetchone()
        return result[1]
コード例 #7
0
    def truncate_table(self, table, disable_foreign_key_checks=False):
        """Truncate a table from the database

        Raises an error if the table does not exists

        Parameters
        ----------
        table: string
            Name of the table to truncate.
        disable_foreign_key_checks: boolean
            Flag indicating whether foreign keys shall be checked

        Returns
        -------
        None
        """

        Assert.py_type(table, str, 'table')
        Assert.table_exists(table, self)

        if disable_foreign_key_checks:
            # Temporarily disable foreign key checks
            sql = '''SET FOREIGN_KEY_CHECKS = 0'''
            self.execute(sql)

        truncate = 'TRUNCATE {}'.format(table)
        self.execute(truncate)

        if disable_foreign_key_checks:
            # Reenable foreign key checks
            sql = '''SET FOREIGN_KEY_CHECKS = 1'''
            self.execute(sql)
コード例 #8
0
    def __init__(self, config=None, database=None, info_mode=True, debug_mode=False):
        if config is None:
            config = {
                'user': '******',
                'passwd': 'Nyon6966',
                'host': '83.161.215.203',
                'port': 8889
            }
        Assert.py_type(config, dict, 'MySQL configuration')
        assert 'user' in config
        assert 'passwd' in config
        assert 'host' in config
        assert 'port' in config
        self.config = config

        connection_config = {'user': config['user'],
                             'passwd': config['passwd'],
                             'host': config['host'],
                             'port': config['port'], }
        self.cnx = self._connect_to_mysql(connection_config)
        self.cursor = self.cnx.cursor()
        self.debug = debug_mode
        self.info = info_mode
        if database is not None:
            self.connect_to_database(database)
        elif 'default_db' in config and config['default_db'] is not None:
            self.connect_to_database(config['default_db'])
        self.cnx.autocommit(True)
コード例 #9
0
def frequency_count2occurrence_list(frequency_count):
    """Transform a frequency count into an occurrence list.

    A frequency count is a list of tuples or a list of lists, in which the first
    element is the property, and the second element is the count. An occurrence
    list is a list with each property <count> times in it.

    Parameters
    ----------
    frequency_count: list of lists or tuples with length 2

    Returns
    -------
    occurrence_list: np.array, dtype = <type(most general key in frequency_count)>

    Example
    -------
    >> a = [ [0, 4], [1, 2], [2, 3], [10, 1] ]
    >> UtilsContainer.frequency_count2occurrence_list(a)
    [ 0  0  0  0  1  1  2  2  2 10]
    >> a = [ ['a', 4], ['b', 2], ['c', 3], ['d', 1] ]
    >> UtilsContainer.frequency_count2occurrence_list(a)
    ['a' 'a' 'a' 'a' 'b' 'b' 'c' 'c' 'c' 'd']
    """

    Assert.frequency_count(frequency_count)

    occurrence_list = np.empty(0)
    for k, v in frequency_count:
        if k is not None:
            k_list = np.empty(v, dtype=type(k))
            k_list.fill(k)
            occurrence_list = np.append(occurrence_list, k_list)

    return occurrence_list
コード例 #10
0
def increment_smallest(lst, dx):
    """Increment the smallest element of the list L with the given amount dx.

    If the minimum occurs multiple times, the first element is incremented."""

    Assert.py_type(lst, np.ndarray, 'L')

    min_index = np.argmin(lst)
    lst[min_index] += dx
コード例 #11
0
    def sneak_preview(self, table):
        """Print the first ten rows of the Table to the console."""

        Assert.py_type(table, str, 'Input table')
        assert self.table_exists(table), \
            'Table {0} shall exist in the database'.format(table)

        show = '''
            SELECT * 
            FROM {0}
            LIMIT 10
        '''.format(table)
        self.execute(show)
        print (self.fetchall())
コード例 #12
0
def list2dict(lst):
    """
    Convert a list to a dictionary, where the key of each entry are the list
    elements, and the values are indices 0..N, where the ordering is the ordering
    used in the list.
    """

    Assert.seq(lst)

    nr_elements = len(lst)
    result = dict(zip(lst, range(nr_elements)))

    assert isinstance(result, dict), 'Output shall be a dictionary'
    msg = 'All elements of input list ({}) shall be in dictionary ({})'.format(len(result), len(lst))
    assert len(result) == len(lst), msg
    return result
コード例 #13
0
    def drop_table(self, table):
        """Drop a table from the database.

        If the table does not exist in the database, nothing happens.

        Parameters
        ----------
        table: string
            Name of the table to drop.

        Returns
        -------
        None
        """

        Assert.py_type(table, str, 'table')

        drop = 'DROP TABLE IF EXISTS {0}'.format(table)
        self.execute(drop)
コード例 #14
0
    def table_exists(self, table, db_name=None):
        """Return whether the given Table exists in the current database.

        Parameters
        ----------
        table: string
            Name of the table to look up.

        Returns
        -------
        table_exists: bool
            Flag indicating whether the Table `table` exists in the current database.

        Prerequisites
        -------------
        Connection to a database must have been made.
        """

        # TODO: This pattern with temporarily disabling debug information happens more frequently.
        # Is there a way to write this in some sort of with statement? 

        # Temporarily disable debug information
        current_debug_mode = self.debug
        self.debug = False

        # Make the assertions
        Assert.py_type(table, str, 'table')
        assert self.current_database() is not None, 'Connection to a database shall have been made.'

        if db_name is None:
            db_name = self.current_database()
        q = '''
            SELECT COUNT(`TABLE_NAME`)
            FROM `INFORMATION_SCHEMA`.TABLES 
            WHERE `TABLE_SCHEMA` = '{}' 
            AND `TABLE_NAME` = '{}'
        '''.format(db_name, table)

        # Reenable debug information
        self.debug = current_debug_mode

        # Return the outcome
        return bool(self.fetch_as_value(q))
コード例 #15
0
    def create_table_like(self, table_ref, table_out, force_create=False):
        """Create an empty Table with columns like another Table.

        Create the Table `table_out` with columns and properties exactly
        as Table `table_ref`, but with no contents.

        Parameters
        ----------
        table_ref: string
            Name of the reference Table to mimic.
        table_out: string
            Name of the Table to create.
        force_create: bool
            Flag indicating whether `table_out` shall be overwritten if it
            already exists. If it shall not be overwritten and does exist yet,
            nothing happens.

        Returns
        -------
        table_created: bool
            Flag indicating whether `table_out` is indeed written to the DB.
            If False, the table already existed and nothing had happened.

        Prerequisites
        -------------
        Connection to a database must have been made.
        Table 'table_ref' exists (in current database if no database is specified).
        """

        Assert.py_type(table_ref, str, 'table_ref')
        Assert.py_type(table_out, str, 'table_out')
        Assert.py_type(force_create, bool, 'force_create')
        assert self.current_database() is not None, \
            'Connection to a database must have been made.'

        if '.' in table_ref:
            i = table_ref.index('.')
            db_name = table_ref[:i]
            table_name = table_ref[i + 1:]
        else:
            db_name = None
            table_name = table_ref
        assert self.table_exists(table_name, db_name), \
            'Table `table_ref` shall exist (in current database if no database is specified).'

        create_table_query = self._show_create(table_ref)
        create_table_query = create_table_query.replace(table_ref, table_out)
        table_created = self.create_table_from_create(create_table_query, force_create)

        return table_created
コード例 #16
0
    def create_table_from_select(self, table_out, select_query, force_create=False):
        """Create a table from a select query.

        Parameters
        ----------
        table_out: string
            Name of the Table to create.
        select_query: string
            Valid SQL query that returns a Table. Example:
            SELECT <fields> FROM <table> WHERE <condition>
        force_create: bool
            Flag indicating whether `table_out` shall be overwritten if it
            already exists. If it shall not be overwritten and does exist yet,
            nothing happens.

        Returns
        -------
        table_created: bool
            Flag indicating whether `table_out` is indeed written to the DB.
            If False, the table already existed and nothing had happened.

        Prerequisites
        -------------
        Connection to a database must have been made.

        Postconditions
        --------------
        Table 'table_out' exists in current database.
        """

        Assert.py_type(table_out, str, 'table_out')
        Assert.py_type(select_query, str, 'select_query')
        Assert.py_type(force_create, bool, 'force_create')
        assert self.current_database() is not None, \
            'Connection to a database must have been made.'

        create_table_query = 'CREATE TABLE {0} AS ({1})'.format(table_out, select_query)
        table_created = self.create_table_from_create(create_table_query, force_create)

        return table_created
コード例 #17
0
    def get_config(predefined_config):
        """Retrieve a predefined config dictionary for DBManager

        Input:
        ------
        predefined_config: string
            Name of the predefined configuration settings. It shall be a recognized predefined configuration,
            otherwise an AssertionError is raised.
            local: Mac server in office in Utrecht
            aws: Aurora AWS RDS

        Returns:
        --------
        config: dict
            Dictionary with DB connection configuration, necessary for the class DBManager.
        """

        Assert.py_type(predefined_config, str, 'predefined config')

        if predefined_config == 'localhost':
            config = {
                'user': '******',
                'passwd': '',
                'host': '127.0.0.1',
                'port': 3306,
                'default_db': 'orderwriter'
            }
        elif predefined_config == 'puzzle':
            config = {
                'user': '******',
                'passwd': '',
                'host': '127.0.0.1',
                'port': 3306,
                'default_db': 'puzzle_words'
            }
        elif predefined_config == 'nyon_office':
            config = {
                'user': '******',
                'passwd': 'Nyon6966',
                'host': '83.161.215.203',
                'port': 8889
            }
        elif predefined_config == 'orderwriter_test':
            config = {
                'user': '******',
                'passwd': 'AbLsLKnq6NJjd8a6',
                'host': '213.206.228.254',
                'port': 3306,
                'default_db': 'orderwriter_20150721',
            }
        elif predefined_config == 'orderwriter_live':
            config = {
                'user': '******',
                'passwd': 'BjaNWs29sUeNWyZ4',
                'host': '213.206.228.254',
                'port': 3306,
                'default_db': 'orderwriter'
            }
        elif predefined_config == 'aws_au':
            config = {
                'user': '******',
                'passwd': 'Nyon6966',
                'host': 'aurora.ck67ii8uyuzl.eu-west-1.rds.amazonaws.com',
                'port': 3306
            }
        elif predefined_config == 'aws_ow':
            config = {
                'user': '******',
                'passwd': 'Nyon6966',
                'host': 'orderwriter.crii9g2k1ak3.eu-central-1.rds.amazonaws.com',
                'port': 3306
            }
        else:
            msg = 'Predefined config "{0}" not recognized.'.format(predefined_config)
            raise AssertionError(msg)
        config['config'] = predefined_config
        return config
コード例 #18
0
    def create_table_from_create(self, create_table_query, force_create=False,
                                 temporary=False):
        """Create a table from a create query.

        Execute the create_table_query to create a table. The name of the Table
        to create is assumed to be the third word of the query.

        Parameters
        ----------
        create_table_query: string
            Valid SQL query to create a Table.
        force_create: bool
            Flag indicating whether `table_out` shall be overwritten if it
            already exists. If it shall not be overwritten and does exist yet,
            nothing happens.
        temporary: bool
            Flag indicating whether `table_out` will be a temporary Table.

        Returns
        -------
        :rtype: bool
            Flag indicating whether `table_out` is indeed written to the DB.
            If False, the table already existed and nothing had happened.

        Prerequisites
        -------------
        Connection to a database must have been made.

        Postconditions
        --------------
        Table exists in current database.

        Example
        -------
        Syntax of create_table_query shall look like:
            CREATE TABLE `name` AS ( <select query> );
        or:
            CREATE TABLE `name` (
              `col1` int(11) NOT NULL,
              `col2` date DEFAULT NULL,
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        or:
            CREATE TABLE `name` LIKE `ref_table`;
        """

        Assert.py_type(create_table_query, str, 'create_table_query')
        Assert.py_type(force_create, bool, 'force_create')
        assert self.current_database() is not None, \
            'Connection to a database shall have been made.'

        table_name = self._table_name_from_create_query(create_table_query, temporary)

        if force_create and self.table_exists(table_name):
            self.drop_table(table_name)
        try:
            self.execute(create_table_query)
            table_created = True
            if self.info:
                print ('Table {} created'.format(table_name))
        except pymysql.err.InternalError:
            print('Last query:\n{0}'.format(create_table_query))
            raise

        return table_created
コード例 #19
0
    def fetch_as_np_matrix(self, select_query, glob2loc_x, glob2loc_y):
        """Execute a select query and return the outcome as an np matrix.

        Parameters
        ----------
        select_query: string
            Valid SQL query that returns three columns from a Table. Example:
                SELECT person_id, article_number, SUM(purchase_weight)
                FROM orders
                GROUP BY person_id, article_number
        glob2loc_x: dict
            Keys: All possible global values for the first column
            Values: Range from 0,...,N where N is the number of items in the dict
        glob2loc_y: dict
            Keys: All possible global values for the second column
            Values: Range from 0,...,N where N is the number of items in the dict

        Returns
        -------
        result: np matrix
            A numpy matrix where the first two columns indicate the row and column index,
            and the third column in the select query indicates the value of the matrix elements.

        Prerequisites
        -------------
        Argument select_query shall select exactly three columns
        Argument glob2loc_x keys shall contain all encountered values in the first column
        Argument glob2loc_y keys shall contain all encountered values in the second column
        Argument glob2loc_x values shall be a continous range from 1,...N
        Argument glob2loc_y values shall be a continous range from 1,...N
        The third column in the select query shall be a number
        """

        Assert.py_type(glob2loc_x, dict, 'glob2loc_x')
        Assert.py_type(glob2loc_y, dict, 'glob2loc_y')
        max_loc_x = max(glob2loc_x.values())
        nr_rows = len(glob2loc_x)
        assert max_loc_x < nr_rows, 'glob2loc_x values shall be a continous range from 1,...N'
        max_loc_y = max(glob2loc_y.values())
        nr_cols = len(glob2loc_y)
        assert max_loc_y < nr_cols, 'glob2loc_y values shall be a continous range from 1,...N'

        self.execute(select_query)
        data = self.fetchall()
        result = np.zeros(shape=[nr_rows, nr_cols])
        for (x, y, val) in data:
            try:
                row = glob2loc_x[x]
            except KeyError:
                msg = 'Global value {0} not found in glob2loc_x'.format(x)
                raise AssertionError(msg)
            try:
                col = glob2loc_y[y]
            except:
                msg = 'Global value {0} not found in glob2loc_y'.format(y)
                raise AssertionError(msg)
            try:
                result[row, col] = val
            except ValueError:
                raise AssertionError('The third column in the select query shall be a number')

        return result
コード例 #20
0
    def insert_into_table(self, table_name, cols, values, update_on_duplicate=True, db_name=None):
        """Insert the values in the columns of the given Table.

        Parameters
        ----------
        table_name: string
            Name of the table to add the records to
        cols: (numpy) array of strings
            List of the columns to fill
        values: (numpy) array of tuples
            List of the values to fill.
        update_on_duplicate: boolean
            Determines what happens if a duplicate key is encountered
            If True: the record is updated (default)
            If False: the insertion is ignored
        db_name: string
            Name of the database in which the table is defined. If no database name is given,
            the current database is used.

        Returns
        -------
        None

        Prerequisites
        -------------
        Connection to a database shall have been made
        Table `table_name` shall exists
        Table `table_name` shall contain all columns in `cols`

        Postconditions
        --------------
        If possible, all records are added to the Table

        Notes
        -----
        Only 1000 records can be added in a single INSERT INTO statement (MySQL restriction)
        This method breaks up the insertion in multiple insertions of a 1000
        when more than 1000 records need to be inserted.
        """
        Assert.py_type(table_name, str, 'table_name')
        if db_name is not None:
            Assert.py_type(db_name, str, 'db_name')
            table_name = db_name + '.' + table_name
        Assert.nonemptylist(cols)
        for col in cols:
            Assert.py_type(col, str, 'Column {0}'.format(col))
        nr_cols = len(cols)
        Assert.nonemptylist(values)
        for val in values:
            Assert.nonemptylist(val, nr_cols)
        assert self.current_database() is not None, \
            'Connection to a database must have been made.'

        # Split up the values in chunks of 1,000
        max_inserts = 1000
        nr_queries = np.ceil(len(values) / max_inserts)
        list_values = np.array_split(values, nr_queries)

        for vals in list_values:
            # Build up the query
            if update_on_duplicate:
                ignore = ''
            else:
                ignore = 'IGNORE '

            q = "INSERT {0}INTO {1}\n".format(ignore, table_name)
            q += "("
            for i_col, col in enumerate(cols):
                q += col
                if i_col < nr_cols - 1:
                    q += ", "
            q += ")\n"
            q += "VALUES\n"
            for i_val, val in enumerate(vals):
                q += "("
                for i_field, field in enumerate(val):
                    # Field with quotes if necessary
                    if field is None:
                        q += "NULL"
                    elif isinstance(field, str):
                        if field.upper() == "NULL":
                            q += "NULL"
                        else:
                            q += "'{0}'".format(field)
                    elif isinstance(field, date) or isinstance(field, time):
                        q += "'{0}'".format(field)
                    elif isinstance(field, Decimal):
                        # TODO: Note! Decimal is converted here to float!
                        # In principle, this should not matter, if you insert it
                        # in the database again, the desired precision is maintained. 
                        q += "{}".format(float(field))
                    elif np.isnan(field):
                        q += "NULL"
                    elif np.isscalar(field):
                        q += "{0}".format(field)
                    else:
                        msg = 'Type {0} not recognized'.format(type(field))
                        raise AssertionError(msg)
                    # Trailing comma
                    if i_field < nr_cols - 1:
                        q += ", "
                q += ")"
                if i_val < len(vals) - 1:
                    q += ","
                q += "\n"
            if update_on_duplicate:
                q += "ON DUPLICATE KEY UPDATE\n"
                for i_col, col in enumerate(cols):
                    q += "{0}=VALUES({0})".format(col)
                    if i_col < nr_cols - 1:
                        q += ",\n"

            # Execute the query
            self.execute(q)