~ubuntu-branches/debian/jessie/sqlalchemy/jessie : revision 35

1

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

2

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

3

4

5

<head>

6

7

8

<title>

9

10

11

Frequently Asked Questions

12

—

13

SQLAlchemy 0.8 Documentation

14

15

</title>

16

17

18

19

20

21

var DOCUMENTATION_OPTIONS = {

22

URL_ROOT: './',

23

VERSION: '0.8.3',

24

COLLAPSE_MODINDEX: false,

25

FILE_SUFFIX: '.html'

26

};

27

</script>

28

29

30

31

32

33

34

35

36

37

</head>

38

<body>

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

<h1>SQLAlchemy 0.8 Documentation</h1>

56

57

58

Search:

59

60

61

62

63

</form>

64

</div>

65

66

67

Release: 0.8.3 | Release Date: October 26, 2013

68

69

70

</div>

71

72

</div>

73

74

75

76

<ul>

77

78

<li>

79

<a href="contents.html">Table of Contents</a> |

80

<a href="genindex.html">Index</a>

81

| <a href="_sources/faq.txt">view source

82

</li>

83

</ul>

84

</div>

85

86

87

<a href="index.html">SQLAlchemy 0.8 Documentation</a>

88

»

89

Frequently Asked Questions

90

91

92

<h2>

93

94

Frequently Asked Questions

95

96

</h2>

97

</div>

98

99

</div>

100

101

102

103

104

<h3><a href="index.html">Table of Contents</a></h3>

105

<ul>

106

<li><a class="reference internal" href="#">Frequently Asked Questions</a><ul>

107

<li><a class="reference internal" href="#connections-engines">Connections / Engines</a><ul>

108

<li><a class="reference internal" href="#how-do-i-configure-logging">How do I configure logging?</a></li>

109

<li><a class="reference internal" href="#how-do-i-pool-database-connections-are-my-connections-pooled">How do I pool database connections? Are my connections pooled?</a></li>

110

<li><a class="reference internal" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api">How do I pass custom connect arguments to my database API?</a></li>

111

<li><a class="reference internal" href="#mysql-server-has-gone-away">“MySQL Server has gone away”</a></li>

112

<li><a class="reference internal" href="#why-does-sqlalchemy-issue-so-many-rollbacks">Why does SQLAlchemy issue so many ROLLBACKs?</a><ul>

113

<li><a class="reference internal" href="#i-m-on-myisam-how-do-i-turn-it-off">I’m on MyISAM - how do I turn it off?</a></li>

114

<li><a class="reference internal" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits">I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?</a></li>

115

</ul>

116

</li>

117

<li><a class="reference internal" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working">I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!</a></li>

118

<li><a class="reference internal" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine">How do I get at the raw DBAPI connection when using an Engine?</a></li>

119

</ul>

120

</li>

121

<li><a class="reference internal" href="#metadata-schema">MetaData / Schema</a><ul>

122

<li><a class="reference internal" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all">My program is hanging when I say <tt class="docutils literal">table.drop()</tt> / <tt class="docutils literal">metadata.drop_all()</tt></a></li>

123

<li><a class="reference internal" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></li>

124

<li><a class="reference internal" href="#how-can-i-sort-table-objects-in-order-of-their-dependency">How can I sort Table objects in order of their dependency?</a></li>

125

<li><a class="reference internal" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></li>

126

<li><a class="reference internal" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations">How can I subclass Table/Column to provide certain behaviors/configurations?</a></li>

127

</ul>

128

</li>

129

<li><a class="reference internal" href="#sql-expressions">SQL Expressions</a><ul>

130

<li><a class="reference internal" href="#why-does-col-in-produce-col-col-why-not-1-0">Why does <tt class="docutils literal">.col.in_([])</tt> Produce <tt class="docutils literal">col != col</tt>? Why not <tt class="docutils literal">1=0</tt>?</a></li>

131

</ul>

132

</li>

133

<li><a class="reference internal" href="#orm-configuration">ORM Configuration</a><ul>

134

<li><a class="reference internal" href="#how-do-i-map-a-table-that-has-no-primary-key">How do I map a table that has no primary key?</a></li>

135

<li><a class="reference internal" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar">How do I configure a Column that is a Python reserved word or similar?</a></li>

136

<li><a class="reference internal" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></li>

137

<li><a class="reference internal" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys">I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal">and_()</tt> or <tt class="docutils literal">or_()</tt>, and I am getting an error message about foreign keys.</a></li>

138

</ul>

139

</li>

140

<li><a class="reference internal" href="#sessions-queries">Sessions / Queries</a><ul>

141

<li><a class="reference internal" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a><ul>

142

<li><a class="reference internal" href="#but-why-does-flush-insist-on-issuing-a-rollback">But why does flush() insist on issuing a ROLLBACK?</a></li>

143

<li><a class="reference internal" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></li>

144

</ul>

145

</li>

146

<li><a class="reference internal" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow">I’m inserting 400,000 rows with the ORM and it’s really slow!</a></li>

147

<li><a class="reference internal" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query">How do I make a Query that always adds a certain filter to every query?</a></li>

148

<li><a class="reference internal" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></li>

149

<li><a class="reference internal" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join">I’m using <tt class="docutils literal">joinedload()</tt> or <tt class="docutils literal">lazy=False</tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></li>

150

<li><a class="reference internal" href="#query-has-no-len-why-not">Query has no <tt class="docutils literal">__len__()</tt>, why not?</a></li>

151

<li><a class="reference internal" href="#how-do-i-use-textual-sql-with-orm-queries">How Do I use Textual SQL with ORM Queries?</a></li>

152

<li><a class="reference internal" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection">I’m calling <tt class="docutils literal">Session.delete(myobject)</tt> and it isn’t removed from the parent collection!</a></li>

153

<li><a class="reference internal" href="#why-isnt-my-init-called-when-i-load-objects">why isnt my <tt class="docutils literal">__init__()</tt> called when I load objects?</a></li>

154

<li><a class="reference internal" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm">how do I use ON DELETE CASCADE with SA’s ORM?</a></li>

155

<li><a class="reference internal" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal">None</tt> - shouldn’t it have loaded Foo with id #7?</a></li>

156

<li><a class="reference internal" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></li>

157

</ul>

158

</li>

159

</ul>

160

</li>

161

</ul>

162

163

164

165

166

<h4>Quick Search</h4>

167

168

169

170

171

172

</form>

173

174

175

</div>

176

177

178

179

180

<h1>Frequently Asked Questions<a class="headerlink" href="#frequently-asked-questions" title="Permalink to this headline">¶</a></h1>

181

182

183

<li><a class="reference internal" href="#connections-engines" id="id1">Connections / Engines</a><ul>

184

<li><a class="reference internal" href="#how-do-i-configure-logging" id="id2">How do I configure logging?</a></li>

185

<li><a class="reference internal" href="#how-do-i-pool-database-connections-are-my-connections-pooled" id="id3">How do I pool database connections? Are my connections pooled?</a></li>

186

<li><a class="reference internal" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api" id="id4">How do I pass custom connect arguments to my database API?</a></li>

