/usr/share/doc/python3-postgresql/html/driver.html is in python3-postgresql 1.1.0-1build1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 | <!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 1.1.0 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.1.0',
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 1.1.0 documentation" href="index.html" />
<link rel="next" title="Copy Management" href="copyman.html" />
<link rel="prev" title="Administration" href="admin.html" />
<link rel="stylesheet" href="_static/unsuck.css" type="text/css" />
</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="py-modindex.html" title="Python Module Index"
>modules</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 1.1.0 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>
<div><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 internal" 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 internal" 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 internal" 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 internal" href="#connectors">Connectors</a></dd>
</dl>
</div></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="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"><div class="highlight"><pre>protocol://user:password@host:port/database?[driver_setting]=value&server_setting=value
</pre></div>
</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 internal" 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 internal" 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 internal" 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>
<div><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>
</div></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>
<div><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>
</dl>
</div></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>
<div><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 internal" 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 internal" 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 internal" 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 internal" 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 internal" 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 internal" 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 internal" 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>
</div></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>
<div><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>
</div></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>
<div><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 can 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 internal" href="#database-messages">Database Messages</a> section for more information.</dd>
</dl>
</div></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>
<div><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>
</div></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 internal" 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>
<div><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 internal" 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 internal" href="#database-messages">Database Messages</a> section for more information.</dd>
</dl>
</div></blockquote>
<p>Cursors have some additional configuration properties that may be modified
during the use of the cursor:</p>
<blockquote>
<div><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>
</div></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>
<div><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>
</div></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>
<div><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">
<div><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>
</div></blockquote>
</dd>
</dl>
</div></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>
<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">seek</span><span class="p">(</span><span class="mi">50</span><span class="p">)</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">50</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">10</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><span class="mi">10</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">10</span><span class="p">,</span> <span class="s">'BACKWARD'</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><span class="mi">10</span><span class="p">,</span> <span class="s">'BACKWARD'</span><span class="p">)</span>
</pre></div>
</div>
<p>And for relative seeks:</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="o">-</span><span class="mi">10</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</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="o">-</span><span class="mi">10</span><span class="p">,</span> <span class="mi">1</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">10</span><span class="p">,</span> <span class="s">'BACKWARD'</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><span class="mi">10</span><span class="p">,</span> <span class="s">'BACKWARD'</span><span class="p">)</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="rows">
<h2>Rows<a class="headerlink" href="#rows" title="Permalink to this headline">¶</a></h2>
<p>Rows received from PostgreSQL are instantiated into <cite>postgresql.types.Row</cite>
objects. Rows are both a sequence and a mapping. Items accessed with an <cite>int</cite>
are seen as indexes and other objects are seen as keys:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">row</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 't'::text AS col0, 2::int4 AS col1"</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">row</span>
<span class="go">('t', 2)</span>
<span class="gp">>>> </span><span class="n">row</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="go">'t'</span>
<span class="gp">>>> </span><span class="n">row</span><span class="p">[</span><span class="s">"col0"</span><span class="p">]</span>
<span class="go">'t'</span>
</pre></div>
</div>
<p>However, this extra functionality is not free. The cost of instantiating
<cite>postgresql.types.Row</cite> objects is quite measurable, so the <cite>chunks()</cite> execution
method will produce <cite>builtins.tuple</cite> objects for cases where performance is
critical.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Attributes aren’t used to provide access to values due to potential conflicts
with existing method and property names.</p>
</div>
<div class="section" id="row-interface-points">
<h3>Row Interface Points<a class="headerlink" href="#row-interface-points" title="Permalink to this headline">¶</a></h3>
<p>Rows implement the <cite>collections.Mapping</cite> and <cite>collections.Sequence</cite> interfaces.</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Row.keys()</span></tt></dt>
<dd>An iterable producing the column names. Order is not guaranteed. See the
<tt class="docutils literal"><span class="pre">column_names</span></tt> property to get an ordered sequence.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.values()</span></tt></dt>
<dd>Iterable to the values in the row.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.get(key_or_index[,</span> <span class="pre">default=None])</span></tt></dt>
<dd>Get the item in the row. If the key doesn’t exist or the index is out of
range, return the default.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.items()</span></tt></dt>
<dd>Iterable of key-value pairs. Ordered by index.</dd>
<dt><tt class="docutils literal"><span class="pre">iter(Row)</span></tt></dt>
<dd>Iterable to the values in index order.</dd>
<dt><tt class="docutils literal"><span class="pre">value</span> <span class="pre">in</span> <span class="pre">Row</span></tt></dt>
<dd>Whether or not the value exists in the row. (__contains__)</dd>
<dt><tt class="docutils literal"><span class="pre">Row[key_or_index]</span></tt></dt>
<dd>If <tt class="docutils literal"><span class="pre">key_or_index</span></tt> is an integer, return the value at that index. If the
index is out of range, raise an <cite>IndexError</cite>. Otherwise, return the value
associated with column name. If the given key, <tt class="docutils literal"><span class="pre">key_or_index</span></tt>, does not
exist, raise a <cite>KeyError</cite>.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.index_from_key(key)</span></tt></dt>
<dd>Return the index associated with the given key.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.key_from_index(index)</span></tt></dt>
<dd>Return the key associated with the given index.</dd>
<dt><tt class="docutils literal"><span class="pre">Row.transform(*args,</span> <span class="pre">**kw)</span></tt></dt>
<dd>Create a new row object of the same length, with the same keys, but with new
values produced by applying the given callables to the corresponding items.
Callables given as <tt class="docutils literal"><span class="pre">args</span></tt> will be associated with values by their index and
callables given as keywords will be associated with values by their key,
column name.</dd>
</dl>
</div></blockquote>
<p>While the mapping interfaces will provide most of the needed information, some
additional properties are provided for consistency with statement and cursor
objects.</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Row.column_names</span></tt></dt>
<dd><p class="first">Property providing an ordered sequence of column names. The index corresponds
to the row value-index that the name refers to.</p>
<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">row</span><span class="p">[</span><span class="n">row</span><span class="o">.</span><span class="n">column_names</span><span class="p">[</span><span class="n">i</span><span class="p">]]</span> <span class="o">==</span> <span class="n">row</span><span class="p">[</span><span class="n">i</span><span class="p">]</span>
</pre></div>
</div>
</dd>
</dl>
</div></blockquote>
</div>
<div class="section" id="row-transformations">
<h3>Row Transformations<a class="headerlink" href="#row-transformations" title="Permalink to this headline">¶</a></h3>
<p>After a row is returned, sometimes the data in the row is not in the desired
format. Further processing is needed if the row object is to going to be
given to another piece of code which requires an object of differring
consistency.</p>
<p>The <tt class="docutils literal"><span class="pre">transform</span></tt> method on row objects provides a means to create a new row
object consisting of the old row’s items, but with certain columns transformed
using the given callables:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">row</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"> 'XX9301423'::text AS product_code,</span>
<span class="gp">... </span><span class="s"> 2::int4 AS quantity,</span>
<span class="gp">... </span><span class="s"> '4.92'::numeric AS total</span>
<span class="gp">... </span><span class="s">"""</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">row</span>
<span class="go">('XX9301423', 2, Decimal("4.92"))</span>
<span class="gp">>>> </span><span class="n">row</span><span class="o">.</span><span class="n">transform</span><span class="p">(</span><span class="n">quantity</span> <span class="o">=</span> <span class="nb">str</span><span class="p">)</span>
<span class="go">('XX9301423', '2', Decimal("4.92"))</span>
</pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">transform</span></tt> supports both positional and keyword arguments in order to
assign the callable for a column’s transformation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">operator</span> <span class="kn">import</span> <span class="n">methodcaller</span>
<span class="gp">>>> </span><span class="n">row</span><span class="o">.</span><span class="n">transform</span><span class="p">(</span><span class="n">methodcaller</span><span class="p">(</span><span class="s">'strip'</span><span class="p">,</span> <span class="s">'XX'</span><span class="p">))</span>
<span class="go">('9301423', 2, Decimal("4.92"))</span>
</pre></div>
</div>
<p>Of course, more than one column can be transformed:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">stripxx</span> <span class="o">=</span> <span class="n">methodcaller</span><span class="p">(</span><span class="s">'strip'</span><span class="p">,</span> <span class="s">'XX'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">row</span><span class="o">.</span><span class="n">transform</span><span class="p">(</span><span class="n">stripxx</span><span class="p">,</span> <span class="nb">str</span><span class="p">,</span> <span class="nb">str</span><span class="p">)</span>
<span class="go">('9301423', '2', '4.92')</span>
</pre></div>
</div>
<p><cite>None</cite> can also be used to indicate no transformation:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">row</span><span class="o">.</span><span class="n">transform</span><span class="p">(</span><span class="bp">None</span><span class="p">,</span> <span class="nb">str</span><span class="p">,</span> <span class="nb">str</span><span class="p">)</span>
<span class="go">('XX9301423', '2', '4.92')</span>
</pre></div>
</div>
<p>More advanced usage can make use of lambdas for compound transformations in a
single pass of the row:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">strip_and_int</span> <span class="o">=</span> <span class="k">lambda</span> <span class="n">x</span><span class="p">:</span> <span class="nb">int</span><span class="p">(</span><span class="n">stripxx</span><span class="p">(</span><span class="n">x</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">row</span><span class="o">.</span><span class="n">transform</span><span class="p">(</span><span class="n">strip_and_int</span><span class="p">)</span>
<span class="go">(9301423, 2, Decimal("4.92"))</span>
</pre></div>
</div>
<p>Transformations will be, more often than not, applied against <em>rows</em> as
opposed to <em>a</em> row. Using <cite>operator.methodcaller</cite> with <cite>map</cite> provides the
necessary functionality to create simple iterables producing transformed row
sequences:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">decimal</span>
<span class="gp">>>> </span><span class="n">apply_tax</span> <span class="o">=</span> <span class="k">lambda</span> <span class="n">x</span><span class="p">:</span> <span class="p">(</span><span class="n">x</span> <span class="o">*</span> <span class="n">decimal</span><span class="o">.</span><span class="n">Decimal</span><span class="p">(</span><span class="s">"0.1"</span><span class="p">))</span> <span class="o">+</span> <span class="n">x</span>
<span class="gp">>>> </span><span class="n">transform_row</span> <span class="o">=</span> <span class="n">methodcaller</span><span class="p">(</span><span class="s">'transform'</span><span class="p">,</span> <span class="n">strip_and_int</span><span class="p">,</span> <span class="bp">None</span><span class="p">,</span> <span class="n">apply_tax</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="nb">map</span><span class="p">(</span><span class="n">transform_row</span><span class="p">,</span> <span class="p">[</span><span class="n">row</span><span class="p">])</span>
<span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
<span class="go">[(9301423, 2, Decimal('5.412'))]</span>
</pre></div>
</div>
<p>And finally, <cite>functools.partial</cite> can be used to create a simple callable:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">functools</span> <span class="kn">import</span> <span class="n">partial</span>
<span class="gp">>>> </span><span class="n">transform_rows</span> <span class="o">=</span> <span class="n">partial</span><span class="p">(</span><span class="nb">map</span><span class="p">,</span> <span class="n">transform_row</span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">transform_rows</span><span class="p">([</span><span class="n">row</span><span class="p">]))</span>
<span class="go">[(9301423, 2, Decimal('5.412'))]</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="queries">
<h2>Queries<a class="headerlink" href="#queries" title="Permalink to this headline">¶</a></h2>
<p>Queries in <cite>py-postgresql</cite> are single use prepared statements. They exist primarily for
syntactic convenience, but they also allow the driver to recognize the short lifetime of
the statement.</p>
<p>Single use statements are supported using the <tt class="docutils literal"><span class="pre">query</span></tt> property on connection
objects, <a class="reference internal" href="reference.html#postgresql.api.Connection.query" title="postgresql.api.Connection.query"><tt class="xref py py-class docutils literal"><span class="pre">postgresql.api.Connection.query</span></tt></a>. The statement object is not
available when using queries as the results, or handle to the results, are directly returned.</p>
<p>Queries have access to all execution methods:</p>
<blockquote>
<div><ul class="simple">
<li><tt class="docutils literal"><span class="pre">Connection.query(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.rows(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.column(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.first(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.chunks(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.declare(sql,</span> <span class="pre">*parameters)</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.load_rows(sql,</span> <span class="pre">collections.Iterable(parameters))</span></tt></li>
<li><tt class="docutils literal"><span class="pre">Connection.query.load_chunks(collections.Iterable(collections.Iterable(parameters)))</span></tt></li>
</ul>
</div></blockquote>
<p>In cases where a sequence of one-shot queries needs to be performed, it may be important to
avoid unnecessary repeat attribute resolution from the connection object as the <tt class="docutils literal"><span class="pre">query</span></tt>
property is an interface object created on access. Caching the target execution methods is
recommended:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">qrows</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">rows</span>
<span class="n">l</span> <span class="o">=</span> <span class="p">[]</span>
<span class="k">for</span> <span class="n">x</span> <span class="ow">in</span> <span class="n">my_queries</span><span class="p">:</span>
<span class="n">l</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">qrows</span><span class="p">(</span><span class="n">x</span><span class="p">))</span>
</pre></div>
</div>
<p>The characteristic of Each execution method is discussed in the prior
<a class="reference internal" href="#prepared-statements">Prepared Statements</a> section.</p>
</div>
<div class="section" id="stored-procedures">
<h2>Stored Procedures<a class="headerlink" href="#stored-procedures" title="Permalink to this headline">¶</a></h2>
<p>The <tt class="docutils literal"><span class="pre">proc</span></tt> method on <cite>postgresql.api.Database</cite> objects provides a means to
create a reference to a stored procedure on the remote database.
<cite>postgresql.api.StoredProcedure</cite> objects are used to represent the referenced
SQL routine.</p>
<p>This provides a direct interface to functions stored on the database. It
leverages knowledge of the parameters and results of the function in order
to provide the user with a natural interface to the procedure:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">func</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">proc</span><span class="p">(</span><span class="s">'version()'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">func</span><span class="p">()</span>
<span class="go">'PostgreSQL 8.3.6 on ...'</span>
</pre></div>
</div>
<div class="section" id="stored-procedure-interface-points">
<h3>Stored Procedure Interface Points<a class="headerlink" href="#stored-procedure-interface-points" title="Permalink to this headline">¶</a></h3>
<p>It’s more-or-less a function, so there’s only one interface point:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">func(*args,</span> <span class="pre">**kw)</span></tt> (<tt class="docutils literal"><span class="pre">__call__</span></tt>)</dt>
<dd><p class="first">Stored procedure objects are callable, executing a procedure will return an
object of suitable representation for a given procedure’s type signature.</p>
<p>If it returns a single object, it will return the single object produced by
the procedure.</p>
<p>If it’s a set returning function, it will return an <em>iterable</em> to the values
produced by the procedure.</p>
<p class="last">In cases of set returning function with multiple OUT-parameters, a cursor
will be returned.</p>
</dd>
</dl>
</div></blockquote>
</div>
<div class="section" id="stored-procedure-type-support">
<h3>Stored Procedure Type Support<a class="headerlink" href="#stored-procedure-type-support" title="Permalink to this headline">¶</a></h3>
<p>Stored procedures support most types of functions. “Function Types” being set
returning functions, multiple-OUT parameters, and simple single-object returns.</p>
<p>Set-returning functions, SRFs return a sequence:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">generate_series</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">proc</span><span class="p">(</span><span class="s">'generate_series(int,int)'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">gs</span> <span class="o">=</span> <span class="n">generate_series</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">20</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">gs</span>
<span class="go"><generator object <genexpr>></span>
<span class="gp">>>> </span><span class="nb">next</span><span class="p">(</span><span class="n">gs</span><span class="p">)</span>
<span class="go">1</span>
<span class="gp">>>> </span><span class="nb">list</span><span class="p">(</span><span class="n">gs</span><span class="p">)</span>
<span class="go">[2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20]</span>
</pre></div>
</div>
<p>For functions like <tt class="docutils literal"><span class="pre">generate_series()</span></tt>, the driver is able to identify that
the return is a sequence of <em>solitary</em> integer objects, so the result of the
function is just that, a sequence of integers.</p>
<p>Functions returning composite types are recognized, and return row 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">"""</span>
<span class="gp">... </span><span class="s">CREATE FUNCTION composite(OUT i int, OUT t text)</span>
<span class="gp">... </span><span class="s">LANGUAGE SQL AS</span>
<span class="gp">... </span><span class="s">$body$</span>
<span class="gp">... </span><span class="s"> SELECT 900::int AS i, 'sample text'::text AS t;</span>
<span class="gp">... </span><span class="s">$body$;</span>
<span class="gp">... </span><span class="s">"""</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">composite</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">proc</span><span class="p">(</span><span class="s">'composite()'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">composite</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">r</span>
<span class="go">(900, 'sample text')</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="s">'i'</span><span class="p">]</span>
<span class="go">900</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="s">'t'</span><span class="p">]</span>
<span class="go">'sample text'</span>
</pre></div>
</div>
<p>Functions returning a set of composites are recognized, and the result is a
<cite>postgresql.api.Cursor</cite> object whose column names are consistent with the names
of the OUT parameters:</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 FUNCTION srfcomposite(out i int, out t text)</span>
<span class="gp">... </span><span class="s">RETURNS SETOF RECORD</span>
<span class="gp">... </span><span class="s">LANGUAGE SQL AS</span>
<span class="gp">... </span><span class="s">$body$</span>
<span class="gp">... </span><span class="s"> SELECT 900::int AS i, 'sample text'::text AS t</span>
<span class="gp">... </span><span class="s"> UNION ALL</span>
<span class="gp">... </span><span class="s"> SELECT 450::int AS i, 'more sample text'::text AS t</span>
<span class="gp">... </span><span class="s">$body$;</span>
<span class="gp">... </span><span class="s">"""</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">srfcomposite</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">proc</span><span class="p">(</span><span class="s">'srfcomposite()'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">srfcomposite</span><span class="p">()</span>
<span class="gp">>>> </span><span class="nb">next</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
<span class="go">(900, 'sample text')</span>
<span class="gp">>>> </span><span class="n">v</span> <span class="o">=</span> <span class="nb">next</span><span class="p">(</span><span class="n">r</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">v</span><span class="p">[</span><span class="s">'i'</span><span class="p">],</span> <span class="n">v</span><span class="p">[</span><span class="s">'t'</span><span class="p">]</span>
<span class="go">(450, 'more sample text')</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="transactions">
<h2>Transactions<a class="headerlink" href="#transactions" title="Permalink to this headline">¶</a></h2>
<p>Transactions are managed by creating an object corresponding to a
transaction started on the server. A transaction is a transaction block,
a savepoint, or a prepared transaction. The <tt class="docutils literal"><span class="pre">xact(...)</span></tt> method on the
connection object provides the standard method for creating a
<cite>postgresql.api.Transaction</cite> object to manage a transaction on the connection.</p>
<p>The creation of a transaction object does not start the transaction. Rather, the
transaction must be explicitly started using the <tt class="docutils literal"><span class="pre">start()</span></tt> method on the
transaction object. Usually, transactions <em>should</em> be managed with the context
manager interfaces:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">db</span><span class="o">.</span><span class="n">xact</span><span class="p">():</span>
<span class="gp">... </span> <span class="o">...</span>
</pre></div>
</div>
<p>The transaction in the above example is opened, started, by the <tt class="docutils literal"><span class="pre">__enter__</span></tt>
method invoked by the with-statement’s usage. It will be subsequently
committed or rolled-back depending on the exception state and the error state
of the connection when <tt class="docutils literal"><span class="pre">__exit__</span></tt> is called.</p>
<p><strong>Using the with-statement syntax for managing transactions is strongly
recommended.</strong> By using the transaction’s context manager, it allows for Python
exceptions to be properly treated as fatal to the transaction as when an
uncaught exception of any kind occurs within the block, it is unlikely that
the state of the transaction can be trusted. Additionally, the <tt class="docutils literal"><span class="pre">__exit__</span></tt>
method provides a safe-guard against invalid commits. This can occur if a
database error is inappropriately caught within a block without being raised.</p>
<p>The context manager interfaces are higher level interfaces to the explicit
instruction methods provided by <cite>postgresql.api.Transaction</cite> objects.</p>
<div class="section" id="transaction-configuration">
<h3>Transaction Configuration<a class="headerlink" href="#transaction-configuration" title="Permalink to this headline">¶</a></h3>
<p>Keyword arguments given to <tt class="docutils literal"><span class="pre">xact()</span></tt> provide the means for configuring the
properties of the transaction. Only three points of configuration are available:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">isolation</span></tt></dt>
<dd><p class="first">The isolation level of the transaction. This must be a string. It will be
interpolated directly into the START TRANSACTION statement. Normally,
‘SERIALIZABLE’ or ‘READ COMMITTED’:</p>
<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">db</span><span class="o">.</span><span class="n">xact</span><span class="p">(</span><span class="s">'SERIALIZABLE'</span><span class="p">):</span>
<span class="gp">... </span> <span class="o">...</span>
</pre></div>
</div>
</dd>
<dt><tt class="docutils literal"><span class="pre">mode</span></tt></dt>
<dd>A string, ‘READ ONLY’ or ‘READ WRITE’. States the mutability of stored
information in the database. Like <tt class="docutils literal"><span class="pre">isolation</span></tt>, this is interpolated
directly into the START TRANSACTION string.</dd>
</dl>
</div></blockquote>
<p>The specification of any of these transaction properties imply that the transaction
is a block. Savepoints do not take configuration, so if a transaction identified
as a block is started while another block is running, an exception will be
raised.</p>
</div>
<div class="section" id="transaction-interface-points">
<h3>Transaction Interface Points<a class="headerlink" href="#transaction-interface-points" title="Permalink to this headline">¶</a></h3>
<p>The methods available on transaction objects manage the state of the transaction
and relay any necessary instructions to the remote server in order to reflect
that change of state.</p>
<blockquote>
<div><div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">x</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">xact</span><span class="p">(</span><span class="o">...</span><span class="p">)</span>
</pre></div>
</div>
<dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">x.start()</span></tt></dt>
<dd>Start the transaction.</dd>
<dt><tt class="docutils literal"><span class="pre">x.commit()</span></tt></dt>
<dd>Commit the transaction.</dd>
<dt><tt class="docutils literal"><span class="pre">x.rollback()</span></tt></dt>
<dd>Abort the transaction.</dd>
</dl>
</div></blockquote>
<p>These methods are primarily provided for applications that manage transactions
in a way that cannot be formed around single, sequential blocks of code.
Generally, using these methods require additional work to be performed by the
code that is managing the transaction.
If usage of these direct, instructional methods is necessary, it is important to
note that if the database is in an error state when a <em>transaction block’s</em>
commit() is executed, an implicit rollback will occur. The transaction object
will simply follow instructions and issue the <tt class="docutils literal"><span class="pre">COMMIT</span></tt> statement, and it will
succeed without exception.</p>
</div>
<div class="section" id="error-control">
<h3>Error Control<a class="headerlink" href="#error-control" title="Permalink to this headline">¶</a></h3>
<p>Handling <em>database</em> errors inside transaction CMs is generally discouraged as
any database operation that occurs within a failed transaction is an error
itself. It is important to trap any recoverable database errors <em>outside</em> of the
scope of the transaction’s context manager:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">try</span><span class="p">:</span>
<span class="gp">... </span> <span class="k">with</span> <span class="n">db</span><span class="o">.</span><span class="n">xact</span><span class="p">():</span>
<span class="gp">... </span> <span class="o">...</span>
<span class="gp">... </span><span class="k">except</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">exceptions</span><span class="o">.</span><span class="n">UniqueError</span><span class="p">:</span>
<span class="gp">... </span> <span class="k">pass</span>
</pre></div>
</div>
<p>In cases where the database is in an error state, but the context exits
without an exception, a <cite>postgresql.exceptions.InFailedTransactionError</cite> is
raised by the driver:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">db</span><span class="o">.</span><span class="n">xact</span><span class="p">():</span>
<span class="gp">... </span> <span class="k">try</span><span class="p">:</span>
<span class="gp">... </span> <span class="o">...</span>
<span class="gp">... </span> <span class="k">except</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">exceptions</span><span class="o">.</span><span class="n">UniqueError</span><span class="p">:</span>
<span class="gp">... </span> <span class="k">pass</span>
<span class="gp">...</span>
<span class="gt">Traceback (most recent call last):</span>
<span class="c">...</span>
<span class="gr">postgresql.exceptions.InFailedTransactionError</span>: <span class="n">invalid block exit detected</span>
<span class="go">CODE: 25P02</span>
<span class="go">SEVERITY: ERROR</span>
</pre></div>
</div>
<p>Normally, if a <tt class="docutils literal"><span class="pre">COMMIT</span></tt> is issued on a failed transaction, the command implies a
<tt class="docutils literal"><span class="pre">ROLLBACK</span></tt> without error. This is a very undesirable result for the CM’s exit
as it may allow for code to be ran that presumes the transaction was committed.
The driver intervenes here and raises the
<cite>postgresql.exceptions.InFailedTransactionError</cite> to safe-guard against such
cases. This effect is consistent with savepoint releases that occur during an
error state. The distinction between the two cases is made using the <tt class="docutils literal"><span class="pre">source</span></tt>
property on the raised exception.</p>
</div>
</div>
<div class="section" id="settings">
<h2>Settings<a class="headerlink" href="#settings" title="Permalink to this headline">¶</a></h2>
<p>SQL’s SHOW and SET provides a means to configure runtime parameters on the
database(“GUC”s). In order to save the user some grief, a
<cite>collections.MutableMapping</cite> interface is provided to simplify configuration.</p>
<p>The <tt class="docutils literal"><span class="pre">settings</span></tt> attribute on the connection provides the interface extension.</p>
<p>The standard dictionary interface is supported:</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">settings</span><span class="p">[</span><span class="s">'search_path'</span><span class="p">]</span> <span class="o">=</span> <span class="s">"$user,public"</span>
</pre></div>
</div>
<p>And <tt class="docutils literal"><span class="pre">update(...)</span></tt> is better performing for multiple sets:</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">settings</span><span class="o">.</span><span class="n">update</span><span class="p">({</span>
<span class="gp">... </span> <span class="s">'search_path'</span> <span class="p">:</span> <span class="s">"$user,public"</span><span class="p">,</span>
<span class="gp">... </span> <span class="s">'default_statistics_target'</span> <span class="p">:</span> <span class="s">"1000"</span>
<span class="gp">... </span><span class="p">})</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The <tt class="docutils literal"><span class="pre">transaction_isolation</span></tt> setting cannot be set using the <tt class="docutils literal"><span class="pre">settings</span></tt>
mapping. Internally, <tt class="docutils literal"><span class="pre">settings</span></tt> uses <tt class="docutils literal"><span class="pre">set_config</span></tt>, which cannot adjust
that particular setting.</p>
</div>
<div class="section" id="settings-interface-points">
<h3>Settings Interface Points<a class="headerlink" href="#settings-interface-points" title="Permalink to this headline">¶</a></h3>
<p>Manipulation and interrogation of the connection’s settings is achieved by
using the standard <cite>collections.MutableMapping</cite> interfaces.</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Connection.settings[k]</span></tt></dt>
<dd>Get the value of a single setting.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings[k]</span> <span class="pre">=</span> <span class="pre">v</span></tt></dt>
<dd>Set the value of a single setting.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.update([(k1,v2),</span> <span class="pre">(k2,v2),</span> <span class="pre">...,</span> <span class="pre">(kn,vn)])</span></tt></dt>
<dd>Set multiple settings using a sequence of key-value pairs.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.update({k1</span> <span class="pre">:</span> <span class="pre">v1,</span> <span class="pre">k2</span> <span class="pre">:</span> <span class="pre">v2,</span> <span class="pre">...,</span> <span class="pre">kn</span> <span class="pre">:</span> <span class="pre">vn})</span></tt></dt>
<dd>Set multiple settings using a dictionary or mapping object.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.getset([k1,</span> <span class="pre">k2,</span> <span class="pre">...,</span> <span class="pre">kn])</span></tt></dt>
<dd>Get a set of a settings. This is the most efficient way to get multiple
settings as it uses a single request.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.keys()</span></tt></dt>
<dd>Get all available setting names.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.values()</span></tt></dt>
<dd>Get all setting values.</dd>
<dt><tt class="docutils literal"><span class="pre">Connection.settings.items()</span></tt></dt>
<dd>Get a sequence of key-value pairs corresponding to all settings on the
database.</dd>
</dl>
</div></blockquote>
</div>
<div class="section" id="settings-management">
<h3>Settings Management<a class="headerlink" href="#settings-management" title="Permalink to this headline">¶</a></h3>
<p><cite>postgresql.api.Settings</cite> objects can create context managers when called.
This gives the user with the ability to specify sections of code that are to
be ran with certain settings. The settings’ context manager takes full
advantage of keyword arguments in order to configure the context manager:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">with</span> <span class="n">db</span><span class="o">.</span><span class="n">settings</span><span class="p">(</span><span class="n">search_path</span> <span class="o">=</span> <span class="s">'local,public'</span><span class="p">,</span> <span class="n">timezone</span> <span class="o">=</span> <span class="s">'mst'</span><span class="p">):</span>
<span class="gp">... </span> <span class="o">...</span>
</pre></div>
</div>
<p><cite>postgresql.api.Settings</cite> objects are callable; the return is a context manager
configured with the given keyword arguments representing the settings to use for
the block of code that is about to be executed.</p>
<p>When the block exits, the settings will be restored to the values that they had
before the block entered.</p>
</div>
</div>
<div class="section" id="type-support">
<h2>Type Support<a class="headerlink" href="#type-support" title="Permalink to this headline">¶</a></h2>
<p>The driver supports a large number of PostgreSQL types at the binary level.
Most types are converted to standard Python types. The remaining types are
usually PostgreSQL specific types that are converted into objects whose class
is defined in <cite>postgresql.types</cite>.</p>
<p>When a conversion function is not available for a particular type, the driver
will use the string format of the type and instantiate a <cite>str</cite> object
for the data. It will also expect <cite>str</cite> data when parameter of a type without a
conversion function is bound.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Generally, these standard types are provided for convenience. If conversions into
these datatypes are not desired, it is recommended that explicit casts into
<tt class="docutils literal"><span class="pre">text</span></tt> are made in statement string.</p>
</div>
<table border="1" class="docutils">
<caption>Python types used to represent PostgreSQL types.</caption>
<colgroup>
<col width="42%" />
<col width="44%" />
<col width="14%" />
</colgroup>
<thead valign="bottom">
<tr class="row-odd"><th class="head">PostgreSQL Types</th>
<th class="head">Python Types</th>
<th class="head">SQL Types</th>
</tr>
</thead>
<tbody valign="top">
<tr class="row-even"><td><cite>postgresql.types.INT2OID</cite></td>
<td><cite>int</cite></td>
<td>smallint</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.INT4OID</cite></td>
<td><cite>int</cite></td>
<td>integer</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.INT8OID</cite></td>
<td><cite>int</cite></td>
<td>bigint</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.FLOAT4OID</cite></td>
<td><cite>float</cite></td>
<td>float</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.FLOAT8OID</cite></td>
<td><cite>float</cite></td>
<td>double</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.VARCHAROID</cite></td>
<td><cite>str</cite></td>
<td>varchar</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.BPCHAROID</cite></td>
<td><cite>str</cite></td>
<td>char</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.XMLOID</cite></td>
<td><cite>xml.etree</cite> (cElementTree)</td>
<td>xml</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.DATEOID</cite></td>
<td><cite>datetime.date</cite></td>
<td>date</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.TIMESTAMPOID</cite></td>
<td><cite>datetime.datetime</cite></td>
<td>timestamp</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.TIMESTAMPTZOID</cite></td>
<td><cite>datetime.datetime</cite> (tzinfo)</td>
<td>timestamptz</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.TIMEOID</cite></td>
<td><cite>datetime.time</cite></td>
<td>time</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.TIMETZOID</cite></td>
<td><cite>datetime.time</cite></td>
<td>timetz</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.INTERVALOID</cite></td>
<td><cite>datetime.timedelta</cite></td>
<td>interval</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.NUMERICOID</cite></td>
<td><cite>decimal.Decimal</cite></td>
<td>numeric</td>
</tr>
<tr class="row-odd"><td><cite>postgresql.types.BYTEAOID</cite></td>
<td><cite>bytes</cite></td>
<td>bytea</td>
</tr>
<tr class="row-even"><td><cite>postgresql.types.TEXTOID</cite></td>
<td><cite>str</cite></td>
<td>text</td>
</tr>
<tr class="row-odd"><td><contrib_hstore></td>
<td><cite>dict</cite></td>
<td>hstore</td>
</tr>
</tbody>
</table>
<p>The mapping in the above table <em>normally</em> goes both ways. So when a parameter
is passed to a statement, the type <em>should</em> be consistent with the corresponding
Python type. However, many times, for convenience, the object will be passed
through the type’s constructor, so it is not always necessary.</p>
<div class="section" id="arrays">
<h3>Arrays<a class="headerlink" href="#arrays" title="Permalink to this headline">¶</a></h3>
<p>Arrays of PostgreSQL types are supported with near transparency. For simple
arrays, arbitrary iterables can just be given as a statement’s parameter and the
array’s constructor will consume the objects produced by the iterator into a
<cite>postgresql.types.Array</cite> instance. However, in situations where the array has
multiple dimensions, <cite>list</cite> objects are used to delimit the boundaries of the
array.</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::int[]"</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="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</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>In the above case, it is apparent that this array is supposed to have two
dimensions. However, this is not the case for other types:</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::point[]"</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="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">)])</span>
<span class="go">postgresql.types.Array([postgresql.types.point((1.0, 2.0)), postgresql.types.point((2.0, 3.0))])</span>
</pre></div>
</div>
<p>Lists are used to provide the necessary boundary information:</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::int[]"</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="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">],[</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">]])</span>
<span class="go">postgresql.types.Array([[1,2],[2,3]])</span>
</pre></div>
</div>
<p>The above is the appropriate way to define the array from the original example.</p>
<div class="admonition hint">
<p class="first admonition-title">Hint</p>
<p class="last">The root-iterable object given as an array parameter does not need to be a
list-type as it’s assumed to be made up of elements.</p>
</div>
</div>
<div class="section" id="composites">
<h3>Composites<a class="headerlink" href="#composites" title="Permalink to this headline">¶</a></h3>
<p>Composites are supported using <cite>postgresql.types.Row</cite> objects to represent
the data. When a composite is referenced for the first time, the driver
queries the database for information about the columns that make up the type.
This information is then used to create the necessary I/O routines for packing
and unpacking the parameters and columns of that type:</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">"CREATE TYPE ctest AS (i int, t text, n numeric);"</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">prepare</span><span class="p">(</span><span class="s">"SELECT $1::ctest"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">i</span> <span class="o">=</span> <span class="p">(</span><span class="mi">100</span><span class="p">,</span> <span class="s">'text'</span><span class="p">,</span> <span class="s">"100.02013"</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="o">.</span><span class="n">first</span><span class="p">(</span><span class="n">i</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="s">"t"</span><span class="p">]</span>
<span class="go">'text'</span>
<span class="gp">>>> </span><span class="n">r</span><span class="p">[</span><span class="s">"n"</span><span class="p">]</span>
<span class="go">Decimal("100.02013")</span>
</pre></div>
</div>
<p>Or if use of a dictionary is desired:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">r</span> <span class="o">=</span> <span class="n">ps</span><span class="o">.</span><span class="n">first</span><span class="p">({</span><span class="s">'t'</span> <span class="p">:</span> <span class="s">'just-the-text'</span><span class="p">})</span>
<span class="gp">>>> </span><span class="n">r</span>
<span class="go">(None, 'just-the-text', None)</span>
</pre></div>
</div>
<p>When a dictionary is given to construct the row, absent values are filled with
<cite>None</cite>.</p>
</div>
</div>
<div class="section" id="database-messages">
<span id="db-messages"></span><h2>Database Messages<a class="headerlink" href="#database-messages" title="Permalink to this headline">¶</a></h2>
<p>By default, py-postgresql gives detailed reports of messages emitted by the
database. Often, the verbosity is excessive due to single target processes or
existing application infrastructure for tracing the sources of various events.
Normally, this verbosity is not a significant problem as the driver defaults the
<tt class="docutils literal"><span class="pre">client_min_messages</span></tt> setting to <tt class="docutils literal"><span class="pre">'WARNING'</span></tt> by default.</p>
<p>However, if <tt class="docutils literal"><span class="pre">NOTICE</span></tt> or <tt class="docutils literal"><span class="pre">INFO</span></tt> messages are needed, finer grained control
over message propagation may be desired, py-postgresql’s object relationship
model provides a common protocol for controlling message propagation and,
ultimately, display.</p>
<p>The <tt class="docutils literal"><span class="pre">msghook</span></tt> attribute on elements–for instance, Statements, Connections,
and Connectors–is absent by default. However, when present on an object that
contributed the cause of a message event, it will be invoked with the Message,
<cite>postgresql.message.Message</cite>, object as its sole parameter. The attribute of
the object that is closest to the event is checked first, if present it will
be called. If the <tt class="docutils literal"><span class="pre">msghook()</span></tt> call returns a <cite>True</cite>
value(specficially, <tt class="docutils literal"><span class="pre">bool(x)</span> <span class="pre">is</span> <span class="pre">True</span></tt>), the message will <em>not</em> be
propagated any further. However, if a <cite>False</cite> value–notably, <cite>None</cite>–is
returned, the next element is checked until the list is exhausted and the
message is given to <cite>postgresql.sys.msghook</cite>. The normal list of elements is
as follows:</p>
<div class="highlight-python"><div class="highlight"><pre>Output → Statement → Connection → Connector → Driver → postgresql.sys
</pre></div>
</div>
<p>Where <tt class="docutils literal"><span class="pre">Output</span></tt> can be a <cite>postgresql.api.Cursor</cite> object produced by
<tt class="docutils literal"><span class="pre">declare(...)</span></tt> or an implicit output management object used <em>internally</em> by
<tt class="docutils literal"><span class="pre">Statement.__call__()</span></tt> and other statement execution methods. Setting the
<tt class="docutils literal"><span class="pre">msghook</span></tt> attribute on <cite>postgresql.api.Statement</cite> gives very fine
control over raised messages. Consider filtering the notice message on create
table statements that implicitly create indexes:</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="o">...</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">settings</span><span class="p">[</span><span class="s">'client_min_messages'</span><span class="p">]</span> <span class="o">=</span> <span class="s">'NOTICE'</span>
<span class="gp">>>> </span><span class="n">ct_this</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">'CREATE TEMP TABLE "this" (i int PRIMARY KEY)'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ct_that</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">'CREATE TEMP TABLE "that" (i int PRIMARY KEY)'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="k">def</span> <span class="nf">filter_notices</span><span class="p">(</span><span class="n">msg</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">if</span> <span class="n">msg</span><span class="o">.</span><span class="n">details</span><span class="p">[</span><span class="s">'severity'</span><span class="p">]</span> <span class="o">==</span> <span class="s">'NOTICE'</span><span class="p">:</span>
<span class="gp">... </span> <span class="k">return</span> <span class="bp">True</span>
<span class="gp">...</span>
<span class="gp">>>> </span><span class="n">ct_that</span><span class="p">()</span>
<span class="go">NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "that_pkey" for table "that"</span>
<span class="gp">...</span>
<span class="go">('CREATE TABLE', None)</span>
<span class="gp">>>> </span><span class="n">ct_this</span><span class="o">.</span><span class="n">msghook</span> <span class="o">=</span> <span class="n">filter_notices</span>
<span class="gp">>>> </span><span class="n">ct_this</span><span class="p">()</span>
<span class="go">('CREATE TABLE', None)</span>
<span class="go">>>></span>
</pre></div>
</div>
<p>The above illustrates the quality of an installed <tt class="docutils literal"><span class="pre">msghook</span></tt> that simply
inhibits further propagation of messages with a severity of ‘NOTICE’–but, only
notices coming from objects derived from the <tt class="docutils literal"><span class="pre">ct_this</span></tt>
<cite>postgresql.api.Statement</cite> object.</p>
<p>Subsequently, if the filter is installed on the connection’s <tt class="docutils literal"><span class="pre">msghook</span></tt>:</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="o">...</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">settings</span><span class="p">[</span><span class="s">'client_min_messages'</span><span class="p">]</span> <span class="o">=</span> <span class="s">'NOTICE'</span>
<span class="gp">>>> </span><span class="n">ct_this</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">'CREATE TEMP TABLE "this" (i int PRIMARY KEY)'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ct_that</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">'CREATE TEMP TABLE "that" (i int PRIMARY KEY)'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="k">def</span> <span class="nf">filter_notices</span><span class="p">(</span><span class="n">msg</span><span class="p">):</span>
<span class="gp">... </span> <span class="k">if</span> <span class="n">msg</span><span class="o">.</span><span class="n">details</span><span class="p">[</span><span class="s">'severity'</span><span class="p">]</span> <span class="o">==</span> <span class="s">'NOTICE'</span><span class="p">:</span>
<span class="gp">... </span> <span class="k">return</span> <span class="bp">True</span>
<span class="gp">...</span>
<span class="gp">>>> </span><span class="n">db</span><span class="o">.</span><span class="n">msghook</span> <span class="o">=</span> <span class="n">filter_notices</span>
<span class="gp">>>> </span><span class="n">ct_that</span><span class="p">()</span>
<span class="go">('CREATE TABLE', None)</span>
<span class="gp">>>> </span><span class="n">ct_this</span><span class="p">()</span>
<span class="go">('CREATE TABLE', None)</span>
<span class="go">>>></span>
</pre></div>
</div>
<p>Any message with <tt class="docutils literal"><span class="pre">'NOTICE'</span></tt> severity coming from the connection, <tt class="docutils literal"><span class="pre">db</span></tt>, will be
suffocated by the <tt class="docutils literal"><span class="pre">filter_notices</span></tt> function. However, if a <tt class="docutils literal"><span class="pre">msghook</span></tt> is
installed on either of those statements, it would be possible for display to
occur depending on the implementation of the hook installed on the statement
objects.</p>
<div class="section" id="message-metadata">
<h3>Message Metadata<a class="headerlink" href="#message-metadata" title="Permalink to this headline">¶</a></h3>
<p>PostgreSQL messages, <cite>postgresql.message.Message</cite>, are primarily described in three
parts: the SQL-state code, the main message string, and a mapping containing the
details. The follow attributes are available on message objects:</p>
<blockquote>
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">Message.message</span></tt></dt>
<dd>The primary message string.</dd>
<dt><tt class="docutils literal"><span class="pre">Message.code</span></tt></dt>
<dd>The SQL-state code associated with a given message.</dd>
<dt><tt class="docutils literal"><span class="pre">Message.source</span></tt></dt>
<dd>The origins of the message. Normally, <tt class="docutils literal"><span class="pre">'SERVER'</span></tt> or <tt class="docutils literal"><span class="pre">'CLIENT'</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Message.location</span></tt></dt>
<dd>A terse, textual representation of <tt class="docutils literal"><span class="pre">'file'</span></tt>, <tt class="docutils literal"><span class="pre">'line'</span></tt>, and <tt class="docutils literal"><span class="pre">'function'</span></tt>
provided by the associated <tt class="docutils literal"><span class="pre">details</span></tt>.</dd>
<dt><tt class="docutils literal"><span class="pre">Message.details</span></tt></dt>
<dd><p class="first">A mapping providing extended information about a message. This mapping
object <strong>can</strong> contain the following keys:</p>
<blockquote class="last">
<div><dl class="docutils">
<dt><tt class="docutils literal"><span class="pre">'severity'</span></tt></dt>
<dd>Any of <tt class="docutils literal"><span class="pre">'DEBUG'</span></tt>, <tt class="docutils literal"><span class="pre">'INFO'</span></tt>, <tt class="docutils literal"><span class="pre">'NOTICE'</span></tt>, <tt class="docutils literal"><span class="pre">'WARNING'</span></tt>, <tt class="docutils literal"><span class="pre">'ERROR'</span></tt>,
<tt class="docutils literal"><span class="pre">'FATAL'</span></tt>, or <tt class="docutils literal"><span class="pre">'PANIC'</span></tt>; the latter three are usually associated with a
<cite>postgresql.exceptions.Error</cite> instance.</dd>
<dt><tt class="docutils literal"><span class="pre">'context'</span></tt></dt>
<dd>The CONTEXT portion of the message.</dd>
<dt><tt class="docutils literal"><span class="pre">'detail'</span></tt></dt>
<dd>The DETAIL portion of the message.</dd>
<dt><tt class="docutils literal"><span class="pre">'hint'</span></tt></dt>
<dd>The HINT portion of the message.</dd>
<dt><tt class="docutils literal"><span class="pre">'position'</span></tt></dt>
<dd>A number identifying the position in the statement string that caused a
parse error.</dd>
<dt><tt class="docutils literal"><span class="pre">'file'</span></tt></dt>
<dd>The name of the file that emitted the message.
(<em>normally</em> server information)</dd>
<dt><tt class="docutils literal"><span class="pre">'function'</span></tt></dt>
<dd>The name of the function that emitted the message.
(<em>normally</em> server information)</dd>
<dt><tt class="docutils literal"><span class="pre">'line'</span></tt></dt>
<dd>The line of the file that emitted the message.
(<em>normally</em> server information)</dd>
</dl>
</div></blockquote>
</dd>
</dl>
</div></blockquote>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<h3><a href="index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Driver</a><ul>
<li><a class="reference internal" href="#establishing-a-connection">Establishing a Connection</a><ul>
<li><a class="reference internal" href="#postgresql-open"><cite>postgresql.open</cite></a></li>
<li><a class="reference internal" href="#postgresql-driver-connect"><cite>postgresql.driver.connect</cite></a></li>
<li><a class="reference internal" href="#connectors">Connectors</a></li>
<li><a class="reference internal" href="#connection-keywords">Connection Keywords</a></li>
</ul>
</li>
<li><a class="reference internal" href="#connections">Connections</a><ul>
<li><a class="reference internal" href="#database-interface-points">Database Interface Points</a></li>
</ul>
</li>
<li><a class="reference internal" href="#prepared-statements">Prepared Statements</a><ul>
<li><a class="reference internal" href="#statement-interface-points">Statement Interface Points</a></li>
<li><a class="reference internal" href="#parameterized-statements">Parameterized Statements</a></li>
<li><a class="reference internal" href="#inserting-and-dml">Inserting and DML</a></li>
<li><a class="reference internal" href="#copy-statements">COPY Statements</a></li>
</ul>
</li>
<li><a class="reference internal" href="#cursors">Cursors</a><ul>
<li><a class="reference internal" href="#cursor-interface-points">Cursor Interface Points</a></li>
<li><a class="reference internal" href="#scrollable-cursors">Scrollable Cursors</a></li>
<li><a class="reference internal" href="#cursor-direction">Cursor Direction</a></li>
</ul>
</li>
<li><a class="reference internal" href="#rows">Rows</a><ul>
<li><a class="reference internal" href="#row-interface-points">Row Interface Points</a></li>
<li><a class="reference internal" href="#row-transformations">Row Transformations</a></li>
</ul>
</li>
<li><a class="reference internal" href="#queries">Queries</a></li>
<li><a class="reference internal" href="#stored-procedures">Stored Procedures</a><ul>
<li><a class="reference internal" href="#stored-procedure-interface-points">Stored Procedure Interface Points</a></li>
<li><a class="reference internal" href="#stored-procedure-type-support">Stored Procedure Type Support</a></li>
</ul>
</li>
<li><a class="reference internal" href="#transactions">Transactions</a><ul>
<li><a class="reference internal" href="#transaction-configuration">Transaction Configuration</a></li>
<li><a class="reference internal" href="#transaction-interface-points">Transaction Interface Points</a></li>
<li><a class="reference internal" href="#error-control">Error Control</a></li>
</ul>
</li>
<li><a class="reference internal" href="#settings">Settings</a><ul>
<li><a class="reference internal" href="#settings-interface-points">Settings Interface Points</a></li>
<li><a class="reference internal" href="#settings-management">Settings Management</a></li>
</ul>
</li>
<li><a class="reference internal" href="#type-support">Type Support</a><ul>
<li><a class="reference internal" href="#arrays">Arrays</a></li>
<li><a class="reference internal" href="#composites">Composites</a></li>
</ul>
</li>
<li><a class="reference internal" href="#database-messages">Database Messages</a><ul>
<li><a class="reference internal" href="#message-metadata">Message Metadata</a></li>
</ul>
</li>
</ul>
</li>
</ul>
<h4>Previous topic</h4>
<p class="topless"><a href="admin.html"
title="previous chapter">Administration</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="copyman.html"
title="next chapter">Copy Management</a></p>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="_sources/driver.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<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>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related">
<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="copyman.html" title="Copy Management"
>next</a> |</li>
<li class="right" >
<a href="admin.html" title="Administration"
>previous</a> |</li>
<li><a href="index.html">py-postgresql 1.1.0 documentation</a> »</li>
</ul>
</div>
<div class="footer">
© Copyright Python+Postgres.
Last updated on Mar 31, 2014.
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.2.2.
</div>
</body>
</html>
|