Running Standalone SQLAlchemy Scripts in Pyramid

From time to time there comes the need to run automated scripts managed by either Python or Cron. In a recent project I had to run a standalone script that checks records on a database at certain hours. So here’s what I came up with. Most of the script is based on this post by Daniel Mayer.

from paste.deploy import appconfig
 
from sqlalchemy import engine_from_config
 
from sqlalchemy.orm.exc import NoResultFound, MultipleResultsFound
 
#here is where sqlalchemy objects are imported
from PRJ.models import DBSession as db,DBRecords
 
#import the session manager. This way commits will be handled automatically by the Zope Transaction Manager
import transaction
 
# Load Application Configuration and Return as Dictionary
conf = appconfig('config:' + 'development.ini', 
        relative_to="/var/www/PRJ",
        name="main")
 
# Bind Engine Based on Config
engine = engine_from_config(conf, 'sqlalchemy.')
db.configure(bind=engine)
 
#Query the DB
data = db.query(DBRecords).one()
 
with transaction.manager:
  for record in data:
    #query or update DB
 

repoze.what with reflected tables using SQLAlchemy in Pylons

One of the many things I like about SQLAlchemy is the feature of reflected tables, this means I can develop dashboards for existing applications with my current favorite framework(Pylons). So I had to create an authorization mechanism for my dashboard and this recipe from the Pylons cookbook was just what I needed.

The only thing I had to change was the way my tables were being mapped since the users table schema was already defined. This is what I did:

user_table = schema.Table('users', Base.metadata, autoload=True, autoload_with=engine)
 
user_group_table = schema.Table('user_groups',Base.metadata,autoload=True,autoload_with=engine)
 
group_permission_table = schema.Table('group_permission',Base.metadata,autoload=True,autoload_with=engine)
 
permission_table = schema.Table('permissions', Base.metadata,autoload=True,autoload_with=engine)
 
group_table = schema.Table('groups', Base.metadata,autoload=True,autoload_with=engine)
 
orm.mapper(User,user_table, properties={
      'groups':orm.relation(Group, secondary=user_group_table),
    })
 
orm.mapper(Permission,permission_table,properties={
      'groups':orm.relation(Group, secondary=group_permission_table),
    })
 
orm.mapper(Group,group_table,properties={
      'permissions':orm.relation(Permission, secondary=group_permission_table),
      'users':orm.relation(User, secondary=user_group_table),
    })
 
orm.mapper(UGT,user_group_table)
 
orm.mapper(GPT,group_permission_table)

And here’s the schema I had to create, except for the users table:

CREATE TABLE user_groups( 
id serial NOT NULL,  
user_id INTEGER,  
group_id INTEGER,  
CONSTRAINT user_groups_pkey PRIMARY KEY (id), 
CONSTRAINT gid_fkey FOREIGN KEY (group_id) REFERENCES groups (id)
CONSTRAINT uid_fkey FOREIGN KEY (user_id)   REFERENCES users (id) 
)
CREATE TABLE group_permission(
id serial NOT NULL,
gid INTEGER,
permid INTEGER,
CONSTRAINT group_permission_pkey PRIMARY KEY (id),
CONSTRAINT groupid_fkey FOREIGN KEY (gid) REFERENCES groups (id),
CONSTRAINT permid_fkey FOREIGN KEY (permid) REFERENCES permissions (id) 
)
CREATE TABLE permissions(
  id serial NOT NULL,
  pname CHARACTER VARYING(50),
  CONSTRAINT permissions_pkey PRIMARY KEY (id)
)
CREATE TABLE groups(
  id serial NOT NULL,
  gname CHARACTER VARYING(50),
  CONSTRAINT groups_pkey PRIMARY KEY (id)
)
CREATE TABLE users(
id serial NOT NULL,
username CHARACTER VARYING(50),
email CHARACTER VARYING(50),
upassword CHARACTER VARYING(150),
CONSTRAINT users_pkey PRIMARY KEY (id)
)

And that’s all there’s to it. I was very surprise on how flexible SQLAlchemy is.