187

<li><a class="reference internal" href="#mysql-server-has-gone-away" id="id5">“MySQL Server has gone away”</a></li>

188

<li><a class="reference internal" href="#why-does-sqlalchemy-issue-so-many-rollbacks" id="id6">Why does SQLAlchemy issue so many ROLLBACKs?</a><ul>

189

<li><a class="reference internal" href="#i-m-on-myisam-how-do-i-turn-it-off" id="id7">I’m on MyISAM - how do I turn it off?</a></li>

190

<li><a class="reference internal" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits" id="id8">I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?</a></li>

191

</ul>

192

</li>

193

<li><a class="reference internal" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working" id="id9">I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!</a></li>

194

<li><a class="reference internal" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine" id="id10">How do I get at the raw DBAPI connection when using an Engine?</a></li>

195

</ul>

196

</li>

197

<li><a class="reference internal" href="#metadata-schema" id="id11">MetaData / Schema</a><ul>

198

<li><a class="reference internal" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" id="id12">My program is hanging when I say <tt class="docutils literal">table.drop()</tt> / <tt class="docutils literal">metadata.drop_all()</tt></a></li>

199

<li><a class="reference internal" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" id="id13">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></li>

200

<li><a class="reference internal" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" id="id14">How can I sort Table objects in order of their dependency?</a></li>

201

<li><a class="reference internal" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" id="id15">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></li>

202

<li><a class="reference internal" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" id="id16">How can I subclass Table/Column to provide certain behaviors/configurations?</a></li>

203

</ul>

204

</li>

205

<li><a class="reference internal" href="#sql-expressions" id="id17">SQL Expressions</a><ul>

206

<li><a class="reference internal" href="#why-does-col-in-produce-col-col-why-not-1-0" id="id18">Why does <tt class="docutils literal">.col.in_([])</tt> Produce <tt class="docutils literal">col != col</tt>? Why not <tt class="docutils literal">1=0</tt>?</a></li>

207

</ul>

208

</li>

209

<li><a class="reference internal" href="#orm-configuration" id="id19">ORM Configuration</a><ul>

210

<li><a class="reference internal" href="#how-do-i-map-a-table-that-has-no-primary-key" id="id20">How do I map a table that has no primary key?</a></li>

211

<li><a class="reference internal" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" id="id21">How do I configure a Column that is a Python reserved word or similar?</a></li>

212

<li><a class="reference internal" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" id="id22">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></li>

213

<li><a class="reference internal" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" id="id23">I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal">and_()</tt> or <tt class="docutils literal">or_()</tt>, and I am getting an error message about foreign keys.</a></li>

214

</ul>

215

</li>

216

<li><a class="reference internal" href="#sessions-queries" id="id24">Sessions / Queries</a><ul>

217

<li><a class="reference internal" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" id="id25">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a><ul>

218

<li><a class="reference internal" href="#but-why-does-flush-insist-on-issuing-a-rollback" id="id26">But why does flush() insist on issuing a ROLLBACK?</a></li>

219

<li><a class="reference internal" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" id="id27">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></li>

220

</ul>

221

</li>

222

<li><a class="reference internal" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow" id="id28">I’m inserting 400,000 rows with the ORM and it’s really slow!</a></li>

223

<li><a class="reference internal" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" id="id29">How do I make a Query that always adds a certain filter to every query?</a></li>

224

<li><a class="reference internal" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" id="id30">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></li>

225

<li><a class="reference internal" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" id="id31">I’m using <tt class="docutils literal">joinedload()</tt> or <tt class="docutils literal">lazy=False</tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></li>

226

<li><a class="reference internal" href="#query-has-no-len-why-not" id="id32">Query has no <tt class="docutils literal">__len__()</tt>, why not?</a></li>

227

<li><a class="reference internal" href="#how-do-i-use-textual-sql-with-orm-queries" id="id33">How Do I use Textual SQL with ORM Queries?</a></li>

228

<li><a class="reference internal" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" id="id34">I’m calling <tt class="docutils literal">Session.delete(myobject)</tt> and it isn’t removed from the parent collection!</a></li>

229

<li><a class="reference internal" href="#why-isnt-my-init-called-when-i-load-objects" id="id35">why isnt my <tt class="docutils literal">__init__()</tt> called when I load objects?</a></li>

230

<li><a class="reference internal" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" id="id36">how do I use ON DELETE CASCADE with SA’s ORM?</a></li>

231

<li><a class="reference internal" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" id="id37">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal">None</tt> - shouldn’t it have loaded Foo with id #7?</a></li>

232

<li><a class="reference internal" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" id="id38">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></li>

233

</ul>

234

</li>

235

</ul>

236

</div>

237

238

<h2>Connections / Engines<a class="headerlink" href="#connections-engines" title="Permalink to this headline">¶</a></h2>

239

240

<h3>How do I configure logging?<a class="headerlink" href="#how-do-i-configure-logging" title="Permalink to this headline">¶</a></h3>

241

See <a class="reference internal" href="core/engines.html#dbengine-logging">Configuring Logging</a>.

242

</div>

243

244

<h3>How do I pool database connections? Are my connections pooled?<a class="headerlink" href="#how-do-i-pool-database-connections-are-my-connections-pooled" title="Permalink to this headline">¶</a></h3>

245

SQLAlchemy performs application-level connection pooling automatically

246

in most cases. With the exception of SQLite, a <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal">Engine</tt></a> object

247

refers to a <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.QueuePool" title="sqlalchemy.pool.QueuePool"><tt class="xref py py-class docutils literal">QueuePool</tt></a> as a source of connectivity.

248

For more detail, see <a class="reference internal" href="core/engines.html">Engine Configuration</a> and <a class="reference internal" href="core/pooling.html">Connection Pooling</a>.

249

</div>

250

251

<h3>How do I pass custom connect arguments to my database API?<a class="headerlink" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api" title="Permalink to this headline">¶</a></h3>

252

The <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal">create_engine()</tt></a> call accepts additional arguments either

253

directly via the <tt class="docutils literal">connect_args</tt> keyword argument:

254

<div class="highlight-python"><div class="highlight"><pre>e = create_engine("mysql://scott:tiger@localhost/test",

255

connect_args={"encoding": "utf8"})</pre></div>

256

</div>

257

Or for basic string and integer arguments, they can usually be specified

258

in the query string of the URL:

259

<div class="highlight-python"><div class="highlight"><pre>e = create_engine("mysql://scott:tiger@localhost/test?encoding=utf8")</pre></div>

260

</div>

261

262

See also

263

<a class="reference internal" href="core/engines.html#custom-dbapi-args">Custom DBAPI connect() arguments</a>

264

</div>

265

</div>

266

267

<h3>“MySQL Server has gone away”<a class="headerlink" href="#mysql-server-has-gone-away" title="Permalink to this headline">¶</a></h3>

268

There are two major causes for this error:

269

1. The MySQL client closes connections which have been idle for a set period

270

of time, defaulting to eight hours. This can be avoided by using the <tt class="docutils literal">pool_recycle</tt>

271

