-
Notifications
You must be signed in to change notification settings - Fork 35
Expand file tree
/
Copy pathdriver.html
More file actions
1848 lines (1839 loc) · 152 KB
/
Copy pathdriver.html
File metadata and controls
1848 lines (1839 loc) · 152 KB
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
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!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>Driver — py-postgresql v1.0.2 documentation</title>
<link rel="stylesheet" href="_static/default.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '',
VERSION: '1.0.2',
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>
<link rel="top" title="py-postgresql v1.0.2 documentation" href="index.html" />
<link rel="next" title="Copy Management" href="copyman.html" />
<link rel="prev" title="Administration" href="admin.html" />
</head>
<body>
<div class="related">
<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="copyman.html" title="Copy Management"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="admin.html" title="Administration"
accesskey="P">previous</a> |</li>
<li><a href="index.html">py-postgresql v1.0.2 documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body">
<div class="section" id="driver">
<span id="db-interface"></span><h1>Driver<a class="headerlink" href="#driver" title="Permalink to this headline">¶</a></h1>
<p><cite>postgresql.driver</cite> provides a PG-API, <cite>postgresql.api</cite>, interface to a
PostgreSQL server using PQ version 3.0 to facilitate communication. It makes
use of the protocol’s extended features to provide binary datatype transmission
and protocol level prepared statements for strongly typed parameters.</p>
<p><cite>postgresql.driver</cite> currently supports PostgreSQL servers as far back as 8.0.
Prior versions are not tested. While any version of PostgreSQL supporting
version 3.0 of the PQ protocol <em>should</em> work, many features may not work due to
absent functionality in the remote end.</p>
<p>For DB-API 2.0 users, the driver module is located at
<cite>postgresql.driver.dbapi20</cite>. The DB-API 2.0 interface extends PG-API. All of the
features discussed in this chapter are available on DB-API connections.</p>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">PostgreSQL versions 8.1 and earlier do not support standard conforming
strings. In order to avoid subjective escape methods on connections,
<cite>postgresql.driver.pq3</cite> enables the <tt class="docutils literal"><span class="pre">standard_conforming_strings</span></tt> setting
by default. Greater care must be taken when working versions that do not
support standard strings.
<strong>The majority of issues surrounding the interpolation of properly quoted literals can be easily avoided by using parameterized statements</strong>.</p>
</div>
<p>The following identifiers are regularly used as shorthands for significant
interface elements:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">db</span></tt></dt>
<dd><cite>postgresql.api.Connection</cite>, a database connection. <a class="reference external" href="#connections">Connections</a></dd>
<dt><tt class="docutils literal"><span class="pre">ps</span></tt></dt>
<dd><cite>postgresql.api.Statement</cite>, a prepared statement. <a class="reference external" href="#prepared-statements">Prepared Statements</a></dd>
<dt><tt class="docutils literal"><span class="pre">c</span></tt></dt>
<dd><cite>postgresql.api.Cursor</cite>, a cursor; the results of a prepared statement.
<a class="reference external" href="#cursors">Cursors</a></dd>
<dt><tt class="docutils literal"><span class="pre">C</span></tt></dt>
<dd><cite>postgresql.api.Connector</cite>, a connector. <a class="reference external" href="#connectors">Connectors</a></dd>
</dl>
</blockquote>
<div class="section" id="establishing-a-connection">
<h2>Establishing a Connection<a class="headerlink" href="#establishing-a-connection" title="Permalink to this headline">¶</a></h2>
<p>There are many ways to establish a <cite>postgresql.api.Connection</cite> to a
PostgreSQL server using <cite>postgresql.driver</cite>. This section discusses those,
connection creation, interfaces.</p>
<div class="section" id="postgresql-open">
<h3><cite>postgresql.open</cite><a class="headerlink" href="#postgresql-open" title="Permalink to this headline">¶</a></h3>
<p>In the root package module, the <tt class="docutils literal"><span class="pre">open()</span></tt> function is provided for accessing
databases using a locator string and optional connection keywords. The string
taken by <cite>postgresql.open</cite> is a URL whose components make up the client
parameters:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">postgresql</span>
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="s">"pq://localhost/postgres"</span><span class="p">)</span>
</pre></div>
</div>
<p>This will connect to the host, <tt class="docutils literal"><span class="pre">localhost</span></tt> and to the database named
<tt class="docutils literal"><span class="pre">postgres</span></tt> via the <tt class="docutils literal"><span class="pre">pq</span></tt> protocol. open will inherit client parameters from
the environment, so the user name given to the server will come from
<tt class="docutils literal"><span class="pre">$PGUSER</span></tt>, or if that is unset, the result of <cite>getpass.getuser</cite>–the username
of the user running the process. The user’s “pgpassfile” will even be
referenced if no password is given:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="s">"pq://username:password@localhost/postgres"</span><span class="p">)</span>
</pre></div>
</div>
<p>In this case, the password <em>is</em> given, so <tt class="docutils literal"><span class="pre">~/.pgpass</span></tt> would never be
referenced. The <tt class="docutils literal"><span class="pre">user</span></tt> client parameter is also given, <tt class="docutils literal"><span class="pre">username</span></tt>, so
<tt class="docutils literal"><span class="pre">$PGUSER</span></tt> or <cite>getpass.getuser</cite> will not be given to the server.</p>
<p>Settings can also be provided by the query portion of the URL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="s">"pq://user@localhost/postgres?search_path=public&timezone=mst"</span><span class="p">)</span>
</pre></div>
</div>
<p>The above syntax ultimately passes the query as settings(see the description of
the <tt class="docutils literal"><span class="pre">settings</span></tt> keyword in <cite>Connection Keywords</cite>). Driver parameters require a
distinction. This distinction is made when the setting’s name is wrapped in
square-brackets, ‘[‘ and ‘]’:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="s">"pq://user@localhost/postgres?[sslmode]=require&[connect_timeout]=5"</span><span class="p">)</span>
</pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">sslmode</span></tt> and <tt class="docutils literal"><span class="pre">connect_timeout</span></tt> are driver parameters. These are never sent
to the server, but if they were not in square-brackets, they would be, and the
driver would never identify them as driver parameters.</p>
<p>The general structure of a PQ-locator is:</p>
<div class="highlight-python"><pre>protocol://user:password@host:port/database?[driver_setting]=value&server_setting=value</pre>
</div>
<p>Optionally, connection keyword arguments can be used to override anything given
in the locator:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="s">"pq://user:secret@host"</span><span class="p">,</span> <span class="n">password</span> <span class="o">=</span> <span class="s">"thE_real_sekrat"</span><span class="p">)</span>
</pre></div>
</div>
<p>Or, if the locator is not desired, individual keywords can be used exclusively:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="n">user</span> <span class="o">=</span> <span class="s">'user'</span><span class="p">,</span> <span class="n">host</span> <span class="o">=</span> <span class="s">'localhost'</span><span class="p">,</span> <span class="n">port</span> <span class="o">=</span> <span class="mi">6543</span><span class="p">)</span>
</pre></div>
</div>
<p>In fact, all arguments to <cite>postgresql.open</cite> are optional as all arguments are
keywords; <tt class="docutils literal"><span class="pre">iri</span></tt> is merely the first keyword argument taken by
<cite>postgresql.open</cite>. If the environment has all the necessary parameters for a
successful connection, there is no need to pass anything to open:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">()</span>
</pre></div>
</div>
<p>For a complete list of keywords that <cite>postgresql.open</cite> can accept, see
<a class="reference external" href="#connection-keywords">Connection Keywords</a>.
For more information about the environment variables, see <a class="reference internal" href="clientparameters.html#pg-envvars"><em>PostgreSQL Environment Variables</em></a>.
For more information about the <tt class="docutils literal"><span class="pre">pgpassfile</span></tt>, see <a class="reference internal" href="clientparameters.html#pg-passfile"><em>PostgreSQL Password File</em></a>.</p>
</div>
<div class="section" id="postgresql-driver-connect">
<h3><cite>postgresql.driver.connect</cite><a class="headerlink" href="#postgresql-driver-connect" title="Permalink to this headline">¶</a></h3>
<p><cite>postgresql.open</cite> is a high-level interface to connection creation. It provides
password resolution services and client parameter inheritance. For some
applications, this is undesirable as such implicit inheritance may lead to
failures due to unanticipated parameters being used. For those applications,
use of <cite>postgresql.open</cite> is not recommended. Rather, <cite>postgresql.driver.connect</cite>
should be used when explicit parameterization is desired by an application:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">postgresql.driver</span> <span class="kn">as</span> <span class="nn">pg_driver</span>
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">pg_driver</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">user</span> <span class="o">=</span> <span class="s">'usename'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">password</span> <span class="o">=</span> <span class="s">'secret'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">host</span> <span class="o">=</span> <span class="s">'localhost'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">port</span> <span class="o">=</span> <span class="mi">5432</span>
<span class="gp">... </span><span class="p">)</span>
</pre></div>
</div>
<p>This will create a connection to the server listening on port <tt class="docutils literal"><span class="pre">5432</span></tt>
on the host <tt class="docutils literal"><span class="pre">localhost</span></tt> as the user <tt class="docutils literal"><span class="pre">usename</span></tt> with the password <tt class="docutils literal"><span class="pre">secret</span></tt>.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last"><cite>connect</cite> will <em>not</em> inherit parameters from the environment as libpq-based drivers do.</p>
</div>
<p>See <a class="reference external" href="#connection-keywords">Connection Keywords</a> for a full list of acceptable keyword parameters and
their meaning.</p>
</div>
<div class="section" id="connectors">
<h3>Connectors<a class="headerlink" href="#connectors" title="Permalink to this headline">¶</a></h3>
<p>Connectors are the supporting objects used to instantiate a connection. They
exist for the purpose of providing connections with the necessary abstractions
for facilitating the client’s communication with the server, <em>and to act as a
container for the client parameters</em>. The latter purpose is of primary interest
to this section.</p>
<p>Each connection object is associated with its connector by the <tt class="docutils literal"><span class="pre">connector</span></tt>
attribute on the connection. This provides the user with access to the
parameters used to establish the connection in the first place, and the means to
create another connection to the same server. The attributes on the connector
should <em>not</em> be altered. If parameter changes are needed, a new connector should
be created.</p>
<p>The attributes available on a connector are consistent with the names of the
connection parameters described in <a class="reference external" href="#connection-keywords">Connection Keywords</a>, so that list can be
used as a reference to identify the information available on the connector.</p>
<p>Connectors fit into the category of “connection creation interfaces”, so
connector instantiation normally takes the same parameters that the
<cite>postgresql.driver.connect</cite> function takes.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Connector implementations are specific to the transport, so keyword arguments
like <tt class="docutils literal"><span class="pre">host</span></tt> and <tt class="docutils literal"><span class="pre">port</span></tt> aren’t supported by the <tt class="docutils literal"><span class="pre">Unix</span></tt> connector.</p>
</div>
<p>The driver, <cite>postgresql.driver.default</cite> provides a set of connectors for making
a connection:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">postgresql.driver.default.host(...)</span></tt></dt>
<dd>Provides a <tt class="docutils literal"><span class="pre">getaddrinfo()</span></tt> abstraction for establishing a connection.</dd>
<dt><tt class="docutils literal"><span class="pre">postgresql.driver.default.ip4(...)</span></tt></dt>
<dd>Connect to a single IPv4 addressed host.</dd>
<dt><tt class="docutils literal"><span class="pre">postgresql.driver.default.ip6(...)</span></tt></dt>
<dd>Connect to a single IPv6 addressed host.</dd>
<dt><tt class="docutils literal"><span class="pre">postgresql.driver.default.unix(...)</span></tt></dt>
<dd>Connect to a single unix domain socket. Requires the <tt class="docutils literal"><span class="pre">unix</span></tt> keyword which
must be an absolute path to the unix domain socket to connect to.</dd>
</dl>
</blockquote>
<p><tt class="docutils literal"><span class="pre">host</span></tt> is the usual connector used to establish a connection:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">C</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">driver</span><span class="o">.</span><span class="n">default</span><span class="o">.</span><span class="n">host</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">user</span> <span class="o">=</span> <span class="s">'auser'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">host</span> <span class="o">=</span> <span class="s">'foo.com'</span><span class="p">,</span>
<span class="gp">... </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="c"># create</span>
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">C</span><span class="p">()</span>
<span class="gp">>>> </span><span class="c"># establish</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
</pre></div>
</div>
<p>If a constant internet address is used, <tt class="docutils literal"><span class="pre">ip4</span></tt> or <tt class="docutils literal"><span class="pre">ip6</span></tt> can be used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">C</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">driver</span><span class="o">.</span><span class="n">default</span><span class="o">.</span><span class="n">ip4</span><span class="p">(</span><span class="n">user</span><span class="o">=</span><span class="s">'auser'</span><span class="p">,</span> <span class="n">host</span><span class="o">=</span><span class="s">'127.0.0.1'</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">db</span> <span class="o">=</span> <span class="n">C</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
</pre></div>
</div>
<p>Additionally, <tt class="docutils literal"><span class="pre">db.connect()</span></tt> on <tt class="docutils literal"><span class="pre">db.__enter__()</span></tt> for with-statement support:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">C</span><span class="p">()</span> <span class="k">as</span> <span class="n">db</span><span class="p">:</span>
<span class="gp">... </span> <span class="o">...</span>
</pre></div>
</div>
<p>Connectors are constant. They have no knowledge of PostgreSQL service files,
environment variables or LDAP services, so changes made to those facilities
will <em>not</em> be reflected in a connector’s configuration. If the latest
information from any of these sources is needed, a new connector needs to be
created as the credentials have changed.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last"><tt class="docutils literal"><span class="pre">host</span></tt> connectors use <tt class="docutils literal"><span class="pre">getaddrinfo()</span></tt>, so if DNS changes are made,
new connections <em>will</em> use the latest information.</p>
</div>
</div>
<div class="section" id="connection-keywords">
<h3>Connection Keywords<a class="headerlink" href="#connection-keywords" title="Permalink to this headline">¶</a></h3>
<p>The following is a list of keywords accepted by connection creation
interfaces:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">user</span></tt></dt>
<dd>The user to connect as.</dd>
<dt><tt class="docutils literal"><span class="pre">password</span></tt></dt>
<dd>The user’s password.</dd>
<dt><tt class="docutils literal"><span class="pre">database</span></tt></dt>
<dd>The name of the database to connect to. (PostgreSQL defaults it to <cite>user</cite>)</dd>
<dt><tt class="docutils literal"><span class="pre">host</span></tt></dt>
<dd>The hostname or IP address to connect to.</dd>
<dt><tt class="docutils literal"><span class="pre">port</span></tt></dt>
<dd>The port on the host to connect to.</dd>
<dt><tt class="docutils literal"><span class="pre">unix</span></tt></dt>
<dd>The unix domain socket to connect to. Exclusive with <tt class="docutils literal"><span class="pre">host</span></tt> and <tt class="docutils literal"><span class="pre">port</span></tt>.
Expects a string containing the <em>absolute path</em> to the unix domain socket to
connect to.</dd>
<dt><tt class="docutils literal"><span class="pre">settings</span></tt></dt>
<dd>A dictionary or key-value pair sequence stating the parameters to give to the
database. These settings are included in the startup packet, and should be
used carefully as when an invalid setting is given, it will cause the
connection to fail.</dd>
<dt><tt class="docutils literal"><span class="pre">connect_timeout</span></tt></dt>
<dd>Amount of time to wait for a connection to be made. (in seconds)</dd>
<dt><tt class="docutils literal"><span class="pre">server_encoding</span></tt></dt>
<dd>Hint given to the driver to properly encode password data and some information
in the startup packet.
This should only be used in cases where connections cannot be made due to
authentication failures that occur while using known-correct credentials.</dd>
<dt><tt class="docutils literal"><span class="pre">sslmode</span></tt></dt>
<dd><dl class="first last docutils">
<dt><tt class="docutils literal"><span class="pre">'disable'</span></tt></dt>
<dd>Don’t allow SSL connections.</dd>
<dt><tt class="docutils literal"><span class="pre">'allow'</span></tt></dt>
<dd>Try without SSL first, but if that doesn’t work, try with.</dd>
<dt><tt class="docutils literal"><span class="pre">'prefer'</span></tt></dt>
<dd>Try SSL first, then without.</dd>
<dt><tt class="docutils literal"><span class="pre">'require'</span></tt></dt>
<dd>Require an SSL connection.</dd>
</dl>
</dd>
<dt><tt class="docutils literal"><span class="pre">sslcrtfile</span></tt></dt>
<dd>Certificate file path given to <cite>ssl.wrap_socket</cite>.</dd>
<dt><tt class="docutils literal"><span class="pre">sslkeyfile</span></tt></dt>
<dd>Key file path given to <cite>ssl.wrap_socket</cite>.</dd>
<dt><tt class="docutils literal"><span class="pre">sslrootcrtfile</span></tt></dt>
<dd>Root certificate file path given to <cite>ssl.wrap_socket</cite></dd>
<dt><tt class="docutils literal"><span class="pre">sslrootcrlfile</span></tt></dt>
<dd>Revocation list file path. [Currently not checked.]</dd>
<dt><tt class="docutils literal"><span class="pre">category</span></tt></dt>
<dd>A <cite>postgresql.api.Category</cite> instance used to further initialize
the database.</dd>
</dl>
</blockquote>
</div>
</div>
<div class="section" id="connections">
<h2>Connections<a class="headerlink" href="#connections" title="Permalink to this headline">¶</a></h2>
<p><cite>postgresql.open</cite> and <cite>postgresql.driver.connect</cite> provide the means to
establish a connection. Connections provide a <cite>postgresql.api.Database</cite>
interface to a PostgreSQL server; specifically, a <cite>postgresql.api.Connection</cite>.</p>
<p>Connections are one-time objects. Once, it is closed or lost, it can longer be
used to interact with the database provided by the server. If further use of the
server is desired, a new connection <em>must</em> be established.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Cannot connect failures, exceptions raised on <tt class="docutils literal"><span class="pre">connect()</span></tt>, are also terminal.</p>
</div>
<p>In cases where operations are performed on a closed connection, a
<cite>postgresql.exceptions.ConnectionDoesNotExistError</cite> will be raised.</p>
<div class="section" id="database-interface-points">
<h3>Database Interface Points<a class="headerlink" href="#database-interface-points" title="Permalink to this headline">¶</a></h3>
<p>After a connection is established:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">postgresql</span>
<span class="gp">>>> </span><span class="n">db</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="o">...</span><span class="p">)</span>
</pre></div>
</div>
<p>The methods and properties on the connection object are ready for use:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Connection.prepare(sql_statement_string)</span></tt></dt>
<dd>Create a <cite>postgresql.api.Statement</cite> object for querying the database.
This provides an “SQL statement template” that can be executed multiple times.
See <a class="reference external" href="#prepared-statements">Prepared Statements</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.proc(procedure_id)</span></tt></dt>
<dd>Create a <cite>postgresql.api.StoredProcedure</cite> object referring to a stored
procedure on the database. The returned object will provide a
<cite>collections.Callable</cite> interface to the stored procedure on the server. See
<a class="reference external" href="#stored-procedures">Stored Procedures</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.statement_from_id(statement_id)</span></tt></dt>
<dd>Create a <cite>postgresql.api.Statement</cite> object from an existing statement
identifier. This is used in cases where the statement was prepared on the
server. See <a class="reference external" href="#prepared-statements">Prepared Statements</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.cursor_from_id(cursor_id)</span></tt></dt>
<dd>Create a <cite>postgresql.api.Cursor</cite> object from an existing cursor identifier.
This is used in cases where the cursor was declared on the server. See
<a class="reference external" href="#cursors">Cursors</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.do(language,</span> <span class="pre">source)</span></tt></dt>
<dd>Execute a DO statement on the server using the specified language.
<em>DO statements are available on PostgreSQL 9.0 and greater.</em>
<em>Executing this method on servers that do not support DO statements will</em>
<em>likely cause a SyntaxError</em>.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.execute(sql_statements_string)</span></tt></dt>
<dd>Run a block of SQL on the server. This method returns <cite>None</cite> unless an error
occurs. If errors occur, the processing of the statements will stop and the
error will be raised.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.xact(isolation</span> <span class="pre">=</span> <span class="pre">None,</span> <span class="pre">mode</span> <span class="pre">=</span> <span class="pre">None)</span></tt></dt>
<dd>The <cite>postgresql.api.Transaction</cite> constructor for creating transactions.
This method creates a transaction reference. The transaction will not be
started until it’s instructed to do so. See <a class="reference external" href="#transactions">Transactions</a> for more
information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings</span></tt></dt>
<dd>A property providing a <cite>collections.MutableMapping</cite> interface to the
database’s SQL settings. See <a class="reference external" href="#settings">Settings</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.clone()</span></tt></dt>
<dd>Create a new connection object based on the same factors that were used to
create <tt class="docutils literal"><span class="pre">db</span></tt>. The new connection returned will already be connected.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.msghook(msg)</span></tt></dt>
<dd>By default, the <cite>msghook</cite> attribute does not exist. If set to a callable, any
message that occurs during an operation of the database or an operation of a
database derived object will be given to the callable. See the
<a class="reference external" href="#database-messages">Database Messages</a> section for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.listen(*channels)</span></tt></dt>
<dd>Start listening for asynchronous notifications in the specified channels.
Sends a batch of <tt class="docutils literal"><span class="pre">LISTEN</span></tt> statements to the server.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.unlisten(*channels)</span></tt></dt>
<dd>Stop listening for asynchronous notifications in the specified channels.
Sends a batch of <tt class="docutils literal"><span class="pre">UNLISTEN</span></tt> statements to the server.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.listening_channels()</span></tt></dt>
<dd>Return an iterator producing the channel names that are currently being
listened to.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.notify(*channels,</span> <span class="pre">**channel_and_payload)</span></tt></dt>
<dd><p class="first">NOTIFY the channels with the given payload. Sends a batch of <tt class="docutils literal"><span class="pre">NOTIFY</span></tt>
statements to the server.</p>
<p>Equivalent to issuing “NOTIFY <channel>” or “NOTIFY <channel>, <payload>”
for each item in <cite>channels</cite> and <cite>channel_and_payload</cite>. All NOTIFYs issued
will occur in the same transaction, regardless of auto-commit.</p>
<p>The items in <cite>channels</cite> can either be a string or a tuple. If a string,
no payload is given, but if an item is a <cite>builtins.tuple</cite>, the second item
in the pair will be given as the payload, and the first as the channel.
<cite>channels</cite> offers a means to issue NOTIFYs in guaranteed order:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">notify</span><span class="p">(</span><span class="s">'channel1'</span><span class="p">,</span> <span class="p">(</span><span class="s">'different_channel'</span><span class="p">,</span> <span class="s">'payload'</span><span class="p">))</span>
</pre></div>
</div>
<p>In the above, <tt class="docutils literal"><span class="pre">NOTIFY</span> <span class="pre">"channel1";</span></tt> will be issued first, followed by
<tt class="docutils literal"><span class="pre">NOTIFY</span> <span class="pre">"different_channel",</span> <span class="pre">'payload';</span></tt>.</p>
<p>The items in <cite>channel_and_payload</cite> are all payloaded NOTIFYs where the
keys are the channels and the values are the payloads. Order is undefined:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">notify</span><span class="p">(</span><span class="n">channel_name</span> <span class="o">=</span> <span class="s">'payload_data'</span><span class="p">)</span>
</pre></div>
</div>
<p class="last"><cite>channels</cite> and <cite>channels_and_payload</cite> can be used together. In such cases all
NOTIFY statements generated from <cite>channels_and_payload</cite> will follow those in
<cite>channels</cite>.</p>
</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.iternotifies(timeout</span> <span class="pre">=</span> <span class="pre">None)</span></tt></dt>
<dd>Return an iterator to the NOTIFYs received on the connection. The iterator
will yield notification triples consisting of <tt class="docutils literal"><span class="pre">(channel,</span> <span class="pre">payload,</span> <span class="pre">pid)</span></tt>.
While iterating, the connection should <em>not</em> be used in other threads.
The optional timeout can be used to enable “idle” events in which <cite>None</cite>
objects will be yielded by the iterator.
See <a class="reference internal" href="notifyman.html#notifyman"><em>Notification Management</em></a> for details.</dd>
</dl>
</blockquote>
<p>When a connection is established, certain pieces of information are collected from
the backend. The following are the attributes set on the connection object after
the connection is made:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Connection.version</span></tt></dt>
<dd>The version string of the <em>server</em>; the result of <tt class="docutils literal"><span class="pre">SELECT</span> <span class="pre">version()</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.version_info</span></tt></dt>
<dd>A <tt class="docutils literal"><span class="pre">sys.version_info</span></tt> form of the <tt class="docutils literal"><span class="pre">server_version</span></tt> setting. eg.
<tt class="docutils literal"><span class="pre">(8,</span> <span class="pre">1,</span> <span class="pre">2,</span> <span class="pre">'final',</span> <span class="pre">0)</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.security</span></tt></dt>
<dd><cite>None</cite> if no security. <tt class="docutils literal"><span class="pre">'ssl'</span></tt> if SSL is enabled.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.backend_id</span></tt></dt>
<dd>The process-id of the backend process.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.backend_start</span></tt></dt>
<dd>When backend was started. <tt class="docutils literal"><span class="pre">datetime.datetime</span></tt> instance.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.client_address</span></tt></dt>
<dd>The address of the client that the backend is communicating with.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.client_port</span></tt></dt>
<dd>The port of the client that the backend is communicating with.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.fileno()</span></tt></dt>
<dd>Method to get the file descriptor number of the connection’s socket. This
method will return <cite>None</cite> if the socket object does not have a <tt class="docutils literal"><span class="pre">fileno</span></tt>.
Under normal circumstances, it will return an <cite>int</cite>.</dd>
</dl>
</blockquote>
<p>The <tt class="docutils literal"><span class="pre">backend_start</span></tt>, <tt class="docutils literal"><span class="pre">client_address</span></tt>, and <tt class="docutils literal"><span class="pre">client_port</span></tt> are collected
from pg_stat_activity. If this information is unavailable, the attributes will
be <cite>None</cite>.</p>
</div>
</div>
<div class="section" id="prepared-statements">
<h2>Prepared Statements<a class="headerlink" href="#prepared-statements" title="Permalink to this headline">¶</a></h2>
<p>Prepared statements are the primary entry point for initiating an operation on
the database. Prepared statement objects represent a request that will, likely,
be sent to the database at some point in the future. A statement is a single
SQL command.</p>
<p>The <tt class="docutils literal"><span class="pre">prepare</span></tt> entry point on the connection provides the standard method for
creating a <cite>postgersql.api.Statement</cite> instance bound to the
connection(<tt class="docutils literal"><span class="pre">db</span></tt>) from an SQL statement string:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT 1"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">()</span>
<span class="go">[(1,)]</span>
</pre></div>
</div>
<p>Statement objects may also be created from a statement identifier using the
<tt class="docutils literal"><span class="pre">statement_from_id</span></tt> method on the connection. When this method is used, the
statement must have already been prepared or an error will be raised.</p>
<div class="highlight-python"><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">"PREPARE a_statement_id AS SELECT 1;"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">statement_from_id</span><span class="p">(</span><span class="s">'a_statement_id'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">()</span>
<span class="go">[(1,)]</span>
</pre></div>
</div>
<p>When a statement is executed, it binds any given parameters to a <em>new</em> cursor
and the entire result-set is returned.</p>
<p>Statements created using <tt class="docutils literal"><span class="pre">prepare()</span></tt> will leverage garbage collection in order
to automatically close statements that are no longer referenced. However,
statements created from pre-existing identifiers, <tt class="docutils literal"><span class="pre">statement_from_id</span></tt>, must
be explicitly closed if the statement is to be discarded.</p>
<p>Statement objects are one-time objects. Once closed, they can no longer be used.</p>
<div class="section" id="statement-interface-points">
<h3>Statement Interface Points<a class="headerlink" href="#statement-interface-points" title="Permalink to this headline">¶</a></h3>
<p>Prepared statements can be executed just like functions:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT 'hello, world!'"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">()</span>
<span class="go">[('hello, world!',)]</span>
</pre></div>
</div>
<p>The default execution method, <tt class="docutils literal"><span class="pre">__call__</span></tt>, produces the entire result set. It
is the simplest form of statement execution. Statement objects can be executed in
different ways to accommodate for the larger results or random access(scrollable
cursors).</p>
<p>Prepared statement objects have a few execution methods:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Statement(*parameters)</span></tt></dt>
<dd>As shown before, statement objects can be invoked like a function to get
the statement’s results.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.rows(*parameters)</span></tt></dt>
<dd>Return a iterator to all the rows produced by the statement. This
method will stream rows on demand, so it is ideal for situations where
each individual row in a large result-set must be processed.</dd>
<dt><tt class="docutils literal"><span class="pre">iter(Statement)</span></tt></dt>
<dd><p class="first">Convenience interface that executes the <tt class="docutils literal"><span class="pre">rows()</span></tt> method without arguments.
This enables the following syntax:</p>
<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">for</span> <span class="n">table_name</span><span class="p">,</span> <span class="ow">in</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT table_name FROM information_schema.tables"</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">print</span><span class="p">(</span><span class="n">table_name</span><span class="p">)</span>
</pre></div>
</div>
</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.column(*parameters)</span></tt></dt>
<dd>Return a iterator to the first column produced by the statement. This
method will stream values on demand, and <em>should</em> only be used with statements
that have a single column; otherwise, bandwidth will ultimately be wasted as
the other columns will be dropped.
<em>This execution method cannot be used with COPY statements.</em></dd>
<dt><tt class="docutils literal"><span class="pre">Statement.first(*parameters)</span></tt></dt>
<dd><p class="first">For simple statements, cursor objects are unnecessary.
Consider the data contained in <tt class="docutils literal"><span class="pre">c</span></tt> from above, ‘hello world!’. To get at this
data directly from the <tt class="docutils literal"><span class="pre">__call__(...)</span></tt> method, it looks something like:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT 'hello, world!'"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">()[</span><span class="mi">0</span><span class="p">][</span><span class="mi">0</span><span class="p">]</span>
<span class="go">'hello, world!'</span>
</pre></div>
</div>
<p>To simplify access to simple data, the <tt class="docutils literal"><span class="pre">first</span></tt> method will simply return
the “first” of the result set:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="go">'hello, world!'</span>
</pre></div>
</div>
<dl class="docutils">
<dt>The first value.</dt>
<dd>When the result set consists of a single column, <tt class="docutils literal"><span class="pre">first()</span></tt> will return
that column in the first row.</dd>
<dt>The first row.</dt>
<dd>When the result set consists of multiple columns, <tt class="docutils literal"><span class="pre">first()</span></tt> will return
that first row.</dd>
<dt>The first, and only, row count.</dt>
<dd><p class="first">When DML–for instance, an INSERT-statement–is executed, <tt class="docutils literal"><span class="pre">first()</span></tt> will
return the row count returned by the statement as an integer.</p>
<div class="last admonition note">
<p class="first admonition-title">Note</p>
<p class="last">DML that returns row data, RETURNING, will <em>not</em> return a row count.</p>
</div>
</dd>
</dl>
<p class="last">The result set created by the statement determines what is actually returned.
Naturally, a statement used with <tt class="docutils literal"><span class="pre">first()</span></tt> should be crafted with these
rules in mind.</p>
</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.chunks(*parameters)</span></tt></dt>
<dd>This access point is designed for situations where rows are being streamed out
quickly. It is a method that returns a <tt class="docutils literal"><span class="pre">collections.Iterator</span></tt> that produces
<em>sequences</em> of rows. This is the most efficient way to get rows from the
database. The rows in the sequences are <tt class="docutils literal"><span class="pre">builtins.tuple</span></tt> objects.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.declare(*parameters)</span></tt></dt>
<dd>Create a scrollable cursor with hold. This returns a <cite>postgresql.api.Cursor</cite>
ready for accessing random rows in the result-set. Applications that use the
database to support paging should use this method to manage the view.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.close()</span></tt></dt>
<dd>Close the statement inhibiting further use.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.load_rows(collections.Iterable(parameters))</span></tt></dt>
<dd>Given an iterable producing parameters, execute the statement for each
iteration. Always returns <cite>None</cite>.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.load_chunks(collections.Iterable(collections.Iterable(parameters)))</span></tt></dt>
<dd><p class="first">Given an iterable of iterables producing parameters, execute the statement
for each parameter produced. However, send the all execution commands with
the corresponding parameters of each chunk before reading any results.
Always returns <cite>None</cite>. This access point is designed to be used in conjunction
with <tt class="docutils literal"><span class="pre">Statement.chunks()</span></tt> for transferring rows from one connection to another with
great efficiency:</p>
<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">dst</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="o">...</span><span class="p">)</span><span class="o">.</span><span class="n">load_chunks</span><span class="p">(</span><span class="n">src</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="o">...</span><span class="p">)</span><span class="o">.</span><span class="n">chunks</span><span class="p">())</span>
</pre></div>
</div>
</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.clone()</span></tt></dt>
<dd>Create a new statement object based on the same factors that were used to
create <tt class="docutils literal"><span class="pre">ps</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.msghook(msg)</span></tt></dt>
<dd>By default, the <cite>msghook</cite> attribute does not exist. If set to a callable, any
message that occurs during an operation of the statement or an operation of a
statement derived object will be given to the callable. See the
<a class="reference external" href="#database-messages">Database Messages</a> section for more information.</dd>
</dl>
</blockquote>
<p>In order to provide the appropriate type transformations, the driver must
acquire metadata about the statement’s parameters and results. This data is
published via the following properties on the statement object:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Statement.sql_parameter_types</span></tt></dt>
<dd>A sequence of SQL type names specifying the types of the parameters used in
the statement.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.sql_column_types</span></tt></dt>
<dd>A sequence of SQL type names specifying the types of the columns produced by
the statement. <cite>None</cite> if the statement does not return row-data.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.pg_parameter_types</span></tt></dt>
<dd>A sequence of PostgreSQL type Oid’s specifying the types of the parameters
used in the statement.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.pg_column_types</span></tt></dt>
<dd>A sequence of PostgreSQL type Oid’s specifying the types of the columns produced by
the statement. <cite>None</cite> if the statement does not return row-data.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.parameter_types</span></tt></dt>
<dd>A sequence of Python types that the statement expects.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.column_types</span></tt></dt>
<dd>A sequence of Python types that the statement will produce.</dd>
<dt><tt class="docutils literal"><span class="pre">Statement.column_names</span></tt></dt>
<dd>A sequence of <cite>str</cite> objects specifying the names of the columns produced by
the statement. <cite>None</cite> if the statement does not return row-data.</dd>
</dl>
</blockquote>
<p>The indexes of the parameter sequences correspond to the parameter’s
identifier, N+1: <tt class="docutils literal"><span class="pre">sql_parameter_types[0]</span></tt> -> <tt class="docutils literal"><span class="pre">'$1'</span></tt>.</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT $1::integer AS intname, $2::varchar AS chardata"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">sql_parameter_types</span>
<span class="go">('INTEGER','VARCHAR')</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">sql_column_types</span>
<span class="go">('INTEGER','VARCHAR')</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">column_names</span>
<span class="go">('intname','chardata')</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">column_types</span>
<span class="go">(<class 'int'>, <class 'str'>)</span>
</pre></div>
</div>
</div>
<div class="section" id="parameterized-statements">
<h3>Parameterized Statements<a class="headerlink" href="#parameterized-statements" title="Permalink to this headline">¶</a></h3>
<p>Statements can take parameters. Using statement parameters is the recommended
way to interrogate the database when variable information is needed to formulate
a complete request. In order to do this, the statement must be defined using
PostgreSQL’s positional parameter notation. <tt class="docutils literal"><span class="pre">$1</span></tt>, <tt class="docutils literal"><span class="pre">$2</span></tt>, <tt class="docutils literal"><span class="pre">$3</span></tt>, etc:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT $1"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">(</span><span class="s">'hello, world!'</span><span class="p">)[</span><span class="mi">0</span><span class="p">][</span><span class="mi">0</span><span class="p">]</span>
<span class="go">'hello, world!'</span>
</pre></div>
</div>
<p>PostgreSQL determines the type of the parameter based on the context of the
parameter’s identifier:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span>
<span class="gp">... </span> <span class="s">"SELECT * FROM information_schema.tables WHERE table_name = $1 LIMIT $2"</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="p">(</span><span class="s">"tables"</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span>
<span class="go">[('postgres', 'information_schema', 'tables', 'VIEW', None, None, None, None, None, 'NO', 'NO', None)]</span>
</pre></div>
</div>
<p>Parameter <tt class="docutils literal"><span class="pre">$1</span></tt> in the above statement will take on the type of the
<tt class="docutils literal"><span class="pre">table_name</span></tt> column and <tt class="docutils literal"><span class="pre">$2</span></tt> will take on the type required by the LIMIT
clause(text and int8).</p>
<p>However, parameters can be forced to a specific type using explicit casts:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT $1::integer"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">first</span><span class="p">(</span><span class="o">-</span><span class="mi">400</span><span class="p">)</span>
<span class="go">-400</span>
</pre></div>
</div>
<p>Parameters are typed. PostgreSQL servers provide the driver with the
type information about a positional parameter, and the serialization routine
will raise an exception if the given object is inappropriate. The Python
types expected by the driver for a given SQL-or-PostgreSQL type are listed
in <a class="reference external" href="#type-support">Type Support</a>.</p>
<p>This usage of types is not always convenient. Notably, the <cite>datetime</cite> module
does not provide a friendly way for a user to express intervals, dates, or
times. There is a likely inclination to forego these parameter type
requirements.</p>
<p>In such cases, explicit casts can be made to work-around the type
requirements:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT $1::text::date"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">first</span><span class="p">(</span><span class="s">'yesterday'</span><span class="p">)</span>
<span class="go">datetime.date(2009, 3, 11)</span>
</pre></div>
</div>
<p>The parameter, <tt class="docutils literal"><span class="pre">$1</span></tt>, is given to the database as a string, which is then
promptly cast into a date. Of course, without the explicit cast as text, the
outcome would be different:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"SELECT $1::date"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ps</span><span class="o">.</span><span class="n">first</span><span class="p">(</span><span class="s">'yesterday'</span><span class="p">)</span>
<span class="go">Traceback:</span>
<span class="go"> ...</span>
<span class="go">postgresql.exceptions.ParameterError</span>
</pre></div>
</div>
<p>The function that processes the parameter expects a <cite>datetime.date</cite> object, and
the given <cite>str</cite> object does not provide the necessary interfaces for the
conversion, so the driver raises a <cite>postgresql.exceptions.ParameterError</cite> from
the original conversion exception.</p>
</div>
<div class="section" id="inserting-and-dml">
<h3>Inserting and DML<a class="headerlink" href="#inserting-and-dml" title="Permalink to this headline">¶</a></h3>
<p>Loading data into the database is facilitated by prepared statements. In these
examples, a table definition is necessary for a complete illustration:</p>
<div class="highlight-python"><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="gp">... </span> <span class="sd">"""</span>
<span class="gp">... </span><span class="sd">CREATE TABLE employee (</span>
<span class="gp">... </span><span class="sd"> employee_name text,</span>
<span class="gp">... </span><span class="sd"> employee_salary numeric,</span>
<span class="gp">... </span><span class="sd"> employee_dob date,</span>
<span class="gp">... </span><span class="sd"> employee_hire_date date</span>
<span class="gp">... </span><span class="sd">);</span>
<span class="gp">... </span><span class="sd"> """</span>
<span class="gp">... </span><span class="p">)</span>
</pre></div>
</div>
<p>Create an INSERT statement using <tt class="docutils literal"><span class="pre">prepare</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">mkemp</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"INSERT INTO employee VALUES ($1, $2, $3, $4)"</span><span class="p">)</span>
</pre></div>
</div>
<p>And add “Mr. Johnson” to the table:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">datetime</span>
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">mkemp</span><span class="p">(</span>
<span class="gp">... </span> <span class="s">"John Johnson"</span><span class="p">,</span>
<span class="gp">... </span> <span class="s">"92000"</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">datetime</span><span class="o">.</span><span class="n">date</span><span class="p">(</span><span class="mi">1950</span><span class="p">,</span> <span class="mi">12</span><span class="p">,</span> <span class="mi">10</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">datetime</span><span class="o">.</span><span class="n">date</span><span class="p">(</span><span class="mi">1998</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">23</span><span class="p">)</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">r</span><span class="p">[</span><span class="mi">0</span><span class="p">])</span>
<span class="go">INSERT</span>
<span class="gp">>>> </span><span class="k">print</span><span class="p">(</span><span class="n">r</span><span class="p">[</span><span class="mi">1</span><span class="p">])</span>
<span class="go">1</span>
</pre></div>
</div>
<p>The execution of DML will return a tuple. This tuple contains the completed
command name and the associated row count.</p>
<p>Using the call interface is fine for making a single insert, but when multiple
records need to be inserted, it’s not the most efficient means to load data. For
multiple records, the <tt class="docutils literal"><span class="pre">ps.load_rows([...])</span></tt> provides an efficient way to load
large quantities of structured data:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">datetime</span> <span class="kn">import</span> <span class="n">date</span>
<span class="gp">>>> </span><span class="n">mkemp</span><span class="o">.</span><span class="n">load_rows</span><span class="p">([</span>
<span class="gp">... </span> <span class="p">(</span><span class="s">"Jack Johnson"</span><span class="p">,</span> <span class="s">"85000"</span><span class="p">,</span> <span class="n">date</span><span class="p">(</span><span class="mi">1962</span><span class="p">,</span> <span class="mi">11</span><span class="p">,</span> <span class="mi">23</span><span class="p">),</span> <span class="n">date</span><span class="p">(</span><span class="mi">1990</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">5</span><span class="p">)),</span>
<span class="gp">... </span> <span class="p">(</span><span class="s">"Debra McGuffer"</span><span class="p">,</span> <span class="s">"52000"</span><span class="p">,</span> <span class="n">date</span><span class="p">(</span><span class="mi">1973</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span> <span class="n">date</span><span class="p">(</span><span class="mi">2002</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">14</span><span class="p">)),</span>
<span class="gp">... </span> <span class="p">(</span><span class="s">"Barbara Smith"</span><span class="p">,</span> <span class="s">"86000"</span><span class="p">,</span> <span class="n">date</span><span class="p">(</span><span class="mi">1965</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">24</span><span class="p">),</span> <span class="n">date</span><span class="p">(</span><span class="mi">2005</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="mi">19</span><span class="p">)),</span>
<span class="gp">... </span><span class="p">])</span>
</pre></div>
</div>
<p>While small, the above illustrates the <tt class="docutils literal"><span class="pre">ps.load_rows()</span></tt> method taking an
iterable of tuples that provides parameters for the each execution of the
statement.</p>
<p><tt class="docutils literal"><span class="pre">load_rows</span></tt> is also used to support <tt class="docutils literal"><span class="pre">COPY</span> <span class="pre">...</span> <span class="pre">FROM</span> <span class="pre">STDIN</span></tt> statements:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">copy_emps_in</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"COPY employee FROM STDIN"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">copy_emps_in</span><span class="o">.</span><span class="n">load_rows</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">b</span><span class="s">'Emp Name1</span><span class="se">\t</span><span class="s">72000</span><span class="se">\t</span><span class="s">1970-2-01</span><span class="se">\t</span><span class="s">1980-10-22</span><span class="se">\n</span><span class="s">'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">b</span><span class="s">'Emp Name2</span><span class="se">\t</span><span class="s">62000</span><span class="se">\t</span><span class="s">1968-9-11</span><span class="se">\t</span><span class="s">1985-11-1</span><span class="se">\n</span><span class="s">'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">b</span><span class="s">'Emp Name3</span><span class="se">\t</span><span class="s">62000</span><span class="se">\t</span><span class="s">1968-9-11</span><span class="se">\t</span><span class="s">1985-11-1</span><span class="se">\n</span><span class="s">'</span><span class="p">,</span>
<span class="gp">... </span><span class="p">])</span>
</pre></div>
</div>
<p>Copy data goes in as bytes and come out as bytes regardless of the type of COPY
taking place. It is the user’s obligation to make sure the row-data is in the
appropriate encoding.</p>
</div>
<div class="section" id="copy-statements">
<h3>COPY Statements<a class="headerlink" href="#copy-statements" title="Permalink to this headline">¶</a></h3>
<p><cite>postgresql.driver</cite> transparently supports PostgreSQL’s COPY command. To the
user, COPY will act exactly like other statements that produce tuples; COPY
tuples, however, are <cite>bytes</cite> objects. The only distinction in usability is that
the COPY <em>should</em> be completed before other actions take place on the
connection–this is important when a COPY is invoked via <tt class="docutils literal"><span class="pre">rows()</span></tt> or
<tt class="docutils literal"><span class="pre">chunks()</span></tt>.</p>
<p>In situations where other actions are invoked during a <tt class="docutils literal"><span class="pre">COPY</span> <span class="pre">TO</span> <span class="pre">STDOUT</span></tt>, the
entire result set of the COPY will be read. However, no error will be raised so
long as there is enough memory available, so it is <em>very</em> desirable to avoid
doing other actions on the connection while a COPY is active.</p>
<p>In situations where other actions are invoked during a <tt class="docutils literal"><span class="pre">COPY</span> <span class="pre">FROM</span> <span class="pre">STDIN</span></tt>, a
COPY failure error will occur. The driver manages the connection state in such
a way that will purposefully cause the error as the COPY was inappropriately
interrupted. This not usually a problem as <tt class="docutils literal"><span class="pre">load_rows(...)</span></tt> and
<tt class="docutils literal"><span class="pre">load_chunks(...)</span></tt> methods must complete the COPY command before returning.</p>
<p>Copy data is always transferred using <tt class="docutils literal"><span class="pre">bytes</span></tt> objects. Even in cases where the
COPY is not in <tt class="docutils literal"><span class="pre">BINARY</span></tt> mode. Any needed encoding transformations <em>must</em> be
made the caller. This is done to avoid any unnecessary overhead by default:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ps</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"COPY (SELECT i FROM generate_series(0, 99) AS g(i)) TO STDOUT"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">ps</span><span class="p">()</span>
<span class="gp">>>> </span><span class="nb">len</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
<span class="go">100</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="go">b'0\n'</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="o">-</span><span class="mi">1</span><span class="p">]</span>
<span class="go">b'99\n'</span>
</pre></div>
</div>
<p>Of course, invoking a statement that way will read the entire result-set into
memory, which is not usually desirable for COPY. Using the <tt class="docutils literal"><span class="pre">chunks(...)</span></tt>
iterator is the <em>fastest</em> way to move data:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ci</span> <span class="o">=</span> <span class="n">ps</span><span class="o">.</span><span class="n">chunks</span><span class="p">()</span>
<span class="gp">>>> </span><span class="kn">import</span> <span class="nn">sys</span>
<span class="gp">>>> </span><span class="k">for</span> <span class="n">rowset</span> <span class="ow">in</span> <span class="n">ps</span><span class="o">.</span><span class="n">chunks</span><span class="p">():</span>
<span class="gp">... </span> <span class="n">sys</span><span class="o">.</span><span class="n">stdout</span><span class="o">.</span><span class="n">buffer</span><span class="o">.</span><span class="n">writelines</span><span class="p">(</span><span class="n">rowset</span><span class="p">)</span>
<span class="gp">...</span>
<span class="go"><lots of data></span>
</pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">COPY</span> <span class="pre">FROM</span> <span class="pre">STDIN</span></tt> commands are supported via
<cite>postgresql.api.Statement.load_rows</cite>. Each invocation to
<tt class="docutils literal"><span class="pre">load_rows</span></tt> is a single invocation of COPY. <tt class="docutils literal"><span class="pre">load_rows</span></tt> takes an iterable of
COPY lines to send to the server:</p>
<div class="highlight-python"><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">"""</span>
<span class="gp">... </span><span class="s">CREATE TABLE sample_copy (</span>
<span class="gp">... </span><span class="s"> sc_number int,</span>
<span class="gp">... </span><span class="s"> sc_text text</span>
<span class="gp">... </span><span class="s">);</span>
<span class="gp">... </span><span class="s">"""</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">copyin</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">'COPY sample_copy FROM STDIN'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">copyin</span><span class="o">.</span><span class="n">load_rows</span><span class="p">([</span>
<span class="gp">... </span> <span class="n">b</span><span class="s">'123</span><span class="se">\t</span><span class="s">one twenty three</span><span class="se">\n</span><span class="s">'</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">b</span><span class="s">'350</span><span class="se">\t</span><span class="s">tree fitty</span><span class="se">\n</span><span class="s">'</span><span class="p">,</span>
<span class="gp">... </span><span class="p">])</span>
</pre></div>
</div>
<p>For direct connection-to-connection COPY, use of <tt class="docutils literal"><span class="pre">load_chunks(...)</span></tt> is
recommended as it will provide the most efficient transfer method:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">copyout</span> <span class="o">=</span> <span class="n">src</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">'COPY atable TO STDOUT'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">copyin</span> <span class="o">=</span> <span class="n">dst</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">'COPY atable FROM STDIN'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">copyin</span><span class="o">.</span><span class="n">load_chunks</span><span class="p">(</span><span class="n">copyout</span><span class="o">.</span><span class="n">chunks</span><span class="p">())</span>
</pre></div>
</div>
<p>Specifically, each chunk of row data produced by <tt class="docutils literal"><span class="pre">chunks()</span></tt> will be written in
full by <tt class="docutils literal"><span class="pre">load_chunks()</span></tt> before getting another chunk to write.</p>
</div>
</div>
<div class="section" id="cursors">
<h2>Cursors<a class="headerlink" href="#cursors" title="Permalink to this headline">¶</a></h2>
<p>When a prepared statement is declared, <tt class="docutils literal"><span class="pre">ps.declare(...)</span></tt>, a
<cite>postgresql.api.Cursor</cite> is created and returned for random access to the rows in
the result set. Direct use of cursors is primarily useful for applications that
need to implement paging. For situations that need to iterate over the result
set, the <tt class="docutils literal"><span class="pre">ps.rows(...)</span></tt> or <tt class="docutils literal"><span class="pre">ps.chunks(...)</span></tt> execution methods should be
used.</p>
<p>Cursors can also be created directly from <tt class="docutils literal"><span class="pre">cursor_id</span></tt>‘s using the
<tt class="docutils literal"><span class="pre">cursor_from_id</span></tt> method on connection objects:</p>
<div class="highlight-python"><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">'DECLARE the_cursor_id CURSOR WITH HOLD FOR SELECT 1;'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">c</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">cursor_from_id</span><span class="p">(</span><span class="s">'the_cursor_id'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">read</span><span class="p">()</span>
<span class="go">[(1,)]</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
</pre></div>
</div>
<div class="admonition hint">
<p class="first admonition-title">Hint</p>
<p class="last">If the cursor that needs to be opened is going to be treated as an iterator,
then a FETCH-statement should be prepared instead using <tt class="docutils literal"><span class="pre">cursor_from_id</span></tt>.</p>
</div>
<p>Like statements created from an identifier, cursors created from an identifier
must be explicitly closed in order to destroy the object on the server.
Likewise, cursors created from statement invocations will be automatically
released when they are no longer referenced.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">PG-API cursors are a direct interface to single result-set SQL cursors. This
is in contrast with DB-API cursors, which have interfaces for dealing with
multiple result-sets. There is no execute method on PG-API cursors.</p>
</div>
<div class="section" id="cursor-interface-points">
<h3>Cursor Interface Points<a class="headerlink" href="#cursor-interface-points" title="Permalink to this headline">¶</a></h3>
<p>For cursors that return row data, these interfaces are provided for accessing
those results:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Cursor.read(quantity</span> <span class="pre">=</span> <span class="pre">None,</span> <span class="pre">direction</span> <span class="pre">=</span> <span class="pre">None)</span></tt></dt>
<dd><p class="first">This method name is borrowed from <cite>file</cite> objects, and are semantically
similar. However, this being a cursor, rows are returned instead of bytes or
characters. When the number of rows returned is less then the quantity
requested, it means that the cursor has been exhausted in the configured
direction. The <tt class="docutils literal"><span class="pre">direction</span></tt> argument can be either <tt class="docutils literal"><span class="pre">'FORWARD'</span></tt> or <cite>True</cite>
to FETCH FORWARD, or <tt class="docutils literal"><span class="pre">'BACKWARD'</span></tt> or <cite>False</cite> to FETCH BACKWARD.</p>
<p class="last">Like, <tt class="docutils literal"><span class="pre">seek()</span></tt>, the <tt class="docutils literal"><span class="pre">direction</span></tt> <em>property</em> on the cursor object effects
this method.</p>
</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.seek(position[,</span> <span class="pre">whence</span> <span class="pre">=</span> <span class="pre">0])</span></tt></dt>
<dd>When the cursor is scrollable, this seek interface can be used to move the
position of the cursor. See <a class="reference external" href="#scrollable-cursors">Scrollable Cursors</a> for more information.</dd>
<dt><tt class="docutils literal"><span class="pre">next(Cursor)</span></tt></dt>
<dd>This fetches the next row in the cursor object. Cursors support the iterator
protocol. While equivalent to <tt class="docutils literal"><span class="pre">cursor.read(1)[0]</span></tt>, <cite>StopIteration</cite> is raised
if the returned sequence is empty. (<tt class="docutils literal"><span class="pre">__next__()</span></tt>)</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.close()</span></tt></dt>
<dd>For cursors opened using <tt class="docutils literal"><span class="pre">cursor_from_id()</span></tt>, this method must be called in
order to <tt class="docutils literal"><span class="pre">CLOSE</span></tt> the cursor. For cursors created by invoking a prepared
statement, this is not necessary as the garbage collection interface will take
the appropriate steps.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.clone()</span></tt></dt>
<dd>Create a new cursor object based on the same factors that were used to
create <tt class="docutils literal"><span class="pre">c</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.msghook(msg)</span></tt></dt>
<dd>By default, the <cite>msghook</cite> attribute does not exist. If set to a callable, any
message that occurs during an operation of the cursor will be given to the
callable. See the <a class="reference external" href="#database-messages">Database Messages</a> section for more information.</dd>
</dl>
</blockquote>
<p>Cursors have some additional configuration properties that may be modified
during the use of the cursor:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Cursor.direction</span></tt></dt>
<dd>A value of <cite>True</cite>, the default, will cause read to fetch forwards, whereas a
value of <cite>False</cite> will cause it to fetch backwards. <tt class="docutils literal"><span class="pre">'BACKWARD'</span></tt> and
<tt class="docutils literal"><span class="pre">'FORWARD'</span></tt> can be used instead of <cite>False</cite> and <cite>True</cite>.</dd>
</dl>
</blockquote>
<p>Cursors normally share metadata with the statements that create them, so it is
usually unnecessary for referencing the cursor’s column descriptions directly.
However, when a cursor is opened from an identifier, the cursor interface must
collect the metadata itself. These attributes provide the metadata in absence of
a statement object:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Cursor.sql_column_types</span></tt></dt>
<dd>A sequence of SQL type names specifying the types of the columns produced by
the cursor. <cite>None</cite> if the cursor does not return row-data.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.pg_column_types</span></tt></dt>
<dd>A sequence of PostgreSQL type Oid’s specifying the types of the columns produced by
the cursor. <cite>None</cite> if the cursor does not return row-data.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.column_types</span></tt></dt>
<dd>A sequence of Python types that the cursor will produce.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.column_names</span></tt></dt>
<dd>A sequence of <cite>str</cite> objects specifying the names of the columns produced by
the cursor. <cite>None</cite> if the cursor does not return row-data.</dd>
<dt><tt class="docutils literal"><span class="pre">Cursor.statement</span></tt></dt>
<dd>The statement that was executed that created the cursor. <cite>None</cite> if
unknown–<tt class="docutils literal"><span class="pre">db.cursor_from_id()</span></tt>.</dd>
</dl>
</blockquote>
</div>
<div class="section" id="scrollable-cursors">
<h3>Scrollable Cursors<a class="headerlink" href="#scrollable-cursors" title="Permalink to this headline">¶</a></h3>
<p>Scrollable cursors are supported for applications that need to implement paging.
When statements are invoked via the <tt class="docutils literal"><span class="pre">declare(...)</span></tt> method, the returned cursor
is scrollable.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Scrollable cursors never pre-fetch in order to provide guaranteed positioning.</p>
</div>
<p>The cursor interface supports scrolling using the <tt class="docutils literal"><span class="pre">seek</span></tt> method. Like
<tt class="docutils literal"><span class="pre">read</span></tt>, it is semantically similar to a file object’s <tt class="docutils literal"><span class="pre">seek()</span></tt>.</p>
<p><tt class="docutils literal"><span class="pre">seek</span></tt> takes two arguments: <tt class="docutils literal"><span class="pre">position</span></tt> and <tt class="docutils literal"><span class="pre">whence</span></tt>:</p>
<blockquote>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">position</span></tt></dt>
<dd>The position to scroll to. The meaning of this is determined by <tt class="docutils literal"><span class="pre">whence</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">whence</span></tt></dt>
<dd><p class="first">How to use the position: absolute, relative, or absolute from end:</p>
<blockquote class="last">
<dl class="docutils">
<dt>absolute: <tt class="docutils literal"><span class="pre">'ABSOLUTE'</span></tt> or <tt class="docutils literal"><span class="pre">0</span></tt> (default)</dt>
<dd>seek to the absolute position in the cursor relative to the beginning of the
cursor.</dd>
<dt>relative: <tt class="docutils literal"><span class="pre">'RELATIVE'</span></tt> or <tt class="docutils literal"><span class="pre">1</span></tt></dt>
<dd>seek to the relative position. Negative <tt class="docutils literal"><span class="pre">position</span></tt>‘s will cause a MOVE
backwards, while positive <tt class="docutils literal"><span class="pre">position</span></tt>‘s will MOVE forwards.</dd>
<dt>from end: <tt class="docutils literal"><span class="pre">'FROM_END'</span></tt> or <tt class="docutils literal"><span class="pre">2</span></tt></dt>
<dd>seek to the end of the cursor and then MOVE backwards by the given
<tt class="docutils literal"><span class="pre">position</span></tt>.</dd>
</dl>
</blockquote>
</dd>
</dl>
</blockquote>
<p>The <tt class="docutils literal"><span class="pre">whence</span></tt> keyword argument allows for either numeric and textual
specifications.</p>
<p>Scrolling through employees:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">emps_by_age</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">"""</span>
<span class="gp">... </span><span class="s">SELECT</span>
<span class="gp">... </span><span class="s"> employee_name, employee_salary, employee_dob, employee_hire_date,</span>
<span class="gp">... </span><span class="s"> EXTRACT(years FROM AGE(employee_dob)) AS age</span>
<span class="gp">... </span><span class="s">ORDER BY age ASC</span>
<span class="gp">... </span><span class="s">"""</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">c</span> <span class="o">=</span> <span class="n">emps_by_age</span><span class="o">.</span><span class="n">declare</span><span class="p">()</span>
<span class="gp">>>> </span><span class="c"># seek to the end, ``2`` works as well.</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="mi">0</span><span class="p">,</span> <span class="s">'FROM_END'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="c"># scroll back one, ``1`` works as well.</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="o">-</span><span class="mi">1</span><span class="p">,</span> <span class="s">'RELATIVE'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="c"># and back to the beginning again</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span>
</pre></div>
</div>
<p>Additionally, scrollable cursors support backward fetches by specifying the
direction keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="mi">0</span><span class="p">,</span> <span class="mi">2</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">read</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">'BACKWARD'</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="cursor-direction">
<h3>Cursor Direction<a class="headerlink" href="#cursor-direction" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="docutils literal"><span class="pre">direction</span></tt> property on the cursor states the default direction for read
and seek operations. Normally, the direction is <cite>True</cite>, <tt class="docutils literal"><span class="pre">'FORWARD'</span></tt>. When the
property is set to <tt class="docutils literal"><span class="pre">'BACKWARD'</span></tt> or <cite>False</cite>, the read method will fetch
backward by default, and seek operations will be inverted to simulate a
reversely ordered cursor. The following example illustrates the effect:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">reverse_c</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">'SELECT i FROM generate_series(99, 0, -1) AS g(i)'</span><span class="p">)</span><span class="o">.</span><span class="n">declare</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">c</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="s">'SELECT i FROM generate_series(0, 99) AS g(i)'</span><span class="p">)</span><span class="o">.</span><span class="n">declare</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">reverse_c</span><span class="o">.</span><span class="n">direction</span> <span class="o">=</span> <span class="s">'BACKWARD'</span>
<span class="gp">>>> </span><span class="n">reverse_c</span><span class="o">.</span><span class="n">seek</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">c</span><span class="o">.</span><span class="n">read</span><span class="p">()</span> <span class="o">==</span> <span class="n">reverse_c</span><span class="o">.</span><span class="n">read</span><span class="p">()</span>
</pre></div>
</div>
<p>Furthermore, when the cursor is configured to read backwards, specifying
<tt class="docutils literal"><span class="pre">'BACKWARD'</span></tt> for read’s <tt class="docutils literal"><span class="pre">direction</span></tt> argument will ultimately cause a forward
fetch. This potentially confusing facet of direction configuration is
implemented in order to create an appropriate symmetry in functionality.
The cursors in the above example contain the same rows, but are ultimately in
reverse order. The backward direction property is designed so that the effect
of any read or seek operation on those cursors is the same:</p>