schema#

Module for table schema and column data type objects which are used to define table schemas for MySQL.

class reata.schema.TableSchema(name, columns=<factory>, primary_key=<factory>)#

Bases: object

MySQL table schema object for defining MySQL tables.

Parameters#

namestr: The name of the MySQL table.
columnsdict: A dictionary containing column names as keys and their respective types as values. See example below.
primary_keytuple: Tuple containing the names of the columns to be used as a primary key.

Returns#

A TableSchema object.

Examples#

Create a table schema for company employees containing columns for record creation time, employee name, and employee age, then pretty-print the schema.

Note

The columns are being passed as a positional argument here.

>>> from reata.schema import TableSchema
>>> schema = TableSchema(
... "Employees", {
...         "ctime": "TIMESTAMP NOT NULL",
...         "name": "VARCHAR(32) NOT NULL",
...         "age": "TINYINT(3) DEFAULT NULL",
...     },
...     primary_key=("ctime", "name"),
... )
>>> print(schema)
Employees
ctime    TIMESTAMP NOT NULL
name     VARCHAR(32) NOT NULL
age      TINYINT(3) DEFAULT NULL

client#

Convenience wrapper for the MySQL DBAPI. Adds an additional layer of abstraction to the connection object in order to simplify common procedures like creating new databases, fetching rows from tables, and other common MySQL operations.

class reata.client.MySQLClient(cnx)#

Bases: object

Client wrapper for MySQLConnection objects.

add_index(table_name, index_name, column_name)#

Ad-hoc method for adding an index to a table which has already been created.

Parameters#

table_namestr: The table to which we wish to add the index.
index_namestr: The name of the index.
column_namestr: The name of the column to be indexed.

bulk_insert(table_name, columns, records, update_method=None)#

Performs an insert for each of the records in a sequence of records. Can optionally update records for duplicate key values by either “upserting” the record or replacing the record entirely.

Parameters#

table_namestr

Name of the table into which the records are being inserted.

columnsSequence

List of column names into which the data is being inserted.

recordsSequence

The records to be inserted into the table.

update_method{str, None}, default None

Whether or not to update records with duplicate keys.

None (default): Do not update records with duplicate keys.
upsert: Add an “ON DUPLICATE KEY UPDATE” clause to the “INSERT” statement to update records.
replace: Use a “REPLACE” statement in place of the “INSERT” statement.

close()#: Close the MySQL connection.

column_count(table_name, include_virtual=False)#

Returns the total number of columns found in the specified table. Optionally allows for inclusion of computed columns in the count.

Parameters#

table_namestr: The name of the table on which the column count is being computed.
include_virtualbool, default False: Whether or not to include virtual columns in the count.

Returns#

An integer representing the total number of columns in the table.

column_names(table_name, include_virtual=False, include_auto=False, exclude=None)#

Returns a tuple of column names from the table. Optionally allows for inclusion of both computed and/or auto-generated columns. The user may also exclude any additional column names from the result set that they desire.

Parameters#

table_namestr: The name of the table which is being queried.
include_virtualbool, default False: Whether or not to include computed columns in the result.
include_autobool, default False: Whether or not to include auto-generated columns in the result.
exclude{Sequence, set, None}, default None: An iterable sequence of column names to exclude from the result.

Returns#

A tuple containing the column names found in the table.

create_database(name)#

Creates a database only if it does not already exist.

Parameters#

namestr: The name of the database to create.

Note

The method submits a “CREATE DATABASE” statement to MySQL with an “IF NOT EXISTS” clause attached. Thus, if the database already exists, the entire statement is ignored and will not raise an error.

create_table(schema)#

Create the specified table in the currently selected database.

Parameters#

schemaTableSchema: The table schema defining the table which is to be created

cursor(*args, **kwargs)#: Exposes the underlying cursor constructor for the MySQLConnection object which returns the requested cursor class. The cursor can be any subclass of MySQLCursor.