setting with <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal">create_engine()</tt></a>, described at <a class="reference internal" href="dialects/mysql.html#mysql-connection-timeouts">Connection Timeouts</a>.

272

2. Usage of the MySQLdb <a class="reference internal" href="glossary.html#term-dbapi">DBAPI</a>, or a similar DBAPI, in a non-threadsafe manner, or in an otherwise

273

inappropriate way. The MySQLdb connection object is not threadsafe - this expands

274

out to any SQLAlchemy system that links to a single connection, which includes the ORM

275

<a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a>. For background

276

on how <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> should be used in a multithreaded environment,

277

see <a class="reference internal" href="orm/session.html#session-faq-threadsafe">Is the session thread-safe?</a>.

278

</div>

279

280

<h3>Why does SQLAlchemy issue so many ROLLBACKs?<a class="headerlink" href="#why-does-sqlalchemy-issue-so-many-rollbacks" title="Permalink to this headline">¶</a></h3>

281

SQLAlchemy currently assumes DBAPI connections are in “non-autocommit” mode -

282

this is the default behavior of the Python database API, meaning it

283

must be assumed that a transaction is always in progress. The

284

connection pool issues <tt class="docutils literal">connection.rollback()</tt> when a connection is returned.

285

This is so that any transactional resources remaining on the connection are

286

released. On a database like Postgresql or MSSQL where table resources are

287

aggressively locked, this is critical so that rows and tables don’t remain

288

locked within connections that are no longer in use. An application can

289

otherwise hang. It’s not just for locks, however, and is equally critical on

290

any database that has any kind of transaction isolation, including MySQL with

291

InnoDB. Any connection that is still inside an old transaction will return

292

stale data, if that data was already queried on that connection within

293

isolation. For background on why you might see stale data even on MySQL, see

294

<a class="reference external" href="http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html">http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html</a>

295

296

<h4>I’m on MyISAM - how do I turn it off?<a class="headerlink" href="#i-m-on-myisam-how-do-i-turn-it-off" title="Permalink to this headline">¶</a></h4>

297

The behavior of the connection pool’s connection return behavior can be

298

configured using <tt class="docutils literal">reset_on_return</tt>:

299

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import create_engine

300

from sqlalchemy.pool import QueuePool

301

302

engine = create_engine('mysql://scott:tiger@localhost/myisam_database', pool=QueuePool(reset_on_return=False))</pre></div>

303

</div>

304

</div>

305

306

<h4>I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?<a class="headerlink" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits" title="Permalink to this headline">¶</a></h4>

307

<tt class="docutils literal">reset_on_return</tt> accepts the values <tt class="docutils literal">commit</tt>, <tt class="docutils literal">rollback</tt> in addition

308

to <tt class="docutils literal">True</tt>, <tt class="docutils literal">False</tt>, and <tt class="docutils literal">None</tt>. Setting to <tt class="docutils literal">commit</tt> will cause

309

a COMMIT as any connection is returned to the pool:

310

<div class="highlight-python"><div class="highlight"><pre>engine = create_engine('mssql://scott:tiger@mydsn', pool=QueuePool(reset_on_return='commit'))</pre></div>

311

</div>

312

</div>

313

</div>

314

315

<h3>I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!<a class="headerlink" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working" title="Permalink to this headline">¶</a></h3>

316

If using a SQLite <tt class="docutils literal">:memory:</tt> database, or a version of SQLAlchemy prior

317

to version 0.7, the default connection pool is the <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal">SingletonThreadPool</tt></a>,

318

which maintains exactly one SQLite connection per thread. So two

319

connections in use in the same thread will actually be the same SQLite

320

connection. Make sure you’re not using a :memory: database and

321

use <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><tt class="xref py py-class docutils literal">NullPool</tt></a>, which is the default for non-memory databases in

322

current SQLAlchemy versions.

323

324

See also

325

<a class="reference internal" href="dialects/sqlite.html#pysqlite-threading-pooling">Threading/Pooling Behavior</a> - info on PySQLite’s behavior.

326

</div>

327

</div>

328

329

<h3>How do I get at the raw DBAPI connection when using an Engine?<a class="headerlink" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine" title="Permalink to this headline">¶</a></h3>

330

With a regular SA engine-level Connection, you can get at a pool-proxied

331

version of the DBAPI connection via the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.connection" title="sqlalchemy.engine.Connection.connection"><tt class="xref py py-attr docutils literal">Connection.connection</tt></a> attribute on

332

<a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a>, and for the really-real DBAPI connection you can call the

333

<tt class="xref py py-attr docutils literal">ConnectionFairy.connection</tt> attribute on that - but there should never be any need to access

334

the non-pool-proxied DBAPI connection, as all methods are proxied through:

335

<div class="highlight-python"><pre>engine = create_engine(...)

336

conn = engine.connect()

337

conn.connection.<do DBAPI things>

338

cursor = conn.connection.cursor(<DBAPI specific arguments..>)</pre>

339

</div>

340

You must ensure that you revert any isolation level settings or other

341

operation-specific settings on the connection back to normal before returning

342

it to the pool.

343

As an alternative to reverting settings, you can call the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.detach" title="sqlalchemy.engine.Connection.detach"><tt class="xref py py-meth docutils literal">Connection.detach()</tt></a> method on

344

either <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal">Connection</tt></a> or the proxied connection, which will de-associate

345

the connection from the pool such that it will be closed and discarded

346

when <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><tt class="xref py py-meth docutils literal">Connection.close()</tt></a> is called:

347

<div class="highlight-python"><pre>conn = engine.connect()

348

conn.detach() # detaches the DBAPI connection from the connection pool

349

conn.connection.<go nuts>

350

conn.close() # connection is closed for real, the pool replaces it with a new connection</pre>

351

</div>

352

</div>

353

</div>

354

355

<h2>MetaData / Schema<a class="headerlink" href="#metadata-schema" title="Permalink to this headline">¶</a></h2>

356

357

<h3>My program is hanging when I say <tt class="docutils literal">table.drop()</tt> / <tt class="docutils literal">metadata.drop_all()</tt><a class="headerlink" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" title="Permalink to this headline">¶</a></h3>

358

This usually corresponds to two conditions: 1. using PostgreSQL, which is really

359

strict about table locks, and 2. you have a connection still open which

360

contains locks on the table and is distinct from the connection being used for

361

the DROP statement. Heres the most minimal version of the pattern:

362

<div class="highlight-python"><div class="highlight"><pre>connection = engine.connect()

363

result = connection.execute(mytable.select())

364

365

mytable.drop(engine)</pre></div>

366

</div>

367

Above, a connection pool connection is still checked out; furthermore, the

368

result object above also maintains a link to this connection. If

369

“implicit execution” is used, the result will hold this connection opened until

370

the result object is closed or all rows are exhausted.

371

The call to <tt class="docutils literal">mytable.drop(engine)</tt> attempts to emit DROP TABLE on a second

372

connection procured from the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal">Engine</tt></a> which will lock.

373

The solution is to close out all connections before emitting DROP TABLE:

374

<div class="highlight-python"><div class="highlight"><pre>connection = engine.connect()

375

result = connection.execute(mytable.select())

376

377

