Author: cito
Date: Sun Jan 10 15:59:01 2016
New Revision: 708
Log:
Move tutorial into a chapter of the docs
Converted the tutorial files from Python scripts to Sphinx docs
and made them a chapter of the overall documentation.
Some of the examples were pretty outdated and did not even run any more on
the PostgreSQL versions supported by PyGreSQL; these have been modernized.
Also added some explanations of some of the classic PyGreSQL methods.
Added:
branches/4.x/docs/contents/postgres/
branches/4.x/docs/contents/postgres/advanced.rst
branches/4.x/docs/contents/postgres/basic.rst
branches/4.x/docs/contents/postgres/func.rst
branches/4.x/docs/contents/postgres/index.rst
branches/4.x/docs/contents/postgres/syscat.rst
Deleted:
branches/4.x/tutorial/
Modified:
branches/4.x/docs/contents/changelog.rst
branches/4.x/docs/contents/general.rst
branches/4.x/docs/contents/index.rst
branches/4.x/docs/contents/pg/connection.rst
branches/4.x/docs/contents/pg/db_wrapper.rst
branches/4.x/docs/contents/pg/large_objects.rst
branches/4.x/docs/contents/pg/module.rst
branches/4.x/docs/contents/pg/query.rst
branches/4.x/docs/contents/pgdb/connection.rst
branches/4.x/docs/contents/pgdb/cursor.rst
branches/4.x/docs/contents/pgdb/module.rst
branches/4.x/docs/contents/pgdb/types.rst
branches/4.x/docs/download/files.rst
branches/4.x/mktar
Modified: branches/4.x/docs/contents/changelog.rst
==============================================================================
--- branches/4.x/docs/contents/changelog.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/changelog.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -12,6 +12,7 @@
- Add option to return money values as string.
- Fix notification handler (Thanks Patrick TJ McPhee).
- Fix a small issue with large objects.
+- The tutorial files have become a chapter in the documentation.
- Greatly improve unit testing, tests run with Python 2.4 to 2.7 again.
Version 4.1.1 (2013-01-08)
Modified: branches/4.x/docs/contents/general.rst
==============================================================================
--- branches/4.x/docs/contents/general.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/general.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,4 +1,4 @@
-General PyGreSQL programming information
+General PyGreSQL Programming Information
----------------------------------------
PyGreSQL consists of two parts: the "classic" PyGreSQL interface
Modified: branches/4.x/docs/contents/index.rst
==============================================================================
--- branches/4.x/docs/contents/index.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/index.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -8,10 +8,11 @@
:maxdepth: 1
Installing PyGreSQL <install>
- What's new and history of changes <changelog>
- General PyGreSQL programming information <general>
+ What's New and History of Changes <changelog>
+ General PyGreSQL Programming Information <general>
The Classic PyGreSQL Interface <pg/index>
The DB-API Compliant Interface <pgdb/index>
+ A PostgreSQL Primer <postgres/index>
Examples for using PyGreSQL <examples>
Indices and tables
Modified: branches/4.x/docs/contents/pg/connection.rst
==============================================================================
--- branches/4.x/docs/contents/pg/connection.rst Sun Jan 10 06:43:51
2016 (r707)
+++ branches/4.x/docs/contents/pg/connection.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -1,6 +1,8 @@
pgobject -- The connection object
=================================
+.. py:currentmodule:: pg
+
.. class:: pgobject
This object handles a connection to a PostgreSQL database. It embeds and
Modified: branches/4.x/docs/contents/pg/db_wrapper.rst
==============================================================================
--- branches/4.x/docs/contents/pg/db_wrapper.rst Sun Jan 10 06:43:51
2016 (r707)
+++ branches/4.x/docs/contents/pg/db_wrapper.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -1,6 +1,8 @@
The DB wrapper class
====================
+.. py:currentmodule:: pg
+
.. class:: DB
The :class:`pgobject` methods are wrapped in the class :class:`DB`.
Modified: branches/4.x/docs/contents/pg/large_objects.rst
==============================================================================
--- branches/4.x/docs/contents/pg/large_objects.rst Sun Jan 10 06:43:51
2016 (r707)
+++ branches/4.x/docs/contents/pg/large_objects.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -1,6 +1,8 @@
pglarge -- Large Objects
========================
+.. py:currentmodule:: pg
+
.. class:: pglarge
Objects that are instances of the class :class:`pglarge` are used to handle
Modified: branches/4.x/docs/contents/pg/module.rst
==============================================================================
--- branches/4.x/docs/contents/pg/module.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/pg/module.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,6 +1,8 @@
Module functions and constants
==============================
+.. py:currentmodule:: pg
+
The :mod:`pg` module defines a few functions that allow to connect
to a database and to define "default variables" that override
the environment variables used by PostgreSQL.
@@ -18,7 +20,7 @@
connect -- Open a PostgreSQL connection
---------------------------------------
-.. function:: pg.connect([dbname], [host], [port], [opt], [tty], [user],
[passwd])
+.. function:: connect([dbname], [host], [port], [opt], [tty], [user], [passwd])
Open a :mod:`pg` connection
Modified: branches/4.x/docs/contents/pg/query.rst
==============================================================================
--- branches/4.x/docs/contents/pg/query.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/pg/query.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,6 +1,8 @@
pgqueryobject methods
=====================
+.. py:currentmodule:: pg
+
.. class:: pgqueryobject
The :class:`pgqueryobject` returned by :meth:`pgobject.query` and
Modified: branches/4.x/docs/contents/pgdb/connection.rst
==============================================================================
--- branches/4.x/docs/contents/pgdb/connection.rst Sun Jan 10 06:43:51
2016 (r707)
+++ branches/4.x/docs/contents/pgdb/connection.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -1,6 +1,8 @@
pgdbCnx -- The connection object
================================
+.. py:currentmodule:: pgdb
+
.. class:: pgdbCnx
These connection objects respond to the following methods.
Modified: branches/4.x/docs/contents/pgdb/cursor.rst
==============================================================================
--- branches/4.x/docs/contents/pgdb/cursor.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/pgdb/cursor.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,6 +1,8 @@
pgdbCursor -- The cursor object
===============================
+.. py:currentmodule:: pgdb
+
.. class:: pgdbCursor
These objects represent a database cursor, which is used to manage the context
Modified: branches/4.x/docs/contents/pgdb/module.rst
==============================================================================
--- branches/4.x/docs/contents/pgdb/module.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/pgdb/module.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,6 +1,8 @@
Module functions and constants
==============================
+.. py:currentmodule:: pgdb
+
The :mod:`pgdb` module defines a :func:`connect` function that allows to
connect to a database, some global constants describing the capabilities
of the module as well as several exception classes.
@@ -8,7 +10,7 @@
connect -- Open a PostgreSQL connection
---------------------------------------
-.. function:: pgdb.connect([dsn], [user], [password], [host], [database])
+.. function:: connect([dsn], [user], [password], [host], [database])
Return a new connection to the database
Modified: branches/4.x/docs/contents/pgdb/types.rst
==============================================================================
--- branches/4.x/docs/contents/pgdb/types.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/contents/pgdb/types.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -1,6 +1,8 @@
pgdbType -- Type objects and constructors
=========================================
+.. py:currentmodule:: pgdb
+
.. class:: pgdbType
The :attr:`pgdbCursor.description` attribute returns information about each
Added: branches/4.x/docs/contents/postgres/advanced.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ branches/4.x/docs/contents/postgres/advanced.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -0,0 +1,152 @@
+Examples for advanced features
+==============================
+
+In this section, we show how to use some advanced features of PostgreSQL
+using the classic PyGreSQL interface.
+
+We assume that you have already created a connection to the PostgreSQL
+database, as explained in the :doc:`basic`::
+
+ >>> from pg import DB
+ >>> db = DB()
+ >>> query = query
+
+Inheritance
+-----------
+
+A table can inherit from zero or more tables. A query can reference either
+all rows of a table or all rows of a table plus all of its descendants.
+
+For example, the capitals table inherits from cities table (it inherits
+all data fields from cities)::
+
+ >>> data = [('cities', [
+ ... "'San Francisco', 7.24E+5, 63",
+ ... "'Las Vegas', 2.583E+5, 2174",
+ ... "'Mariposa', 1200, 1953"]),
+ ... ('capitals', [
+ ... "'Sacramento',3.694E+5,30,'CA'",
+ ... "'Madison', 1.913E+5, 845, 'WI'"])]
+
+Now, let's populate the tables::
+
+ >>> data = ['cities', [
+ ... "'San Francisco', 7.24E+5, 63"
+ ... "'Las Vegas', 2.583E+5, 2174"
+ ... "'Mariposa', 1200, 1953"],
+ ... 'capitals', [
+ ... "'Sacramento',3.694E+5,30,'CA'",
+ ... "'Madison', 1.913E+5, 845, 'WI'"]]
+ >>> for table, rows in data:
+ ... for row in rows:
+ ... query("INSERT INTO %s VALUES (%s)" % (table, row))
+ >>> print query("SELECT * FROM cities")
+ name |population|altitude
+ -------------+----------+--------
+ San Francisco| 724000| 63
+ Las Vegas | 258300| 2174
+ Mariposa | 1200| 1953
+ Sacramento | 369400| 30
+ Madison | 191300| 845
+ (5 rows)
+ >>> print query("SELECT * FROM capitals")
+ name |population|altitude|state
+ ----------+----------+--------+-----
+ Sacramento| 369400| 30|CA
+ Madison | 191300| 845|WI
+ (2 rows)
+
+You can find all cities, including capitals, that are located at an altitude
+of 500 feet or higher by::
+
+ >>> print query("""SELECT c.name, c.altitude
+ ... FROM cities
+ ... WHERE altitude > 500""")
+ name |altitude
+ ---------+--------
+ Las Vegas| 2174
+ Mariposa | 1953
+ Madison | 845
+ (3 rows)
+
+On the other hand, the following query references rows of the base table only,
+i.e. it finds all cities that are not state capitals and are situated at an
+altitude of 500 feet or higher::
+
+ >>> print query("""SELECT name, altitude
+ ... FROM ONLY cities
+ ... WHERE altitude > 500""")
+ name |altitude
+ ---------+--------
+ Las Vegas| 2174
+ Mariposa | 1953
+ (2 rows)
+
+Arrays
+------
+
+Attributes can be arrays of base types or user-defined types::
+
+ >>> query("""CREATE TABLE sal_emp (
+ ... name text,
+ ... pay_by_quarter int4[],
+ ... pay_by_extra_quarter int8[],
+ ... schedule text[][])""")
+
+
+Insert instances with array attributes. Note the use of braces::
+
+ >>> query("""INSERT INTO sal_emp VALUES (
+ ... 'Bill', '{10000,10000,10000,10000}',
+ ... '{9223372036854775800,9223372036854775800,9223372036854775800}',
+ ... '{{"meeting", "lunch"}, {"training", "presentation"}}')""")
+ >>> query("""INSERT INTO sal_emp VALUES (
+ ... 'Carol', '{20000,25000,25000,25000}',
+ ... '{9223372036854775807,9223372036854775807,9223372036854775807}',
+ ... '{{"breakfast", "consulting"}, {"meeting", "lunch"}}')""")
+
+
+Queries on array attributes::
+
+ >>> query("""SELECT name FROM sal_emp WHERE
+ ... sal_emp.pay_by_quarter[1] != sal_emp.pay_by_quarter[2]""")
+ name
+ -----
+ Carol
+ (1 row)
+
+Retrieve third quarter pay of all employees::
+
+ >>> query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp")
+ pay_by_quarter
+ --------------
+ 10000
+ 25000
+ (2 rows)
+
+Retrieve third quarter extra pay of all employees::
+
+ >>> query("SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp")
+ pay_by_extra_quarter
+ --------------------
+ 9223372036854775800
+ 9223372036854775807
+ (2 rows)
+
+Retrieve first two quarters of extra quarter pay of all employees::
+
+ >>> query("SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp")
+ pay_by_extra_quarter
+ -----------------------------------------
+ {9223372036854775800,9223372036854775800}
+ {9223372036854775807,9223372036854775807}
+ (2 rows)
+
+Select subarrays::
+
+ >>> query("""SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp
+ ... WHERE sal_emp.name = 'Bill'""")
+ schedule
+ ----------------------
+ {{meeting},{training}}
+ (1 row)
Added: branches/4.x/docs/contents/postgres/basic.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ branches/4.x/docs/contents/postgres/basic.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -0,0 +1,359 @@
+Basic examples
+==============
+
+In this section, we demonstrate how to use some of the very basic features
+of PostgreSQL using the classic PyGreSQL interface.
+
+Creating a connection to the database
+-------------------------------------
+
+We start by creating a **connection** to the PostgreSQL database::
+
+ >>> from pg import DB
+ >>> db = DB()
+
+If you pass no parameters when creating the :class:`DB` instance, then
+PyGreSQL will try to connect to the database on the local host that has
+the same name as the current user, and also use that name for login.
+
+You can also pass the database name, host, port and login information
+as parameters when creating the :class:`DB` instance::
+
+ >>> db = DB(dbname='testdb', host='pgserver', port=5432,
+ ... user='fred', passwd='tiger')
+
+The :class:`DB` class of which ``db`` is an object is a wrapper around
+the lower level :class:`pgobject` class of the :mod:`pg` module.
+The most important method of such connection objects is the ``query``
+method that allows you to send SQL commands to the database.
+
+Creating tables
+---------------
+
+The first thing you would want to do in an empty database is creating a
+table. To do this, you need to send a **CREATE TABLE** command to the
+database. PostgreSQL has its own set of built-in types that can be used
+for the table columns. Let us create two tables "weather" and "cities"::
+
+ >>> db.query("""CREATE TABLE weather (
+ ... city varchar(80),
+ ... temp_lo int, temp_hi int,
+ ... prcp float8,
+ ... date date)""")
+ >>> db.query("""CREATE TABLE cities (
+ ... name varchar(80),
+ ... location point)""")
+
+.. note::
+ Keywords are case-insensitive but identifiers are case-sensitive.
+
+You can get a list of all tables in the database with::
+
+ >>> db.get_tables()
+ ['public.cities', 'public.weather']
+
+
+Insert data
+-----------
+
+Now we want to fill our tables with data. An **INSERT** statement is used
+to insert a new row into a table. There are several ways you can specify
+what columns the data should go to.
+
+Let us insert a row into each of these tables. Tthe simplest case is when
+the list of values corresponds to the order of the columns specified in the
+CREATE TABLE command::
+
+ >>> db.query("""INSERT INTO weather
+ ... VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')""")
+ >>> db.query("""INSERT INTO cities
+ ... VALUES ('San Francisco', '(-194.0, 53.0)')""")
+
+You can also specify what column the values correspond to. The columns can
+be specified in any order. You may also omit any number of columns,
+unknown precipitation below::
+
+ >>> db.query("""INSERT INTO weather (date, city, temp_hi, temp_lo)
+ ... VALUES ('11/29/1994', 'Hayward', 54, 37)""")
+
+
+If you get errors regarding the format of the date values, your database
+is probably set to a different date style. In this case you must change
+the date style like this::
+
+ >>> db.query("set datestyle = MDY")
+
+Instead of explicitly writing the INSERT statement and sending it to the
+database with the :meth:`DB.query` method, you can also use the more
+convenient :meth:`DB.insert` method that does the same under the hood::
+
+ >>> db.insert('weather',
+ ... date='11/29/1994', city='Hayward', temp_hi=54, temp_lo=37)
+
+And instead of using keyword parameters, you can also pass the values
+to the :meth:`DB.insert` method in a single Python dictionary.
+
+If you have a Python list with many rows that shall be used to fill
+a database table quickly, you can use the :meth:`DB.inserttable` method.
+
+Retrieving data
+---------------
+
+After having entered some data into our tables, let's see how we can get
+the data out again. A **SELECT** statement is used for retrieving data.
+The basic syntax is:
+
+.. code-block:: psql
+
+ SELECT columns FROM tables WHERE predicates
+
+A simple one would be the following query::
+
+ >>> q = db.query("SELECT * FROM weather")
+ >>> print q
+ city |temp_lo|temp_hi|prcp| date
+ -------------+-------+-------+----+----------
+ San Francisco| 46| 50|0.25|1994-11-27
+ Hayward | 37| 54| |1994-11-29
+ (2 rows)
+
+You may also specify expressions in the target list.
+(The 'AS column' specifies the column name of the result. It is optional.)
+
+::
+
+ >>> print db.query("""SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date
+ ... FROM weather""")
+ city |temp_avg| date
+ -------------+--------+----------
+ San Francisco| 48|1994-11-27
+ Hayward | 45|1994-11-29
+ (2 rows)
+
+If you want to retrieve rows that satisfy certain condition (i.e. a
+restriction), specify the condition in a WHERE clause. The following
+retrieves the weather of San Francisco on rainy days::
+
+ >>> print db.query("""SELECT * FROM weather
+ ... WHERE city = 'San Francisco' AND prcp > 0.0""")
+ city |temp_lo|temp_hi|prcp| date
+ -------------+-------+-------+----+----------
+ San Francisco| 46| 50|0.25|1994-11-27
+ (1 row)
+
+Here is a more complicated one. Duplicates are removed when DISTINCT is
+specified. ORDER BY specifies the column to sort on. (Just to make sure the
+following won't confuse you, DISTINCT and ORDER BY can be used separately.)
+
+::
+
+ >>> print db.query("SELECT DISTINCT city FROM weather ORDER BY city")
+ city
+ -------------
+ Hayward
+ San Francisco
+ (2 rows)
+
+So far we have only printed the output of a SELECT query. The object that
+is returned by the query is an instance of the :class:`pgqueryobject` class
+that can print itself in the nicely formatted way we saw above. But you can
+also retrieve the results as a list of tuples, by using the
+:meth:`pgqueryobject.getresult` method::
+
+ >>> from pprint import pprint
+ >>> q = db.query("SELECT * FROM weather")
+ >>> pprint(q.getresult())
+ [('San Francisco', 46, 50, 0.25, '1994-11-27'),
+ ('Hayward', 37, 54, None, '1994-11-29')]
+
+Here we used pprint to print out the returned list in a nicely formatted way.
+
+If you want to retrieve the results as a list of dictionaries instead of
+tuples, use the :meth:`pgqueryobject.dictresult` method instead::
+
+ >>> pprint(q.dictresult())
+ [{'city': 'San Francisco',
+ 'date': '1994-11-27',
+ 'prcp': 0.25,
+ 'temp_hi': 50,
+ 'temp_lo': 46},
+ {'city': 'Hayward',
+ 'date': '1994-11-29',
+ 'prcp': None,
+ 'temp_hi': 54,
+ 'temp_lo': 37}]
+
+Finally, in Python 2.5 and above you can also retrieve the results as a list
+of named tuples, using the :meth:`pgqueryobject.namedresult` method.
+This can be a good compromise between simple tuples and the more memory
+intensive dictionaries:
+
+ >>> for row in q.namedresult():
+ ... print row.city, row.date
+ ...
+ San Francisco 1994-11-27
+ Hayward 1994-11-29
+
+If you only want to retrieve a single row of data, you can use the more
+convenient :meth:`DB.get` method that does the same under the hood::
+
+ >>> d = dict(city='Hayward')
+ >>> db.get('weather', d, 'city')
+ >>> pprint(d)
+ {'city': 'Hayward',
+ 'date': '1994-11-29',
+ 'prcp': None,
+ 'temp_hi': 54,
+ 'temp_lo': 37}
+
+As you see, the :meth:`DB.get` method returns a dictionary with the column
+names as keys. In the third parameter you can specify which column should
+be looked up in the WHERE statement of the SELECT statement that is executed
+by the :meth:`DB.get` method. You normally don't need it when the table was
+created with a primary key.
+
+Retrieving data into other tables
+---------------------------------
+
+A SELECT ... INTO statement can be used to retrieve data into another table::
+
+ >>> db.query("""SELECT * INTO TEMPORARY TABLE temptab FROM weather
+ ... WHERE city = 'San Francisco' and prcp > 0.0""")
+
+This fills a temporary table "temptab" with a subset of the data in the
+original "weather" table. It can be listed with::
+
+ >>> print db.query("SELECT * from temptab")
+ city |temp_lo|temp_hi|prcp| date
+ -------------+-------+-------+----+----------
+ San Francisco| 46| 50|0.25|1994-11-27
+ (1 row)
+
+Aggregates
+----------
+
+Let's try the following query::
+
+ >>> print db.query("SELECT max(temp_lo) FROM weather")
+ max
+ ---
+ 46
+ (1 row)
+
+You can also use aggregates with the GROUP BY clause::
+
+ >>> print db.query("SELECT city, max(temp_lo) FROM weather GROUP BY city")
+ city |max
+ -------------+---
+ Hayward | 37
+ San Francisco| 46
+ (2 rows)
+
+Joining tables
+--------------
+
+Queries can access multiple tables at once or access the same table in such a
+way that multiple instances of the table are being processed at the same time.
+
+Suppose we want to find all the records that are in the temperature range of
+other records. W1 and W2 are aliases for weather. We can use the following
+query to achieve that::
+
+ >>> print db.query("""SELECT W1.city, W1.temp_lo, W1.temp_hi,
+ ... W2.city, W2.temp_lo, W2.temp_hi FROM weather W1, weather W2
+ ... WHERE W1.temp_lo < W2.temp_lo and W1.temp_hi > W2.temp_hi""")
+ city |temp_lo|temp_hi| city |temp_lo|temp_hi
+ -------+-------+-------+-------------+-------+-------
+ Hayward| 37| 54|San Francisco| 46| 50
+ (1 row)
+
+Now let's join two tables. The following joins the "weather" table and the
+"cities" table::
+
+ >>> print db.query("""SELECT city, location, prcp, date FROM weather,
cities
+ ... WHERE name = city""")
+ city |location |prcp| date
+ -------------+---------+----+----------
+ San Francisco|(-194,53)|0.25|1994-11-27
+ (1 row)
+
+Since the column names are all different, we don't have to specify the table
+name. If you want to be clear, you can do the following. They give identical
+results, of course::
+
+ >>> print db.query("""SELECT w.city, c.location, w.prcp, w.date
+ ... FROM weather w, cities c WHERE c.name = w.city""")
+ city |location |prcp| date
+ -------------+---------+----+----------
+ San Francisco|(-194,53)|0.25|1994-11-27
+ (1 row)
+
+Updating data
+-------------
+
+It you want to change the data that has already been inserted into a database
+table, you will need the **UPDATE** statement.
+
+Suppose you discover the temperature readings are all off by 2 degrees as of
+Nov 28, you may update the data as follow::
+
+ >>> db.query("""UPDATE weather
+ ... SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
+ ... WHERE date > '11/28/1994'""")
+ '1'
+ >>> print db.query("SELECT * from weather")
+ city |temp_lo|temp_hi|prcp| date
+ -------------+-------+-------+----+----------
+ San Francisco| 46| 50|0.25|1994-11-27
+ Hayward | 35| 52| |1994-11-29
+ (2 rows)
+
+Note that the UPDATE statement returned the string ``'1'``, indicating that
+exactly one row of data has been affected by the update.
+
+If you retrieved one row of data as a dictionary using the :meth:`DB.get`
+method, then you can also update that row with the :meth:`DB.update` method.
+
+Deleting data
+-------------
+
+To delete rows from a table, a **DELETE** statement can be used.
+
+Suppose you are no longer interested in the weather of Hayward, you can do
+the following to delete those rows from the table::
+
+ >>> db.query("DELETE FROM weather WHERE city = 'Hayward'")
+ '1'
+
+Again, you get the string ``'1'`` as return value, indicating that exactly
+one row of data has been deleted.
+
+You can also delete all the rows in a table by doing the following.
+This is different from DROP TABLE which removes the table itself in addition
+to the removing the rows, as explained in the next section.
+
+::
+
+ >>> db.query("DELETE FROM weather")
+ '1'
+ >>> print db.query("SELECT * from weather")
+ city|temp_lo|temp_hi|prcp|date
+ ----+-------+-------+----+----
+ (0 rows)
+
+Since only one row was left in the table, the DELETE query again returns the
+string ``'1'``. The SELECT query now gives an empty result.
+
+If you retrieved a row of data as a dictionary using the :meth:`DB.get`
+method, then you can also delete that row with the :meth:`DB.delete` method.
+
+
+Removing the tables
+-------------------
+The **DROP TABLE** command is used to remove tables. After you have done this,
+you can no longer use those tables::
+
+ >>> db.query("DROP TABLE weather, cities")
+ >>> db.query("select * from weather")
+ pg.ProgrammingError: Error: Relation "weather" does not exist
+
Added: branches/4.x/docs/contents/postgres/func.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ branches/4.x/docs/contents/postgres/func.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -0,0 +1,160 @@
+Examples for using SQL functions
+================================
+
+We assume that you have already created a connection to the PostgreSQL
+database, as explained in the :doc:`basic`::
+
+ >>> from pg import DB
+ >>> db = DB()
+ >>> query = db.query
+
+Creating SQL Functions on Base Types
+------------------------------------
+
+A **CREATE FUNCTION** statement lets you create a new function that can be
+used in expressions (in SELECT, INSERT, etc.). We will start with functions
+that return values of base types.
+
+Let's create a simple SQL function that takes no arguments and returns 1::
+
+ >>> query("""CREATE FUNCTION one() RETURNS int4
+ ... AS 'SELECT 1 as ONE' LANGUAGE SQL""")
+
+Functions can be used in any expressions (eg. in the target"list or
+qualifications)::
+
+ >>> print db.query("SELECT one() AS answer")
+ answer
+ ------
+ 1
+ (1 row)
+
+
+Here's how you create a function that takes arguments. The following function
+returns the sum of its two arguments::
+
+ >>> query("""CREATE FUNCTION add_em(int4, int4) RETURNS int4
+ ... AS $$ SELECT $1 + $2 $$ LANGUAGE SQL""")
+ >>> print query("SELECT add_em(1, 2) AS answer")
+ answer
+ ------
+ 3
+ (1 row)
+
+
+Creating SQL Functions on Composite Types
+-----------------------------------------
+
+It is also possible to create functions that return values of composite types.
+
+Before we create more sophisticated functions, let's populate an EMP table::
+
+ >>> query("""CREATE TABLE EMP (
+ ... name text,
+ ... salary int4,
+ ... age f int4,
+ ... dept varchar(16))""")
+ >>> emps = ["'Sam', 1200, 16, 'toy'",
+ ... "'Claire', 5000, 32, 'shoe'",
+ ... "'Andy', -1000, 2, 'candy'",
+ ... "'Bill', 4200, 36, 'shoe'",
+ ... "'Ginger', 4800, 30, 'candy'"]
+ >>> for emp in emps:
+ ... query("INSERT INTO EMP VALUES (%s)" % emp)
+
+Every INSERT statement will return a '1' indicating that it has inserted
+one row into the EMP table.
+
+The argument of a function can also be a tuple. For instance, *double_salary*
+takes a tuple of the EMP table::
+
+ >>> query("""CREATE FUNCTION double_salary(EMP) RETURNS int4
+ ... AS $$ SELECT $1.salary * 2 AS salary $$ LANGUAGE SQL""")
+ >>> print query("""SELECT name, double_salary(EMP) AS dream
+ ... FROM EMP WHERE EMP.dept = 'toy'""")
+ name|dream
+ ----+-----
+ Sam | 2400
+ (1 row)
+
+The return value of a function can also be a tuple. However, make sure that the
+expressions in the target list are in the same order as the columns of EMP::
+
+ >>> query("""CREATE FUNCTION new_emp() RETURNS EMP AS $$
+ ... SELECT 'None'::text AS name,
+ ... 1000 AS salary,
+ ... 25 AS age,
+ ... 'None'::varchar(16) AS dept
+ ... $$ LANGUAGE SQL""")
+
+You can then project a column out of resulting the tuple by using the
+"function notation" for projection columns (i.e. ``bar(foo)`` is equivalent
+to ``foo.bar``). Note that ``new_emp().name`` isn't supported::
+
+ >>> print query("SELECT name(new_emp()) AS nobody")
+ nobody
+ ------
+ None
+ (1 row)
+
+Let's try one more function that returns tuples::
+
+ >>> query("""CREATE FUNCTION high_pay() RETURNS setof EMP
+ ... AS 'SELECT * FROM EMP where salary > 1500'
+ ... LANGUAGE SQL""")
+ >>> query("SELECT name(high_pay()) AS overpaid")
+ overpaid
+ --------
+ Claire
+ Bill
+ Ginger
+ (3 rows)
+
+
+Creating SQL Functions with multiple SQL statements
+---------------------------------------------------
+
+You can also create functions that do more than just a SELECT.
+
+You may have noticed that Andy has a negative salary. We'll create a function
+that removes employees with negative salaries::
+
+ >>> query("SELECT * FROM EMP")
+ name |salary|age|dept
+ ------+------+---+-----
+ Sam | 1200| 16|toy
+ Claire| 5000| 32|shoe
+ Andy | -1000| 2|candy
+ Bill | 4200| 36|shoe
+ Ginger| 4800| 30|candy
+ (5 rows)
+ >>> query("""CREATE FUNCTION clean_EMP () RETURNS int4 AS
+ ... 'DELETE FROM EMP WHERE EMP.salary <= 0;
+ ... SELECT 1 AS ignore_this'
+ ... LANGUAGE SQL""")
+ >>> query("SELECT clean_EMP()")
+ clean_emp
+ ---------
+ 1
+ (1 row)
+ >>> query("SELECT * FROM EMP")
+ name |salary|age|dept
+ ------+------+---+-----
+ Sam | 1200| 16|toy
+ Claire| 5000| 32|shoe
+ Bill | 4200| 36|shoe
+ Ginger| 4800| 30|candy
+ (4 rows)
+
+Remove functions that were created in this example
+--------------------------------------------------
+
+We can remove the functions that we have created in this example and the
+table EMP, by using the DROP command::
+
+ query("DROP FUNCTION clean_EMP()")
+ query("DROP FUNCTION high_pay()")
+ query("DROP FUNCTION new_emp()")
+ query("DROP FUNCTION add_em(int4, int4)")
+ query("DROP FUNCTION one()")
+ query("DROP TABLE EMP CASCADE")
Added: branches/4.x/docs/contents/postgres/index.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ branches/4.x/docs/contents/postgres/index.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -0,0 +1,17 @@
+-------------------
+A PostgreSQL Primer
+-------------------
+
+The examples in this chapter of the documentation have been taken
+from the PostgreSQL manual. They demonstrate some PostgreSQL features
+using the classic PyGreSQL interface. They can serve as an introduction
+to PostgreSQL, but not so much as examples for the use of PyGreSQL.
+
+Contents
+========
+
+.. toctree::
+ basic
+ advanced
+ func
+ syscat
Added: branches/4.x/docs/contents/postgres/syscat.rst
==============================================================================
--- /dev/null 00:00:00 1970 (empty, because file is newly added)
+++ branches/4.x/docs/contents/postgres/syscat.rst Sun Jan 10 15:59:01
2016 (r708)
@@ -0,0 +1,132 @@
+Examples for using the system catalogs
+======================================
+
+The system catalogs are regular tables where PostgreSQL stores schema metadata,
+such as information about tables and columns, and internal bookkeeping
+information. You can drop and recreate the tables, add columns, insert and
+update values, and severely mess up your system that way. Normally, one
+should not change the system catalogs by hand, there are always SQL commands
+to do that. For example, CREATE DATABASE inserts a row into the *pg_database*
+catalog — and actually creates the database on disk.
+
+It this section we want to show examples for how to parse some of the system
+catalogs, making queries with the classic PyGreSQL interface.
+
+We assume that you have already created a connection to the PostgreSQL
+database, as explained in the :doc:`basic`::
+
+ >>> from pg import DB
+ >>> db = DB()
+ >>> query = query
+
+Lists indices
+-------------
+
+This query lists all simple indices in the database::
+
+ print query("""SELECT bc.relname AS class_name,
+ ic.relname AS index_name, a.attname
+ FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
+ WHERE i.indrelid = bc.oid AND i.indexrelid = ic.oid
+ AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid
+ AND NOT a.attisdropped
+ ORDER BY class_name, index_name, attname""")
+
+
+List user defined attributes
+----------------------------
+
+This query lists all user defined attributes and their type
+in user-defined classes::
+
+ print query("""SELECT c.relname, a.attname, t.typname
+ FROM pg_class c, pg_attribute a, pg_type t
+ WHERE c.relkind = 'r' and c.relname !~ '^pg_'
+ AND c.relname !~ '^Inv' and a.attnum > 0
+ AND a.attrelid = c.oid and a.atttypid = t.oid
+ AND NOT a.attisdropped
+ ORDER BY relname, attname""")
+
+List user defined base types
+----------------------------
+
+This query lists all user defined base types::
+
+ print query("""SELECT r.rolname, t.typname
+ FROM pg_type t, pg_authid r
+ WHERE r.oid = t.typowner
+ AND t.typrelid = '0'::oid and t.typelem = '0'::oid
+ AND r.rolname != 'postgres'
+ ORDER BY rolname, typname""")
+
+
+List operators
+---------------
+
+This query lists all right-unary operators::
+
+ print query("""SELECT o.oprname AS right_unary,
+ lt.typname AS operand, result.typname AS return_type
+ FROM pg_operator o, pg_type lt, pg_type result
+ WHERE o.oprkind='r' and o.oprleft = lt.oid
+ AND o.oprresult = result.oid
+ ORDER BY operand""")
+
+
+This query lists all left-unary operators::
+
+ print query("""SELECT o.oprname AS left_unary,
+ rt.typname AS operand, result.typname AS return_type
+ FROM pg_operator o, pg_type rt, pg_type result
+ WHERE o.oprkind='l' AND o.oprright = rt.oid
+ AND o.oprresult = result.oid
+ ORDER BY operand""")
+
+
+And this one lists all of the binary operators::
+
+ print query("""SELECT o.oprname AS binary_op,
+ rt.typname AS right_opr, lt.typname AS left_opr,
+ result.typname AS return_type
+ FROM pg_operator o, pg_type rt, pg_type lt, pg_type result
+ WHERE o.oprkind = 'b' AND o.oprright = rt.oid
+ AND o.oprleft = lt.oid AND o.oprresult = result.oid""")
+
+
+List functions of a language
+----------------------------
+
+Given a programming language, this query returns the name, args and return
+type from all functions of a language::
+
+ language = 'sql'
+ print query("""SELECT p.proname, p.pronargs, t.typname
+ FROM pg_proc p, pg_language l, pg_type t
+ WHERE p.prolang = l.oid AND p.prorettype = t.oid
+ AND l.lanname = $1
+ ORDER BY proname""", (language,))
+
+
+List aggregate functions
+------------------------
+
+This query lists all of the aggregate functions and the type to which
+they can be applied::
+
+ print query("""SELECT p.proname, t.typname
+ FROM pg_aggregate a, pg_proc p, pg_type t
+ WHERE a.aggfnoid = p.oid
+ and p.proargtypes[0] = t.oid
+ ORDER BY proname, typname""")
+
+List operator families
+----------------------
+
+The following query lists all defined operator families and all the operators
+included in each family::
+
+ print query("""SELECT am.amname, opf.opfname, amop.amopopr::regoperator
+ FROM pg_am am, pg_opfamily opf, pg_amop amop
+ WHERE opf.opfmethod = am.oid
+ AND amop.amopfamily = opf.oid
+ ORDER BY amname, opfname, amopopr""")
Modified: branches/4.x/docs/download/files.rst
==============================================================================
--- branches/4.x/docs/download/files.rst Sun Jan 10 06:43:51 2016
(r707)
+++ branches/4.x/docs/download/files.rst Sun Jan 10 15:59:01 2016
(r708)
@@ -24,10 +24,4 @@
tests/ a suite of unit tests for PyGreSQL
-tutorial/ demos directory
-
- The samples contained in this directory have been taken
- from the PostgreSQL manual and were used for module testing.
- They demonstrate some PostgreSQL features.
-
========== =
Modified: branches/4.x/mktar
==============================================================================
--- branches/4.x/mktar Sun Jan 10 06:43:51 2016 (r707)
+++ branches/4.x/mktar Sun Jan 10 15:59:01 2016 (r708)
@@ -40,7 +40,6 @@
docs/_static docs/_templates"
HTMLFILES="docs/_build/html"
TESTFILES="module/tests/*.py"
-TUTFILES="tutorial/*.py"
echo "Making source tarball..."
@@ -50,12 +49,10 @@
mkdir $TD
mkdir -p $TD/docs/_build/html
mkdir $TD/tests
-mkdir $TD/tutorial
cp $MODFILES $TD
cp -r $DOCFILES $TD/docs
cp -r $HTMLFILES $TD/docs/_build
cp $TESTFILES $TD/tests
-cp $TUTFILES $TD/tutorial
tar -cvzf $TF $TD
chmod 644 $TF
rm -rf $TD
@@ -63,4 +60,3 @@
ln -s $TD.tgz $DISTDIR/$SYMLINK
echo "$TF has been built"
-
_______________________________________________
PyGreSQL mailing list
[email protected]
https://mail.vex.net/mailman/listinfo.cgi/pygresql
