~james-w/ubuntu/lucid/psycopg2/precise-backport

<p>Here are a few gotchas you may encounter using <a title="" class="reference external" href="module.html#module-psycopg2"><tt class="xref docutils literal"><span class="pre">psycopg2</span></tt></a>. Feel free to

suggest new entries!</p>

<h2>Problems with transactions handling<a class="headerlink" href="#problems-with-transactions-handling" title="Permalink to this headline">¶</a></h2>

<dt>Why does <tt class="xref docutils literal"><span class="pre">psycopg2</span></tt> leave database sessions “idle in transaction”?</dt>

<dd><p class="first">Psycopg normally starts a new transaction the first time a query is

executed, e.g. calling <a title="cursor.execute" class="reference external" href="cursor.html#cursor.execute"><tt class="xref docutils literal"><span class="pre">cursor.execute()</span></tt></a>, even if the command is a

<tt class="sql docutils literal"><span class="pre">SELECT</span></tt>. The transaction is not closed until an explicit

<a title="connection.commit" class="reference external" href="connection.html#connection.commit"><tt class="xref docutils literal"><span class="pre">commit()</span></tt></a> or <a title="connection.rollback" class="reference external" href="connection.html#connection.rollback"><tt class="xref docutils literal"><span class="pre">rollback()</span></tt></a>.</p>

<p class="last">If you are writing a long-living program, you should probably ensure to

call one of the transaction closing methods before leaving the connection

unused for a long time (which may also be a few seconds, depending on the

concurrency level in your database). Alternatively you can use a

connection in <a class="reference external" href="connection.html#autocommit"><em>autocommit</em></a> mode to avoid a new

transaction to be started at the first command.</p>

</dd>

<dt>I receive the error <em>current transaction is aborted, commands ignored until end of transaction block</em> and can’t do anything else!</dt>

<dd>There was a problem <em>in the previous</em> command to the database, which

resulted in an error. The database will not recover automatically from

this condition: you must run a <a title="connection.rollback" class="reference external" href="connection.html#connection.rollback"><tt class="xref docutils literal"><span class="pre">rollback()</span></tt></a> before sending

new commands to the session (if this seems too harsh, remember that

PostgreSQL supports nested transactions using the <a class="reference external" href="http://www.postgresql.org/docs/8.4/static/sql-savepoint.html"><tt class="sql docutils literal"><span class="pre">SAVEPOINT</span></tt></a> command).</dd>

<dt>Why do i get the error <em>current transaction is aborted, commands ignored until end of transaction block</em> when I use <tt class="xref docutils literal"><span class="pre">multiprocessing</span></tt> (or any other forking system) and not when use <tt class="xref docutils literal"><span class="pre">threading</span></tt>?</dt>

<dd>Psycopg’s connections can’t be shared across processes (but are thread

safe). If you are forking the Python process ensure to create a new

connection in each forked child.</dd>

</dl>

</div>

<h2>Problems with type conversions<a class="headerlink" href="#problems-with-type-conversions" title="Permalink to this headline">¶</a></h2>

<dt>Why does <tt class="xref docutils literal"><span class="pre">cursor.execute()</span></tt> raise the exception <em>can’t adapt</em>?</dt>

<dd>Psycopg converts Python objects in a SQL string representation by looking

at the object class. The exception is raised when you are trying to pass

as query parameter an object for which there is no adapter registered for

its class. See <a class="reference external" href="advanced.html#adapting-new-types"><em>Adapting new Python types to SQL syntax</em></a> for informations.</dd>

<dt>I can’t pass an integer or a float parameter to my query: it says <em>a number is required</em>, but <em>it is</em> a number!</dt>

<dd><p class="first">In your query string, you always have to use <tt class="docutils literal"><span class="pre">%s</span></tt> placeholders,

event when passing a number. All Python objects are converted by Psycopg

in their SQL representation, so they get passed to the query as strings.

See <a class="reference external" href="usage.html#query-parameters"><em>Passing parameters to SQL queries</em></a>.</p>

<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO numbers VALUES (</span><span class="si">%d</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="mi">42</span><span class="p">,))</span> <span class="c"># WRONG</span>

<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO numbers VALUES (</span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="mi">42</span><span class="p">,))</span> <span class="c"># correct</span>

