2
# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file>
4
# This module is part of SQLAlchemy and is released under
5
# the MIT License: http://www.opensource.org/licenses/mit-license.php
9
.. versionchanged:: 0.8
10
SQLSoup is now its own project. Documentation
11
and project status are available at:
12
http://pypi.python.org/pypi/sqlsoup and
13
http://readthedocs.org/docs/sqlsoup\ .
14
SQLSoup will no longer be included with SQLAlchemy.
20
SqlSoup provides a convenient way to access existing database
21
tables without having to declare table or mapper classes ahead
22
of time. It is built on top of the SQLAlchemy ORM and provides a
23
super-minimalistic interface to an existing database.
25
SqlSoup effectively provides a coarse grained, alternative
26
interface to working with the SQLAlchemy ORM, providing a "self
27
configuring" interface for extremely rudimental operations. It's
28
somewhat akin to a "super novice mode" version of the ORM. While
29
SqlSoup can be very handy, users are strongly encouraged to use
30
the full ORM for non-trivial applications.
32
Suppose we have a database with users, books, and loans tables
33
(corresponding to the PyWebOff dataset, if you're curious).
35
Creating a SqlSoup gateway is just like creating an SQLAlchemy
38
>>> from sqlalchemy.ext.sqlsoup import SqlSoup
39
>>> db = SqlSoup('sqlite:///:memory:')
41
or, you can re-use an existing engine::
43
>>> db = SqlSoup(engine)
45
You can optionally specify a schema within the database for your
48
>>> db.schema = myschemaname
53
Loading objects is as easy as this::
55
>>> users = db.users.all()
59
MappedUsers(name=u'Joe Student',email=u'student@example.edu',
60
password=u'student',classname=None,admin=0),
61
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
62
password=u'basepair',classname=None,admin=1)
65
Of course, letting the database do the sort is better::
67
>>> db.users.order_by(db.users.name).all()
69
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
70
password=u'basepair',classname=None,admin=1),
71
MappedUsers(name=u'Joe Student',email=u'student@example.edu',
72
password=u'student',classname=None,admin=0)
75
Field access is intuitive::
78
u'student@example.edu'
80
Of course, you don't want to load all users very often. Let's
81
add a WHERE clause. Let's also switch the order_by to DESC while
84
>>> from sqlalchemy import or_, and_, desc
85
>>> where = or_(db.users.name=='Bhargan Basepair', db.users.email=='student@example.edu')
86
>>> db.users.filter(where).order_by(desc(db.users.name)).all()
88
MappedUsers(name=u'Joe Student',email=u'student@example.edu',
89
password=u'student',classname=None,admin=0),
90
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
91
password=u'basepair',classname=None,admin=1)
94
You can also use .first() (to retrieve only the first object
95
from a query) or .one() (like .first when you expect exactly one
96
user -- it will raise an exception if more were returned)::
98
>>> db.users.filter(db.users.name=='Bhargan Basepair').one()
99
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
100
password=u'basepair',classname=None,admin=1)
102
Since name is the primary key, this is equivalent to
104
>>> db.users.get('Bhargan Basepair')
105
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
106
password=u'basepair',classname=None,admin=1)
108
This is also equivalent to
110
>>> db.users.filter_by(name='Bhargan Basepair').one()
111
MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',
112
password=u'basepair',classname=None,admin=1)
114
filter_by is like filter, but takes kwargs instead of full
115
clause expressions. This makes it more concise for simple
116
queries like this, but you can't do complex queries like the
117
or\_ above or non-equality based comparisons this way.
119
Full query documentation
120
------------------------
122
Get, filter, filter_by, order_by, limit, and the rest of the
123
query methods are explained in detail in
124
:ref:`ormtutorial_querying`.
129
Modifying objects is intuitive::
132
>>> user.email = 'basepair+nospam@example.edu'
135
(SqlSoup leverages the sophisticated SQLAlchemy unit-of-work
136
code, so multiple updates to a single object will be turned into
137
a single ``UPDATE`` statement when you commit.)
139
To finish covering the basics, let's insert a new loan, then
142
>>> book_id = db.books.filter_by(title='Regional Variation in Moss').first().id
143
>>> db.loans.insert(book_id=book_id, user_name=user.name)
144
MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)
146
>>> loan = db.loans.filter_by(book_id=2, user_name='Bhargan Basepair').one()
150
You can also delete rows that have not been loaded as objects.
151
Let's do our insert/delete cycle once more, this time using the
152
loans table's delete method. (For SQLAlchemy experts: note that
153
no flush() call is required since this delete acts at the SQL
154
level, not at the Mapper level.) The same where-clause
155
construction rules apply here as to the select methods::
157
>>> db.loans.insert(book_id=book_id, user_name=user.name)
158
MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)
159
>>> db.loans.delete(db.loans.book_id==2)
161
You can similarly update multiple rows at once. This will change the
162
book_id to 1 in all loans whose book_id is 2::
164
>>> db.loans.filter_by(db.loans.book_id==2).update({'book_id':1})
165
>>> db.loans.filter_by(book_id=1).all()
166
[MappedLoans(book_id=1,user_name=u'Joe Student',
167
loan_date=datetime.datetime(2006, 7, 12, 0, 0))]
173
Occasionally, you will want to pull out a lot of data from related
174
tables all at once. In this situation, it is far more efficient to
175
have the database perform the necessary join. (Here we do not have *a
176
lot of data* but hopefully the concept is still clear.) SQLAlchemy is
177
smart enough to recognize that loans has a foreign key to users, and
178
uses that as the join condition automatically::
180
>>> join1 = db.join(db.users, db.loans, isouter=True)
181
>>> join1.filter_by(name='Joe Student').all()
183
MappedJoin(name=u'Joe Student',email=u'student@example.edu',
184
password=u'student',classname=None,admin=0,book_id=1,
185
user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))
188
If you're unfortunate enough to be using MySQL with the default MyISAM
189
storage engine, you'll have to specify the join condition manually,
190
since MyISAM does not store foreign keys. Here's the same join again,
191
with the join condition explicitly specified::
193
>>> db.join(db.users, db.loans, db.users.name==db.loans.user_name, isouter=True)
194
<class 'sqlalchemy.ext.sqlsoup.MappedJoin'>
196
You can compose arbitrarily complex joins by combining Join objects
197
with tables or other joins. Here we combine our first join with the
200
>>> join2 = db.join(join1, db.books)
203
MappedJoin(name=u'Joe Student',email=u'student@example.edu',
204
password=u'student',classname=None,admin=0,book_id=1,
205
user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0),
206
id=1,title=u'Mustards I Have Known',published_year=u'1989',
210
If you join tables that have an identical column name, wrap your join
211
with `with_labels`, to disambiguate columns with their table name
212
(.c is short for .columns)::
214
>>> db.with_labels(join1).c.keys()
215
[u'users_name', u'users_email', u'users_password',
216
u'users_classname', u'users_admin', u'loans_book_id',
217
u'loans_user_name', u'loans_loan_date']
219
You can also join directly to a labeled object::
221
>>> labeled_loans = db.with_labels(db.loans)
222
>>> db.join(db.users, labeled_loans, isouter=True).c.keys()
223
[u'name', u'email', u'password', u'classname',
224
u'admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date']
230
You can define relationships on SqlSoup classes:
232
>>> db.users.relate('loans', db.loans)
234
These can then be used like a normal SA property:
236
>>> db.users.get('Joe Student').loans
237
[MappedLoans(book_id=1,user_name=u'Joe Student',
238
loan_date=datetime.datetime(2006, 7, 12, 0, 0))]
240
>>> db.users.filter(~db.users.loans.any()).all()
241
[MappedUsers(name=u'Bhargan Basepair',
242
email='basepair+nospam@example.edu',
243
password=u'basepair',classname=None,admin=1)]
245
relate can take any options that the relationship function
246
accepts in normal mapper definition:
248
>>> del db._cache['users']
249
>>> db.users.relate('loans', db.loans, order_by=db.loans.loan_date, cascade='all, delete-orphan')
254
Sessions, Transactions and Application Integration
255
---------------------------------------------------
259
Please read and understand this section thoroughly
260
before using SqlSoup in any web application.
262
SqlSoup uses a ScopedSession to provide thread-local sessions.
263
You can get a reference to the current one like this::
265
>>> session = db.session
267
The default session is available at the module level in SQLSoup,
270
>>> from sqlalchemy.ext.sqlsoup import Session
272
The configuration of this session is ``autoflush=True``,
273
``autocommit=False``. This means when you work with the SqlSoup
274
object, you need to call ``db.commit()`` in order to have
275
changes persisted. You may also call ``db.rollback()`` to roll
278
Since the SqlSoup object's Session automatically enters into a
279
transaction as soon as it's used, it is *essential* that you
280
call ``commit()`` or ``rollback()`` on it when the work within a
281
thread completes. This means all the guidelines for web
282
application integration at :ref:`session_lifespan` must be
285
The SqlSoup object can have any session or scoped session
286
configured onto it. This is of key importance when integrating
287
with existing code or frameworks such as Pylons. If your
288
application already has a ``Session`` configured, pass it to
289
your SqlSoup object::
291
>>> from myapplication import Session
292
>>> db = SqlSoup(session=Session)
294
If the ``Session`` is configured with ``autocommit=True``, use
295
``flush()`` instead of ``commit()`` to persist changes - in this
296
case, the ``Session`` closes out its transaction immediately and
297
no external management is needed. ``rollback()`` is also not
298
available. Configuring a new SQLSoup object in "autocommit" mode
301
>>> from sqlalchemy.orm import scoped_session, sessionmaker
302
>>> db = SqlSoup('sqlite://', session=scoped_session(sessionmaker(autoflush=False, expire_on_commit=False, autocommit=True)))
305
Mapping arbitrary Selectables
306
-----------------------------
308
SqlSoup can map any SQLAlchemy :class:`.Selectable` with the map
309
method. Let's map an :func:`.expression.select` object that uses an aggregate
310
function; we'll use the SQLAlchemy :class:`.Table` that SqlSoup
311
introspected as the basis. (Since we're not mapping to a simple
312
table or join, we need to tell SQLAlchemy how to find the
313
*primary key* which just needs to be unique within the select,
314
and not necessarily correspond to a *real* PK in the database.)::
316
>>> from sqlalchemy import select, func
317
>>> b = db.books._table
318
>>> s = select([b.c.published_year, func.count('*').label('n')], from_obj=[b], group_by=[b.c.published_year])
319
>>> s = s.alias('years_with_count')
320
>>> years_with_count = db.map(s, primary_key=[s.c.published_year])
321
>>> years_with_count.filter_by(published_year='1989').all()
322
[MappedBooks(published_year=u'1989',n=1)]
324
Obviously if we just wanted to get a list of counts associated with
325
book years once, raw SQL is going to be less work. The advantage of
326
mapping a Select is reusability, both standalone and in Joins. (And if
327
you go to full SQLAlchemy, you can perform mappings like this directly
328
to your object models.)
330
An easy way to save mapped selectables like this is to just hang them on
333
>>> db.years_with_count = years_with_count
335
Python is flexible like that!
340
SqlSoup works fine with SQLAlchemy's text construct, described
341
in :ref:`sqlexpression_text`. You can also execute textual SQL
342
directly using the `execute()` method, which corresponds to the
343
`execute()` method on the underlying `Session`. Expressions here
344
are expressed like ``text()`` constructs, using named parameters
347
>>> rp = db.execute('select name, email from users where name like :name order by name', name='%Bhargan%')
348
>>> for name, email in rp.fetchall(): print name, email
349
Bhargan Basepair basepair+nospam@example.edu
351
Or you can get at the current transaction's connection using
352
`connection()`. This is the raw connection object which can
353
accept any sort of SQL expression or raw SQL string passed to
356
>>> conn = db.connection()
357
>>> conn.execute("'select name, email from users where name like ? order by name'", '%Bhargan%')
362
You can load a table whose name is specified at runtime with the
365
>>> tablename = 'loans'
366
>>> db.entity(tablename) == db.loans
369
entity() also takes an optional schema argument. If none is
370
specified, the default schema is used.
374
from sqlalchemy import Table, MetaData, join
375
from sqlalchemy import schema, sql, util
376
from sqlalchemy.engine.base import Engine
377
from sqlalchemy.orm import scoped_session, sessionmaker, mapper, \
378
class_mapper, relationship, session,\
379
object_session, attributes
380
from sqlalchemy.orm.interfaces import MapperExtension, EXT_CONTINUE
381
from sqlalchemy.exc import SQLAlchemyError, InvalidRequestError, ArgumentError
382
from sqlalchemy.sql import expression
385
__all__ = ['PKNotFoundError', 'SqlSoup']
387
Session = scoped_session(sessionmaker(autoflush=True, autocommit=False))
389
class AutoAdd(MapperExtension):
390
def __init__(self, scoped_session):
391
self.scoped_session = scoped_session
393
def instrument_class(self, mapper, class_):
394
class_.__init__ = self._default__init__(mapper)
396
def _default__init__(ext, mapper):
397
def __init__(self, **kwargs):
398
for key, value in kwargs.iteritems():
399
setattr(self, key, value)
402
def init_instance(self, mapper, class_, oldinit, instance, args, kwargs):
403
session = self.scoped_session()
404
state = attributes.instance_state(instance)
405
session._save_impl(state)
408
def init_failed(self, mapper, class_, oldinit, instance, args, kwargs):
409
sess = object_session(instance)
411
sess.expunge(instance)
414
class PKNotFoundError(SQLAlchemyError):
418
msg = 'SQLSoup can only modify mapped Tables (found: %s)' \
419
% cls._table.__class__.__name__
420
raise InvalidRequestError(msg)
422
# metaclass is necessary to expose class methods with getattr, e.g.
423
# we want to pass db.users.select through to users._mapper.select
424
class SelectableClassType(type):
425
def insert(cls, **kwargs):
428
def __clause_element__(cls):
431
def __getattr__(cls, attr):
433
# called during mapper init
434
raise AttributeError()
435
return getattr(cls._query, attr)
437
class TableClassType(SelectableClassType):
438
def insert(cls, **kwargs):
440
o.__dict__.update(kwargs)
443
def relate(cls, propname, *args, **kwargs):
444
class_mapper(cls)._configure_property(propname, relationship(*args, **kwargs))
446
def _is_outer_join(selectable):
447
if not isinstance(selectable, sql.Join):
449
if selectable.isouter:
451
return _is_outer_join(selectable.left) or _is_outer_join(selectable.right)
453
def _selectable_name(selectable):
454
if isinstance(selectable, sql.Alias):
455
return _selectable_name(selectable.element)
456
elif isinstance(selectable, sql.Select):
457
return ''.join(_selectable_name(s) for s in selectable.froms)
458
elif isinstance(selectable, schema.Table):
459
return selectable.name.capitalize()
461
x = selectable.__class__.__name__
466
def _class_for_table(session, engine, selectable, base_cls, mapper_kwargs):
467
selectable = expression._clause_element_as_expr(selectable)
468
mapname = 'Mapped' + _selectable_name(selectable)
470
if isinstance(mapname, unicode):
471
engine_encoding = engine.dialect.encoding
472
mapname = mapname.encode(engine_encoding)
475
if isinstance(selectable, Table):
476
klass = TableClassType(mapname, (base_cls,), {})
478
klass = SelectableClassType(mapname, (base_cls,), {})
480
def _compare(self, o):
481
L = list(self.__class__.c.keys())
483
t1 = [getattr(self, k) for k in L]
485
t2 = [getattr(o, k) for k in L]
486
except AttributeError:
487
raise TypeError('unable to compare with %s' % o.__class__)
490
# python2/python3 compatible system of
491
# __cmp__ - __lt__ + __eq__
494
t1, t2 = _compare(self, o)
498
t1, t2 = _compare(self, o)
502
L = ["%s=%r" % (key, getattr(self, key, ''))
503
for key in self.__class__.c.keys()]
504
return '%s(%s)' % (self.__class__.__name__, ','.join(L))
506
for m in ['__eq__', '__repr__', '__lt__']:
507
setattr(klass, m, eval(m))
508
klass._table = selectable
509
klass.c = expression.ColumnCollection()
510
mappr = mapper(klass,
512
extension=AutoAdd(session),
515
for k in mappr.iterate_properties:
516
klass.c[k.key] = k.columns[0]
518
klass._query = session.query_property()
521
class SqlSoup(object):
522
"""Represent an ORM-wrapped database resource."""
524
def __init__(self, engine_or_metadata, base=object, session=None):
525
"""Initialize a new :class:`.SqlSoup`.
527
:param engine_or_metadata: a string database URL, :class:`.Engine`
528
or :class:`.MetaData` object to associate with. If the
529
argument is a :class:`.MetaData`, it should be *bound*
530
to an :class:`.Engine`.
531
:param base: a class which will serve as the default class for
532
returned mapped classes. Defaults to ``object``.
533
:param session: a :class:`.ScopedSession` or :class:`.Session` with
534
which to associate ORM operations for this :class:`.SqlSoup` instance.
535
If ``None``, a :class:`.ScopedSession` that's local to this
540
self.session = session or Session
543
if isinstance(engine_or_metadata, MetaData):
544
self._metadata = engine_or_metadata
545
elif isinstance(engine_or_metadata, (basestring, Engine)):
546
self._metadata = MetaData(engine_or_metadata)
548
raise ArgumentError("invalid engine or metadata argument %r" %
556
"""The :class:`.Engine` associated with this :class:`.SqlSoup`."""
557
return self._metadata.bind
561
def delete(self, instance):
562
"""Mark an instance as deleted."""
564
self.session.delete(instance)
566
def execute(self, stmt, **params):
567
"""Execute a SQL statement.
569
The statement may be a string SQL string,
570
an :func:`.expression.select` construct, or an :func:`.expression.text`
574
return self.session.execute(sql.text(stmt, bind=self.bind), **params)
577
def _underlying_session(self):
578
if isinstance(self.session, session.Session):
581
return self.session()
583
def connection(self):
584
"""Return the current :class:`.Connection` in use by the current transaction."""
586
return self._underlying_session._connection_for_bind(self.bind)
589
"""Flush pending changes to the database.
591
See :meth:`.Session.flush`.
597
"""Rollback the current transaction.
599
See :meth:`.Session.rollback`.
602
self.session.rollback()
605
"""Commit the current transaction.
607
See :meth:`.Session.commit`.
610
self.session.commit()
613
"""Synonym for :meth:`.SqlSoup.expunge_all`."""
615
self.session.expunge_all()
617
def expunge(self, instance):
618
"""Remove an instance from the :class:`.Session`.
620
See :meth:`.Session.expunge`.
623
self.session.expunge(instance)
625
def expunge_all(self):
626
"""Clear all objects from the current :class:`.Session`.
628
See :meth:`.Session.expunge_all`.
631
self.session.expunge_all()
633
def map_to(self, attrname, tablename=None, selectable=None,
634
schema=None, base=None, mapper_args=util.immutabledict()):
635
"""Configure a mapping to the given attrname.
637
This is the "master" method that can be used to create any
640
.. versionadded:: 0.6.6
642
:param attrname: String attribute name which will be
643
established as an attribute on this :class:.`.SqlSoup`
645
:param base: a Python class which will be used as the
646
base for the mapped class. If ``None``, the "base"
647
argument specified by this :class:`.SqlSoup`
648
instance's constructor will be used, which defaults to
650
:param mapper_args: Dictionary of arguments which will
651
be passed directly to :func:`.orm.mapper`.
652
:param tablename: String name of a :class:`.Table` to be
653
reflected. If a :class:`.Table` is already available,
654
use the ``selectable`` argument. This argument is
655
mutually exclusive versus the ``selectable`` argument.
656
:param selectable: a :class:`.Table`, :class:`.Join`, or
657
:class:`.Select` object which will be mapped. This
658
argument is mutually exclusive versus the ``tablename``
660
:param schema: String schema name to use if the
661
``tablename`` argument is present.
665
if attrname in self._cache:
666
raise InvalidRequestError(
667
"Attribute '%s' is already mapped to '%s'" % (
669
class_mapper(self._cache[attrname]).mapped_table
672
if tablename is not None:
673
if not isinstance(tablename, basestring):
674
raise ArgumentError("'tablename' argument must be a string."
676
if selectable is not None:
677
raise ArgumentError("'tablename' and 'selectable' "
678
"arguments are mutually exclusive")
680
selectable = Table(tablename,
683
autoload_with=self.bind,
684
schema=schema or self.schema)
686
raise ArgumentError("'tablename' argument is required when "
688
elif selectable is not None:
689
if not isinstance(selectable, expression.FromClause):
690
raise ArgumentError("'selectable' argument must be a "
691
"table, select, join, or other "
692
"selectable construct.")
694
raise ArgumentError("'tablename' or 'selectable' argument is "
697
if not selectable.primary_key.columns:
699
raise PKNotFoundError(
700
"table '%s' does not have a primary "
701
"key defined" % tablename)
703
raise PKNotFoundError(
704
"selectable '%s' does not have a primary "
705
"key defined" % selectable)
707
mapped_cls = _class_for_table(
714
self._cache[attrname] = mapped_cls
718
def map(self, selectable, base=None, **mapper_args):
719
"""Map a selectable directly.
721
.. versionchanged:: 0.6.6
722
The class and its mapping are not cached and will
723
be discarded once dereferenced.
725
:param selectable: an :func:`.expression.select` construct.
726
:param base: a Python class which will be used as the
727
base for the mapped class. If ``None``, the "base"
728
argument specified by this :class:`.SqlSoup`
729
instance's constructor will be used, which defaults to
731
:param mapper_args: Dictionary of arguments which will
732
be passed directly to :func:`.orm.mapper`.
736
return _class_for_table(
744
def with_labels(self, selectable, base=None, **mapper_args):
745
"""Map a selectable directly, wrapping the
746
selectable in a subquery with labels.
748
.. versionchanged:: 0.6.6
749
The class and its mapping are not cached and will
750
be discarded once dereferenced.
752
:param selectable: an :func:`.expression.select` construct.
753
:param base: a Python class which will be used as the
754
base for the mapped class. If ``None``, the "base"
755
argument specified by this :class:`.SqlSoup`
756
instance's constructor will be used, which defaults to
758
:param mapper_args: Dictionary of arguments which will
759
be passed directly to :func:`.orm.mapper`.
763
# TODO give meaningful aliases
765
expression._clause_element_as_expr(selectable).
766
select(use_labels=True).
767
alias('foo'), base=base, **mapper_args)
769
def join(self, left, right, onclause=None, isouter=False,
770
base=None, **mapper_args):
771
"""Create an :func:`.expression.join` and map to it.
773
.. versionchanged:: 0.6.6
774
The class and its mapping are not cached and will
775
be discarded once dereferenced.
777
:param left: a mapped class or table object.
778
:param right: a mapped class or table object.
779
:param onclause: optional "ON" clause construct..
780
:param isouter: if True, the join will be an OUTER join.
781
:param base: a Python class which will be used as the
782
base for the mapped class. If ``None``, the "base"
783
argument specified by this :class:`.SqlSoup`
784
instance's constructor will be used, which defaults to
786
:param mapper_args: Dictionary of arguments which will
787
be passed directly to :func:`.orm.mapper`.
791
j = join(left, right, onclause=onclause, isouter=isouter)
792
return self.map(j, base=base, **mapper_args)
794
def entity(self, attr, schema=None):
795
"""Return the named entity from this :class:`.SqlSoup`, or
796
create if not present.
798
For more generalized mapping, see :meth:`.map_to`.
802
return self._cache[attr]
804
return self.map_to(attr, tablename=attr, schema=schema)
806
def __getattr__(self, attr):
807
return self.entity(attr)
810
return 'SqlSoup(%r)' % self._metadata
added
removed
lib/sqlalchemy/ext/sqlsoup.py
