breezeblocks.sql package¶

Submodules¶

breezeblocks.sql.aggregates module¶

SQL Aggregate functions.

class breezeblocks.sql.aggregates.Avg_(expr)¶

Bases: breezeblocks.sql.aggregates._Aggregator

SQL “AVG” aggregate function.

Finds the average of all values in the expression provided.

class breezeblocks.sql.aggregates.Count_(expr)¶

Bases: breezeblocks.sql.aggregates._Aggregator

SQL “COUNT” aggregate function.

Finds the number of non-null values in the expression provided.

class breezeblocks.sql.aggregates.Max_(expr)¶

Bases: breezeblocks.sql.aggregates._Aggregator

SQL “MAX” aggregate function.

Finds the maximum value from the expression provided.

class breezeblocks.sql.aggregates.Min_(expr)¶

Bases: breezeblocks.sql.aggregates._Aggregator

SQL “MIN” aggregate function.

Finds the minimum value from the expression provided.

class breezeblocks.sql.aggregates.RecordCount¶

Bases: breezeblocks.sql.query_components.Selectable

Count of the records in the tables of the query.

as_(alias)¶

Returns:	An aliased version of this expression.

class breezeblocks.sql.aggregates.Sum_(expr)¶

Bases: breezeblocks.sql.aggregates._Aggregator

SQL “SUM” aggregate function.

Finds the sum of all values in the expression provided.

breezeblocks.sql.column module¶

Provides a column class and an expression for using columns in queries.

class breezeblocks.sql.column.AliasedColumnExpr(alias, column)¶

Bases: breezeblocks.sql.expressions.ValueExpr

A column with an alias used in querying.

Initializes an aliased column from an existing column.

Parameters:	alias – The alias that will be assigned to this object. column – The `Column` that this is going to reference.

as_(alias)¶

Provides a different alias for the same underlying column.

Parameters:	alias – Another alias to use.

full_name¶: The full name of the underlying column.

class breezeblocks.sql.column.ColumnExpr(name, table)¶

Bases: breezeblocks.sql.expressions.ValueExpr

Represents a database column.

Initializes a column.

Parameters:	name – The name of this column. table – The table to which this column belongs.

as_(alias)¶

Provides a different alias for the same underlying column.

Parameters:	alias – Another alias to use.

breezeblocks.sql.column_collection module¶

Classes that represent a collection of columns.

It is convenient to be able to reference a collection of columns that are a subset of what is in a table, or from one of the tables in a join, or in any other scenario where the columns referenced are not part of a collection implied by another class.

class breezeblocks.sql.column_collection.ColumnCollection(columns)¶

Bases: object

Represents a collection of columns in a database.

Instances are meant to be passed to the select method of Query objects. Calling select on one of these adds all of the columns in it to the select clause of the query.

get_column(key)¶

Returns:	The specified column from the collection.

get_names()¶

Returns:	The names of all columns in the collection.

breezeblocks.sql.dml module¶

class breezeblocks.sql.dml.Delete(statement, params, db=None)¶

Bases: breezeblocks.sql.statement.Statement

Represents a database delete.

Initializes a delete statement against a specific database.

Parameters:	statement – The SQL statement for the delete. params – A list of literal values to pass into the statement. db – The database to perform the delete on.

execute(conn=None)¶

Executes the prepared statement in the database.

Parameters:	conn – Optional connection to use to execute this statement. If this is not provided one will be borrowed from the database’s pool.

set_param(param_key, value)¶

Sets a bound parameter for the statement.

Parameters:	param_key – The identifier of the parameter to set. value – The value to assign to the parameter.

show()¶: Prints the constructed SQL with placeholders for bound parameters.

class breezeblocks.sql.dml.Insert(statement_base, table, columns, db=None)¶

Bases: breezeblocks.sql.statement.Statement

Represents a database insert.

This can be used to insert data either from your python processes or from the database itself using insert-into-select.

Initializes an insert statement against a specific database.

Parameters:	statement_base – The first part of the insert statement. table – The table to insert into. columns – The columns that data is being inserted in. db – The database to perform the insert on.

execute(data, conn=None)¶

Insert rows from data into the database.

The “data” provided can either be a query or actual data.

For a query, this will execute an insert-into-select statement in the database.

For in-memory data in python, this translated to a cursor.executemany call. The data should be a list of suitable objects, which at this time is limited to lists or tuples.

Parameters:	data – The query or rows to insert. conn – Optional connection to use to execute this statement. An insert will get and put back a connection if this isn’t provided.

