import sqlalchemy.mods.threadlocal
from sqlalchemy import *
sqlite = 'sqlite:///:memory:'
db = create_engine(sqlite,
strategy='plain',
echo=True
)
metadata = BoundMetaData(db)
################################################################################
# TABLE DEFS
################################################################################
users = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String(64)),
Column('last_name', String(64)),
Column('email', String(128)),
Column('password', String(16)),
Column('is_active', Boolean)
)
roles = Table('role', metadata,
Column('id', Integer, primary_key=True),
Column('description', String(32))
)
user_roles = Table('user_role', metadata,
Column('user_id', Integer, ForeignKey('user.id'), primary_key=True),
Column('role_id', Integer, ForeignKey(' role.id'), primary_key=True)
)
admins = Table('admin', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey('user.id'))
)
################################################################################
# CLASS DEFS
################################################################################
class User(object):
def __init__(self, fname=None, lname=None, email=None, is_active=1):
self.first_name = fname
self.last_name = lname
self.email = email
self.is_active = is_active
def has_role(self, role_name):
if role_name in [role.description for role in self.roles]:
return True
else:
return False
class Role(object):
def __init__(self, description=None):
self.description = description
class UserRole(object): pass
class Admin(User):
def __init__(self, fname=None, lname=None, email=None, is_active=1):
User.__init__(self, fname, lname, email, is_active)
# give user 'admin' role
self.roles.append(Query(Role).get_by(description='admin'))
################################################################################
# MAPPER DEFS
################################################################################
role_mapper = mapper(Role, roles)
user_mapper = mapper(User, users, properties = {
'roles' : relation(Role, secondary=user_roles, lazy=False, private=False)
}
)
admin_mapper = mapper(Admin, admins, inherits=user_mapper)
################################################################################
# MAIN
################################################################################
if __name__ == "__main__":
# create tables
users.create()
roles.create()
user_roles.create()
admins.create()
# create roles
adminrole = Role('admin')
clientrole = Role('client')
adjusterrole = Role('adjuster')
objectstore.flush()
# create admin user
a = Admin('Tim',
'Van Steenburgh',
' [EMAIL PROTECTED]'
)
a.password = 'admin'
objectstore.flush()
[2006-06-22 15:46:05,573] [engine]: None
[2006-06-22 15:46:05,573] [engine]: COMMIT
[2006-06-22 15:46:05,577] [engine]: BEGIN
[2006-06-22 15:46:05,578] [engine]: INSERT INTO role (description) VALUES (?)
[2006-06-22 15:46:05,578] [engine]: ['admin']
[2006-06-22 15:46:05,579] [engine]: INSERT INTO role (description) VALUES (?)
[2006-06-22 15:46:05,579] [engine]: ['client']
[2006-06-22 15:46:05,579] [engine]: INSERT INTO role (description) VALUES (?)
[2006-06-22 15:46:05,579] [engine]: ['adjuster']
[2006-06-22 15:46:05,580] [engine]: COMMIT
[2006-06-22 15:46:05,583] [engine]: SELECT role.id AS role_id, role.description AS role_description
FROM role
WHERE role.description = ? ORDER BY role.oid
LIMIT 1 OFFSET 0
[2006-06-22 15:46:05,583] [engine]: ['admin']
[2006-06-22 15:46:05,585] [engine]: BEGIN
[2006-06-22 15:46:05,586] [engine]: INSERT INTO user (first_name, last_name, email, password, is_active) VALUES (?, ?, ?, ?, ?)
[2006-06-22 15:46:05,587] [engine]: ['Tim', 'Van Steenburgh', ' [EMAIL PROTECTED]', 'admin', 1]
[2006-06-22 15:46:05,588] [engine]: INSERT INTO admin (id, user_id) VALUES (?, ?)
[2006-06-22 15:46:05,588] [engine]: [1, 1]
[2006-06-22 15:46:05,589] [engine]: INSERT INTO user_role (user_id, role_id) VALUES (?, ?)
[2006-06-22 15:46:05,589] [engine]: [1, 1]
[2006-06-22 15:46:05,590] [engine]: INSERT INTO user_role (user_id, role_id) VALUES (?, ?)
[2006-06-22 15:46:05,590] [engine]: [1, 1]
[2006-06-22 15:46:05,590] [engine]: ROLLBACK
Traceback (most recent call last):
File "model_test.py", line 93, in ?
objectstore.flush()
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 233, in flush
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 228, in flush
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 396, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 669, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 634, in _execute_childtasks
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 669, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 634, in _execute_childtasks
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 667, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 626, in _execute_dependencies
File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 525, in execute
File "build/bdist.linux-i686/egg/sqlalchemy/orm/dependency.py", line 288, in process_dependencies
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy-0.2.3-py2.4.egg/sqlalchemy/engine/base.py", line 240, in execute
return Connection.executors[type(object).__mro__[-2]](self, object, *multiparams, **params)
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy-0.2.3-py2.4.egg/sqlalchemy/engine/base.py", line 264, in execute_clauseelement
return self.execute_compiled(elem.compile(engine=self.__engine, parameters=param), *multiparams, **params)
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy-0.2.3-py2.4.egg/sqlalchemy/engine/base.py", line 280, in execute_compiled
proxy(str(compiled), parameters)
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy- 0.2.3-py2.4.egg/sqlalchemy/engine/base.py", line 276, in proxy
self._execute_raw(statement, parameters, cursor=cursor, context=context)
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy-0.2.3-py2.4.egg /sqlalchemy/engine/base.py", line 315, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File "/home2/hbcadjusting/lib/python2.4/SQLAlchemy-0.2.3-py2.4.egg/sqlalchemy/engine/base.py", line 334, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (IntegrityError) columns user_id, role_id are not unique 'INSERT INTO user_role (user_id, role_id) VALUES (?, ?)' [1, 1]
Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