# fully read result sets

378

result.fetchall()

379

380

# close connections

381

connection.close()

382

383

# now locks are removed

384

mytable.drop(engine)</pre></div>

385

</div>

386

</div>

387

388

<h3>Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?<a class="headerlink" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" title="Permalink to this headline">¶</a></h3>

389

General ALTER support isn’t present in SQLAlchemy directly. For special DDL

390

on an ad-hoc basis, the <a class="reference internal" href="core/ddl.html#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal">DDL</tt></a> and related constructs can be used.

391

See <a class="reference internal" href="core/ddl.html">Customizing DDL</a> for a discussion on this subject.

392

A more comprehensive option is to use schema migration tools, such as Alembic

393

or SQLAlchemy-Migrate; see <a class="reference internal" href="core/metadata.html#schema-migrations">Altering Schemas through Migrations</a> for discussion on this.

394

</div>

395

396

<h3>How can I sort Table objects in order of their dependency?<a class="headerlink" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" title="Permalink to this headline">¶</a></h3>

397

This is available via the <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables" title="sqlalchemy.schema.MetaData.sorted_tables"><tt class="xref py py-attr docutils literal">MetaData.sorted_tables</tt></a> function:

398

<div class="highlight-python"><pre>metadata = MetaData()

399

# ... add Table objects to metadata

400

ti = metadata.sorted_tables:

401

for t in ti:

402

print t</pre>

403

</div>

404

</div>

405

406

<h3>How can I get the CREATE TABLE/ DROP TABLE output as a string?<a class="headerlink" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" title="Permalink to this headline">¶</a></h3>

407

Modern SQLAlchemy has clause constructs which represent DDL operations. These

408

can be rendered to strings like any other SQL expression:

409

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy.schema import CreateTable

410

411

print CreateTable(mytable)</pre></div>

412

</div>

413

To get the string specific to a certain engine:

414

<div class="highlight-python"><div class="highlight"><pre>print CreateTable(mytable).compile(engine)</pre></div>

415

</div>

416

There’s also a special form of <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal">Engine</tt></a> that can let you dump an entire

417

metadata creation sequence, using this recipe:

418

<div class="highlight-python"><div class="highlight"><pre>def dump(sql, *multiparams, **params):

419

print sql.compile(dialect=engine.dialect)

420

engine = create_engine('postgresql://', strategy='mock', executor=dump)

421

metadata.create_all(engine, checkfirst=False)</pre></div>

422

</div>

423

The <a class="reference external" href="https://bitbucket.org/zzzeek/alembic">Alembic</a> tool also supports

424

an “offline” SQL generation mode that renders database migrations as SQL scripts.

425

</div>

426

427

<h3>How can I subclass Table/Column to provide certain behaviors/configurations?<a class="headerlink" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" title="Permalink to this headline">¶</a></h3>

428

<a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> and <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> are not good targets for direct subclassing.

429

However, there are simple ways to get on-construction behaviors using creation

430

functions, and behaviors related to the linkages between schema objects such as

431

constraint conventions or naming conventions using attachment events.

432

An example of many of these

433

techniques can be seen at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions">Naming Conventions</a>.

434

</div>

435

</div>

436

437

<h2>SQL Expressions<a class="headerlink" href="#sql-expressions" title="Permalink to this headline">¶</a></h2>

438

439

<h3>Why does <tt class="docutils literal">.col.in_([])</tt> Produce <tt class="docutils literal">col != col</tt>? Why not <tt class="docutils literal">1=0</tt>?<a class="headerlink" href="#why-does-col-in-produce-col-col-why-not-1-0" title="Permalink to this headline">¶</a></h3>

440

A little introduction to the issue. The IN operator in SQL, given a list of

441

elements to compare against a column, generally does not accept an empty list,

442

that is while it is valid to say:

443

<div class="highlight-python"><pre>column IN (1, 2, 3)</pre>

444

</div>

445

it’s not valid to say:

446

<div class="highlight-python"><pre>column IN ()</pre>

447

</div>

448

SQLAlchemy’s <tt class="xref py py-meth docutils literal">Operators.in_()</tt> operator, when given an empty list, produces this

449

expression:

450

<div class="highlight-python"><div class="highlight"><pre>column != column</pre></div>

451

</div>

452

As of version 0.6, it also produces a warning stating that a less efficient

453

comparison operation will be rendered. This expression is the only one that is

454

both database agnostic and produces correct results.

455

For example, the naive approach of “just evaluate to false, by comparing 1=0

456

or 1!=1”, does not handle nulls properly. An expression like:

457

<div class="highlight-python"><pre>NOT column != column</pre>

458

</div>

459

will not return a row when “column” is null, but an expression which does not

460

take the column into account:

461

462

</div>

463

will.

464

Closer to the mark is the following CASE expression:

465

<div class="highlight-python"><pre>CASE WHEN column IS NOT NULL THEN 1=0 ELSE NULL END</pre>

466

</div>

467

We don’t use this expression due to its verbosity, and its also not

468

typically accepted by Oracle within a WHERE clause - depending

469

on how you phrase it, you’ll either get “ORA-00905: missing keyword” or

470

“ORA-00920: invalid relational operator”. It’s also still less efficient than

471

just rendering SQL without the clause altogether (or not issuing the SQL at

472

all, if the statement is just a simple search).

473

The best approach therefore is to avoid the usage of IN given an argument list

474

of zero length. Instead, don’t emit the Query in the first place, if no rows

475

should be returned. The warning is best promoted to a full error condition

476

