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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
|
<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>First Steps with PyGreSQL — PyGreSQL 5.0 documentation</title>
<link rel="stylesheet" href="../_static/cloud.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygresql.css" type="text/css" />
<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Noticia+Text|Open+Sans|Droid+Sans+Mono" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '5.0.3',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/jquery.cookie.js"></script>
<script type="text/javascript" src="../_static/cloud.js"></script>
<link rel="shortcut icon" href="../_static/favicon.ico"/>
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="PyGreSQL 5.0 documentation" href="index.html" />
<link rel="next" title="pg — The Classic PyGreSQL Interface" href="pg/index.html" />
<link rel="prev" title="General PyGreSQL programming information" href="general.html" />
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body role="document">
<div class="pageheader related" role="navigation" aria-label="related navigation">
<ul>
<li><a href="../index.html">Home</a></li>
<li><a href="../download/index.html">Download</a></li>
<li><a href="index.html">Documentation</a></li>
<li><a href="../community/index.html">Community</a></li>
</ul>
<div class="logo">
<a href="../index.html">PyGreSQL</a>
</div>
</div>
</div>
<div class="relbar-top">
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="../genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="../py-modindex.html" title="Python Module Index"
>modules</a> </li>
<li class="right" >
<a href="pg/index.html" title="pg — The Classic PyGreSQL Interface"
accesskey="N">next</a> </li>
<li class="right" >
<a href="general.html" title="General PyGreSQL programming information"
accesskey="P">previous</a> </li>
<li><a href="index.html">PyGreSQL 5.0 documentation</a> »</li>
</ul>
</div>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body" role="main">
<div class="section" id="first-steps-with-pygresql">
<h1>First Steps with PyGreSQL<a class="headerlink" href="#first-steps-with-pygresql" title="Permalink to this headline">¶</a></h1>
<p>In this small tutorial we show you the basic operations you can perform
with both flavors of the PyGreSQL interface. Please choose your flavor:</p>
<div class="contents local topic" id="contents">
<ul class="simple">
<li><a class="reference internal" href="#first-steps-with-the-classic-pygresql-interface" id="id1">First Steps with the classic PyGreSQL Interface</a></li>
<li><a class="reference internal" href="#first-steps-with-the-db-api-2-0-interface" id="id2">First Steps with the DB-API 2.0 Interface</a></li>
</ul>
</div>
<div class="section" id="first-steps-with-the-classic-pygresql-interface">
<h2><a class="toc-backref" href="#id1">First Steps with the classic PyGreSQL Interface</a><a class="headerlink" href="#first-steps-with-the-classic-pygresql-interface" title="Permalink to this headline">¶</a></h2>
<p>The first thing you need to do anything with your PostgreSQL database is
to create a database connection.</p>
<p>To do this, simply import the <a class="reference internal" href="pg/db_wrapper.html#pg.DB" title="pg.DB"><code class="xref py py-class docutils literal"><span class="pre">DB</span></code></a> wrapper class and create an
instance of it, passing the necessary connection parameters, like this:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">pg</span> <span class="k">import</span> <span class="n">DB</span>
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">DB</span><span class="p">(</span><span class="n">dbname</span><span class="o">=</span><span class="s">'testdb'</span><span class="p">,</span> <span class="n">host</span><span class="o">=</span><span class="s">'pgserver'</span><span class="p">,</span> <span class="n">port</span><span class="o">=</span><span class="mi">5432</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">user</span><span class="o">=</span><span class="s">'scott'</span><span class="p">,</span> <span class="n">passwd</span><span class="o">=</span><span class="s">'tiger'</span><span class="p">)</span>
</pre></div>
</div>
<p>You can omit one or even all parameters if you want to use their default
values. PostgreSQL will use the name of the current operating system user
as the login and the database name, and will try to connect to the local
host on port 5432 if nothing else is specified.</p>
<p>The <cite>db</cite> object has all methods of the lower-level <a class="reference internal" href="pg/connection.html#pg.Connection" title="pg.Connection"><code class="xref py py-class docutils literal"><span class="pre">Connection</span></code></a> class
plus some more convenience methods provided by the <a class="reference internal" href="pg/db_wrapper.html#pg.DB" title="pg.DB"><code class="xref py py-class docutils literal"><span class="pre">DB</span></code></a> wrapper.</p>
<p>You can now execute database queries using the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.query" title="pg.DB.query"><code class="xref py py-meth docutils literal"><span class="pre">DB.query()</span></code></a> method:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">"create table fruits(id serial primary key, name varchar)"</span><span class="p">)</span>
</pre></div>
</div>
<p>You can list all database tables with the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.get_tables" title="pg.DB.get_tables"><code class="xref py py-meth docutils literal"><span class="pre">DB.get_tables()</span></code></a> method:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">get_tables</span><span class="p">()</span>
<span class="go">['public.fruits']</span>
</pre></div>
</div>
<p>To get the attributes of the <em>fruits</em> table, use <a class="reference internal" href="pg/db_wrapper.html#pg.DB.get_attnames" title="pg.DB.get_attnames"><code class="xref py py-meth docutils literal"><span class="pre">DB.get_attnames()</span></code></a>:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">get_attnames</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">)</span>
<span class="go">{'id': 'int', 'name': 'text'}</span>
</pre></div>
</div>
<p>Verify that you can insert into the newly created <em>fruits</em> table:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">has_table_privilege</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="s">'insert'</span><span class="p">)</span>
<span class="go">True</span>
</pre></div>
</div>
<p>You can insert a new row into the table using the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.insert" title="pg.DB.insert"><code class="xref py py-meth docutils literal"><span class="pre">DB.insert()</span></code></a> method,
for example:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'apple'</span><span class="p">)</span>
<span class="go">{'name': 'apple', 'id': 1}</span>
</pre></div>
</div>
<p>Note how this method returns the full row as a dictionary including its <em>id</em>
column that has been generated automatically by a database sequence. You can
also pass a dictionary to the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.insert" title="pg.DB.insert"><code class="xref py py-meth docutils literal"><span class="pre">DB.insert()</span></code></a> method instead of or in
addition to using keyword arguments.</p>
<p>Let’s add another row to the table:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">banana</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'banana'</span><span class="p">)</span>
</pre></div>
</div>
<p>Or, you can add a whole bunch of fruits at the same time using the
<a class="reference internal" href="pg/connection.html#pg.Connection.inserttable" title="pg.Connection.inserttable"><code class="xref py py-meth docutils literal"><span class="pre">Connection.inserttable()</span></code></a> method. Note that this method uses the COPY
command of PostgreSQL to insert all data in one batch operation, which is much
faster than sending many individual INSERT commands:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">more_fruits</span> <span class="o">=</span> <span class="s">'cherimaya durian eggfruit fig grapefruit'</span><span class="o">.</span><span class="n">split</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">data</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span><span class="nb">enumerate</span><span class="p">(</span><span class="n">more_fruits</span><span class="p">,</span> <span class="n">start</span><span class="o">=</span><span class="mi">3</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">inserttable</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">data</span><span class="p">)</span>
</pre></div>
</div>
<p>We can now query the database for all rows that have been inserted into
the <em>fruits</em> table:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">'select * from fruits'</span><span class="p">))</span>
<span class="go">id| name</span>
<span class="go">--+----------</span>
<span class="go"> 1|apple</span>
<span class="go"> 2|banana</span>
<span class="go"> 3|cherimaya</span>
<span class="go"> 4|durian</span>
<span class="go"> 5|eggfruit</span>
<span class="go"> 6|fig</span>
<span class="go"> 7|grapefruit</span>
<span class="go">(7 rows)</span>
</pre></div>
</div>
<p>Instead of simply printing the <a class="reference internal" href="pg/query.html#pg.Query" title="pg.Query"><code class="xref py py-class docutils literal"><span class="pre">Query</span></code></a> instance that has been returned
by this query, we can also request the data as list of tuples:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">q</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">'select * from fruits'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">q</span><span class="o">.</span><span class="n">getresult</span><span class="p">()</span>
<span class="gp">... </span><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">'apple'</span><span class="p">),</span> <span class="o">...</span><span class="p">,</span> <span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="s">'grapefruit'</span><span class="p">)]</span>
</pre></div>
</div>
<p>Instead of a list of tuples, we can also request a list of dicts:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">q</span><span class="o">.</span><span class="n">dictresult</span><span class="p">()</span>
<span class="go">[{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]</span>
</pre></div>
</div>
<p>You can also return the rows as named tuples:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">rows</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">namedresult</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">rows</span><span class="p">[</span><span class="mi">3</span><span class="p">]</span><span class="o">.</span><span class="n">name</span>
<span class="go">'durian'</span>
</pre></div>
</div>
<p>Using the method <a class="reference internal" href="pg/db_wrapper.html#pg.DB.get_as_dict" title="pg.DB.get_as_dict"><code class="xref py py-meth docutils literal"><span class="pre">DB.get_as_dict()</span></code></a>, you can easily import the whole table
into a Python dictionary mapping the primary key <em>id</em> to the <em>name</em>:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">get_as_dict</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">scalar</span><span class="o">=</span><span class="k">True</span><span class="p">)</span>
<span class="go">OrderedDict([(1, 'apple'),</span>
<span class="go"> (2, 'banana'),</span>
<span class="go"> (3, 'cherimaya'),</span>
<span class="go"> (4, 'durian'),</span>
<span class="go"> (5, 'eggfruit'),</span>
<span class="go"> (6, 'fig'),</span>
<span class="go"> (7, 'grapefruit')])</span>
</pre></div>
</div>
<p>To change a single row in the database, you can use the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.update" title="pg.DB.update"><code class="xref py py-meth docutils literal"><span class="pre">DB.update()</span></code></a>
method. For instance, if you want to capitalize the name ‘banana’:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">banana</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="n">banana</span><span class="p">[</span><span class="s">'name'</span><span class="p">]</span><span class="o">.</span><span class="n">capitalize</span><span class="p">())</span>
<span class="go">{'id': 2, 'name': 'Banana'}</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">'select * from fruits where id between 1 and 3'</span><span class="p">))</span>
<span class="go">id| name</span>
<span class="go">--+---------</span>
<span class="go"> 1|apple</span>
<span class="go"> 2|Banana</span>
<span class="go"> 3|cherimaya</span>
<span class="go">(3 rows)</span>
</pre></div>
</div>
<p>Let’s also capitalize the other names in the database:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">'update fruits set name=initcap(name)'</span><span class="p">)</span>
<span class="go">'7'</span>
</pre></div>
</div>
<p>The returned string <cite>‘7’</cite> tells us the number of updated rows. It is returned
as a string to discern it from an OID which will be returned as an integer,
if a new row has been inserted into a table with an OID column.</p>
<p>To delete a single row from the database, use the <a class="reference internal" href="pg/db_wrapper.html#pg.DB.delete" title="pg.DB.delete"><code class="xref py py-meth docutils literal"><span class="pre">DB.delete()</span></code></a> method:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">banana</span><span class="p">)</span>
<span class="go">1</span>
</pre></div>
</div>
<p>The returned integer value <cite>1</cite> tells us that one row has been deleted. If we
try it again, the method returns the integer value <cite>0</cite>. Naturally, this method
can only return 0 or 1:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">banana</span><span class="p">)</span>
<span class="go">0</span>
</pre></div>
</div>
<p>Of course, we can insert the row back again:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">banana</span><span class="p">)</span>
<span class="go">{'id': 2, 'name': 'Banana'}</span>
</pre></div>
</div>
<p>If we want to change a different row, we can get its current state with:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">apple</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">apple</span>
<span class="go">{'name': 'Apple', 'id': 1}</span>
</pre></div>
</div>
<p>We can duplicate the row like this:</p>
<div class="highlight-default"><div class="highlight"><pre> <span class="o">>>></span> <span class="n">db</span><span class="o">.</span><span class="n">insert</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="n">apple</span><span class="p">,</span> <span class="nb">id</span><span class="o">=</span><span class="mi">8</span><span class="p">)</span>
<span class="p">{</span><span class="s">'id'</span><span class="p">:</span> <span class="mi">8</span><span class="p">,</span> <span class="s">'name'</span><span class="p">:</span> <span class="s">'Apple'</span><span class="p">}</span>
<span class="n">To</span> <span class="n">remove</span> <span class="n">the</span> <span class="n">duplicated</span> <span class="n">row</span><span class="p">,</span> <span class="n">we</span> <span class="n">can</span> <span class="n">do</span><span class="p">::</span>
<span class="o">>>></span> <span class="n">db</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="s">'fruits'</span><span class="p">,</span> <span class="nb">id</span><span class="o">=</span><span class="mi">8</span><span class="p">)</span>
<span class="mi">1</span>
</pre></div>
</div>
<p>Finally, to remove the table from the database and close the connection:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">"drop table fruits"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
</pre></div>
</div>
<p>For more advanced features and details, see the reference: <a class="reference internal" href="pg/index.html"><span class="doc">pg — The Classic PyGreSQL Interface</span></a></p>
</div>
<div class="section" id="first-steps-with-the-db-api-2-0-interface">
<h2><a class="toc-backref" href="#id2">First Steps with the DB-API 2.0 Interface</a><a class="headerlink" href="#first-steps-with-the-db-api-2-0-interface" title="Permalink to this headline">¶</a></h2>
<p>As with the classic interface, the first thing you need to do is to create
a database connection. To do this, use the function <a class="reference internal" href="pgdb/module.html#pgdb.connect" title="pgdb.connect"><code class="xref py py-func docutils literal"><span class="pre">pgdb.connect()</span></code></a>
in the <a class="reference internal" href="pgdb/index.html#module-pgdb" title="pgdb"><code class="xref py py-mod docutils literal"><span class="pre">pgdb</span></code></a> module, passing the connection parameters:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">pgdb</span> <span class="k">import</span> <span class="n">connect</span>
<span class="gp">>>> </span><span class="n">con</span> <span class="o">=</span> <span class="n">connect</span><span class="p">(</span><span class="n">database</span><span class="o">=</span><span class="s">'testdb'</span><span class="p">,</span> <span class="n">host</span><span class="o">=</span><span class="s">'pgserver:5432'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">user</span><span class="o">=</span><span class="s">'scott'</span><span class="p">,</span> <span class="n">password</span><span class="o">=</span><span class="s">'tiger'</span><span class="p">)</span>
</pre></div>
</div>
<p>Note that like in the classic interface, you can omit parameters if they
are the default values used by PostgreSQL.</p>
<p>To do anything with the connection, you need to request a cursor object
from it, which is thought of as the Python representation of a database
cursor. The connection has a method that lets you get a cursor:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span> <span class="o">=</span> <span class="n">con</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
</pre></div>
</div>
<p>The cursor now has a method that lets you execute database queries:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"create table fruits("</span>
<span class="gp">... </span> <span class="s">"id serial primary key, name varchar)"</span><span class="p">)</span>
</pre></div>
</div>
<p>To insert data into the table, also can also use this method:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"insert into fruits (name) values ('apple')"</span><span class="p">)</span>
</pre></div>
</div>
<p>You can pass parameters in a safe way:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"insert into fruits (name) values (%s)"</span><span class="p">,</span> <span class="p">(</span><span class="s">'banana'</span><span class="p">,))</span>
</pre></div>
</div>
<p>For inserting multiple rows at once, you can use the following method:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">more_fruits</span> <span class="o">=</span> <span class="s">'cherimaya durian eggfruit fig grapefruit'</span><span class="o">.</span><span class="n">split</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">parameters</span> <span class="o">=</span> <span class="p">[(</span><span class="n">name</span><span class="p">,)</span> <span class="k">for</span> <span class="n">name</span> <span class="ow">in</span> <span class="n">more_fruits</span><span class="p">]</span>
<span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">executemany</span><span class="p">(</span><span class="s">"insert into fruits (name) values (%s)"</span><span class="p">,</span> <span class="n">parameters</span><span class="p">)</span>
</pre></div>
</div>
<p>The cursor also has a <a class="reference internal" href="pgdb/cursor.html#pgdb.Cursor.copy_from" title="pgdb.Cursor.copy_from"><code class="xref py py-meth docutils literal"><span class="pre">Cursor.copy_from()</span></code></a> method to quickly insert
large amounts of data into the database, and a <a class="reference internal" href="pgdb/cursor.html#pgdb.Cursor.copy_to" title="pgdb.Cursor.copy_to"><code class="xref py py-meth docutils literal"><span class="pre">Cursor.copy_to()</span></code></a>
method to quickly dump large amounts of data from the database, using the
PostgreSQL COPY command. Note however, that these methods are an extension
provided by PyGreSQL, they are not part of the DB-API 2 standard.</p>
<p>Also note that the DB API 2.0 interface does not have an autocommit as you
may be used from PostgreSQL. So in order to make these inserts permanent,
you need to commit them to the database first:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">con</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
</pre></div>
</div>
<p>If you end the program without calling the commit method of the connection,
or if you call the rollback method of the connection, then all the changes
will be discarded.</p>
<p>In a similar way, you can also update or delete rows in the database,
executing UPDATE or DELETE statements instead of INSERT statements.</p>
<p>To fetch rows from the database, execute a SELECT statement first. Then
you can use one of several fetch methods to retrieve the results. For
instance, to request a single row:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select * from fruits where id=1'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="go">Row(id=1, name='apple')</span>
</pre></div>
</div>
<p>The result is a named tuple. This means you can access its elements either
using an index number like in an ordinary tuple, or using the column name
like you access object attributes.</p>
<p>To fetch all rows of the query, use this method instead:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select * from fruits'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="go">[Row(id=1, name='apple'), ..., Row(id=7, name='grapefruit')]</span>
</pre></div>
</div>
<p>The output is a list of named tuples.</p>
<p>If you want to fetch only a limited number of rows from the query:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">'select * from fruits'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">cursor</span><span class="o">.</span><span class="n">fetchmany</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="go">[Row(id=1, name='apple'), Row(id=2, name='banana')]</span>
</pre></div>
</div>
<p>Finally, to remove the table from the database and close the connection:</p>
<div class="highlight-default"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"drop table fruits"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">cur</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
</pre></div>
</div>
<p>For more advanced features and details, see the reference: <a class="reference internal" href="pgdb/index.html"><span class="doc">pgdb — The DB-API Compliant Interface</span></a></p>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar" role="navigation" aria-label="main navigation">
<div class="sphinxsidebarwrapper">
<p class="logo"><a href="index.html" title="contents/index">
<img class="logo" src="../_static/pygresql.png" alt="Logo"/>
</a></p><div class="sphinxlocaltoc">
<h3><a href="../index.html">Page contents</a></h3>
<ul>
<li><a class="reference internal" href="#">First Steps with PyGreSQL</a><ul>
<li><a class="reference internal" href="#first-steps-with-the-classic-pygresql-interface">First Steps with the classic PyGreSQL Interface</a></li>
<li><a class="reference internal" href="#first-steps-with-the-db-api-2-0-interface">First Steps with the DB-API 2.0 Interface</a></li>
</ul>
</li>
</ul>
</div>
<div class="sphinxprev">
<h4>Previous page</h4>
<p class="topless"><a href="general.html"
title="Previous page">← General PyGreSQL programming information</a></p>
</div>
<div class="sphinxnext">
<h4>Next page</h4>
<p class="topless"><a href="pg/index.html"
title="Next page">→ <code class="docutils literal"><span class="pre">pg</span></code> — The Classic PyGreSQL Interface</a></p>
</div>
<div role="note" aria-label="source link">
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="../_sources/contents/tutorial.txt"
rel="nofollow">Show Source</a></li>
</ul>
</div>
<div id="searchbox" style="display: none" role="search">
<h3>Quick search</h3>
<form class="search" action="../search.html" method="get">
<input type="text" name="q" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="relbar-bottom">
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="../genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="../py-modindex.html" title="Python Module Index"
>modules</a> </li>
<li class="right" >
<a href="pg/index.html" title="pg — The Classic PyGreSQL Interface"
>next</a> </li>
<li class="right" >
<a href="general.html" title="General PyGreSQL programming information"
>previous</a> </li>
<li><a href="index.html">PyGreSQL 5.0 documentation</a> »</li>
</ul>
</div>
</div>
<div class="footer" role="contentinfo">
© <a href="../copyright.html">Copyright</a> 2016, The PyGreSQL team.
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.4.1.
</div>
<!-- cloud_sptheme 1.4 -->
</body>
</html>
|
