[Sqlalchemy-commits] [1272] sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py: latest from j.
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1272] sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py: latest from j.
|
From: <co...@sq...> - 2006-04-10 13:08:55
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1272] sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py: latest from j.</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1272</dd> <dt>Author</dt> <dd>zzzeek</dd> <dt>Date</dt> <dd>2006-04-10 00:44:03 -0500 (Mon, 10 Apr 2006)</dd> </dl> <h3>Log Message</h3> <pre>latest from j. ellis...</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemytrunklibsqlalchemyextsqlsouppy">sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemytrunklibsqlalchemyextsqlsouppy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py (1271 => 1272)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py 2006-04-10 00:58:15 UTC (rev 1271) +++ sqlalchemy/trunk/lib/sqlalchemy/ext/sqlsoup.py 2006-04-10 05:44:03 UTC (rev 1272) </span><span class="lines">@@ -1,72 +1,182 @@ </span><del>-from sqlalchemy import * - -class NoSuchTableError(SQLAlchemyError): pass - -# metaclass is necessary to expose class methods with getattr, e.g. -# we want to pass db.users.select through to users._mapper.select -class TableClassType(type): - def insert(cls, **kwargs): - o = cls() - o.__dict__.update(kwargs) - return o - def __getattr__(cls, attr): - if attr == '_mapper': - # called during mapper init - raise AttributeError() - return getattr(cls._mapper, attr) - -def class_for_table(table): - klass = TableClassType('Class_' + table.name.capitalize(), (object,), {}) - def __repr__(self): - import locale - encoding = locale.getdefaultlocale()[1] - L = [] - for k in self.__class__.c.keys(): - value = getattr(self, k, '') - if isinstance(value, unicode): - value = value.encode(encoding) - L.append("%s=%r" % (k, value)) - return '%s(%s)' % (self.__class__.__name__, ','.join(L)) - klass.__repr__ = __repr__ - klass._mapper = mapper(klass, table) - return klass - -class SqlSoup: - def __init__(self, *args, **kwargs): - """ - args may either be an SQLEngine or a set of arguments suitable - for passing to create_engine - """ - from sqlalchemy.engine import SQLEngine - # meh, sometimes having method overloading instead of kwargs would be easier - if isinstance(args[0], SQLEngine): - engine = args.pop(0) - if args or kwargs: - raise ArgumentError('Extra arguments not allowed when engine is given') - else: - engine = create_engine(*args, **kwargs) - self._engine = engine - self._cache = {} - def delete(self, *args, **kwargs): - objectstore.delete(*args, **kwargs) - def commit(self): - objectstore.get_session().commit() - def rollback(self): - objectstore.clear() - def _reset(self): - # for debugging - self._cache = {} - self.rollback() - def __getattr__(self, attr): - try: - t = self._cache[attr] - except KeyError: - table = Table(attr, self._engine, autoload=True) - if table.columns: - t = class_for_table(table) - else: - t = None - self._cache[attr] = t - if not t: - raise NoSuchTableError('%s does not exist' % attr) - return t </del><ins>+from sqlalchemy import * + +""" +SqlSoup provides a convenient way to access database tables without having +to declare table or mapper classes ahead of time. + +Suppose we have a database with users, books, and loans tables +(corresponding to the PyWebOff dataset, if you're curious). +For testing purposes, we can create this db as follows: + +>>> from sqlalchemy import create_engine +>>> e = create_engine('sqlite://filename=:memory:') +>>> for sql in _testsql: e.execute(sql) +... + +Creating a SqlSoup gateway is just like creating an SqlAlchemy engine: +>>> from sqlalchemy.ext.sqlsoup import SqlSoup +>>> soup = SqlSoup('sqlite://filename=:memory:') + +or, you can re-use an existing engine: +>>> soup = SqlSoup(e) + +Loading objects is as easy as this: +>>> users = soup.users.select() +>>> users.sort() +>>> users +[Class_Users(name='Bhargan Basepair',email='bas...@ex...',password='basepair',classname=None,admin=1), Class_Users(name='Joe Student',email='st...@ex...',password='student',classname=None,admin=0)] + +Of course, letting the database do the sort is better (".c" is short for ".columns"): +>>> soup.users.select(order_by=[soup.users.c.name]) +[Class_Users(name='Bhargan Basepair',email='bas...@ex...',password='basepair',classname=None,admin=1), + Class_Users(name='Joe Student',email='st...@ex...',password='student',classname=None,admin=0)] + +Field access is intuitive: +>>> users[0].email +u'bas...@ex...' + +Of course, you don't want to load all users very often. The common case is to +select by a key or other field: +>>> soup.users.selectone_by(name='Bhargan Basepair') +Class_Users(name='Bhargan Basepair',email='bas...@ex...',password='basepair',classname=None,admin=1) + +All the SqlAlchemy mapper select variants (select, select_by, selectone, selectone_by, selectfirst, selectfirst_by) +are available. See the SqlAlchemy documentation for details: +http://www.sqlalchemy.org/docs/sqlconstruction.myt + +Modifying objects is intuitive: +>>> user = _ +>>> user.email = 'bas...@ex...' +>>> soup.commit() + +(SqlSoup leverages the sophisticated SqlAlchemy unit-of-work code, so +multiple updates to a single object will be turned into a single UPDATE +statement when you commit.) + +Finally, insert and delete. Let's insert a new loan, then delete it: +>>> soup.loans.insert(book_id=soup.books.selectfirst().id, user_name=user.name) +Class_Loans(book_id=1,user_name='Bhargan Basepair',loan_date=None) +>>> soup.commit() + +>>> loan = soup.loans.selectone_by(book_id=1, user_name='Bhargan Basepair') +>>> soup.delete(loan) +>>> soup.commit() +""" + +_testsql = """ +CREATE TABLE books ( + id integer PRIMARY KEY, -- auto-SERIAL in sqlite + title text NOT NULL, + published_year char(4) NOT NULL, + authors text NOT NULL +); + +CREATE TABLE users ( + name varchar(32) PRIMARY KEY, + email varchar(128) NOT NULL, + password varchar(128) NOT NULL, + classname text, + admin int NOT NULL -- 0 = false +); + +CREATE TABLE loans ( + book_id int PRIMARY KEY REFERENCES books(id), + user_name varchar(32) references users(name) + ON DELETE SET NULL ON UPDATE CASCADE, + loan_date date NOT NULL DEFAULT current_timestamp +); + +insert into users(name, email, password, admin) +values('Bhargan Basepair', 'bas...@ex...', 'basepair', 1); +insert into users(name, email, password, admin) +values('Joe Student', 'st...@ex...', 'student', 0); + +insert into books(title, published_year, authors) +values('Mustards I Have Known', '1989', 'Jones'); +insert into books(title, published_year, authors) +values('Regional Variation in Moss', '1971', 'Flim and Flam'); + +insert into loans(book_id, user_name) +values ( + (select min(id) from books), + (select name from users where name like 'Joe%')) +; +""".split(';') + +__all__ = ['NoSuchTableError', 'SqlSoup'] + +class NoSuchTableError(SQLAlchemyError): pass + +# metaclass is necessary to expose class methods with getattr, e.g. +# we want to pass db.users.select through to users._mapper.select +class TableClassType(type): + def insert(cls, **kwargs): + o = cls() + o.__dict__.update(kwargs) + return o + def __getattr__(cls, attr): + if attr == '_mapper': + # called during mapper init + raise AttributeError() + return getattr(cls._mapper, attr) + +def class_for_table(table): + klass = TableClassType('Class_' + table.name.capitalize(), (object,), {}) + def __repr__(self): + import locale + encoding = locale.getdefaultlocale()[1] + L = [] + for k in self.__class__.c.keys(): + value = getattr(self, k, '') + if isinstance(value, unicode): + value = value.encode(encoding) + L.append("%s=%r" % (k, value)) + return '%s(%s)' % (self.__class__.__name__, ','.join(L)) + klass.__repr__ = __repr__ + klass._mapper = mapper(klass, table) + return klass + +class SqlSoup: + def __init__(self, *args, **kwargs): + """ + args may either be an SQLEngine or a set of arguments suitable + for passing to create_engine + """ + from sqlalchemy.engine import SQLEngine + # meh, sometimes having method overloading instead of kwargs would be easier + if isinstance(args[0], SQLEngine): + args = list(args) + engine = args.pop(0) + if args or kwargs: + raise ArgumentError('Extra arguments not allowed when engine is given') + else: + engine = create_engine(*args, **kwargs) + self._engine = engine + self._cache = {} + def delete(self, *args, **kwargs): + objectstore.delete(*args, **kwargs) + def commit(self): + objectstore.get_session().commit() + def rollback(self): + objectstore.clear() + def _reset(self): + # for debugging + self._cache = {} + self.rollback() + def __getattr__(self, attr): + try: + t = self._cache[attr] + except KeyError: + table = Table(attr, self._engine, autoload=True) + if table.columns: + t = class_for_table(table) + else: + t = None + self._cache[attr] = t + if not t: + raise NoSuchTableError('%s does not exist' % attr) + return t + +if __name__ == '__main__': + import doctest + doctest.testmod() </ins></span></pre> </div> </div> </body> </html> |