using the Python warnings filter (see <a class="reference external" href="http://docs.python.org/library/warnings.html">http://docs.python.org/library/warnings.html</a>).

477

</div>

478

</div>

479

480

<h2>ORM Configuration<a class="headerlink" href="#orm-configuration" title="Permalink to this headline">¶</a></h2>

481

482

<h3>How do I map a table that has no primary key?<a class="headerlink" href="#how-do-i-map-a-table-that-has-no-primary-key" title="Permalink to this headline">¶</a></h3>

483

In almost all cases, a table does have a so-called candidate key, which is a column or series

484

of columns that uniquely identify a row. If a table truly doesn’t have this, and has actual

485

fully duplicate rows, the table is not corresponding to <a class="reference external" href="http://en.wikipedia.org/wiki/First_normal_form">first normal form</a> and cannot be mapped. Otherwise, whatever columns comprise the best candidate key can be

486

applied directly to the mapper:

487

<div class="highlight-python"><div class="highlight"><pre>class SomeClass(Base):

488

__table__ = some_table_with_no_pk

489

__mapper_args__ = {

490

'primary_key':[some_table_with_no_pk.c.uid, some_table_with_no_pk.c.bar]

491

}</pre></div>

492

</div>

493

Better yet is when using fully declared table metadata, use the <tt class="docutils literal">primary_key=True</tt>

494

flag on those columns:

495

<div class="highlight-python"><div class="highlight"><pre>class SomeClass(Base):

496

__tablename__ = "some_table_with_no_pk"

497

498

uid = Column(Integer, primary_key=True)

499

bar = Column(String, primary_key=True)</pre></div>

500

</div>

501

All tables in a relational database should have primary keys. Even a many-to-many

502

association table - the primary key would be the composite of the two association

503

columns:

504

<div class="highlight-python"><pre>CREATE TABLE my_association (

505

user_id INTEGER REFERENCES user(id),

506

account_id INTEGER REFERENCES account(id),

507

PRIMARY KEY (user_id, account_id)

508

)</pre>

509

</div>

510

</div>

511

512

<h3>How do I configure a Column that is a Python reserved word or similar?<a class="headerlink" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" title="Permalink to this headline">¶</a></h3>

513

Column-based attributes can be given any name desired in the mapping. See

514

<a class="reference internal" href="orm/mapper_config.html#mapper-column-distinct-names">Naming Columns Distinctly from Attribute Names</a>.

515

</div>

516

517

<h3>How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?<a class="headerlink" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" title="Permalink to this headline">¶</a></h3>

518

This information is all available from the <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal">Mapper</tt></a> object.

519

To get at the <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal">Mapper</tt></a> for a particular mapped class, call the

520

<a class="reference internal" href="core/inspection.html#sqlalchemy.inspection.inspect" title="sqlalchemy.inspection.inspect"><tt class="xref py py-func docutils literal">inspect()</tt></a> function on it:

521

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import inspect

522

523

mapper = inspect(MyClass)</pre></div>

524

</div>

525

From there, all information about the class can be acquired using such methods as:

526

527

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.attrs" title="sqlalchemy.orm.mapper.Mapper.attrs"><tt class="xref py py-attr docutils literal">Mapper.attrs</tt></a> - a namespace of all mapped attributes. The attributes

528

themselves are instances of <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.interfaces.MapperProperty" title="sqlalchemy.orm.interfaces.MapperProperty"><tt class="xref py py-class docutils literal">MapperProperty</tt></a>, which contain additional

529

attributes that can lead to the mapped SQL expression or column, if applicable.</li>

530

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.column_attrs" title="sqlalchemy.orm.mapper.Mapper.column_attrs"><tt class="xref py py-attr docutils literal">Mapper.column_attrs</tt></a> - the mapped attribute namespace

531

limited to column and SQL expression attributes. You might want to use

532

<a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><tt class="xref py py-attr docutils literal">Mapper.columns</tt></a> to get at the <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects directly.</li>

533

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.relationships" title="sqlalchemy.orm.mapper.Mapper.relationships"><tt class="xref py py-attr docutils literal">Mapper.relationships</tt></a> - namespace of all <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty" title="sqlalchemy.orm.properties.RelationshipProperty"><tt class="xref py py-class docutils literal">RelationshipProperty</tt></a> attributes.</li>

534

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors" title="sqlalchemy.orm.mapper.Mapper.all_orm_descriptors"><tt class="xref py py-attr docutils literal">Mapper.all_orm_descriptors</tt></a> - namespace of all mapped attributes, plus user-defined

535

attributes defined using systems such as <a class="reference internal" href="orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property" title="sqlalchemy.ext.hybrid.hybrid_property"><tt class="xref py py-class docutils literal">hybrid_property</tt></a>, <a class="reference internal" href="orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy" title="sqlalchemy.ext.associationproxy.AssociationProxy"><tt class="xref py py-class docutils literal">AssociationProxy</tt></a> and others.</li>

536

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><tt class="xref py py-attr docutils literal">Mapper.columns</tt></a> - A namespace of <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects and other named

537

SQL expressions associated with the mapping.</li>

538

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><tt class="xref py py-attr docutils literal">Mapper.mapped_table</tt></a> - The <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> or other selectable to which

539

this mapper is mapped.</li>

540

<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.local_table" title="sqlalchemy.orm.mapper.Mapper.local_table"><tt class="xref py py-attr docutils literal">Mapper.local_table</tt></a> - The <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> that is “local” to this mapper;

541

this differs from <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><tt class="xref py py-attr docutils literal">Mapper.mapped_table</tt></a> in the case of a mapper mapped

542

using inheritance to a composed selectable.</li>

543

</ul>

544

</div>

545

546

<h3>I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal">and_()</tt> or <tt class="docutils literal">or_()</tt>, and I am getting an error message about foreign keys.<a class="headerlink" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" title="Permalink to this headline">¶</a></h3>

547

Are you doing this?:

548

<div class="highlight-python"><div class="highlight"><pre>class MyClass(Base):

549

# ....

550

551

foo = relationship("Dest", primaryjoin=and_("MyClass.id==Dest.foo_id", "MyClass.foo==Dest.bar"))</pre></div>

552

</div>

553

That’s an <tt class="docutils literal">and_()</tt> of two string expressions, which SQLAlchemy cannot apply any mapping towards. Declarative allows <a class="reference internal" href="orm/relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> arguments to be specified as strings, which are converted into expression objects using <tt class="docutils literal">eval()</tt>. But this doesn’t occur inside of an <tt class="docutils literal">and_()</tt> expression - it’s a special operation declarative applies only to the entirety of what’s passed to primaryjoin or other arguments as a string:

554

<div class="highlight-python"><div class="highlight"><pre>class MyClass(Base):

555

# ....

556

557

foo = relationship("Dest", primaryjoin="and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar)")</pre></div>

558

</div>

559

Or if the objects you need are already available, skip the strings:

560

<div class="highlight-python"><div class="highlight"><pre>class MyClass(Base):

561

# ....

562

563

foo = relationship(Dest, primaryjoin=and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar))</pre></div>

564

</div>

565

The same idea applies to all the other arguments, such as <tt class="docutils literal">foreign_keys</tt>:

566

<div class="highlight-python"><div class="highlight"><pre># wrong !

567

foo = relationship(Dest, foreign_keys=["Dest.foo_id", "Dest.bar_id"])

568

569

# correct !

570

foo = relationship(Dest, foreign_keys="[Dest.foo_id, Dest.bar_id]")

571

572

# also correct !

573

foo = relationship(Dest, foreign_keys=[Dest.foo_id, Dest.bar_id])

574

575

# if you're using columns from the class that you're inside of, just use the column objects !

576

class MyClass(Base):

577

foo_id = Column(...)

578

bar_id = Column(...)

579

# ...

580

581

foo = relationship(Dest, foreign_keys=[foo_id, bar_id])</pre></div>

582

</div>

583

</div>

584

</div>

585

586

<h2>Sessions / Queries<a class="headerlink" href="#sessions-queries" title="Permalink to this headline">¶</a></h2>

587

588

<h3>“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)<a class="headerlink" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" title="Permalink to this headline">¶</a></h3>

589

This is an error that occurs when a <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal">Session.flush()</tt></a> raises an exception, rolls back

590

the transaction, but further commands upon the <cite>Session</cite> are called without an

591

explicit call to <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-meth docutils literal">Session.rollback()</tt></a> or <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><tt class="xref py py-meth docutils literal">Session.close()</tt></a>.

592

It usually corresponds to an application that catches an exception

593

upon <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal">Session.flush()</tt></a> or <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal">Session.commit()</tt></a> and

594

does not properly handle the exception. For example:

595

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import create_engine, Column, Integer

