source: branches/4.x/docs/contents/postgres/basic.rst @ 741

Last change on this file since 741 was 741, checked in by darcy, 4 years ago

Fix typo.

File size: 12.1 KB
Line 
1Basic examples
2==============
3
4In this section, we demonstrate how to use some of the very basic features
5of PostgreSQL using the classic PyGreSQL interface.
6
7Creating a connection to the database
8-------------------------------------
9
10We start by creating a **connection** to the PostgreSQL database::
11
12    >>> from pg import DB
13    >>> db = DB()
14
15If you pass no parameters when creating the :class:`DB` instance, then
16PyGreSQL will try to connect to the database on the local host that has
17the same name as the current user, and also use that name for login.
18
19You can also pass the database name, host, port and login information
20as parameters when creating the :class:`DB` instance::
21
22    >>> db = DB(dbname='testdb', host='pgserver', port=5432,
23    ...     user='scott', passwd='tiger')
24
25The :class:`DB` class of which ``db`` is an object is a wrapper around
26the lower level :class:`pgobject` class of the :mod:`pg` module.
27The most important method of such connection objects is the ``query``
28method that allows you to send SQL commands to the database.
29
30Creating tables
31---------------
32
33The first thing you would want to do in an empty database is creating a
34table. To do this, you need to send a **CREATE TABLE** command to the
35database. PostgreSQL has its own set of built-in types that can be used
36for the table columns. Let us create two tables "weather" and "cities"::
37
38    >>> db.query("""CREATE TABLE weather (
39    ...     city varchar(80),
40    ...     temp_lo int, temp_hi int,
41    ...     prcp float8,
42    ...     date date)""")
43    >>> db.query("""CREATE TABLE cities (
44    ...     name varchar(80),
45    ...     location point)""")
46
47.. note::
48    Keywords are case-insensitive but identifiers are case-sensitive.
49
50You can get a list of all tables in the database with::
51
52    >>> db.get_tables()
53    ['public.cities', 'public.weather']
54
55
56Insert data
57-----------
58
59Now we want to fill our tables with data. An **INSERT** statement is used
60to insert a new row into a table. There are several ways you can specify
61what columns the data should go to.
62
63Let us insert a row into each of these tables. The simplest case is when
64the list of values corresponds to the order of the columns specified in the
65CREATE TABLE command::
66
67    >>> db.query("""INSERT INTO weather
68    ...     VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')""")
69    >>> db.query("""INSERT INTO cities
70    ...     VALUES ('San Francisco', '(-194.0, 53.0)')""")
71
72You can also specify what column the values correspond to. The columns can
73be specified in any order. You may also omit any number of columns,
74unknown precipitation below::
75
76    >>> db.query("""INSERT INTO weather (date, city, temp_hi, temp_lo)
77    ...     VALUES ('11/29/1994', 'Hayward', 54, 37)""")
78
79
80If you get errors regarding the format of the date values, your database
81is probably set to a different date style. In this case you must change
82the date style like this::
83
84    >>> db.query("set datestyle = MDY")
85
86Instead of explicitly writing the INSERT statement and sending it to the
87database with the :meth:`DB.query` method, you can also use the more
88convenient :meth:`DB.insert` method that does the same under the hood::
89
90    >>> db.insert('weather',
91    ...     date='11/29/1994', city='Hayward', temp_hi=54, temp_lo=37)
92
93And instead of using keyword parameters, you can also pass the values
94to the :meth:`DB.insert` method in a single Python dictionary.
95
96If you have a Python list with many rows that shall be used to fill
97a database table quickly, you can use the :meth:`DB.inserttable` method.
98
99Retrieving data
100---------------
101
102After having entered some data into our tables, let's see how we can get
103the data out again. A **SELECT** statement is used for retrieving data.
104The basic syntax is:
105
106.. code-block:: psql
107
108    SELECT columns FROM tables WHERE predicates
109
110A simple one would be the following query::
111
112    >>> q = db.query("SELECT * FROM weather")
113    >>> print q
114        city     |temp_lo|temp_hi|prcp|   date
115    -------------+-------+-------+----+----------
116    San Francisco|     46|     50|0.25|1994-11-27
117    Hayward      |     37|     54|    |1994-11-29
118    (2 rows)
119
120You may also specify expressions in the target list.
121(The 'AS column' specifies the column name of the result. It is optional.)
122
123::
124
125    >>> print db.query("""SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date
126    ...     FROM weather""")
127        city     |temp_avg|   date
128    -------------+--------+----------
129    San Francisco|      48|1994-11-27
130    Hayward      |      45|1994-11-29
131    (2 rows)
132
133If you want to retrieve rows that satisfy certain condition (i.e. a
134restriction), specify the condition in a WHERE clause. The following
135retrieves the weather of San Francisco on rainy days::
136
137    >>> print db.query("""SELECT * FROM weather
138    ...     WHERE city = 'San Francisco' AND prcp > 0.0""")
139        city     |temp_lo|temp_hi|prcp|   date
140    -------------+-------+-------+----+----------
141    San Francisco|     46|     50|0.25|1994-11-27
142    (1 row)
143
144Here is a more complicated one. Duplicates are removed when DISTINCT is
145specified. ORDER BY specifies the column to sort on. (Just to make sure the
146following won't confuse you, DISTINCT and ORDER BY can be used separately.)
147
148::
149
150    >>> print db.query("SELECT DISTINCT city FROM weather ORDER BY city")
151        city
152    -------------
153    Hayward
154    San Francisco
155    (2 rows)
156
157So far we have only printed the output of a SELECT query. The object that
158is returned by the query is an instance of the :class:`pgqueryobject` class
159that can print itself in the nicely formatted way we saw above. But you can
160also retrieve the results as a list of tuples, by using the
161:meth:`pgqueryobject.getresult` method::
162
163    >>> from pprint import pprint
164    >>> q = db.query("SELECT * FROM weather")
165    >>> pprint(q.getresult())
166    [('San Francisco', 46, 50, 0.25, '1994-11-27'),
167     ('Hayward', 37, 54, None, '1994-11-29')]
168
169Here we used pprint to print out the returned list in a nicely formatted way.
170
171If you want to retrieve the results as a list of dictionaries instead of
172tuples, use the :meth:`pgqueryobject.dictresult` method instead::
173
174    >>> pprint(q.dictresult())
175    [{'city': 'San Francisco',
176      'date': '1994-11-27',
177      'prcp': 0.25,
178      'temp_hi': 50,
179      'temp_lo': 46},
180     {'city': 'Hayward',
181      'date': '1994-11-29',
182      'prcp': None,
183      'temp_hi': 54,
184      'temp_lo': 37}]
185
186Finally, in Python 2.5 and above you can also retrieve the results as a list
187of named tuples, using the :meth:`pgqueryobject.namedresult` method.
188This can be a good compromise between simple tuples and the more memory
189intensive dictionaries:
190
191    >>> for row in q.namedresult():
192    ...     print row.city, row.date
193    ...
194    San Francisco 1994-11-27
195    Hayward 1994-11-29
196
197If you only want to retrieve a single row of data, you can use the more
198convenient :meth:`DB.get` method that does the same under the hood::
199
200    >>> d = dict(city='Hayward')
201    >>> db.get('weather', d, 'city')
202    >>> pprint(d)
203    {'city': 'Hayward',
204     'date': '1994-11-29',
205     'prcp': None,
206     'temp_hi': 54,
207     'temp_lo': 37}
208
209As you see, the :meth:`DB.get` method returns a dictionary with the column
210names as keys. In the third parameter you can specify which column should
211be looked up in the WHERE statement of the SELECT statement that is executed
212by the :meth:`DB.get` method. You normally don't need it when the table was
213created with a primary key.
214
215Retrieving data into other tables
216---------------------------------
217
218A SELECT ... INTO statement can be used to retrieve data into another table::
219
220    >>> db.query("""SELECT * INTO TEMPORARY TABLE temptab FROM weather
221    ...     WHERE city = 'San Francisco' and prcp > 0.0""")
222
223This fills a temporary table "temptab" with a subset of the data in the
224original "weather" table. It can be listed with::
225
226    >>> print db.query("SELECT * from temptab")
227        city     |temp_lo|temp_hi|prcp|   date
228    -------------+-------+-------+----+----------
229    San Francisco|     46|     50|0.25|1994-11-27
230    (1 row)
231
232Aggregates
233----------
234
235Let's try the following query::
236
237    >>> print db.query("SELECT max(temp_lo) FROM weather")
238    max
239    ---
240     46
241    (1 row)
242
243You can also use aggregates with the GROUP BY clause::
244
245    >>> print db.query("SELECT city, max(temp_lo) FROM weather GROUP BY city")
246        city     |max
247    -------------+---
248    Hayward      | 37
249    San Francisco| 46
250    (2 rows)
251
252Joining tables
253--------------
254
255Queries can access multiple tables at once or access the same table in such a
256way that multiple instances of the table are being processed at the same time.
257
258Suppose we want to find all the records that are in the temperature range of
259other records. W1 and W2 are aliases for weather. We can use the following
260query to achieve that::
261
262    >>> print db.query("""SELECT W1.city, W1.temp_lo, W1.temp_hi,
263    ...     W2.city, W2.temp_lo, W2.temp_hi FROM weather W1, weather W2
264    ...     WHERE W1.temp_lo < W2.temp_lo and W1.temp_hi > W2.temp_hi""")
265     city  |temp_lo|temp_hi|    city     |temp_lo|temp_hi
266    -------+-------+-------+-------------+-------+-------
267    Hayward|     37|     54|San Francisco|     46|     50
268    (1 row)
269
270Now let's join two tables. The following joins the "weather" table and the
271"cities" table::
272
273    >>> print db.query("""SELECT city, location, prcp, date FROM weather, cities
274    ...     WHERE name = city""")
275        city     |location |prcp|   date
276    -------------+---------+----+----------
277    San Francisco|(-194,53)|0.25|1994-11-27
278    (1 row)
279
280Since the column names are all different, we don't have to specify the table
281name. If you want to be clear, you can do the following. They give identical
282results, of course::
283
284    >>> print db.query("""SELECT w.city, c.location, w.prcp, w.date
285    ...     FROM weather w, cities c WHERE c.name = w.city""")
286        city     |location |prcp|   date
287    -------------+---------+----+----------
288    San Francisco|(-194,53)|0.25|1994-11-27
289    (1 row)
290
291Updating data
292-------------
293
294It you want to change the data that has already been inserted into a database
295table, you will need the **UPDATE** statement.
296
297Suppose you discover the temperature readings are all off by 2 degrees as of
298Nov 28, you may update the data as follow::
299
300    >>> db.query("""UPDATE weather
301    ...     SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
302    ...     WHERE date > '11/28/1994'""")
303    '1'
304    >>> print db.query("SELECT * from weather")
305        city     |temp_lo|temp_hi|prcp|   date
306    -------------+-------+-------+----+----------
307    San Francisco|     46|     50|0.25|1994-11-27
308    Hayward      |     35|     52|    |1994-11-29
309    (2 rows)
310
311Note that the UPDATE statement returned the string ``'1'``, indicating that
312exactly one row of data has been affected by the update.
313
314If you retrieved one row of data as a dictionary using the :meth:`DB.get`
315method, then you can also update that row with the :meth:`DB.update` method.
316
317Deleting data
318-------------
319
320To delete rows from a table, a **DELETE** statement can be used.
321
322Suppose you are no longer interested in the weather of Hayward, you can do
323the following to delete those rows from the table::
324
325    >>> db.query("DELETE FROM weather WHERE city = 'Hayward'")
326    '1'
327
328Again, you get the string ``'1'`` as return value, indicating that exactly
329one row of data has been deleted.
330
331You can also delete all the rows in a table by doing the following.
332This is different from DROP TABLE which removes the table itself in addition
333to the removing the rows, as explained in the next section.
334
335::
336
337    >>> db.query("DELETE FROM weather")
338    '1'
339    >>> print db.query("SELECT * from weather")
340    city|temp_lo|temp_hi|prcp|date
341    ----+-------+-------+----+----
342    (0 rows)
343
344Since only one row was left in the table, the DELETE query again returns the
345string ``'1'``. The SELECT query now gives an empty result.
346
347If you retrieved a row of data as a dictionary using the :meth:`DB.get`
348method, then you can also delete that row with the :meth:`DB.delete` method.
349
350
351Removing the tables
352-------------------
353The **DROP TABLE** command is used to remove tables. After you have done this,
354you can no longer use those tables::
355
356    >>> db.query("DROP TABLE weather, cities")
357    >>> db.query("select * from weather")
358    pg.ProgrammingError: Error:  Relation "weather" does not exist
359
Note: See TracBrowser for help on using the repository browser.