1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
10
— SQLAlchemy 0.6.6 Documentation</title>
12
<link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
13
<link rel="stylesheet" href="../../_static/docs.css" type="text/css" />
15
<script type="text/javascript">
16
var DOCUMENTATION_OPTIONS = {
19
COLLAPSE_MODINDEX: false,
23
<script type="text/javascript" src="../../_static/jquery.js"></script>
24
<script type="text/javascript" src="../../_static/underscore.js"></script>
25
<script type="text/javascript" src="../../_static/doctools.js"></script>
26
<script type="text/javascript" src="../../_static/init.js"></script>
27
<link rel="index" title="Index" href="../../genindex.html" />
28
<link rel="search" title="Search" href="../../search.html" />
29
<link rel="copyright" title="Copyright" href="../../copyright.html" />
30
<link rel="top" title="SQLAlchemy 0.6.6 Documentation" href="../../index.html" />
31
<link rel="up" title="ORM Extensions" href="index.html" />
32
<link rel="next" title="Examples" href="../examples.html" />
33
<link rel="prev" title="Horizontal Sharding" href="horizontal_shard.html" />
43
<h1>SQLAlchemy 0.6.6 Documentation</h1>
47
<form class="search" action="../../search.html" method="get">
48
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
49
<input type="hidden" name="check_keywords" value="yes" />
50
<input type="hidden" name="area" value="default" />
54
<div class="versionheader">
55
Version: <span class="versionnum">0.6.6</span> Last Updated: 01/08/2011 17:20:49
57
<div class="clearboth"></div>
60
<div id="pagecontrol">
61
<a href="../../genindex.html">Index</a>
63
<div class="sourcelink">(<a href="../../_sources/orm/extensions/sqlsoup.txt">view source)</div>
66
<div class="navbanner">
67
<a class="totoc" href="../../index.html">Table of Contents</a>
68
» <a href="../index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
69
» <a href="index.html" title="ORM Extensions">ORM Extensions</a>
75
<div class="prevnext">
77
<a href="horizontal_shard.html" title="previous chapter">Horizontal Sharding</a>
79
<a href="../examples.html" title="next chapter">Examples</a>
89
<li><a class="reference internal" href="#">SqlSoup</a><ul>
90
<li><a class="reference internal" href="#introduction">Introduction</a></li>
91
<li><a class="reference internal" href="#loading-objects">Loading objects</a><ul>
92
<li><a class="reference internal" href="#full-query-documentation">Full query documentation</a></li>
95
<li><a class="reference internal" href="#modifying-objects">Modifying objects</a></li>
96
<li><a class="reference internal" href="#joins">Joins</a></li>
97
<li><a class="reference internal" href="#relationships">Relationships</a></li>
98
<li><a class="reference internal" href="#advanced-use">Advanced Use</a><ul>
99
<li><a class="reference internal" href="#sessions-transations-and-application-integration">Sessions, Transations and Application Integration</a></li>
100
<li><a class="reference internal" href="#mapping-arbitrary-selectables">Mapping arbitrary Selectables</a></li>
101
<li><a class="reference internal" href="#raw-sql">Raw SQL</a></li>
102
<li><a class="reference internal" href="#dynamic-table-names">Dynamic table names</a></li>
105
<li><a class="reference internal" href="#sqlsoup-api">SqlSoup API</a></li>
110
<div class="clearboth"></div>
113
<div class="document">
116
<div class="section" id="module-sqlalchemy.ext.sqlsoup">
117
<span id="sqlsoup"></span><h1>SqlSoup<a class="headerlink" href="#module-sqlalchemy.ext.sqlsoup" title="Permalink to this headline">¶</a></h1>
118
<div class="section" id="introduction">
119
<h2>Introduction<a class="headerlink" href="#introduction" title="Permalink to this headline">¶</a></h2>
120
<p>SqlSoup provides a convenient way to access existing database
121
tables without having to declare table or mapper classes ahead
122
of time. It is built on top of the SQLAlchemy ORM and provides a
123
super-minimalistic interface to an existing database.</p>
124
<p>SqlSoup effectively provides a coarse grained, alternative
125
interface to working with the SQLAlchemy ORM, providing a “self
126
configuring” interface for extremely rudimental operations. It’s
127
somewhat akin to a “super novice mode” version of the ORM. While
128
SqlSoup can be very handy, users are strongly encouraged to use
129
the full ORM for non-trivial applications.</p>
130
<p>Suppose we have a database with users, books, and loans tables
131
(corresponding to the PyWebOff dataset, if you’re curious).</p>
132
<p>Creating a SqlSoup gateway is just like creating an SQLAlchemy
134
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.sqlsoup</span> <span class="kn">import</span> <span class="n">SqlSoup</span>
135
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">SqlSoup</span><span class="p">(</span><span class="s">'sqlite:///:memory:'</span><span class="p">)</span></pre></div>
137
<p>or, you can re-use an existing engine:</p>
138
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">SqlSoup</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
140
<p>You can optionally specify a schema within the database for your
142
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">schema</span> <span class="o">=</span> <span class="n">myschemaname</span></pre></div>
145
<div class="section" id="loading-objects">
146
<h2>Loading objects<a class="headerlink" href="#loading-objects" title="Permalink to this headline">¶</a></h2>
147
<p>Loading objects is as easy as this:</p>
148
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">users</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
149
<span class="gp">>>> </span><span class="n">users</span><span class="o">.</span><span class="n">sort</span><span class="p">()</span>
150
<span class="gp">>>> </span><span class="n">users</span>
151
<span class="go">[</span>
152
<span class="go"> MappedUsers(name=u'Joe Student',email=u'student@example.edu',</span>
153
<span class="go"> password=u'student',classname=None,admin=0), </span>
154
<span class="go"> MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
155
<span class="go"> password=u'basepair',classname=None,admin=1)</span>
156
<span class="go">]</span></pre></div>
158
<p>Of course, letting the database do the sort is better:</p>
159
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
160
<span class="go">[</span>
161
<span class="go"> MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
162
<span class="go"> password=u'basepair',classname=None,admin=1), </span>
163
<span class="go"> MappedUsers(name=u'Joe Student',email=u'student@example.edu',</span>
164
<span class="go"> password=u'student',classname=None,admin=0)</span>
165
<span class="go">]</span></pre></div>
167
<p>Field access is intuitive:</p>
168
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">users</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">email</span>
169
<span class="go">u'student@example.edu'</span></pre></div>
171
<p>Of course, you don’t want to load all users very often. Let’s
172
add a WHERE clause. Let’s also switch the order_by to DESC while
173
we’re at it:</p>
174
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">or_</span><span class="p">,</span> <span class="n">and_</span><span class="p">,</span> <span class="n">desc</span>
175
<span class="gp">>>> </span><span class="n">where</span> <span class="o">=</span> <span class="n">or_</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'Bhargan Basepair'</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">email</span><span class="o">==</span><span class="s">'student@example.edu'</span><span class="p">)</span>
176
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">where</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">desc</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
177
<span class="go">[</span>
178
<span class="go"> MappedUsers(name=u'Joe Student',email=u'student@example.edu',</span>
179
<span class="go"> password=u'student',classname=None,admin=0), </span>
180
<span class="go"> MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
181
<span class="go"> password=u'basepair',classname=None,admin=1)</span>
182
<span class="go">]</span></pre></div>
184
<p>You can also use .first() (to retrieve only the first object
185
from a query) or .one() (like .first when you expect exactly one
186
user – it will raise an exception if more were returned):</p>
187
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'Bhargan Basepair'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
188
<span class="go">MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
189
<span class="go"> password=u'basepair',classname=None,admin=1)</span></pre></div>
191
<p>Since name is the primary key, this is equivalent to</p>
192
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s">'Bhargan Basepair'</span><span class="p">)</span>
193
<span class="go">MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
194
<span class="go"> password=u'basepair',classname=None,admin=1)</span></pre></div>
196
<p>This is also equivalent to</p>
197
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'Bhargan Basepair'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
198
<span class="go">MappedUsers(name=u'Bhargan Basepair',email=u'basepair@example.edu',</span>
199
<span class="go"> password=u'basepair',classname=None,admin=1)</span></pre></div>
201
<p>filter_by is like filter, but takes kwargs instead of full
202
clause expressions. This makes it more concise for simple
203
queries like this, but you can’t do complex queries like the
204
or_ above or non-equality based comparisons this way.</p>
205
<div class="section" id="full-query-documentation">
206
<h3>Full query documentation<a class="headerlink" href="#full-query-documentation" title="Permalink to this headline">¶</a></h3>
207
<p>Get, filter, filter_by, order_by, limit, and the rest of the
208
query methods are explained in detail in
209
<a class="reference internal" href="../tutorial.html#ormtutorial-querying"><em>Querying</em></a>.</p>
212
<div class="section" id="modifying-objects">
213
<h2>Modifying objects<a class="headerlink" href="#modifying-objects" title="Permalink to this headline">¶</a></h2>
214
<p>Modifying objects is intuitive:</p>
215
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">user</span> <span class="o">=</span> <span class="n">_</span>
216
<span class="gp">>>> </span><span class="n">user</span><span class="o">.</span><span class="n">email</span> <span class="o">=</span> <span class="s">'basepair+nospam@example.edu'</span>
217
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
219
<p>(SqlSoup leverages the sophisticated SQLAlchemy unit-of-work
220
code, so multiple updates to a single object will be turned into
221
a single <tt class="docutils literal"><span class="pre">UPDATE</span></tt> statement when you commit.)</p>
222
<p>To finish covering the basics, let’s insert a new loan, then
224
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">book_id</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">books</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">title</span><span class="o">=</span><span class="s">'Regional Variation in Moss'</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span><span class="o">.</span><span class="n">id</span>
225
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="n">book_id</span><span class="o">=</span><span class="n">book_id</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="n">user</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
226
<span class="go">MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)</span>
228
<span class="gp">>>> </span><span class="n">loan</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">book_id</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="s">'Bhargan Basepair'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
229
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">loan</span><span class="p">)</span>
230
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
232
<p>You can also delete rows that have not been loaded as objects.
233
Let’s do our insert/delete cycle once more, this time using the
234
loans table’s delete method. (For SQLAlchemy experts: note that
235
no flush() call is required since this delete acts at the SQL
236
level, not at the Mapper level.) The same where-clause
237
construction rules apply here as to the select methods:</p>
238
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="n">book_id</span><span class="o">=</span><span class="n">book_id</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="n">user</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
239
<span class="go">MappedLoans(book_id=2,user_name=u'Bhargan Basepair',loan_date=None)</span>
240
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">book_id</span><span class="o">==</span><span class="mi">2</span><span class="p">)</span></pre></div>
242
<p>You can similarly update multiple rows at once. This will change the
243
book_id to 1 in all loans whose book_id is 2:</p>
244
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">book_id</span><span class="o">==</span><span class="mi">2</span><span class="p">,</span> <span class="n">book_id</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
245
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">book_id</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
246
<span class="go">[MappedLoans(book_id=1,user_name=u'Joe Student',</span>
247
<span class="go"> loan_date=datetime.datetime(2006, 7, 12, 0, 0))]</span></pre></div>
250
<div class="section" id="joins">
251
<h2>Joins<a class="headerlink" href="#joins" title="Permalink to this headline">¶</a></h2>
252
<p>Occasionally, you will want to pull out a lot of data from related
253
tables all at once. In this situation, it is far more efficient to
254
have the database perform the necessary join. (Here we do not have <em>a
255
lot of data</em> but hopefully the concept is still clear.) SQLAlchemy is
256
smart enough to recognize that loans has a foreign key to users, and
257
uses that as the join condition automatically:</p>
258
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">join1</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="p">,</span> <span class="n">isouter</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
259
<span class="gp">>>> </span><span class="n">join1</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'Joe Student'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
260
<span class="go">[</span>
261
<span class="go"> MappedJoin(name=u'Joe Student',email=u'student@example.edu',</span>
262
<span class="go"> password=u'student',classname=None,admin=0,book_id=1,</span>
263
<span class="go"> user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0))</span>
264
<span class="go">]</span></pre></div>
266
<p>If you’re unfortunate enough to be using MySQL with the default MyISAM
267
storage engine, you’ll have to specify the join condition manually,
268
since MyISAM does not store foreign keys. Here’s the same join again,
269
with the join condition explicitly specified:</p>
270
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">user_name</span><span class="p">,</span> <span class="n">isouter</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
271
<span class="go"><class 'sqlalchemy.ext.sqlsoup.MappedJoin'></span></pre></div>
273
<p>You can compose arbitrarily complex joins by combining Join objects
274
with tables or other joins. Here we combine our first join with the
276
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">join2</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">join1</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">books</span><span class="p">)</span>
277
<span class="gp">>>> </span><span class="n">join2</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
278
<span class="go">[</span>
279
<span class="go"> MappedJoin(name=u'Joe Student',email=u'student@example.edu',</span>
280
<span class="go"> password=u'student',classname=None,admin=0,book_id=1,</span>
281
<span class="go"> user_name=u'Joe Student',loan_date=datetime.datetime(2006, 7, 12, 0, 0),</span>
282
<span class="go"> id=1,title=u'Mustards I Have Known',published_year=u'1989',</span>
283
<span class="go"> authors=u'Jones')</span>
284
<span class="go">]</span></pre></div>
286
<p>If you join tables that have an identical column name, wrap your join
287
with <cite>with_labels</cite>, to disambiguate columns with their table name
288
(.c is short for .columns):</p>
289
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">with_labels</span><span class="p">(</span><span class="n">join1</span><span class="p">)</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keys</span><span class="p">()</span>
290
<span class="go">[u'users_name', u'users_email', u'users_password', </span>
291
<span class="go"> u'users_classname', u'users_admin', u'loans_book_id', </span>
292
<span class="go"> u'loans_user_name', u'loans_loan_date']</span></pre></div>
294
<p>You can also join directly to a labeled object:</p>
295
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">labeled_loans</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">with_labels</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="p">)</span>
296
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="p">,</span> <span class="n">labeled_loans</span><span class="p">,</span> <span class="n">isouter</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keys</span><span class="p">()</span>
297
<span class="go">[u'name', u'email', u'password', u'classname', </span>
298
<span class="go"> u'admin', u'loans_book_id', u'loans_user_name', u'loans_loan_date']</span></pre></div>
301
<div class="section" id="relationships">
302
<h2>Relationships<a class="headerlink" href="#relationships" title="Permalink to this headline">¶</a></h2>
303
<p>You can define relationships on SqlSoup classes:</p>
304
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">relate</span><span class="p">(</span><span class="s">'loans'</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="p">)</span></pre></div>
306
<p>These can then be used like a normal SA property:</p>
307
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s">'Joe Student'</span><span class="p">)</span><span class="o">.</span><span class="n">loans</span>
308
<span class="go">[MappedLoans(book_id=1,user_name=u'Joe Student',</span>
309
<span class="go"> loan_date=datetime.datetime(2006, 7, 12, 0, 0))]</span></pre></div>
311
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">~</span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">any</span><span class="p">())</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
312
<span class="go">[MappedUsers(name=u'Bhargan Basepair',</span>
313
<span class="go"> email='basepair+nospam@example.edu',</span>
314
<span class="go"> password=u'basepair',classname=None,admin=1)]</span></pre></div>
316
<p>relate can take any options that the relationship function
317
accepts in normal mapper definition:</p>
318
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">del</span> <span class="n">db</span><span class="o">.</span><span class="n">_cache</span><span class="p">[</span><span class="s">'users'</span><span class="p">]</span>
319
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">users</span><span class="o">.</span><span class="n">relate</span><span class="p">(</span><span class="s">'loans'</span><span class="p">,</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="n">db</span><span class="o">.</span><span class="n">loans</span><span class="o">.</span><span class="n">loan_date</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">'all, delete-orphan'</span><span class="p">)</span></pre></div>
322
<div class="section" id="advanced-use">
323
<h2>Advanced Use<a class="headerlink" href="#advanced-use" title="Permalink to this headline">¶</a></h2>
324
<div class="section" id="sessions-transations-and-application-integration">
325
<h3>Sessions, Transations and Application Integration<a class="headerlink" href="#sessions-transations-and-application-integration" title="Permalink to this headline">¶</a></h3>
326
<p><strong>Note:</strong> please read and understand this section thoroughly
327
before using SqlSoup in any web application.</p>
328
<p>SqlSoup uses a ScopedSession to provide thread-local sessions.
329
You can get a reference to the current one like this:</p>
330
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">session</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">session</span></pre></div>
332
<p>The default session is available at the module level in SQLSoup,
334
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.sqlsoup</span> <span class="kn">import</span> <span class="n">Session</span></pre></div>
336
<p>The configuration of this session is <tt class="docutils literal"><span class="pre">autoflush=True</span></tt>,
337
<tt class="docutils literal"><span class="pre">autocommit=False</span></tt>. This means when you work with the SqlSoup
338
object, you need to call <tt class="docutils literal"><span class="pre">db.commit()</span></tt> in order to have
339
changes persisted. You may also call <tt class="docutils literal"><span class="pre">db.rollback()</span></tt> to roll
341
<p>Since the SqlSoup object’s Session automatically enters into a
342
transaction as soon as it’s used, it is <em>essential</em> that you
343
call <tt class="docutils literal"><span class="pre">commit()</span></tt> or <tt class="docutils literal"><span class="pre">rollback()</span></tt> on it when the work within a
344
thread completes. This means all the guidelines for web
345
application integration at <a class="reference internal" href="../session.html#session-lifespan"><em>Lifespan of a Contextual Session</em></a> must be
347
<p>The SqlSoup object can have any session or scoped session
348
configured onto it. This is of key importance when integrating
349
with existing code or frameworks such as Pylons. If your
350
application already has a <tt class="docutils literal"><span class="pre">Session</span></tt> configured, pass it to
351
your SqlSoup object:</p>
352
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">myapplication</span> <span class="kn">import</span> <span class="n">Session</span>
353
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">SqlSoup</span><span class="p">(</span><span class="n">session</span><span class="o">=</span><span class="n">Session</span><span class="p">)</span></pre></div>
355
<p>If the <tt class="docutils literal"><span class="pre">Session</span></tt> is configured with <tt class="docutils literal"><span class="pre">autocommit=True</span></tt>, use
356
<tt class="docutils literal"><span class="pre">flush()</span></tt> instead of <tt class="docutils literal"><span class="pre">commit()</span></tt> to persist changes - in this
357
case, the <tt class="docutils literal"><span class="pre">Session</span></tt> closes out its transaction immediately and
358
no external management is needed. <tt class="docutils literal"><span class="pre">rollback()</span></tt> is also not
359
available. Configuring a new SQLSoup object in “autocommit” mode
361
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">scoped_session</span><span class="p">,</span> <span class="n">sessionmaker</span>
362
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">SqlSoup</span><span class="p">(</span><span class="s">'sqlite://'</span><span class="p">,</span> <span class="n">session</span><span class="o">=</span><span class="n">scoped_session</span><span class="p">(</span><span class="n">sessionmaker</span><span class="p">(</span><span class="n">autoflush</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">expire_on_commit</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">autocommit</span><span class="o">=</span><span class="bp">True</span><span class="p">)))</span></pre></div>
365
<div class="section" id="mapping-arbitrary-selectables">
366
<h3>Mapping arbitrary Selectables<a class="headerlink" href="#mapping-arbitrary-selectables" title="Permalink to this headline">¶</a></h3>
367
<p>SqlSoup can map any SQLAlchemy <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.Selectable" title="sqlalchemy.sql.expression.Selectable"><tt class="xref py py-class docutils literal"><span class="pre">Selectable</span></tt></a> with the map
368
method. Let’s map an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">expression.select()</span></tt></a> object that uses an aggregate
369
function; we’ll use the SQLAlchemy <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> that SqlSoup
370
introspected as the basis. (Since we’re not mapping to a simple
371
table or join, we need to tell SQLAlchemy how to find the
372
<em>primary key</em> which just needs to be unique within the select,
373
and not necessarily correspond to a <em>real</em> PK in the database.):</p>
374
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span>
375
<span class="gp">>>> </span><span class="n">b</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">books</span><span class="o">.</span><span class="n">_table</span>
376
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">b</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">published_year</span><span class="p">,</span> <span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="s">'*'</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'n'</span><span class="p">)],</span> <span class="n">from_obj</span><span class="o">=</span><span class="p">[</span><span class="n">b</span><span class="p">],</span> <span class="n">group_by</span><span class="o">=</span><span class="p">[</span><span class="n">b</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">published_year</span><span class="p">])</span>
377
<span class="gp">>>> </span><span class="n">s</span> <span class="o">=</span> <span class="n">s</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">'years_with_count'</span><span class="p">)</span>
378
<span class="gp">>>> </span><span class="n">years_with_count</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">map</span><span class="p">(</span><span class="n">s</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="p">[</span><span class="n">s</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">published_year</span><span class="p">])</span>
379
<span class="gp">>>> </span><span class="n">years_with_count</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">published_year</span><span class="o">=</span><span class="s">'1989'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
380
<span class="go">[MappedBooks(published_year=u'1989',n=1)]</span></pre></div>
382
<p>Obviously if we just wanted to get a list of counts associated with
383
book years once, raw SQL is going to be less work. The advantage of
384
mapping a Select is reusability, both standalone and in Joins. (And if
385
you go to full SQLAlchemy, you can perform mappings like this directly
386
to your object models.)</p>
387
<p>An easy way to save mapped selectables like this is to just hang them on
389
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">years_with_count</span> <span class="o">=</span> <span class="n">years_with_count</span></pre></div>
391
<p>Python is flexible like that!</p>
393
<div class="section" id="raw-sql">
394
<h3>Raw SQL<a class="headerlink" href="#raw-sql" title="Permalink to this headline">¶</a></h3>
395
<p>SqlSoup works fine with SQLAlchemy’s text construct, described
396
in <a class="reference internal" href="../../core/tutorial.html#sqlexpression-text"><em>Using Text</em></a>. You can also execute textual SQL
397
directly using the <cite>execute()</cite> method, which corresponds to the
398
<cite>execute()</cite> method on the underlying <cite>Session</cite>. Expressions here
399
are expressed like <tt class="docutils literal"><span class="pre">text()</span></tt> constructs, using named parameters
401
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">rp</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select name, email from users where name like :name order by name'</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'%Bhargan%'</span><span class="p">)</span>
402
<span class="gp">>>> </span><span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="n">email</span> <span class="ow">in</span> <span class="n">rp</span><span class="o">.</span><span class="n">fetchall</span><span class="p">():</span> <span class="k">print</span> <span class="n">name</span><span class="p">,</span> <span class="n">email</span>
403
<span class="go">Bhargan Basepair basepair+nospam@example.edu</span></pre></div>
405
<p>Or you can get at the current transaction’s connection using
406
<cite>connection()</cite>. This is the raw connection object which can
407
accept any sort of SQL expression or raw SQL string passed to
409
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">conn</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">connection</span><span class="p">()</span>
410
<span class="gp">>>> </span><span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"'select name, email from users where name like ? order by name'"</span><span class="p">,</span> <span class="s">'%Bhargan%'</span><span class="p">)</span></pre></div>
413
<div class="section" id="dynamic-table-names">
414
<h3>Dynamic table names<a class="headerlink" href="#dynamic-table-names" title="Permalink to this headline">¶</a></h3>
415
<p>You can load a table whose name is specified at runtime with the
417
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">tablename</span> <span class="o">=</span> <span class="s">'loans'</span>
418
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">entity</span><span class="p">(</span><span class="n">tablename</span><span class="p">)</span> <span class="o">==</span> <span class="n">db</span><span class="o">.</span><span class="n">loans</span>
419
<span class="go">True</span></pre></div>
421
<p>entity() also takes an optional schema argument. If none is
422
specified, the default schema is used.</p>
425
<div class="section" id="sqlsoup-api">
426
<h2>SqlSoup API<a class="headerlink" href="#sqlsoup-api" title="Permalink to this headline">¶</a></h2>
428
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup">
429
<em class="property">class </em><tt class="descclassname">sqlalchemy.ext.sqlsoup.</tt><tt class="descname">SqlSoup</tt><big>(</big><em>engine_or_metadata</em>, <em>base=<type 'object'></em>, <em>session=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="Permalink to this definition">¶</a></dt>
430
<dd><p>Represent an ORM-wrapped database resource.</p>
432
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.__init__">
433
<tt class="descname">__init__</tt><big>(</big><em>engine_or_metadata</em>, <em>base=<type 'object'></em>, <em>session=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.__init__" title="Permalink to this definition">¶</a></dt>
434
<dd><p>Initialize a new <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>.</p>
435
<table class="docutils field-list" frame="void" rules="none">
436
<col class="field-name" />
437
<col class="field-body" />
439
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
440
<li><strong>engine_or_metadata</strong> – a string database URL, <a class="reference internal" href="../../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>
441
or <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a> object to associate with. If the
442
argument is a <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>, it should be <em>bound</em>
443
to an <a class="reference internal" href="../../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>.</li>
444
<li><strong>base</strong> – a class which will serve as the default class for
445
returned mapped classes. Defaults to <tt class="docutils literal"><span class="pre">object</span></tt>.</li>
446
<li><strong>session</strong> – a <a class="reference internal" href="../session.html#sqlalchemy.orm.scoping.ScopedSession" title="sqlalchemy.orm.scoping.ScopedSession"><tt class="xref py py-class docutils literal"><span class="pre">ScopedSession</span></tt></a> or <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> with
447
which to associate ORM operations for this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a> instance.
448
If <tt class="xref docutils literal"><span class="pre">None</span></tt>, a <a class="reference internal" href="../session.html#sqlalchemy.orm.scoping.ScopedSession" title="sqlalchemy.orm.scoping.ScopedSession"><tt class="xref py py-class docutils literal"><span class="pre">ScopedSession</span></tt></a> that’s local to this
457
<dl class="attribute">
458
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.bind">
459
<tt class="descname">bind</tt><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.bind" title="Permalink to this definition">¶</a></dt>
460
<dd><p>The <a class="reference internal" href="../../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> associated with this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>.</p>
464
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.clear">
465
<tt class="descname">clear</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.clear" title="Permalink to this definition">¶</a></dt>
466
<dd><p>Synonym for <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup.expunge_all" title="sqlalchemy.ext.sqlsoup.SqlSoup.expunge_all"><tt class="xref py py-meth docutils literal"><span class="pre">SqlSoup.expunge_all()</span></tt></a>.</p>
470
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.commit">
471
<tt class="descname">commit</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.commit" title="Permalink to this definition">¶</a></dt>
472
<dd><p>Commit the current transaction.</p>
473
<p>See <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">Session.commit()</span></tt></a>.</p>
477
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.connection">
478
<tt class="descname">connection</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.connection" title="Permalink to this definition">¶</a></dt>
479
<dd><p>Return the current <a class="reference internal" href="../../core/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> in use by the current transaction.</p>
483
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.delete">
484
<tt class="descname">delete</tt><big>(</big><em>instance</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.delete" title="Permalink to this definition">¶</a></dt>
485
<dd><p>Mark an instance as deleted.</p>
488
<dl class="attribute">
489
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.engine">
490
<tt class="descname">engine</tt><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.engine" title="Permalink to this definition">¶</a></dt>
491
<dd><p>The <a class="reference internal" href="../../core/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> associated with this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>.</p>
495
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.entity">
496
<tt class="descname">entity</tt><big>(</big><em>attr</em>, <em>schema=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.entity" title="Permalink to this definition">¶</a></dt>
497
<dd><p>Return the named entity from this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>, or
498
create if not present.</p>
499
<p>For more generalized mapping, see <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup.map_to" title="sqlalchemy.ext.sqlsoup.SqlSoup.map_to"><tt class="xref py py-meth docutils literal"><span class="pre">map_to()</span></tt></a>.</p>
503
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.execute">
504
<tt class="descname">execute</tt><big>(</big><em>stmt</em>, <em>**params</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.execute" title="Permalink to this definition">¶</a></dt>
505
<dd><p>Execute a SQL statement.</p>
506
<p>The statement may be a string SQL string,
507
an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">expression.select()</span></tt></a> construct, or an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><tt class="xref py py-func docutils literal"><span class="pre">expression.text()</span></tt></a>
512
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.expunge">
513
<tt class="descname">expunge</tt><big>(</big><em>instance</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.expunge" title="Permalink to this definition">¶</a></dt>
514
<dd><p>Remove an instance from the <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>.</p>
515
<p>See <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session.expunge" title="sqlalchemy.orm.session.Session.expunge"><tt class="xref py py-meth docutils literal"><span class="pre">Session.expunge()</span></tt></a>.</p>
519
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.expunge_all">
520
<tt class="descname">expunge_all</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.expunge_all" title="Permalink to this definition">¶</a></dt>
521
<dd><p>Clear all objects from the current <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>.</p>
522
<p>See <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session.expunge_all" title="sqlalchemy.orm.session.Session.expunge_all"><tt class="xref py py-meth docutils literal"><span class="pre">Session.expunge_all()</span></tt></a>.</p>
526
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.flush">
527
<tt class="descname">flush</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.flush" title="Permalink to this definition">¶</a></dt>
528
<dd><p>Flush pending changes to the database.</p>
529
<p>See <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal"><span class="pre">Session.flush()</span></tt></a>.</p>
533
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.join">
534
<tt class="descname">join</tt><big>(</big><em>left</em>, <em>right</em>, <em>onclause=None</em>, <em>isouter=False</em>, <em>base=None</em>, <em>**mapper_args</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.join" title="Permalink to this definition">¶</a></dt>
535
<dd><p>Create an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.join" title="sqlalchemy.sql.expression.join"><tt class="xref py py-func docutils literal"><span class="pre">expression.join()</span></tt></a> and map to it.</p>
536
<p>The class and its mapping are not cached and will
537
be discarded once dereferenced (as of 0.6.6).</p>
538
<table class="docutils field-list" frame="void" rules="none">
539
<col class="field-name" />
540
<col class="field-body" />
542
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
543
<li><strong>left</strong> – a mapped class or table object.</li>
544
<li><strong>right</strong> – a mapped class or table object.</li>
545
<li><strong>onclause</strong> – optional “ON” clause construct..</li>
546
<li><strong>isouter</strong> – if True, the join will be an OUTER join.</li>
547
<li><strong>base</strong> – a Python class which will be used as the
548
base for the mapped class. If <tt class="xref docutils literal"><span class="pre">None</span></tt>, the “base”
549
argument specified by this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>
550
instance’s constructor will be used, which defaults to
551
<tt class="docutils literal"><span class="pre">object</span></tt>.</li>
552
<li><strong>mapper_args</strong> – Dictionary of arguments which will
553
be passed directly to <a class="reference internal" href="../mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">orm.mapper()</span></tt></a>.</li>
562
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.map">
563
<tt class="descname">map</tt><big>(</big><em>selectable</em>, <em>base=None</em>, <em>**mapper_args</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.map" title="Permalink to this definition">¶</a></dt>
564
<dd><p>Map a selectable directly.</p>
565
<p>The class and its mapping are not cached and will
566
be discarded once dereferenced (as of 0.6.6).</p>
567
<table class="docutils field-list" frame="void" rules="none">
568
<col class="field-name" />
569
<col class="field-body" />
571
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
572
<li><strong>selectable</strong> – an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">expression.select()</span></tt></a> construct.</li>
573
<li><strong>base</strong> – a Python class which will be used as the
574
base for the mapped class. If <tt class="xref docutils literal"><span class="pre">None</span></tt>, the “base”
575
argument specified by this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>
576
instance’s constructor will be used, which defaults to
577
<tt class="docutils literal"><span class="pre">object</span></tt>.</li>
578
<li><strong>mapper_args</strong> – Dictionary of arguments which will
579
be passed directly to <a class="reference internal" href="../mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">orm.mapper()</span></tt></a>.</li>
588
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.map_to">
589
<tt class="descname">map_to</tt><big>(</big><em>attrname</em>, <em>tablename=None</em>, <em>selectable=None</em>, <em>schema=None</em>, <em>base=None</em>, <em>mapper_args=frozendict({})</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.map_to" title="Permalink to this definition">¶</a></dt>
590
<dd><p>Configure a mapping to the given attrname.</p>
591
<p>This is the “master” method that can be used to create any
593
<p>(new in 0.6.6)</p>
594
<table class="docutils field-list" frame="void" rules="none">
595
<col class="field-name" />
596
<col class="field-body" />
598
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
599
<li><strong>attrname</strong> – String attribute name which will be
600
established as an attribute on this :class:.`.SqlSoup`
602
<li><strong>base</strong> – a Python class which will be used as the
603
base for the mapped class. If <tt class="xref docutils literal"><span class="pre">None</span></tt>, the “base”
604
argument specified by this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>
605
instance’s constructor will be used, which defaults to
606
<tt class="docutils literal"><span class="pre">object</span></tt>.</li>
607
<li><strong>mapper_args</strong> – Dictionary of arguments which will
608
be passed directly to <a class="reference internal" href="../mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">orm.mapper()</span></tt></a>.</li>
609
<li><strong>tablename</strong> – String name of a <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> to be
610
reflected. If a <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> is already available,
611
use the <tt class="docutils literal"><span class="pre">selectable</span></tt> argument. This argument is
612
mutually exclusive versus the <tt class="docutils literal"><span class="pre">selectable</span></tt> argument.</li>
613
<li><strong>selectable</strong> – a <a class="reference internal" href="../../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>, <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><tt class="xref py py-class docutils literal"><span class="pre">Join</span></tt></a>, or
614
<a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><tt class="xref py py-class docutils literal"><span class="pre">Select</span></tt></a> object which will be mapped. This
615
argument is mutually exclusive versus the <tt class="docutils literal"><span class="pre">tablename</span></tt>
617
<li><strong>schema</strong> – String schema name to use if the
618
<tt class="docutils literal"><span class="pre">tablename</span></tt> argument is present.</li>
627
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.rollback">
628
<tt class="descname">rollback</tt><big>(</big><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.rollback" title="Permalink to this definition">¶</a></dt>
629
<dd><p>Rollback the current transction.</p>
630
<p>See <a class="reference internal" href="../session.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-meth docutils literal"><span class="pre">Session.rollback()</span></tt></a>.</p>
634
<dt id="sqlalchemy.ext.sqlsoup.SqlSoup.with_labels">
635
<tt class="descname">with_labels</tt><big>(</big><em>selectable</em>, <em>base=None</em>, <em>**mapper_args</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.sqlsoup.SqlSoup.with_labels" title="Permalink to this definition">¶</a></dt>
636
<dd><p>Map a selectable directly, wrapping the
637
selectable in a subquery with labels.</p>
638
<p>The class and its mapping are not cached and will
639
be discarded once dereferenced (as of 0.6.6).</p>
640
<table class="docutils field-list" frame="void" rules="none">
641
<col class="field-name" />
642
<col class="field-body" />
644
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
645
<li><strong>selectable</strong> – an <a class="reference internal" href="../../core/expression_api.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">expression.select()</span></tt></a> construct.</li>
646
<li><strong>base</strong> – a Python class which will be used as the
647
base for the mapped class. If <tt class="xref docutils literal"><span class="pre">None</span></tt>, the “base”
648
argument specified by this <a class="reference internal" href="#sqlalchemy.ext.sqlsoup.SqlSoup" title="sqlalchemy.ext.sqlsoup.SqlSoup"><tt class="xref py py-class docutils literal"><span class="pre">SqlSoup</span></tt></a>
649
instance’s constructor will be used, which defaults to
650
<tt class="docutils literal"><span class="pre">object</span></tt>.</li>
651
<li><strong>mapper_args</strong> – Dictionary of arguments which will
652
be passed directly to <a class="reference internal" href="../mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">orm.mapper()</span></tt></a>.</li>
670
<div class="bottomnav">
672
<div class="prevnext">
674
<a href="horizontal_shard.html" title="previous chapter">Horizontal Sharding</a>
676
<a href="../examples.html" title="next chapter">Examples</a>
679
<div class="doc_copyright">
680
© <a href="../../copyright.html">Copyright</a> 2007-2011, the SQLAlchemy authors and contributors.
681
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.1.
added
removed
doc/orm/extensions/sqlsoup.html
