Table#

redbird.sql.Table is further abstraction from sqlalchemy.Table. It combines the connection with the table itself thus it is always pointing to an actual SQL table (which may not yet exist). It also makes certain operations more intuitive and let you leverage Python’s native data types more than SQLAlchemy does.

Class#

class redbird.sql.Table(name, bind)#

SQL Table

Similar to sqlalchemy.Table except this class always points to a specific SQL table in a specific database (which may not yet exist) and provides further abstraction.

name#

Name of the table.

Type: str

bind#

SQLAlchemy engine or connection.

Type: sqlalchemy.engine.Connectable

Examples

from sqlalchemy import create_engine
from redbird.sql import Table

table = Table("mytable", bind=create_engine("sqlite://"))

select(qry=None, columns=None, parameters=None)#

Read the database table using a query.

Parameters

qry (str, dict, sqlalchemy.sql.ClauseElement, optional) –
Query to filter the data. The argument can take various forms:
- str: Query is considered to be raw SQL
- dict: Query is considered to be column-filter pairs. The pairs are combined using AND operator. If the filter is Operation, it is turned to corresponing SQLAlchemy expression. Else, the filter is considered to be an equal operator.
- sqlalchemy expression: The query is considered to be the where clause of the select query.
If not given, all rows are returned.
columns (list of string, optional) – List of columns to return. By default returns all columns.
parameters (dict, optional) – Parameters for the query. If the query is as raw SQL, they should be set in the query with :myparam, ie. select * from table where col = :myparam.

Returns

Found rows as dicts.

Return type

List of dicts

Examples

Select all rows:

table.select()

Select using raw SQL:

table.select("SELECT * FROM mytable")

Select using dictionary:

table.select({"column_1": "a value", "column_2": 10})

Note

The above is same as:

SELECT *
FROM mytable
WHERE column_1 = 'a value' AND column_2 = 10

Select using SQLAlchemy expressions:

from sqlalchemy import Column
table.select((Column("column_1") == "a value") & (Column("column_2") == 10))

Note

The above is same as:

SELECT *
FROM mytable
WHERE column_1 = 'a value' AND column_2 = 10

Select and return specific column(s):

table.select(columns=["column_1", "column_2"])

Note

The above is same as:

SELECT column_1, column_2
FROM mytable

Select using raw strings and SQL parameters:

table.select(
    "SELECT * FROM mytable WHERE column_1 = :myparam",
    parameters={"myparam": "a value"}
)

insert(data)#

Insert data to the database.

Parameters: data (dict, list of dicts) – Data to be inserted.

Examples

Insert a single row:

table.insert({"column_1": "a", "column_2": 1})

Insert multiple rows:

table.insert([
    {"column_1": "a", "column_2": 1},
    {"column_1": "b", "column_2": 2},
])

delete(where)#

Delete row(s) from the table.

Parameters: where (dict, sqlalchemy expression) – Where clause to delete data.
Returns: Count of rows deleted.
Return type: int

Examples

Delete using dictionary:

table.delete({"column_1": "a", "column_2": 1})

Note

The above is same as:

DELETE FROM mytable
WHERE column_1 = 'a' AND column_2 = 1

Delete using SQL expressions:

from sqlalchemy import Column
table.delete((Column("column_1") == "a") & (Column("column_2") == 1))

Note

The above is same as:

DELETE FROM mytable
WHERE column_1 = 'a' AND column_2 = 1

Delete all:

table.delete({})

update(where, values)#

Update row(s) in the table.

Parameters

where (dict, sqlalchemy expression) – Where clause to update rows.
values (dict) – Column-value pairs to update the rows matching the where clause.

Returns

Count of rows updated.

Return type

int

Examples

Update using dicts:

table.update({"column_1": "a", "column_2": 1}, {"column_3": "new value"})

Note

The above is same as:

UPDATE mytable
SET column_3='new value'
WHERE column_1 = 'a' and column_2 = 1

Update using expressions:

from sqlalchemy import Column
table.update((Column("column_1") == "a") & (Column("column_2") == 1), {"column_3": "new value"})

Note

The above is same as:

UPDATE mytable
SET column_3='new value'
WHERE column_1 = 'a' and column_2 = 1

Update all:

table.update({}, {"column_3": "new value"})

count(where=None)#

Count the number of rows.

Parameters: where (dict, sqlalchemy expression, optional) – Where clause to be satisfied for counting the rows.
Returns: Count of rows (satisfying the where clause).
Return type: int

Examples

Count based on dict:

table.count({"column_1": "a", "column_2": 1})

drop()#: Drop the table.

exists()#: Check if the table exists.

create(columns, exist_ok=False)#

Create the table

Parameters

columns (dict, list of sqlalchemy.Column, dict or string) – Columns to be created.
exist_ok (bool) – If false (default), an exception is raised.

Examples

There are various ways to call this method:

Using list of columns (all of the)

Create a table with columns column_1, column_2 and column_3 (all of them are textual columns):

table.create(["column_1", "column_2", "column_3"])

Create a table with columns column_1, column_2 and column_3 with varying data types:

import datetime
table.create({"column_1": str, "column_2": int, "column_3": datetime.datetime})

Create a table with columns column_1, column_2 and column_3 using SQLAlchemy columns:

from sqlalchemy import Column, String, Integer, DateTime
table.create([
    Column("column_1", type_=String()),
    Column("column_2", type_=Integer()),
    Column("column_3", type_=DateTime())
])

Create a table with columns column_1, column_2 and column_3 using list of dicts:

from sqlalchemy import DateTime
table.create([
    {"name": "column_1", "type_": str},
    {"name": "column_2", "type_": int},
    {"name": "column_3", "type_": DateTime()},
])

execute(*args, **kwargs)#

Execute SQL statement or raw SQL.

Parameters

*args (tuple) – Passed directly to sqlalchemy.Connection.execute.
**args (dict) – Passed directly to sqlalchemy.Connection.execute.

open_transaction()#

Open a transaction.

Examples

from redbird.sql import Table
from sqlalchemy import create_engine

table = Table("mytable", bind=create_engine(...))

# Open the transaction
transaction = table.open_transaction()

# Perform operations
transaction.insert({"col_1": "a", "col_2": "b"})
transaction.delete({"col_2": "c"})

# Commit or rollback the changes
if successful:
    transaction.commit()
else:
    transaction.rollback()

transaction()#

Open a transaction context.

If an error is raised inside the with block, the changes are rollbacked. Else they are commited.

Examples

from redbird.sql import Table
from sqlalchemy import create_engine

table = Table("mytable", bind=create_engine(...))

with table.transaction() as trans:

    # Perform operations
    trans.insert({"col_1": "a", "col_2": "b"})
    trans.delete({"col_2": "c"})

Repository patterns for Python

Table

Contents

Table#

Class#