596

from sqlalchemy.orm import sessionmaker

597

from sqlalchemy.ext.declarative import declarative_base

598

599

Base = declarative_base(create_engine('sqlite://'))

600

601

class Foo(Base):

602

__tablename__ = 'foo'

603

id = Column(Integer, primary_key=True)

604

605

Base.metadata.create_all()

606

607

session = sessionmaker()()

608

609

# constraint violation

610

session.add_all([Foo(id=1), Foo(id=1)])

611

612

try:

613

session.commit()

614

except:

615

# ignore error

616

pass

617

618

# continue using session without rolling back

619

session.commit()</pre></div>

620

</div>

621

The usage of the <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> should fit within a structure similar to this:

622

<div class="highlight-python"><pre>try:

623

624

session.commit()

625

except:

626

session.rollback()

627

raise

628

finally:

629

session.close() # optional, depends on use case</pre>

630

</div>

631

Many things can cause a failure within the try/except besides flushes. You

632

should always have some kind of “framing” of your session operations so that

633

connection and transaction resources have a definitive boundary, otherwise

634

your application doesn’t really have its usage of resources under control.

635

This is not to say that you need to put try/except blocks all throughout your

636

application - on the contrary, this would be a terrible idea. You should

637

architect your application such that there is one (or few) point(s) of

638

“framing” around session operations.

639

For a detailed discussion on how to organize usage of the <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a>,

640

please see <a class="reference internal" href="orm/session.html#session-faq-whentocreate">When do I construct a Session, when do I commit it, and when do I close it?</a>.

641

642

<h4>But why does flush() insist on issuing a ROLLBACK?<a class="headerlink" href="#but-why-does-flush-insist-on-issuing-a-rollback" title="Permalink to this headline">¶</a></h4>

643

It would be great if <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal">Session.flush()</tt></a> could partially complete and then not roll

644

back, however this is beyond its current capabilities since its internal

645

bookkeeping would have to be modified such that it can be halted at any time

646

and be exactly consistent with what’s been flushed to the database. While this

647

is theoretically possible, the usefulness of the enhancement is greatly

648

decreased by the fact that many database operations require a ROLLBACK in any

649

case. Postgres in particular has operations which, once failed, the

650

transaction is not allowed to continue:

651

<div class="highlight-python"><pre>test=> create table foo(id integer primary key);

652

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"

653

CREATE TABLE

654

test=> begin;

655

BEGIN

656

test=> insert into foo values(1);

657

INSERT 0 1

658

test=> commit;

659

COMMIT

660

test=> begin;

661

BEGIN

662

test=> insert into foo values(1);

663

ERROR: duplicate key value violates unique constraint "foo_pkey"

664

test=> insert into foo values(2);

665

ERROR: current transaction is aborted, commands ignored until end of transaction block</pre>

666

</div>

667

What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via

668

<a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-meth docutils literal">Session.begin_nested()</tt></a>. Using <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-meth docutils literal">Session.begin_nested()</tt></a>, you can frame an operation that may

669

potentially fail within a transaction, and then “roll back” to the point

670

before its failure while maintaining the enclosing transaction.

671

</div>

672

673

<h4>But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?<a class="headerlink" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" title="Permalink to this headline">¶</a></h4>

674

This is again a matter of the <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> providing a consistent interface and

675

refusing to guess about what context its being used. For example, the

676

<a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> supports “framing” above within multiple levels. Such as, suppose

677

you had a decorator <tt class="docutils literal">@with_session()</tt>, which did this:

678

<div class="highlight-python"><div class="highlight"><pre>def with_session(fn):

679

def go(*args, **kw):

680

session.begin(subtransactions=True)

681

try:

682

ret = fn(*args, **kw)

683

session.commit()

684

return ret

685

except:

686

session.rollback()

687

raise

688

return go</pre></div>

689

</div>

690

The above decorator begins a transaction if one does not exist already, and

691

then commits it, if it were the creator. The “subtransactions” flag means that

692

if <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-meth docutils literal">Session.begin()</tt></a> were already called by an enclosing function, nothing happens

693

except a counter is incremented - this counter is decremented when <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal">Session.commit()</tt></a>

694

is called and only when it goes back to zero does the actual COMMIT happen. It

695

allows this usage pattern:

696

<div class="highlight-python"><pre>@with_session

697

def one():

698

# do stuff

699

two()

700

701

702

@with_session

703

def two():

704

# etc.

705

706

one()

707

708

two()</pre>

709

</div>

710

<tt class="docutils literal">one()</tt> can call <tt class="docutils literal">two()</tt>, or <tt class="docutils literal">two()</tt> can be called by itself, and the

711

<tt class="docutils literal">@with_session</tt> decorator ensures the appropriate “framing” - the transaction

712

boundaries stay on the outermost call level. As you can see, if <tt class="docutils literal">two()</tt> calls

713

<tt class="docutils literal">flush()</tt> which throws an exception and then issues a <tt class="docutils literal">rollback()</tt>, there will

714

always be a second <tt class="docutils literal">rollback()</tt> performed by the decorator, and possibly a

715

third corresponding to two levels of decorator. If the <tt class="docutils literal">flush()</tt> pushed the

716

<tt class="docutils literal">rollback()</tt> all the way out to the top of the stack, and then we said that

717

all remaining <tt class="docutils literal">rollback()</tt> calls are moot, there is some silent behavior going

718

on there. A poorly written enclosing method might suppress the exception, and

719

then call <tt class="docutils literal">commit()</tt> assuming nothing is wrong, and then you have a silent

720

failure condition. The main reason people get this error in fact is because

721

they didn’t write clean “framing” code and they would have had other problems

722

down the road.

723

If you think the above use case is a little exotic, the same kind of thing

724

comes into play if you want to SAVEPOINT- you might call <tt class="docutils literal">begin_nested()</tt>

725

several times, and the <tt class="docutils literal">commit()</tt>/<tt class="docutils literal">rollback()</tt> calls each resolve the most

726

recent <tt class="docutils literal">begin_nested()</tt>. The meaning of <tt class="docutils literal">rollback()</tt> or <tt class="docutils literal">commit()</tt> is

727

dependent upon which enclosing block it is called, and you might have any

728

sequence of <tt class="docutils literal">rollback()</tt>/<tt class="docutils literal">commit()</tt> in any order, and its the level of nesting

729

that determines their behavior.

730

In both of the above cases, if <tt class="docutils literal">flush()</tt> broke the nesting of transaction

731

blocks, the behavior is, depending on scenario, anywhere from “magic” to

732

silent failure to blatant interruption of code flow.

733

<tt class="docutils literal">flush()</tt> makes its own “subtransaction”, so that a transaction is started up

734

regardless of the external transactional state, and when complete it calls

735

<tt class="docutils literal">commit()</tt>, or <tt class="docutils literal">rollback()</tt> upon failure - but that <tt class="docutils literal">rollback()</tt> corresponds

736

to its own subtransaction - it doesn’t want to guess how you’d like to handle

737

the external “framing” of the transaction, which could be nested many levels

738

with any combination of subtransactions and real SAVEPOINTs. The job of

