Project Design Simpycity's core philosophy is that the DBA is going to perform the vast majority of the schema design without the aid of a conventional ORM. This is a marked divergence from most other ORMs and database abstraction layers, and it has an impact on how your project should be designed. The best results with Simpycity will be seen with a strong up-front requirements analysis, thorough schema design, and a consistent, fixed database API. To use an example from one of our in-development applications, the majority of our business logic is is stored in stored procedures, with a small number of views. The tables and internal layout is hidden from the application code, with all access being performed through stored procedure interfaces such as:

    CREATE OR REPLACE FUNCTION create_db_item (
        in_user_id
    ) RETURNS int
    AS $body$
        DECLARE
            v_user users;
            v_db_id;
        BEGIN
            SELECT * INTO v_user FROM users WHERE id = in_user_id;
            
            IF NOT FOUND THEN
                RAISE EXCEPTION 'Could not find user.';
            END IF;
            
            v_db_id = nextval('db_item_seq');
            
            INSERT INTO db_item (id, owner) VALUES (v_db_id, v_user.id);
            
            RETURN v_db_id;
        END;
    $body$ language plpgsql;

Configuration Configuration of Simpycity for use with Pylons is fairly simple, due largely to Pylons' natural decoupling of components. To start, add a few keys to your .ini file. In the default case, this is $appdir/development.ini, in the [app:main] section:

    
    [app:main]
    use = egg:helloworld
    full_stack = true

    cache_dir = %(here)s/data
    beaker.session.key = helloworld
    beaker.session.secret = somesecret
    beaker.session.type = memory

    # If you'd like to fine-tune the individual locations of the cache data dirs
    # for the Cache data, or the Session saves, un-comment the desired settings
    # here:
    #beaker.cache.data_dir = %(here)s/data/cache
    #beaker.session.data_dir = %(here)s/data/sessions

    # WARNING: *THE LINE BELOW MUST BE UNCOMMENTED ON A PRODUCTION ENVIRONMENT*
    # Debug mode will enable the interactive debugging tool, allowing ANYONE to
    # execute malicious code after an exception is raised.
    set debug = true

    db.database = helloworld
    db.user     = helloworld_user
    db.host     = localhost
    db.port     = 5432
    db.password = 12345

These are the basic keys for the DB backend, and should be changed according to your environment. Next, we'll need to configure Simpycity itself during the application startup. To do this, open $appdir/config/environment.py and add

    from simpycity import config as db_config

to the top of the file. As Simpycity uses a global config module by default, this will give you allow setup before any Simpycity code gets executed. Next, still in environment.py, underneath of

    # CONFIGURATION OPTIONS HERE (note: all config options will override
    # any Pylons config options)

add

    app_conf = config['app_conf']

    db_config.port = app_conf['db.port']
    db_config.database= app_conf['db.database']
    db_config.host= app_conf['db.host']
    db_config.user = app_conf['db.user']
    db_config.password = app_conf['db.password']
    db_config.debug = False

Thus configuring Simpycity. Due to the nature of Simpycity, these configuration options will be accessible by any Simpycity object created by your application. The .ini keys should also match the name of variables that Simpycity uses, for the sake of clarity. Models As Pylons has no tight integration with any ORM, the model "system" is easy to use with Simpycity. For a smaller app, the best practise is to create our models in the model/__init__.py, such as

    from simpycity.core import Function
    from simpycity.model import SimpleModel

    from Update import UpdateModel

    class Hello(SimpleModel):

        f = Function("hello")
        
    class Item(SimpleModel):
        create_item = Function("create_db_item",[''])

where "hello" is a PostgreSQL stored procedure of

    
    CREATE OR REPLACE FUNCTION hello () RETURNS setof hello_test AS $body$
        SELECT * FROM hello_test;
    $body$ language SQL;

Allowing you to do, in your controller:

    from helloworld.model import Hello
    ... # rest of controller imports
    
    class HelloController(BaseController):

        def index(self):
            # Return a rendered template
            #   return render('/template.mako')
            # or, Return a response

            h = Hello()
            result = h.f()
            

For a more complex app, and more complex models, it might make sense to split model definitions into separate files, as in

    
    model/hello.py:
        from simpycity.core import Function, Raw
        from simpycity.model import Construct, SimpleModel

        class Hello(SimpleModel):

            f = Function("hello")
            

    model/create_item.py:
        from simpycity.core import Function, Raw
        from simpycity.model import Construct, SimpleModel
        
        class Item(SimpleModel):
            create_item = Function("create_db_item",['user_id'])

And then, in model/__init__.py,

    from hello import Hello
    from create_item import Item

This will allow the same controller code to perform as expected. Connection Isolation/Scope By default, Simpycity queries all run in an implicit transaction with the normal isolation level as set by psycopg2. As a result, anything you do in the database won't show up to two different connections, and anything you do MUST be explicitly committed. While this behaviour can be overridden, the safest way to perform all DB operations is

    
    from helloworld import Hello, Item
    ... # remainder of controller imports
    
    class HelloController(BaseController):

        def index(self):
            # Return a rendered template
            #   return render('/template.mako')
            # or, Return a response

            h = Hello()
            i = Item()
            try:
                rs = i.create_item(request.session['user_id'])

                for row in rs:
                    # ... Check DB response.
                    all_is_okay = True
                if all_is_okay:
                    i.commit()
                    result = h.f()
                    return result.next()
                else:
                    i.rollback()
                    return redirect_to("error_page")
                

            except:
                i.rollback()
                response.status = '500 Internal Server Error'
                return redirect_to("error_page")
            

This will provide you with a known-consistent database state, regardless of a problem with your input data. Connection Pooling By default, Simpycity does not offer any connection pooling system. If your application requires connection pooling, the excellent pgbouncer package will work as a drop-in connection pooler for Simpycity.