1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head><title xmlns="http://www.w3.org/1999/xhtml">pyPgSQL FAQ</title><meta xmlns="http://www.w3.org/1999/xhtml" name="generator" content="DocBook XSL Stylesheets V1.56.1"/><meta name="generator" content="e-smith XSLT customization layer $Revision: 1.2 $"/><!--
Generated by e-smith XSLT customization layer $Revision: 1.2 $
based on Norman Walsh's DocBook XSL stylesheets 1.44
More information at http://www.e-smith.org/docs/
-->
<link rel="stylesheet" href="global.css" type="text/css"/></head><body><div xmlns="http://www.w3.org/1999/xhtml" class="article"><div xmlns="http://www.w3.org/1999/xhtml" class="titlepage"><div xmlns="http://www.w3.org/1999/xhtml"><h1 xmlns="http://www.w3.org/1999/xhtml" class="title"><a id="id2781834"/>pyPgSQL FAQ</h1></div><hr xmlns="http://www.w3.org/1999/xhtml"/></div><h2 xmlns="http://www.w3.org/1999/xhtml"><a xmlns="http://www.w3.org/1999/xhtml" id="id2782135"/>
</h2><div xmlns="http://www.w3.org/1999/xhtml" class="qandaset"><dl xmlns="http://www.w3.org/1999/xhtml"><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2836040">I'm getting a
ImportError: No module named DateTime
</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2836561">Inserting data for the SQL IN-operator doesn't seem to work.</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2836440">I get an error like:
>>> cursor.execute("select foo from bar where baz=%f", (3.14,))
Traceback (most recent call last):
File "somefile.py", line 1, in ?
File "pyPgSQL/PgSQL.py", line 2579, in execute
self.res = self.conn.conn.query(_qstr % parms)
TypeError: bad argument type for built-in operation
</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2836475">I've heard of Unicode support for pyPgSQL. What's the current status?</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2836498">Large Object support doesn't work with Python 2.2. What gives?</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2787127">Why isn't cursor.rowcount working as expected?</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2787229">When building pyPgSQL, I get an error message like the following:
gcc: unrecognized option `-R/usr/lib/' What's up?
</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2787317">I've little or no experience using Python for database programming, where can I find additional documentation?
</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2787367">How can I speed up the fetching of data with PgSQL?</a></dt><dt xmlns="http://www.w3.org/1999/xhtml">Q:. <a href="#id2787415">Is pyPgSQL available in packaged form by third parties?</a></dt></dl><table border="0" summary="Q and A Set"><col align="left" width="1%"/><tbody><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2836040"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2836043"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">I'm getting a
</p><table border="0" bgcolor="#E0E0E0" width="90%"><tr><td><pre class="programlisting">
ImportError: No module named DateTime
</pre></td></tr></table><p xmlns="http://www.w3.org/1999/xhtml">
</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">You need to install the BASE package from the eGenix mx Extensions: http://www.egenix.com/files/python/
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2836561"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2836564"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">Inserting data for the SQL IN-operator doesn't seem to work.</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">In pyPgSQL 2.3, you can use tuples:
</p><table border="0" bgcolor="#E0E0E0" width="90%"><tr><td><pre class="programlisting">
cursor.execute("select * from test where id in %s", ((3,4,5),))
</pre></td></tr></table><p xmlns="http://www.w3.org/1999/xhtml">
If you want to use ARRAYs, you'll now need to wrap your (nested) lists with
PgSQL.PgArray.
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2836440"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2836443"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">I get an error like:
</p><table border="0" bgcolor="#E0E0E0" width="90%"><tr><td><pre class="programlisting">
>>> cursor.execute("select foo from bar where baz=%f", (3.14,))
Traceback (most recent call last):
File "somefile.py", line 1, in ?
File "pyPgSQL/PgSQL.py", line 2579, in execute
self.res = self.conn.conn.query(_qstr % parms)
TypeError: bad argument type for built-in operation
</pre></td></tr></table><p xmlns="http://www.w3.org/1999/xhtml">
</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
Using pyPgSQL, you use %s for all parameters, no matter which type they have.
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2836475"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2836478"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">I've heard of Unicode support for pyPgSQL. What's the current status?</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">It's integrated in pyPgSQL 2.3.</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2836498"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2836501"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">Large Object support doesn't work with Python 2.2. What gives?</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">It's a Python bug. Please upgrade to Python 2.2.2.</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2787127"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2787130"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">Why isn't cursor.rowcount working as expected?</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
By default, PgSQL uses PostgreSQL Portals (i.e. cursors). As a result of
this, PgSQL doesn't know how many rows resulted from the query until they
are fetched. (Note: rowcount will be set to the number of rows returned by
the fectchXXX() method. If you fecthone(), rowcount will be 1.)
There are a couple of ways to get the number of rows returned by a query:
</p><div xmlns="http://www.w3.org/1999/xhtml" class="orderedlist"><ol type="1"><li xmlns="http://www.w3.org/1999/xhtml"><p xmlns="http://www.w3.org/1999/xhtml">Do a <tt>fetchall()</tt>. <tt>rowcount</tt> will then be set to the number of rows
retrieved.</p></li><li xmlns="http://www.w3.org/1999/xhtml"><p xmlns="http://www.w3.org/1999/xhtml">
Set <tt>PgSQL.noPostgresCursor</tt> to 1. This will prevent PgSQL from using
PostgreSQL Portals (which will result in the entire result set being
read into memory). Rowcount will be set to the number of rows
retrieved.
</p></li><li xmlns="http://www.w3.org/1999/xhtml"><p xmlns="http://www.w3.org/1999/xhtml">Execute a <b>select count(*) ...</b> to get the number of rows.</p></li><li xmlns="http://www.w3.org/1999/xhtml"><p xmlns="http://www.w3.org/1999/xhtml">Re-evaluate why you need to know the number of rows before processing
the results.</p></li></ol></div></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2787229"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2787235"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">When building pyPgSQL™, I get an error message like the following:
<tt>gcc: unrecognized option `-R/usr/lib/'</tt> What's up?
</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
This happens with pre-2.2 versions of Python™'s distutils™, when using the gcc™ compiler. This warning can be safely ignored if your PostgreSQL™ libraries can normally be found by the dynamic loader. Otherwise, you need to work around this problem by putting the PostgreSQL™ library directory into the search path of your dynamic loader. See <b>man 1 ld</b> for details.
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2787317"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2787320"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">I've little or no experience using Python for database programming, where can I find additional documentation?
</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
pyPgSQL™ is an implementation of the
<a href="http://www.python.org/topics/database/DatabaseAPI-2.0.html" target="_top">Python DB-API 2.0 specification</a>.
There's also an article from Andrew Kuchling
<a href="http://www.amk.ca/python/writing/DB-API.html" target="_top">about using the DB-API</a>. Unfortunately, there doesn't seem to be a lot of beginner-level documentation out there currently.
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2787367"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2787370"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">How can I speed up the fetching of data with PgSQL?</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
You can use
</p><table border="0" bgcolor="#E0E0E0" width="90%"><tr><td><pre class="programlisting">
PgSQL.fetchReturnsList = 1
</pre></td></tr></table><p xmlns="http://www.w3.org/1999/xhtml">
and
</p><table border="0" bgcolor="#E0E0E0" width="90%"><tr><td><pre class="programlisting">
PgSQL.noPostgresCursor = 1
</pre></td></tr></table><p xmlns="http://www.w3.org/1999/xhtml">
This will increase the performance of fetches by ca. 25 %. There's potential for increasing the performance of fetches in PgSQL by a lot more. This is something that will be looked onto for future releases of pyPgSQL™.
</p></td></tr></div><hr/><div class="qandaentry"><tr xmlns="http://www.w3.org/1999/xhtml" class="question"><td align="left" valign="top"><a xmlns="http://www.w3.org/1999/xhtml" id="id2787415"/><a xmlns="http://www.w3.org/1999/xhtml" id="id2787418"/><b>Q:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">Is pyPgSQL available in packaged form by third parties?</p></td></tr><tr xmlns="http://www.w3.org/1999/xhtml" class="answer"><td align="left" valign="top"><b>A:. </b></td><td align="left" valign="top"><p xmlns="http://www.w3.org/1999/xhtml">
There's a pyPgSQL™ port available for FreeBSD. It can be installed with
<b><tt>cd /usr/ports/databases/py-pyPgSQL && make install</tt></b>
</p><p xmlns="http://www.w3.org/1999/xhtml">
pyPgSQL™ is also available as a Debian package. You can install it with <b><tt>apt-get install python-pgsql</tt></b>
</p></td></tr></div></tbody></table></div></div></body></html>
|