show()¶: Prints the constructed SQL with placeholders for bound parameters.

class breezeblocks.sql.dml.Update(statement, params, db=None)¶

Bases: breezeblocks.sql.statement.Statement

Represents a database update.

Initializes an update statement against a specific database.

Parameters:	statement – The SQL statement for the update. params – A list of literal values to pass into the statement. db – The database to perform the update on.

execute(conn=None)¶

Executes the prepared statement in the database.

Parameters:	conn – Optional connection to use to execute this statement. If this is not provided one will be borrowed from the database’s pool.

set_param(param_key, value)¶

Sets a bound parameter for the statement.

Parameters:	param_key – The identifier of the parameter to set. value – The value to assign to the parameter.

show()¶: Prints the constructed SQL with placeholders for bound parameters.

breezeblocks.sql.expressions module¶

Defines the building blocks for SQL expressions.

These classes are meant to be extended for concrete expression classes and provide most necessary functionality.

Also includes implementation of several SQL operators which are used in python operators on expression classes.

class breezeblocks.sql.expressions.ConstantExpr(value=None, *, param_name=None)¶

Bases: breezeblocks.sql.expressions.Value

Sets value equal to the provided value.

class breezeblocks.sql.expressions.Div_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL / operator.

class breezeblocks.sql.expressions.Equal_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL = operator.

class breezeblocks.sql.expressions.Exp_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL ^ operator.

class breezeblocks.sql.expressions.GreaterThanEqual_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL >= operator.

class breezeblocks.sql.expressions.GreaterThan_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL > operator.

class breezeblocks.sql.expressions.LessThanEqual_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL <= operator.

class breezeblocks.sql.expressions.LessThan_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL < operator.

class breezeblocks.sql.expressions.Minus_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL - operator.

class breezeblocks.sql.expressions.Mod_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL % operator.

class breezeblocks.sql.expressions.Mult_(*operands)¶

Bases: breezeblocks.sql.expressions._ChainableOperator

SQL * operator.

class breezeblocks.sql.expressions.NotEqual_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL != or <> operator.

class breezeblocks.sql.expressions.Plus_(*operands)¶

Bases: breezeblocks.sql.expressions._ChainableOperator

SQL + operator.

class breezeblocks.sql.expressions.UnaryMinus_(operand)¶

Bases: breezeblocks.sql.expressions._UnaryOperator

SQL Unary - operator

class breezeblocks.sql.expressions.UnaryPlus_(operand)¶

Bases: breezeblocks.sql.expressions._UnaryOperator

SQL Unary + operator

class breezeblocks.sql.expressions.Value(value=None, *, param_name=None)¶

Bases: breezeblocks.sql.expressions.ValueExpr

A constant value or literal for safe use in a query.

This is meant to be used as a bound parameter for SQL statements constructed through BreezeBlocks.

Sets value equal to the provided value.

get_value()¶

Getter for the underlying value of this parameter.

Returns:	The value of this parameter.

set_value(value)¶

Setter for the underlying value of this parameter.

Parameters:	value – The value to assign to this parameter.

class breezeblocks.sql.expressions.ValueExpr¶

Bases: breezeblocks.sql.query_components.Referenceable, breezeblocks.sql.query_components.Selectable

An expression that can be used by a BreezeBlocks Query.

Using this as a base class for query-bound expressions will allow them to use python operators to generate BreezeBlocks SQL operators.

Several Built-in methods on this class do not return the Python-intuitive value, but an operation from operators that can be used in query-building. For instance, the Python + operator applied to two of these results in a Plus_ instance for the left and right operands and the operator will be applied in SQL for whatever meaning it has.

as_(alias)¶

Returns:	An aliased version of this expression.

breezeblocks.sql.join module¶

class breezeblocks.sql.join.CrossJoin(left, right)¶

Bases: breezeblocks.sql.join._Join

Represents a cross join of two table expressions.

Creates a join for the left and right expressions.

class breezeblocks.sql.join.FullJoin(left, right, *, on=None, using=None)¶

Bases: breezeblocks.sql.join._QualifiedJoin

Represents a full outer join of two table expressions.

class breezeblocks.sql.join.InnerJoin(left, right, *, on=None, using=None)¶

Bases: breezeblocks.sql.join._QualifiedJoin

Represents an inner join of two table expressions.

class breezeblocks.sql.join.LeftJoin(left, right, *, on=None, using=None)¶

Bases: breezeblocks.sql.join._QualifiedJoin

Represents a left outer join of two table expressions.

class breezeblocks.sql.join.RightJoin(left, right, *, on=None, using=None)¶

