root/galaxy-central/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/ext/sqlsoup.py

リビジョン 3, 19.2 KB (コミッタ: kohda, 14 年 前)

Install Unix tools  http://hannonlab.cshl.edu/galaxy_unix_tools/galaxy.html

行番号 
1"""
2Introduction
3============
4
5SqlSoup provides a convenient way to access database tables without
6having to declare table or mapper classes ahead of time.
7
8Suppose we have a database with users, books, and loans tables
9(corresponding to the PyWebOff dataset, if you're curious).  For
10testing purposes, we'll create this db as follows::
11
12    >>> from sqlalchemy import create_engine
13    >>> e = create_engine('sqlite:///:memory:')
14    >>> for sql in _testsql: e.execute(sql) #doctest: +ELLIPSIS
15    <...
16
17Creating a SqlSoup gateway is just like creating an SQLAlchemy
18engine::
19
20    >>> from sqlalchemy.ext.sqlsoup import SqlSoup
21    >>> db = SqlSoup('sqlite:///:memory:')
22
23or, you can re-use an existing metadata or engine::
24
25    >>> db = SqlSoup(MetaData(e))
26
27You can optionally specify a schema within the database for your
28SqlSoup::
29
30    # >>> db.schema = myschemaname
31
32
33Loading objects
34===============
35
36Loading objects is as easy as this::
37
38    >>> users = db.users.all()
39    >>> users.sort()
40    >>> users
41    [MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0), MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)]
42
43Of course, letting the database do the sort is better::
44
45    >>> db.users.order_by(db.users.name).all()
46    [MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1), MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0)]
47
48Field access is intuitive::
49
50    >>> users[0].email
51    u'student@example.edu'
52
53Of course, you don't want to load all users very often.  Let's add a
54WHERE clause.  Let's also switch the order_by to DESC while we're at
55it::
56
57    >>> from sqlalchemy import or_, and_, desc
58    >>> where = or_(db.users.name=='Bhargan Basepair', db.users.email=='student@example.edu')
59    >>> db.users.filter(where).order_by(desc(db.users.name)).all()
60    [MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0), MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)]
61
62You can also use .first() (to retrieve only the first object from a query) or
63.one() (like .first when you expect exactly one user -- it will raise an
64exception if more were returned)::
65
66    >>> db.users.filter(db.users.name=='Bhargan Basepair').one()
67    MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)
68
69Since name is the primary key, this is equivalent to
70
71    >>> db.users.get('Bhargan Basepair')
72    MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)
73
74This is also equivalent to
75
76    >>> db.users.filter_by(name='Bhargan Basepair').one()
77    MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',password=u'basepair',classname=None,admin=1)
78
79filter_by is like filter, but takes kwargs instead of full clause expressions.
80This makes it more concise for simple queries like this, but you can't do
81complex queries like the or\_ above or non-equality based comparisons this way.
82
83Full query documentation
84------------------------
85
86Get, filter, filter_by, order_by, limit, and the rest of the
87query methods are explained in detail in the `SQLAlchemy documentation`__.
88
89__ http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_querying
90
91
92Modifying objects
93=================
94
95Modifying objects is intuitive::
96
97    >>> user = _
98    >>> user.email = 'basepair+nospam@example.edu'
99    >>> db.flush()
100
101(SqlSoup leverages the sophisticated SQLAlchemy unit-of-work code, so
102multiple updates to a single object will be turned into a single
103``UPDATE`` statement when you flush.)
104
105To finish covering the basics, let's insert a new loan, then delete
106it::
107
108    >>> book_id = db.books.filter_by(title='Regional Variation in Moss').first().id
109    >>> db.loans.insert(book_id=book_id, user_name=user.name)
110    MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)
111    >>> db.flush()
112
113    >>> loan = db.loans.filter_by(book_id=2, user_name='Bhargan Basepair').one()
114    >>> db.delete(loan)
115    >>> db.flush()
116
117You can also delete rows that have not been loaded as objects. Let's
118do our insert/delete cycle once more, this time using the loans
119table's delete method. (For SQLAlchemy experts: note that no flush()
120call is required since this delete acts at the SQL level, not at the
121Mapper level.) The same where-clause construction rules apply here as
122to the select methods.
123
124::
125
126    >>> db.loans.insert(book_id=book_id, user_name=user.name)
127    MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)
128    >>> db.flush()
129    >>> db.loans.delete(db.loans.book_id==2)
130
131You can similarly update multiple rows at once. This will change the
132book_id to 1 in all loans whose book_id is 2::
133
134    >>> db.loans.update(db.loans.book_id==2, book_id=1)
135    >>> db.loans.filter_by(book_id=1).all()
136    [MappedLoans(book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))]
137
138
139Joins
140=====
141
142Occasionally, you will want to pull out a lot of data from related
143tables all at once.  In this situation, it is far more efficient to
144have the database perform the necessary join.  (Here we do not have *a
145lot of data* but hopefully the concept is still clear.)  SQLAlchemy is
146smart enough to recognize that loans has a foreign key to users, and
147uses that as the join condition automatically.
148
149::
150
151    >>> join1 = db.join(db.users, db.loans, isouter=True)
152    >>> join1.filter_by(name='Joe Student').all()
153    [MappedJoin(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0,book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))]
154
155If you're unfortunate enough to be using MySQL with the default MyISAM
156storage engine, you'll have to specify the join condition manually,
157since MyISAM does not store foreign keys.  Here's the same join again,
158with the join condition explicitly specified::
159
160    >>> db.join(db.users, db.loans, db.users.name==db.loans.user_name, isouter=True)
161    <class 'sqlalchemy.ext.sqlsoup.MappedJoin'>
162
163You can compose arbitrarily complex joins by combining Join objects
164with tables or other joins.  Here we combine our first join with the
165books table::
166
167    >>> join2 = db.join(join1, db.books)
168    >>> join2.all()
169    [MappedJoin(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0,book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0),id=1,title=u'Mustards I Have Known',published_year=u'1989',authors=u'Jones')]
170
171If you join tables that have an identical column name, wrap your join
172with `with_labels`, to disambiguate columns with their table name
173(.c is short for .columns)::
174
175    >>> db.with_labels(join1).c.keys()
176    [u'users_name', u'users_email', u'users_password', u'users_classname', u'users_admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date']
177
178You can also join directly to a labeled object::
179
180    >>> labeled_loans = db.with_labels(db.loans)
181    >>> db.join(db.users, labeled_loans, isouter=True).c.keys()
182    [u'name', u'email', u'password', u'classname', u'admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date']
183
184
185Relations
186=========
187
188You can define relations on SqlSoup classes:
189
190    >>> db.users.relate('loans', db.loans)
191
192These can then be used like a normal SA property:
193
194    >>> db.users.get('Joe Student').loans
195    [MappedLoans(book_id=1,user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))]
196
197    >>> db.users.filter(~db.users.loans.any()).all()
198    [MappedUsers(name=u'Bhargan Basepair',email='basepair+nospam@example.edu',password=u'basepair',classname=None,admin=1)]
199
200
201relate can take any options that the relation function accepts in normal mapper definition:
202
203    >>> del db._cache['users']
204    >>> db.users.relate('loans', db.loans, order_by=db.loans.loan_date, cascade='all, delete-orphan')
205
206
207Advanced Use
208============
209
210Accessing the Session
211---------------------
212
213SqlSoup uses a ScopedSession to provide thread-local sessions.  You
214can get a reference to the current one like this::
215
216    >>> from sqlalchemy.ext.sqlsoup import Session
217    >>> session = Session()
218
219Now you have access to all the standard session-based SA features,
220such as transactions.  (SqlSoup's ``flush()`` is normally
221transactionalized, but you can perform manual transaction management
222if you need a transaction to span multiple flushes.)
223
224
225Mapping arbitrary Selectables
226-----------------------------
227
228SqlSoup can map any SQLAlchemy ``Selectable`` with the map
229method. Let's map a ``Select`` object that uses an aggregate function;
230we'll use the SQLAlchemy ``Table`` that SqlSoup introspected as the
231basis. (Since we're not mapping to a simple table or join, we need to
232tell SQLAlchemy how to find the *primary key* which just needs to be
233unique within the select, and not necessarily correspond to a *real*
234PK in the database.)
235
236::
237
238    >>> from sqlalchemy import select, func
239    >>> b = db.books._table
240    >>> s = select([b.c.published_year, func.count('*').label('n')], from_obj=[b], group_by=[b.c.published_year])
241    >>> s = s.alias('years_with_count')
242    >>> years_with_count = db.map(s, primary_key=[s.c.published_year])
243    >>> years_with_count.filter_by(published_year='1989').all()
244    [MappedBooks(published_year=u'1989',n=1)]
245
246Obviously if we just wanted to get a list of counts associated with
247book years once, raw SQL is going to be less work. The advantage of
248mapping a Select is reusability, both standalone and in Joins. (And if
249you go to full SQLAlchemy, you can perform mappings like this directly
250to your object models.)
251
252An easy way to save mapped selectables like this is to just hang them on
253your db object::
254
255    >>> db.years_with_count = years_with_count
256
257Python is flexible like that!
258
259
260Raw SQL
261-------
262
263SqlSoup works fine with SQLAlchemy's `text block support`__.
264
265__ http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_text
266
267You can also access the SqlSoup's `engine` attribute to compose SQL
268directly.  The engine's ``execute`` method corresponds to the one of a
269DBAPI cursor, and returns a ``ResultProxy`` that has ``fetch`` methods
270you would also see on a cursor::
271
272    >>> rp = db.bind.execute('select name, email from users order by name')
273    >>> for name, email in rp.fetchall(): print name, email
274    Bhargan Basepair basepair+nospam@example.edu
275    Joe Student student@example.edu
276
277You can also pass this engine object to other SQLAlchemy constructs.
278
279
280Dynamic table names
281-------------------
282
283You can load a table whose name is specified at runtime with the entity() method:
284
285    >>> tablename = 'loans'
286    >>> db.entity(tablename) == db.loans
287    True
288
289entity() also takes an optional schema argument.  If none is specified, the
290default schema is used.
291
292
293Extra tests
294===========
295
296Boring tests here.  Nothing of real expository value.
297
298::
299
300    >>> db.users.filter_by(classname=None).order_by(db.users.name).all()
301    [MappedUsers(name=u'Bhargan Basepair',email=u'basepair+nospam@example.edu',password=u'basepair',classname=None,admin=1), MappedUsers(name=u'Joe Student',email=u'student@example.edu',password=u'student',classname=None,admin=0)]
302
303    >>> db.nopk
304    Traceback (most recent call last):
305    ...
306    PKNotFoundError: table 'nopk' does not have a primary key defined [columns: i]
307
308    >>> db.nosuchtable
309    Traceback (most recent call last):
310    ...
311    NoSuchTableError: nosuchtable
312
313    >>> years_with_count.insert(published_year='2007', n=1)
314    Traceback (most recent call last):
315    ...
316    InvalidRequestError: SQLSoup can only modify mapped Tables (found: Alias)
317
318    [tests clear()]
319    >>> db.loans.count()
320    1
321    >>> _ = db.loans.insert(book_id=1, user_name='Bhargan Basepair')
322    >>> db.expunge_all()
323    >>> db.flush()
324    >>> db.loans.count()
325    1
326"""
327
328from sqlalchemy import Table, MetaData, join
329from sqlalchemy import schema, sql
330from sqlalchemy.orm import scoped_session, sessionmaker, mapper, class_mapper, relation
331from sqlalchemy.exceptions import SQLAlchemyError, InvalidRequestError
332from sqlalchemy.sql import expression
333
334_testsql = """
335CREATE TABLE books (
336    id                   integer PRIMARY KEY, -- auto-increments in sqlite
337    title                text NOT NULL,
338    published_year       char(4) NOT NULL,
339    authors              text NOT NULL
340);
341
342CREATE TABLE users (
343    name                 varchar(32) PRIMARY KEY,
344    email                varchar(128) NOT NULL,
345    password             varchar(128) NOT NULL,
346    classname            text,
347    admin                int NOT NULL -- 0 = false
348);
349
350CREATE TABLE loans (
351    book_id              int PRIMARY KEY REFERENCES books(id),
352    user_name            varchar(32) references users(name)
353        ON DELETE SET NULL ON UPDATE CASCADE,
354    loan_date            datetime DEFAULT current_timestamp
355);
356
357insert into users(name, email, password, admin)
358values('Bhargan Basepair', 'basepair@example.edu', 'basepair', 1);
359insert into users(name, email, password, admin)
360values('Joe Student', 'student@example.edu', 'student', 0);
361
362insert into books(title, published_year, authors)
363values('Mustards I Have Known', '1989', 'Jones');
364insert into books(title, published_year, authors)
365values('Regional Variation in Moss', '1971', 'Flim and Flam');
366
367insert into loans(book_id, user_name, loan_date)
368values (
369    (select min(id) from books),
370    (select name from users where name like 'Joe%'),
371    '2006-07-12 0:0:0')
372;
373
374CREATE TABLE nopk (
375    i                    int
376);
377""".split(';')
378
379__all__ = ['PKNotFoundError', 'SqlSoup']
380
381Session = scoped_session(sessionmaker(autoflush=True))
382
383class PKNotFoundError(SQLAlchemyError):
384    pass
385
386def _ddl_error(cls):
387    msg = 'SQLSoup can only modify mapped Tables (found: %s)' \
388          % cls._table.__class__.__name__
389    raise InvalidRequestError(msg)
390
391# metaclass is necessary to expose class methods with getattr, e.g.
392# we want to pass db.users.select through to users._mapper.select
393class SelectableClassType(type):
394    def insert(cls, **kwargs):
395        _ddl_error(cls)
396
397    def delete(cls, *args, **kwargs):
398        _ddl_error(cls)
399
400    def update(cls, whereclause=None, values=None, **kwargs):
401        _ddl_error(cls)
402
403    def __clause_element__(cls):
404        return cls._table
405
406    def __getattr__(cls, attr):
407        if attr == '_query':
408            # called during mapper init
409            raise AttributeError()
410        return getattr(cls._query, attr)
411
412class TableClassType(SelectableClassType):
413    def insert(cls, **kwargs):
414        o = cls()
415        o.__dict__.update(kwargs)
416        return o
417
418    def delete(cls, *args, **kwargs):
419        cls._table.delete(*args, **kwargs).execute()
420
421    def update(cls, whereclause=None, values=None, **kwargs):
422        cls._table.update(whereclause, values).execute(**kwargs)
423
424    def relate(cls, propname, *args, **kwargs):
425        class_mapper(cls)._configure_property(propname, relation(*args, **kwargs))
426
427def _is_outer_join(selectable):
428    if not isinstance(selectable, sql.Join):
429        return False
430    if selectable.isouter:
431        return True
432    return _is_outer_join(selectable.left) or _is_outer_join(selectable.right)
433
434def _selectable_name(selectable):
435    if isinstance(selectable, sql.Alias):
436        return _selectable_name(selectable.element)
437    elif isinstance(selectable, sql.Select):
438        return ''.join(_selectable_name(s) for s in selectable.froms)
439    elif isinstance(selectable, schema.Table):
440        return selectable.name.capitalize()
441    else:
442        x = selectable.__class__.__name__
443        if x[0] == '_':
444            x = x[1:]
445        return x
446
447def class_for_table(selectable, **mapper_kwargs):
448    selectable = expression._clause_element_as_expr(selectable)
449    mapname = 'Mapped' + _selectable_name(selectable)
450    if isinstance(mapname, unicode):
451        engine_encoding = selectable.metadata.bind.dialect.encoding
452        mapname = mapname.encode(engine_encoding)
453    if isinstance(selectable, Table):
454        klass = TableClassType(mapname, (object,), {})
455    else:
456        klass = SelectableClassType(mapname, (object,), {})
457   
458    def __cmp__(self, o):
459        L = self.__class__.c.keys()
460        L.sort()
461        t1 = [getattr(self, k) for k in L]
462        try:
463            t2 = [getattr(o, k) for k in L]
464        except AttributeError:
465            raise TypeError('unable to compare with %s' % o.__class__)
466        return cmp(t1, t2)
467
468    def __repr__(self):
469        L = ["%s=%r" % (key, getattr(self, key, ''))
470             for key in self.__class__.c.keys()]
471        return '%s(%s)' % (self.__class__.__name__, ','.join(L))
472
473    for m in ['__cmp__', '__repr__']:
474        setattr(klass, m, eval(m))
475    klass._table = selectable
476    klass.c = expression.ColumnCollection()
477    mappr = mapper(klass,
478                   selectable,
479                   extension=Session.extension,
480                   allow_null_pks=_is_outer_join(selectable),
481                   **mapper_kwargs)
482                   
483    for k in mappr.iterate_properties:
484        klass.c[k.key] = k.columns[0]
485
486    klass._query = Session.query_property()
487    return klass
488
489class SqlSoup:
490    def __init__(self, *args, **kwargs):
491        """Initialize a new ``SqlSoup``.
492
493        `args` may either be an ``SQLEngine`` or a set of arguments
494        suitable for passing to ``create_engine``.
495        """
496
497        # meh, sometimes having method overloading instead of kwargs would be easier
498        if isinstance(args[0], MetaData):
499            args = list(args)
500            metadata = args.pop(0)
501            if args or kwargs:
502                raise ArgumentError('Extra arguments not allowed when metadata is given')
503        else:
504            metadata = MetaData(*args, **kwargs)
505        self._metadata = metadata
506        self._cache = {}
507        self.schema = None
508
509    def engine(self):
510        return self._metadata.bind
511
512    engine = property(engine)
513    bind = engine
514
515    def delete(self, *args, **kwargs):
516        Session.delete(*args, **kwargs)
517
518    def flush(self):
519        Session.flush()
520
521    def clear(self):
522        Session.expunge_all()
523
524    def expunge_all(self):
525        Session.expunge_all()
526
527    def map(self, selectable, **kwargs):
528        try:
529            t = self._cache[selectable]
530        except KeyError:
531            t = class_for_table(selectable, **kwargs)
532            self._cache[selectable] = t
533        return t
534
535    def with_labels(self, item):
536        # TODO give meaningful aliases
537        return self.map(expression._clause_element_as_expr(item).select(use_labels=True).alias('foo'))
538
539    def join(self, *args, **kwargs):
540        j = join(*args, **kwargs)
541        return self.map(j)
542
543    def entity(self, attr, schema=None):
544        try:
545            t = self._cache[attr]
546        except KeyError:
547            table = Table(attr, self._metadata, autoload=True, schema=schema or self.schema)
548            if not table.primary_key.columns:
549                raise PKNotFoundError('table %r does not have a primary key defined [columns: %s]' % (attr, ','.join(table.c.keys())))
550            if table.columns:
551                t = class_for_table(table)
552            else:
553                t = None
554            self._cache[attr] = t
555        return t
556   
557    def __getattr__(self, attr):
558        return self.entity(attr)
559
560    def __repr__(self):
561        return 'SqlSoup(%r)' % self._metadata
562
563if __name__ == '__main__':
564    import logging
565    logging.basicConfig()
566    import doctest
567    doctest.testmod()
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。