739

starting/ending the “frame” is kept consistently with the code external to the

740

<tt class="docutils literal">flush()</tt>, and we made a decision that this was the most consistent approach.

741

</div>

742

</div>

743

744

<h3>I’m inserting 400,000 rows with the ORM and it’s really slow!<a class="headerlink" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow" title="Permalink to this headline">¶</a></h3>

745

The SQLAlchemy ORM uses the <a class="reference internal" href="glossary.html#term-unit-of-work">unit of work</a> pattern when synchronizing

746

changes to the database. This pattern goes far beyond simple “inserts”

747

of data. It includes that attributes which are assigned on objects are

748

received using an attribute instrumentation system which tracks

749

changes on objects as they are made, includes that all rows inserted

750

are tracked in an identity map which has the effect that for each row

751

SQLAlchemy must retrieve its “last inserted id” if not already given,

752

and also involves that rows to be inserted are scanned and sorted for

753

dependencies as needed. Objects are also subject to a fair degree of

754

bookkeeping in order to keep all of this running, which for a very

755

large number of rows at once can create an inordinate amount of time

756

spent with large data structures, hence it’s best to chunk these.

757

Basically, unit of work is a large degree of automation in order to

758

automate the task of persisting a complex object graph into a

759

relational database with no explicit persistence code, and this

760

automation has a price.

761

ORMs are basically not intended for high-performance bulk inserts -

762

this is the whole reason SQLAlchemy offers the Core in addition to the

763

ORM as a first-class component.

764

For the use case of fast bulk inserts, the

765

SQL generation and execution system that the ORM builds on top of

766

is part of the Core. Using this system directly, we can produce an INSERT that

767

is competitive with using the raw database API directly.

768

The example below illustrates time-based tests for four different

769

methods of inserting rows, going from the most automated to the least.

770

With cPython 2.7, runtimes observed:

771

<div class="highlight-python"><pre>classics-MacBook-Pro:sqlalchemy classic$ python test.py

772

SQLAlchemy ORM: Total time for 100000 records 14.3528850079 secs

773

SQLAlchemy ORM pk given: Total time for 100000 records 10.0164160728 secs

774

SQLAlchemy Core: Total time for 100000 records 0.775382995605 secs

775

sqlite3: Total time for 100000 records 0.676795005798 sec</pre>

776

</div>

777

We can reduce the time by a factor of three using recent versions of <a class="reference external" href="http://pypy.org/">Pypy</a>:

778

<div class="highlight-python"><pre>classics-MacBook-Pro:sqlalchemy classic$ /usr/local/src/pypy-2.1-beta2-osx64/bin/pypy test.py

779

SQLAlchemy ORM: Total time for 100000 records 5.88369488716 secs

780

SQLAlchemy ORM pk given: Total time for 100000 records 3.52294301987 secs

781

SQLAlchemy Core: Total time for 100000 records 0.613556146622 secs

782

sqlite3: Total time for 100000 records 0.442467927933 sec</pre>

783

</div>

784

Script:

785

<div class="highlight-python"><div class="highlight"><pre>import time

786

import sqlite3

787

788

from sqlalchemy.ext.declarative import declarative_base

789

from sqlalchemy import Column, Integer, String, create_engine

790

from sqlalchemy.orm import scoped_session, sessionmaker

791

792

Base = declarative_base()

793

DBSession = scoped_session(sessionmaker())

794

engine = None

795

796

class Customer(Base):

797

__tablename__ = "customer"

798

id = Column(Integer, primary_key=True)

799

name = Column(String(255))

800

801

def init_sqlalchemy(dbname='sqlite:///sqlalchemy.db'):

802

global engine

803

engine = create_engine(dbname, echo=False)

804

DBSession.remove()

805

DBSession.configure(bind=engine, autoflush=False, expire_on_commit=False)

806

Base.metadata.drop_all(engine)

807

Base.metadata.create_all(engine)

808

809

def test_sqlalchemy_orm(n=100000):

810

init_sqlalchemy()

811

t0 = time.time()

812

for i in range(n):

813

customer = Customer()

814

customer.name = 'NAME ' + str(i)

815

DBSession.add(customer)

816

if i % 1000 == 0:

817

DBSession.flush()

818

DBSession.commit()

819

print("SQLAlchemy ORM: Total time for " + str(n) +

820

" records " + str(time.time() - t0) + " secs")

821

822

def test_sqlalchemy_orm_pk_given(n=100000):

823

init_sqlalchemy()

824

t0 = time.time()

825

for i in range(n):

826

customer = Customer(id=i+1, name="NAME " + str(i))

827

DBSession.add(customer)

828

if i % 1000 == 0:

829

DBSession.flush()

830

DBSession.commit()

831

print("SQLAlchemy ORM pk given: Total time for " + str(n) +

832

" records " + str(time.time() - t0) + " secs")

833

834

def test_sqlalchemy_core(n=100000):

835

init_sqlalchemy()

836

t0 = time.time()

837

engine.execute(

838

Customer.__table__.insert(),

839

[{"name": 'NAME ' + str(i)} for i in range(n)]

840

)

841

print("SQLAlchemy Core: Total time for " + str(n) +

842

" records " + str(time.time() - t0) + " secs")

843

844

def init_sqlite3(dbname):

845

conn = sqlite3.connect(dbname)

846

c = conn.cursor()

847

c.execute("DROP TABLE IF EXISTS customer")

848

c.execute("CREATE TABLE customer (id INTEGER NOT NULL, "

849

"name VARCHAR(255), PRIMARY KEY(id))")

850

conn.commit()

851

return conn

852

853

def test_sqlite3(n=100000, dbname='sqlite3.db'):

854

conn = init_sqlite3(dbname)

855

c = conn.cursor()

856

t0 = time.time()

857

for i in range(n):

858

row = ('NAME ' + str(i),)

859

c.execute("INSERT INTO customer (name) VALUES (?)", row)

860

conn.commit()

861

print("sqlite3: Total time for " + str(n) +

862

" records " + str(time.time() - t0) + " sec")

863

864

if __name__ == '__main__':

865

test_sqlalchemy_orm(100000)

866

test_sqlalchemy_orm_pk_given(100000)

867

test_sqlalchemy_core(100000)

868

test_sqlite3(100000)</pre></div>

869

</div>

870

</div>

871

872

<h3>How do I make a Query that always adds a certain filter to every query?<a class="headerlink" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" title="Permalink to this headline">¶</a></h3>

873

See the recipe at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery">PreFilteredQuery</a>.

874

</div>

875

876

<h3>I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?<a class="headerlink" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" title="Permalink to this headline">¶</a></h3>

877

Rows returned by an outer join may contain NULL for part of the primary key,

878

as the primary key is the composite of both tables. The <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal">Query</tt></a> object ignores incoming rows

879

that don’t have an acceptable primary key. Based on the setting of the <tt class="docutils literal">allow_partial_pks</tt>

880

flag on <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal">mapper()</tt></a>, a primary key is accepted if the value has at least one non-NULL

881

value, or alternatively if the value has no NULL values. See <tt class="docutils literal">allow_partial_pks</tt>

