Working with SQLAlchemy

Note

This extension is currently BETA.

Things will change, and although we use it on some real-world software, it may, or may not, satisfy your needs.

Read the introduction: https://www.bonobo-project.org/with/sqlalchemy

Installation

To install the extension, use the sqlalchemy extra:

$ pip install bonobo[sqlalchemy]

Note

You can install more than one extra at a time separating the names with commas.

Overview and examples

First, you’ll need a database connection (sqlalchemy.engine.Engine instance), that must be provided as a service.

import sqlalchemy

def get_services():
    return {
        'sqlalchemy.engine': sqlalchemy.create_engine(...)
    }

The sqlalchemy.engine name is the default name used by the provided transformations, but you can override it (for example if you need more than one connection) and specify the service name using engine=’myengine’ while building your transformations.

Lets create some tables and add some data. (You may need to edit the SQL if your database server uses a different version of SQL.)

CREATE TABLE test_in (
  id INTEGER PRIMARY KEY NOT NULL,
  text TEXT
);

CREATE TABLE test_out (
  id INTEGER PRIMARY KEY NOT NULL,
  text TEXT
);

INSERT INTO test_in (id, text) VALUES (1, 'Cat');
INSERT INTO test_in (id, text) VALUES (2, 'Dog');

There are two transformation classes provided by this extension.

One reader, one writer.

Let’s select some data:

import bonobo
import bonobo_sqlalchemy

def get_graph():
    graph = bonobo.Graph()
    graph.add_chain(
        bonobo_sqlalchemy.Select('SELECT * FROM test_in', limit=100),
        bonobo.PrettyPrinter(),
    )
    return graph

You should see:

$ python tutorial.py

│ id[0] = 1
│ text[1] = 'Cat'


│ id[0] = 2
│ text[1] = 'Dog'

 - Select in=1 out=2 [done]
 - PrettyPrinter in=2 out=2 [done]

Now let’s insert some data:

import bonobo
import bonobo_sqlalchemy


def get_graph(**options):
    graph = bonobo.Graph()
    graph.add_chain(
        bonobo_sqlalchemy.Select('SELECT * FROM test_in', limit=100),
        bonobo_sqlalchemy.InsertOrUpdate('test_out')
    )

    return graph

If you check the test_out table, it should now have the data.