database_exists(name)#

Returns a boolean representing the existence of the database.

Parameters#

namestr: The name of the database for which to search.

Returns#

A boolean indicating whether or not the database exists.

dataframe_insert(table_name, df, include_index=True, update_method=None)#

Insert data into a table directly from a Pandas dataframe.

Parameters#

table_namestr

Name of the table into which the records are being inserted.

dfDataFrame

A Pandas dataframe containing the records to be inserted.

include_indexbool

Whether or not to include the dataframe index in the records.

update_method{str, None}, default None

Whether or not to update records with duplicate keys.

None (default): Do not update records with duplicate keys.
upsert: Add an “ON DUPLICATE KEY UPDATE” clause to the “INSERT” statement to update records.
replace: Use a “REPLACE” statement in place of the “INSERT” statement.

fetch_rows(table_name, columns='*', where=None, order_by=None, offset=0, limit=9223372036854775807, decimal_to_float=False, map_records=False)#

Returns a tuple of rows from the table. Provides the option to offset the selection window, filter the results, sort by column, and limit the total number of returned records.

Parameters#

table_namestr: The name of the table from which to fetch rows
columns{Sequence, str}, default “*”: Some iterable sequence containing the names of the columns from which to fetch the data.
wherestr: An optional “WHERE” clause in valid MySQL syntax.
order_by{str, None}, default None: An optional “ORDER BY” clause in valid MySQL syntax.
offset{int, None}, default None: The number of rows by which to offset the query.
limitint, default sys.maxsize: Optional limit for the number of rows to be returned.
decimal_to_floatbool, default False: Whether or not to convert values of type Decimal to float.
map_recordsbool, default False: Whether or not to map the returned records. If True the rows returned will each be a dictionary with values mapped to column names.

Returns#

A list of rows from the table.

fetch_table(table_name, columns='*', where=None, order_by=None, offset=0, limit=9223372036854775807, decimal_to_float=False, auto_downcast=False)#

Returns records from the table as a dataframe. Uses roughly the same arguments as fetch_rows with the added option to automatically downcast all numeric types in the returned dataframe.

Parameters#

table_namestr: The name of the table from which to fetch rows
columns{Sequence, str}, default “*”: Some iterable sequence containing the names of the columns from which to fetch the data.
wherestr: An optional “WHERE” clause in valid MySQL syntax.
order_by{str, None}, default None: An optional “ORDER BY” clause in valid MySQL syntax.
offset{int, None}, default None: The number of rows by which to offset the query.
limitint, default sys.maxsize: Optional limit for the number of rows to be returned.
decimal_to_floatbool, default False: Whether or not to convert values of type Decimal to float.
auto_downcastbool: Whether or not to downcast numeric types in the returned dataframe.

Returns#

A PDXtra DataFrame object.

index_exists(table_name, index_name, column_name)#

Checks to see if the index exists on the specified column of the specified table.

Parameters#

table_namestr: The table to which we wish to add the index.
index_namestr: The name of the index.
column_namestr: The name of the column to be indexed.

Returns#

A boolean representing the existence of the index.

switch_db(db_name)#

Temporarily switch the currently selected database.

Parameters#

db_namestr: The name of the database to be temporarily selected.

table_exists(table_name)#

Returns a boolean indicating the existence of the table in the specified database.

Parameters#

table_namestr: The name of the table for which to search.

Returns#

A boolean indicating the existence of the table in the currently selected database.

use(database, auto_create=False)#

Switch the current database. If auto_create is True, then create the database if it does not yet exist.

Parameters#

databasestr: The name of the database to which we wish to switch.
auto_createbool: If True then attempt to create the database if it does not already exist. If False raise an error when attempting to connect to a non-existant database.

Raises#

ERROR_BAD_DB_ERROR:: When the database does not exist and auto_create is set to False.

property database#: The name of the currently selected database.

property table_names#: Returns the set of all tables stored in the currently selected database.