Create a Websocket endpoint in Python with Postgres.
Real-time Notifications with Python and Postgres
SQLAlchemy
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
UUID Objects in Pylons with SQLAlchemy
Recently I had to generate and store uuid objects into a Postgres database, but to my surprise SQLAlchemy kept showing the following error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'UUID'
So the solution was that I had to change my field type from varchar to uuid on my Postgres database, import the psycopg2 extras functions and register the uuid type:
import psycopg2.extras
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import MetaData,Column, Table, ForeignKey
engine = create_engine('postgresql://user:pass@localhost/db',echo=True)
metadata = MetaData(bind=engine)
hash_key = uuid.uuid4()
psycopg2.extras.register_uuid()
conn = engine.connect()
query = conn.execute("insert into tbl (uuid_col) values(%(uuid)s",uuid=hash_key)
And voila, values were inserted correctly on my database.
Pylons and Facebook based Authorization with OAuth 2.0
Authorizing a user using Facebook OAuth 2.0 based login it’s pretty straighfoard. This is for the person who asked a question on stackoverflow.com I hope it helps.
Add the apikey, appid and secret of my Facebook app to the development.ini file
facebook.apikey = 42bc5a4051b274ede5cb73a6cd6fad56
facebook.secret = a355e66e8c0293390896a170f9d5r4c3
facebook.appid = 20894420580890
Configure my model/init.py file:
from PRJNAME.model.meta import Session, Base
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy import Table
from sqlalchemy import Text
from sqlalchemy import Unicode
from sqlalchemy import BigInteger
from sqlalchemy import TIMESTAMP
from sqlalchemy import ForeignKey,ForeignKeyConstraint
from sqlalchemy.orm import mapper
import datetime
def init_model(engine):
"""Call me before using any of the tables or classes in the model"""
Session.configure(bind=engine,autoflush=True,autocommit=False)
def now():
return datetime.datetime.now()
social_user = Table('social_user', Base.metadata,
Column('id', Integer, primary_key=True),
Column('oauth_provider', Text(), nullable=False),
Column('oauth_id', Text() , nullable=False),
Column('username', Unicode(50), nullable=False),
Column('access_token', Unicode(200)),
Column('created', TIMESTAMP(timezone=True), default=now()),
)
class Social(object):
pass
mapper(Social,social_user)
Download the Facebook Python-SDK
https://github.com/facebook/python-sdk
And save it on your project lib directory
Create a controller named account
~$ paster controller account
Add a view named login and render a login button
I’ll use https://graph.facebook.com/oauth/authorize as the url to start the authorization process
import PRJNAME.lib.facebook as facebook
import simplejson
from pylons import config
class AccountController(BaseController):
def login(self):
params = {
'client_id':config['facebook.appid'],
'scope' : 'offline_access,publish_stream,email', #permissions needed by the app
'redirect_uri' : 'http://'+request.environ['HTTP_HOST']+'/account/auth'
}
keys = params.keys()[:]
keys.sort()
q_str = unicode()
for name in keys:
q_str+= ("&%s=%s" % (name,params[name]))
c.login_url = 'https://graph.facebook.com/oauth/authorize?' + q_str
return render('/website/login.html')
login.html
Login with Facebook
Add a function on helpers.py
This function will create our access_token from our code request params
import urllib
import json
def getAccessTokenFromCode(applicationId, applicationSecret, code,redirect_uri):
q_str = unicode()
params = {
'client_id':applicationId,
'client_secret':applicationSecret,
'code':code,
'redirect_uri':'http://'+request.environ['HTTP_HOST']+redirect_uri
}
keys = params.keys()[:]
keys.sort()
for name in keys:
q_str+= ("&%s=%s" % (name, params[name]))
url = 'https://graph.facebook.com/oauth/access_token?'+ q_str
response = urllib.urlopen(url).read()
result = response.split('=')[-1]
if result:
return result
Create an auth view on account controller
I do this because in the link button there is an argument called redirect_uri that points to http://ww.example.com/account/auth.
from PRJNAME.lib.helpers import getAccessTokenFromCode
from pylons import config
def auth(self):
if request.params.has_key('code'):
access_token = getAccessTokenFromCode(
config['facebook.appid'],
config['facebook.secret'],
request.params.get('code'),
redirect_uri='/account/auth'
)
graph = facebook.GraphAPI(access_token)
#grab the user data
fbdata = graph.get_object("me")
#query the db to check if user is already authorized
q = Session.query(model.Social)
user = q.filter_by(oauth_id=fbdata['id']).first()
if user:
# you can redirect to a dashboard
return 'already in db and authorized'
else:
#insert user data to db
fb = model.Social()
fb.oauth_provider = 'Facebook'
fb.oauth_id = fbdata['id']
fb.username = fbdata['name']
fb.access_token = access_token
Session.add(fb)
Session.commit()
# redirect to dashboard page
And that’s pretty much it.
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.