882

at <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal">mapper()</tt></a>.

883

</div>

884

885

<h3>I’m using <tt class="docutils literal">joinedload()</tt> or <tt class="docutils literal">lazy=False</tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)<a class="headerlink" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" title="Permalink to this headline">¶</a></h3>

886

The joins generated by joined eager loading are only used to fully load related

887

collections, and are designed to have no impact on the primary results of the query.

888

Since they are anonymously aliased, they cannot be referenced directly.

889

For detail on this beahvior, see <a class="reference internal" href="orm/loading.html">Relationship Loading Techniques</a>.

890

</div>

891

892

<h3>Query has no <tt class="docutils literal">__len__()</tt>, why not?<a class="headerlink" href="#query-has-no-len-why-not" title="Permalink to this headline">¶</a></h3>

893

The Python <tt class="docutils literal">__len__()</tt> magic method applied to an object allows the <tt class="docutils literal">len()</tt>

894

builtin to be used to determine the length of the collection. It’s intuitive

895

that a SQL query object would link <tt class="docutils literal">__len__()</tt> to the <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal">Query.count()</tt></a>

896

method, which emits a <cite>SELECT COUNT</cite>. The reason this is not possible is

897

because evaluating the query as a list would incur two SQL calls instead of

898

one:

899

<div class="highlight-python"><div class="highlight"><pre>class Iterates(object):

900

def __len__(self):

901

print "LEN!"

902

return 5

903

904

def __iter__(self):

905

print "ITER!"

906

return iter([1, 2, 3, 4, 5])

907

908

list(Iterates())</pre></div>

909

</div>

910

output:

911

<div class="highlight-python"><pre>ITER!

912

LEN!</pre>

913

</div>

914

</div>

915

916

<h3>How Do I use Textual SQL with ORM Queries?<a class="headerlink" href="#how-do-i-use-textual-sql-with-orm-queries" title="Permalink to this headline">¶</a></h3>

917

See:

918

919

<li><a class="reference internal" href="orm/tutorial.html#orm-tutorial-literal-sql">Using Literal SQL</a> - Ad-hoc textual blocks with <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal">Query</tt></a></li>

920

<li><a class="reference internal" href="orm/session.html#session-sql-expressions">Using SQL Expressions with Sessions</a> - Using <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a> with textual SQL directly.</li>

921

</ul>

922

</div>

923

924

<h3>I’m calling <tt class="docutils literal">Session.delete(myobject)</tt> and it isn’t removed from the parent collection!<a class="headerlink" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" title="Permalink to this headline">¶</a></h3>

925

See <a class="reference internal" href="orm/session.html#session-deleting-from-collections">Deleting from Collections</a> for a description of this behavior.

926

</div>

927

928

<h3>why isnt my <tt class="docutils literal">__init__()</tt> called when I load objects?<a class="headerlink" href="#why-isnt-my-init-called-when-i-load-objects" title="Permalink to this headline">¶</a></h3>

929

See <a class="reference internal" href="orm/mapper_config.html#mapping-constructors">Constructors and Object Initialization</a> for a description of this behavior.

930

</div>

931

932

<h3>how do I use ON DELETE CASCADE with SA’s ORM?<a class="headerlink" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" title="Permalink to this headline">¶</a></h3>

933

SQLAlchemy will always issue UPDATE or DELETE statements for dependent

934

rows which are currently loaded in the <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal">Session</tt></a>. For rows which

935

are not loaded, it will by default issue SELECT statements to load

936

those rows and udpate/delete those as well; in other words it assumes

937

there is no ON DELETE CASCADE configured.

938

To configure SQLAlchemy to cooperate with ON DELETE CASCADE, see

939

<a class="reference internal" href="orm/collections.html#passive-deletes">Using Passive Deletes</a>.

940

</div>

941

942

<h3>I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal">None</tt> - shouldn’t it have loaded Foo with id #7?<a class="headerlink" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" title="Permalink to this headline">¶</a></h3>

943

The ORM is not constructed in such a way as to support

944

immediate population of relationships driven from foreign

945

key attribute changes - instead, it is designed to work the

946

other way around - foreign key attributes are handled by the

947

ORM behind the scenes, the end user sets up object

948

relationships naturally. Therefore, the recommended way to

949

set <tt class="docutils literal">o.foo</tt> is to do just that - set it!:

950

<div class="highlight-python"><div class="highlight"><pre>foo = Session.query(Foo).get(7)

951

o.foo = foo

952

Session.commit()</pre></div>

953

</div>

954

Manipulation of foreign key attributes is of course entirely legal. However,

955

setting a foreign-key attribute to a new value currently does not trigger

956

an “expire” event of the <a class="reference internal" href="orm/relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal">relationship()</tt></a> in which it’s involved (this may

957

be implemented in the future). This means

958

that for the following sequence:

959

<div class="highlight-python"><div class="highlight"><pre>o = Session.query(SomeClass).first()

960

assert o.foo is None

961

o.foo_id = 7</pre></div>

962

</div>

963

<tt class="docutils literal">o.foo</tt> is loaded when we checked it for <tt class="docutils literal">None</tt>. Setting

964

<tt class="docutils literal">o.foo_id=7</tt> will have the value of “7” as pending, but no flush

965

has occurred.

966

For <tt class="docutils literal">o.foo</tt> to load based on the foreign key mutation is usually achieved

967

naturally after the commit, which both flushes the new foreign key value

968

and expires all state:

969

<div class="highlight-python"><pre>Session.commit()

970

assert o.foo is <Foo object with id 7></pre>

971

</div>

972

A more minimal operation is to expire the attribute individually. The

973

<a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal">Session.flush()</tt></a> is also needed if the object is pending (hasn’t been INSERTed yet),

974

or if the relationship is many-to-one prior to 0.6.5:

975

<div class="highlight-python"><pre>Session.expire(o, ['foo'])

976

977

Session.flush()

978

979

assert o.foo is <Foo object with id 7></pre>

980

</div>

981

Where above, expiring the attribute triggers a lazy load on the next access of <tt class="docutils literal">o.foo</tt>.

982

The object does not “autoflush” on access of <tt class="docutils literal">o.foo</tt> if the object is pending, since

983

it is usually desirable that a pending object doesn’t autoflush prematurely and/or

984

excessively, while its state is still being populated.

985

Also see the recipe <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange">ExpireRelationshipOnFKChange</a>, which features a mechanism to actually achieve this behavior to a reasonable degree in simple situations.

986

</div>

987

988

<h3>Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?<a class="headerlink" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" title="Permalink to this headline">¶</a></h3>

989

When people read the many-to-many example in the docs, they get hit with the

990

fact that if you create the same <tt class="docutils literal">Keyword</tt> twice, it gets put in the DB twice.

991

Which is somewhat inconvenient.

992

This <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject">UniqueObject</a> recipe was created to address this issue.

993

</div>

994

</div>

995

</div>

996

997

</div>

998

999

</div>

1000

1001

1002

1003

1004

1005

Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.

1006

</div>

1007

</div>

1008

1009

</div>

1010

1011

1012

</body>

1013

</html>

1014

1015