</pre></div>

</div>

</dd>

<dt>I try to execute a query but it fails with the error <em>not all arguments converted during string formatting</em> (or <em>object does not support indexing</em>). Why?</dt>

<dd><p class="first">Psycopg always require positional arguments to be passed as a tuple, even

when the query takes a single parameter. And remember that to make a

single item tuple in Python you need a comma! See <a class="reference external" href="usage.html#query-parameters"><em>Passing parameters to SQL queries</em></a>.</p>

100

<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO foo VALUES (</span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="s">"bar"</span><span class="p">)</span> <span class="c"># WRONG</span>

101

<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO foo VALUES (</span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="s">"bar"</span><span class="p">))</span> <span class="c"># WRONG</span>

102

<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO foo VALUES (</span><span class="si">%s</span><span class="s">)"</span><span class="p">,</span> <span class="p">(</span><span class="s">"bar"</span><span class="p">,))</span> <span class="c"># correct</span>

103

</pre></div>

104

</div>

105

</dd>

106

<dt>My database is Unicode, but I receive all the strings as UTF-8 <tt class="xref docutils literal"><span class="pre">str</span></tt>. Can I receive <tt class="xref docutils literal"><span class="pre">unicode</span></tt> objects instead?</dt>

107

<dd><p class="first">The following magic formula will do the trick:</p>

108

<div class="highlight-python"><div class="highlight"><pre><span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">register_type</span><span class="p">(</span><span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">UNICODE</span><span class="p">)</span>

109

<span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">register_type</span><span class="p">(</span><span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">UNICODEARRAY</span><span class="p">)</span>

110

</pre></div>

111

</div>

112

<p class="last">See <a class="reference external" href="usage.html#unicode-handling"><em>Unicode handling</em></a> for the gory details.</p>

113

</dd>

114

<dt>Psycopg converts <tt class="sql docutils literal"><span class="pre">decimal</span></tt>/<tt class="sql docutils literal"><span class="pre">numeric</span></tt> database types into Python <tt class="xref docutils literal"><span class="pre">Decimal</span></tt> objects. Can I have <tt class="xref docutils literal"><span class="pre">float</span></tt> instead?</dt>

115

<dd><p class="first">You can register a customized adapter for PostgreSQL decimal type:</p>

116

<div class="highlight-python"><div class="highlight"><pre><span class="n">DEC2FLOAT</span> <span class="o">=</span> <span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">new_type</span><span class="p">(</span>

117

<span class="n">psycopg2</span><span class="o">.</span><span class="n">extensions</span><span class="o">.</span><span class="n">DECIMAL</span><span class="o">.</span><span class="n">values</span><span class="p">,</span>

118

<span class="s">'DEC2FLOAT'</span><span class="p">,</span>

119

<span class="k">lambda</span> <span class="n">value</span><span class="p">,</span> <span class="n">curs</span><span class="p">:</span> <span class="nb">float</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="bp">None</span> <span class="k">else</span> <span class="bp">None</span><span class="p">)</span>

120

121

</pre></div>

122

</div>

123

<p class="last">See <a class="reference external" href="advanced.html#type-casting-from-sql-to-python"><em>Type casting of SQL types into Python objects</em></a> to read the relevant

124

documentation. If you find <tt class="xref docutils literal"><span class="pre">psycopg2.extensions.DECIMAL</span></tt> not avalable, use

125

<tt class="xref docutils literal"><span class="pre">psycopg2._psycopg.DECIMAL</span></tt> instead.</p>

126

</dd>

127

</dl>

128

</div>

129

130

<h2>Best practices<a class="headerlink" href="#best-practices" title="Permalink to this headline">¶</a></h2>

131

132

<dt>When should I save and re-use a cursor as opposed to creating a new one as needed?</dt>

133

<dd>Cursors are lightweight objects and creating lots of them should not pose

134

any kind of problem. But note that cursors used to fetch result sets will

135

cache the data and use memory in proportion to the result set size. Our

136

suggestion is to almost always create a new cursor and dispose old ones as

137

soon as the data is not required anymore (call <a title="cursor.close" class="reference external" href="cursor.html#cursor.close"><tt class="xref docutils literal"><span class="pre">close()</span></tt></a> on

138

them.) The only exception are tight loops where one usually use the same