Bases: breezeblocks.sql.join._QualifiedJoin

Represents a right outer join of two table expressions.

breezeblocks.sql.operators module¶

Provides access to class representations of SQL operators.

Many of these are actually implemented in breezeblocks.sql.expressions because their functionality is used in certain Python operators on expressions. They are imported here for convenience anyway.

class breezeblocks.sql.operators.And_(*operands)¶

Bases: breezeblocks.sql.expressions._ChainableOperator

SQL AND operator.

class breezeblocks.sql.operators.Between_(comp_expr, low, high)¶

Bases: breezeblocks.sql.expressions._Operator

SQL BETWEEN operator.

This special operator takes exactly three arguments.

class breezeblocks.sql.operators.In_(l_expr, r_query)¶

Bases: breezeblocks.sql.operators._SubqueryOperator

SQL IN operator.

class breezeblocks.sql.operators.IsNull_(operand)¶

Bases: breezeblocks.sql.expressions._UnaryOperator

SQL IS NULL operator.

class breezeblocks.sql.operators.Is_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL IS operator.

class breezeblocks.sql.operators.Like_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL LIKE operator.

Performs a string comparison with support for two wildcards.

class breezeblocks.sql.operators.NotNull_(operand)¶

Bases: breezeblocks.sql.expressions._UnaryOperator

SQL IS NOT NULL operator.

class breezeblocks.sql.operators.Not_(operand)¶

Bases: breezeblocks.sql.expressions._UnaryOperator

SQL NOT operator.

class breezeblocks.sql.operators.Or_(*operands)¶

Bases: breezeblocks.sql.expressions._ChainableOperator

SQL OR operator.

class breezeblocks.sql.operators.SimilarTo_(lhs, rhs)¶

Bases: breezeblocks.sql.expressions._BinaryOperator

SQL SIMILAR TO operator.

Performs a string comparison with a string in a regex-like syntax as the second argument.

breezeblocks.sql.param_store module¶

class breezeblocks.sql.param_store.FormatParamStore¶

Bases: breezeblocks.sql.param_store.OrderedParamStore

get_param_marker(value)¶

class breezeblocks.sql.param_store.MappedParamStore¶

Bases: breezeblocks.sql.param_store.ParamStore

add_param(param)¶

get_dbapi_params()¶

class breezeblocks.sql.param_store.NamedParamStore¶

Bases: breezeblocks.sql.param_store.MappedParamStore

get_param_marker(value)¶

class breezeblocks.sql.param_store.NumberedParamStore¶

Bases: breezeblocks.sql.param_store.ParamStore

add_param(param)¶

get_dbapi_params()¶

class breezeblocks.sql.param_store.NumericParamStore¶

Bases: breezeblocks.sql.param_store.NumberedParamStore

get_param_marker(value)¶

class breezeblocks.sql.param_store.OrderedParamStore¶

Bases: breezeblocks.sql.param_store.ParamStore

get_dbapi_params()¶

class breezeblocks.sql.param_store.ParamStore¶

Bases: object

A storage mechanism for query parameters.

Using subclasses of this, query parameters can be stored, retrieved, and re-assigned allowing for re-use of a fully-built SQL statement by changing the parameter values.

add_param(param)¶

add_params(params)¶

get_all_params()¶

get_dbapi_params()¶

get_param_marker(value)¶

get_param_value(key)¶

set_param_value(key, value)¶

class breezeblocks.sql.param_store.PyformatParamStore¶

Bases: breezeblocks.sql.param_store.MappedParamStore

get_param_marker(value)¶

class breezeblocks.sql.param_store.QmarkParamStore¶

Bases: breezeblocks.sql.param_store.OrderedParamStore

get_param_marker(value)¶

breezeblocks.sql.param_store.get_param_store(paramstyle)¶

breezeblocks.sql.query module¶

class breezeblocks.sql.query.AliasedQuery(query, alias)¶

Bases: breezeblocks.sql.table.AliasedTableExpression

A finalized query that has been given an alias.

This class is only for use as a table expression in other queries.

class breezeblocks.sql.query.Query(spec, statement, params, db=None)¶

Bases: breezeblocks.sql.statement.Statement, breezeblocks.sql.query_components.TableExpression

Represents a database query.

This can be executed to fetch rows from the corresponding database, or it can be used as a table expression for other queries.

Parameters:	db – The database to perform the query on. spec – The spec for the expressions used to build this query. statement – The generated SQL this query represents. params – The parameters to pass to the cursor along with the SQL.

as_(alias)¶

