Welcome to SQLTables’s documentation!

Motivation

Relational data schemas are a proven way of organizing data, that fit the majority of query and processing use cases.

SQL is a powerful query language, that generally maintains high readability and is familiar to most people working with data.

SQLite is a powerful relational database, part of the Python standard library, that supports both in-memory and out-of-memory processing.

The goal of this module is to provide a high performance relational data structure that integrates seamlessly with Python’s features for structuring programs, such as control flow constructs, functions or classes.

Source: https://github.com/bobpepin/sqltables

Main Concepts and Example

The main objects are tables, represented by the Table class and associated with a Database. New tables are created with the load_values method on the Database object. Tables are queried with the view and table methods, which execute an SQL query and return a new Table object backed by a temporary view or table. Within SQL queries, the special name _ refers to the table associated with self.

See the examples/ folder in the source repository for usage examples.

API Reference

class sqltables.generic.Database(conn)

Connection to a Generic SQL database.

Parameters:

conn – DB-API 2.0 connection object

create_table(rows=None, column_names=[], column_types={}, name=None)

Create a new table.

Parameters:

  • values (iterable(sequence)) – The values to insert into the new table, as an iterable of rows.

  • column_names (list(str)) – The column names of the new table.

  • column_types (dict(str, str)) – Data types for the columns. Note that SQLite mostly uses column types for documentation purposes.

  • name (str) – The name of the table inside the database. The default value None causes a name to be automatically generated.

Returns:

A new Table object that can be used to manipulate the created table.

Return type:

Table

drop_table(table_name)

Drop a table from the database.

Parameters:

table_name – The name of the table to drop.

query(select_stmt, kind='view', parameters=None, bindings={})

Execute an SQL select statement.

Parameters:

  • select_stmt (str) – The SQL select statement to execute. Does not support “with” clauses.

  • kind (str) – The underlying temporary object to create. Either “view” or “table”.

  • parameters (list or dict) – Query parameters for the SQL statement. Only supported if kind is “table”

  • bindings (dict(str, Table)) – For each key name, make the table available within the query as name.

Returns:

A Table object that represents the result of the query

Return type:

Table

class sqltables.sqlite.Database(name='')

Connection to a SQLite database.

Parameters:

name – Name of the database, passed to sqlite3.connect(). The default value “” creates a new in-memory database.

create_function(name, nargs, fn)

Register a SQLite user-defined function

Parameters:

  • name (str) – name of the function in SQLite

  • nargs (int) – number of arguments

  • fn (callable) – Python function object

drop_table(table_name)

Drop a table from the database. To work around locking issues, the table is first renamed and then dropped once all active iterators on the connection have ended.

Parameters:

table_name – The name of the table to drop.

class sqltables.sqltables.Table(name, db)

Represents a table or view. Returned by Database.query(), Table.view() or Table.table(). Not to be instantiated directly.

__iter__()

Iterate over the rows from this table.

Returns:

An iterator over the rows in this table.

Return type:

RowIterator

table(select_stmt=None, parameters=None, *, bindings={})

Create a new view by running a SQL select statement.

Parameters:

  • select_stmt (str) – SQL select statement. The special table name _ (underscore) represents the table associated with self. If None, defaults to select * from _.

  • parameters (list or dict) – Values for SQL query parameters

  • bindings (dict(str, Table)) – Additional tables to be made accessible in the SQL statement.

Returns:

A new table object representing the result of the query.

Return type:

Table

view(select_stmt, *, bindings={})

Create a new view by running a SQL select statement.

Parameters:

  • select_stmt (str) – SQL select statement. The special table name _ (underscore) represents the table associated with self.

  • bindings (dict(str, Table)) – Additional tables to be made accessible in the SQL statement.

Returns:

A new table object representing the result of the query.

Return type:

Table

class sqltables.sqltables.RowIterator(statement, table)

An iterator over the rows in a view or table. Never instantiate this directly, created by iterating over a Table object.

column_names

The names of the columns in the table or view.

Type:

list(str)

Row

The collections.namedtuple class used for representing the rows.

Type:

class

Indices and tables