139

cursor for a whole bunch of <tt class="sql docutils literal"><span class="pre">INSERT</span></tt>s or <tt class="sql docutils literal"><span class="pre">UPDATE</span></tt>s.</dd>

140

<dt>When should I save and re-use a connection as opposed to creating a new one as needed?</dt>

141

<dd>Creating a connection can be slow (think of SSL over TCP) so the best

142

practice is to create a single connection and keep it open as long as

143

required. It is also good practice to rollback or commit frequently (even

144

after a single <tt class="sql docutils literal"><span class="pre">SELECT</span></tt> statement) to make sure the backend is never

145

left “idle in transaction”. See also <a title="" class="reference external" href="pool.html#module-psycopg2.pool"><tt class="xref docutils literal"><span class="pre">psycopg2.pool</span></tt></a> for lightweight

146

connection pooling.</dd>

147

<dt>What are the advantages or disadvantages of using named cursors?</dt>

148

<dd>The only disadvantages is that they use up resources on the server and

149

that there is a little overhead because a at least two queries (one to

150

create the cursor and one to fetch the initial result set) are issued to

151

the backend. The advantage is that data is fetched one chunk at a time:

152

using small <a title="cursor.fetchmany" class="reference external" href="cursor.html#cursor.fetchmany"><tt class="xref docutils literal"><span class="pre">fetchmany()</span></tt></a> values it is possible to use very

153

little memory on the client and to skip or discard parts of the result set.</dd>

154

</dl>

155

</div>

156

157

<h2>Problems compiling Psycopg from source<a class="headerlink" href="#problems-compiling-psycopg-from-source" title="Permalink to this headline">¶</a></h2>

158

159

<dt>I can’t compile <tt class="xref docutils literal"><span class="pre">psycopg2</span></tt>: the compiler says <em>error: Python.h: No such file or directory</em>. What am I missing?</dt>

160

<dd>You need to install a Python development package: it is usually called

161

<tt class="docutils literal"><span class="pre">python-dev</span></tt>.</dd>

162

<dt>I can’t compile <tt class="xref docutils literal"><span class="pre">psycopg2</span></tt>: the compiler says <em>error: libpq-fe.h: No such file or directory</em>. What am I missing?</dt>

163

<dd>You need to install the development version of the libpq: the package is

164

usually called <tt class="docutils literal"><span class="pre">libpq-dev</span></tt>.</dd>

165

</dl>

166

</div>

167

</div>

168

169

170

</div>

171

</div>

172

</div>

173

174

175

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

176

<ul>

177

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

178

<li><a class="reference external" href="#problems-with-transactions-handling">Problems with transactions handling</a></li>

179

<li><a class="reference external" href="#problems-with-type-conversions">Problems with type conversions</a></li>

180

<li><a class="reference external" href="#best-practices">Best practices</a></li>

181

<li><a class="reference external" href="#problems-compiling-psycopg-from-source">Problems compiling Psycopg from source</a></li>

182

</ul>

183

</li>

184

</ul>

185

186

<h4>Previous topic</h4>

187

<p class="topless"><a href="errorcodes.html"

188

title="previous chapter"><tt class="docutils literal docutils literal docutils literal"><span class="pre">psycopg2.errorcodes</span></tt> – Error codes defined by PostgreSQL</a></p>

189

190

191

<li><a href="_sources/faq.txt"

192

rel="nofollow">Show Source</a></li>

193

</ul>

194

195

<h3>Quick search</h3>

196

197

198

199

200

201

</form>

202

203

Enter search terms or a module, class or function name.

204

</p>

205

</div>

206

207

</div>

208

</div>

209

210

</div>

211

212

<h3>Navigation</h3>

213

<ul>

214

215

<a href="genindex.html" title="General Index"

216

>index</a></li>

217

218

<a href="modindex.html" title="Global Module Index"

219

>modules</a> |</li>

220

221

<a href="errorcodes.html" title="psycopg2.errorcodes – Error codes defined by PostgreSQL"

222

>previous</a> |</li>

223

<li><a href="index.html">Psycopg v2.2.1 documentation</a> »</li>

224

</ul>

225

</div>

226

227

228

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

229

</div>

230

</body>

231

</html>

b'\\ No newline at end of file'

Older »