Creates an aliased version of this query for use in other queries.

Returns:	An `AliasedQuery` corresponding to this query.

columns¶: A ColumnCollection instance containing all columns in this table.

execute(limit=None, offset=None, conn=None)¶

Fetch rows in this query from the database.

Parameters:	limit – LIMIT argument for this execution. offset – OFFSET argument for this execution. conn – Optional connection to use to execute this query. A query will get and put back a connection if this isn’t provided.
Returns:	The rows returned by the query.

get_column(key)¶

Gets a specific column in the table.

Parameters:	name – The name of the column to get.
Returns:	The corresponding `ColumnExpr`

get_name()¶

Provides a way for a user to get a name to identify the table with.

Returns:	A user-facing name for the table

set_param(param_key, value)¶

Sets a bound parameter for the query.

Parameters:	param_key – The identifier of the parameter to set. value – The value to assign to the parameter.

show()¶: Prints the constructed SQL with placeholders for bound parameters.

breezeblocks.sql.query_components module¶

Collections of classes representing specific parts of a SQL Query.

The classes defined here do not provide functionality, but are used for typechecking in the Query class.

The methods they present are the methods required for them to be used in the correct parts of the query, but raise runtime errors if not overridden. In this way these classes also serve as a reference.

class breezeblocks.sql.query_components.Referenceable¶

Bases: object

An object that can be used as a reference field in a SQL query.

class breezeblocks.sql.query_components.Selectable¶

Bases: object

An object that can be used as a select field in a SQL query.

class breezeblocks.sql.query_components.TableExpression¶

Bases: object

Any object that can be used as a from field in a SQL query.

get_name()¶

Provides a way for a user to get a name to identify the table with.

Returns:	A user-facing name for the table

breezeblocks.sql.statement module¶

class breezeblocks.sql.statement.Statement(db=None)¶

Bases: object

Represents a SQL statement which has been built.

Methods on child classes have at least the parameters listed here so specifying any of them by name will work, but child classes may add more parameters to support statement-specific functionality.

Parameters:	db – The database to execute this statement against.

execute(conn=None)¶

Executes the prepared statement in the database.

Parameters:	conn – Optional connection to use to execute this statement. If this is not provided one will be borrowed from the database’s pool.

set_param(param_key, value)¶

Sets a bound parameter for the statement.

Parameters:	param_key – The identifier of the parameter to set. value – The value to assign to the parameter.

show()¶: Prints the constructed SQL with placeholders for bound parameters.

breezeblocks.sql.table module¶

class breezeblocks.sql.table.AliasedTableExpression(table_expr, alias)¶

Bases: breezeblocks.sql.query_components.TableExpression

A table expression that has been given an alias for use in queries.

Initializes an aliased table from a table and an alias.

columns¶: A ColumnCollection instance containing all columns in this table.

get_column(key)¶

Gets a specific column in the table.

Parameters:	name – The name of the column to get.
Returns:	The corresponding `ColumnExpr`

get_name()¶

Gets the name of this table as used in the queries.

Returns:	The alias of this table.

class breezeblocks.sql.table.Table(table_name, column_names, schema=None)¶

Bases: breezeblocks.sql.query_components.TableExpression

Represents a database table.

Parameters:	table_name – The name of the table in the database. column_names – A list of the names of columns in the table. schema – The name of the schema the table is in. Optional.

as_(alias)¶

Creates an aliased version of this table for use in queries.

Returns:	An `AliasedTableExpression` corresponding to this table.

columns¶: A ColumnCollection instance containing all columns in this table.

get_column(name)¶

Gets a specific column in the table.

Parameters:	name – The name of the column to get.
Returns:	The corresponding `ColumnExpr`

get_name()¶

Gets the name of this table as used in the database.

Returns:	The full name of this table.

Module contents¶

BreezeBlocks SQL sub-package.

Contains the expression that allow users to execute queries. This includes representations of fundamental parts of the schema such as tables and columns, as well as the expressions allowed in queries.

breezeblocks.sql package¶

Submodules¶

breezeblocks.sql.aggregates module¶

breezeblocks.sql.column module¶

breezeblocks.sql.column_collection module¶

breezeblocks.sql.dml module¶

breezeblocks.sql.expressions module¶

breezeblocks.sql.join module¶

breezeblocks.sql.operators module¶

breezeblocks.sql.param_store module¶

breezeblocks.sql.query module¶

breezeblocks.sql.query_components module¶

breezeblocks.sql.statement module¶

breezeblocks.sql.table module¶

Module contents¶

Table Of Contents

Previous topic

This Page