|
| 1 | +#! /usr/bin/env python |
| 2 | +# advanced.py - demo of advanced features of PostGres. Some may not be ANSI. |
| 3 | +# inspired from the Postgres tutorial |
| 4 | +# adapted to Python 1995 by Pascal Andre |
| 5 | + |
| 6 | +print""" |
| 7 | +__________________________________________________________________ |
| 8 | +MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL |
| 9 | +
|
| 10 | +This module is designed for being imported from python prompt |
| 11 | +
|
| 12 | +In order to run the samples included here, first create a connection |
| 13 | +using : cnx = advanced.DB(...) |
| 14 | +
|
| 15 | +The "..." should be replaced with whatever arguments you need to open an |
| 16 | +existing database. Usually all you need is the name of the database and, |
| 17 | +in fact, if it is the same as your login name, you can leave it empty. |
| 18 | +
|
| 19 | +then start the demo with: advanced.demo(cnx) |
| 20 | +__________________________________________________________________ |
| 21 | +""" |
| 22 | + |
| 23 | +frompgimportDB |
| 24 | +importsys |
| 25 | + |
| 26 | +# waits for a key |
| 27 | +defwait_key(): |
| 28 | +print"Press <enter>" |
| 29 | +sys.stdin.read(1) |
| 30 | + |
| 31 | +# inheritance features |
| 32 | +definherit_demo(pgcnx): |
| 33 | +print"-----------------------------" |
| 34 | +print"-- Inheritance:" |
| 35 | +print"-- a table can inherit from zero or more tables. A query" |
| 36 | +print"-- can reference either all rows of a table or all rows " |
| 37 | +print"-- of a table plus all of its descendants." |
| 38 | +print"-----------------------------" |
| 39 | +print |
| 40 | +print"-- For example, the capitals table inherits from cities table." |
| 41 | +print"-- (It inherits all data fields from cities.)" |
| 42 | +print |
| 43 | +print"CREATE TABLE cities (" |
| 44 | +print" name text," |
| 45 | +print" population float8," |
| 46 | +print" altitude int" |
| 47 | +print")" |
| 48 | +print |
| 49 | +print"CREATE TABLE capitals (" |
| 50 | +print" state varchar(2)" |
| 51 | +print") INHERITS (cities)" |
| 52 | +pgcnx.query("""CREATE TABLE cities ( |
| 53 | + name text, |
| 54 | + population float8, |
| 55 | + altitude int)""") |
| 56 | +pgcnx.query("""CREATE TABLE capitals ( |
| 57 | + state varchar(2)) INHERITS (cities)""") |
| 58 | +wait_key() |
| 59 | +print |
| 60 | +print"-- now, let's populate the tables" |
| 61 | +print |
| 62 | +print"INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)" |
| 63 | +print"INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)" |
| 64 | +print"INSERT INTO cities VALUES ('Mariposa', 1200, 1953)" |
| 65 | +print |
| 66 | +print"INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')" |
| 67 | +print"INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')" |
| 68 | +print |
| 69 | +pgcnx.query("INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)") |
| 70 | +pgcnx.query("INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)") |
| 71 | +pgcnx.query("INSERT INTO cities VALUES ('Mariposa', 1200, 1953)") |
| 72 | +pgcnx.query("INSERT INTO capitals VALUES ('Sacramento',3.694E+5,30,'CA')") |
| 73 | +pgcnx.query("INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')") |
| 74 | +print |
| 75 | +print"SELECT * FROM cities" |
| 76 | +printpgcnx.query("SELECT * FROM cities") |
| 77 | +print"SELECT * FROM capitals" |
| 78 | +printpgcnx.query("SELECT * FROM capitals") |
| 79 | +print |
| 80 | +print"-- like before, a regular query references rows of the base" |
| 81 | +print"-- table only" |
| 82 | +print |
| 83 | +print"SELECT name, altitude" |
| 84 | +print"FROM cities" |
| 85 | +print"WHERE altitude > 500;" |
| 86 | +printpgcnx.query("""SELECT name, altitude |
| 87 | + FROM cities |
| 88 | + WHERE altitude > 500""") |
| 89 | +print |
| 90 | +print"-- on the other hand, you can find all cities, including " |
| 91 | +print"-- capitals, that are located at an altitude of 500 'ft " |
| 92 | +print"-- or higher by:" |
| 93 | +print |
| 94 | +print"SELECT c.name, c.altitude" |
| 95 | +print"FROM cities* c" |
| 96 | +print"WHERE c.altitude > 500" |
| 97 | +printpgcnx.query("""SELECT c.name, c.altitude |
| 98 | + FROM cities* c |
| 99 | + WHERE c.altitude > 500""") |
| 100 | + |
| 101 | +# arrays attributes |
| 102 | +defarray_demo(pgcnx): |
| 103 | +print"----------------------" |
| 104 | +print"-- Arrays:" |
| 105 | +print"-- attributes can be arrays of base types or user-defined " |
| 106 | +print"-- types" |
| 107 | +print"----------------------" |
| 108 | +print |
| 109 | +print"CREATE TABLE sal_emp (" |
| 110 | +print" name text," |
| 111 | +print" pay_by_quarter int4[]," |
| 112 | +print" schedule text[][]" |
| 113 | +print")" |
| 114 | +pgcnx.query("""CREATE TABLE sal_emp ( |
| 115 | + name text, |
| 116 | + pay_by_quarter int4[], |
| 117 | + schedule text[][])""") |
| 118 | +wait_key() |
| 119 | +print |
| 120 | +print"-- insert instances with array attributes. " |
| 121 | +print" Note the use of braces" |
| 122 | +print |
| 123 | +print"INSERT INTO sal_emp VALUES (" |
| 124 | +print" 'Bill'," |
| 125 | +print" '{10000,10000,10000,10000}'," |
| 126 | +print" '{{\"meeting\",\"lunch\"}, {}}')" |
| 127 | +print |
| 128 | +print"INSERT INTO sal_emp VALUES (" |
| 129 | +print" 'Carol'," |
| 130 | +print" '{20000,25000,25000,25000}'," |
| 131 | +print" '{{\"talk\",\"consult\"}, {\"meeting\"}}')" |
| 132 | +print |
| 133 | +pgcnx.query("""INSERT INTO sal_emp VALUES ( |
| 134 | + 'Bill', '{10000,10000,10000,10000}', |
| 135 | + '{{\"meeting\",\"lunch\"}, {}}')""") |
| 136 | +pgcnx.query("""INSERT INTO sal_emp VALUES ( |
| 137 | + 'Carol', '{20000,25000,25000,25000}', |
| 138 | + '{{\"talk\",\"consult\"}, {\"meeting\"}}')""") |
| 139 | +wait_key() |
| 140 | +print |
| 141 | +print"----------------------" |
| 142 | +print"-- queries on array attributes" |
| 143 | +print"----------------------" |
| 144 | +print |
| 145 | +print"SELECT name FROM sal_emp WHERE" |
| 146 | +print" sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]" |
| 147 | +print |
| 148 | +printpgcnx.query("""SELECT name FROM sal_emp WHERE |
| 149 | + sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]""") |
| 150 | +print |
| 151 | +print"-- retrieve third quarter pay of all employees" |
| 152 | +print |
| 153 | +print"SELECT sal_emp.pay_by_quarter[3] FROM sal_emp" |
| 154 | +print |
| 155 | +printpgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp") |
| 156 | +print |
| 157 | +print"-- select subarrays" |
| 158 | +print |
| 159 | +print"SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE" |
| 160 | +print" sal_emp.name = 'Bill'" |
| 161 | +printpgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \ |
| 162 | +"sal_emp.name = 'Bill'") |
| 163 | + |
| 164 | +# base cleanup |
| 165 | +defdemo_cleanup(pgcnx): |
| 166 | +print"-- clean up (you must remove the children first)" |
| 167 | +print"DROP TABLE sal_emp" |
| 168 | +print"DROP TABLE capitals" |
| 169 | +print"DROP TABLE cities;" |
| 170 | +pgcnx.query("DROP TABLE sal_emp") |
| 171 | +pgcnx.query("DROP TABLE capitals") |
| 172 | +pgcnx.query("DROP TABLE cities") |
| 173 | + |
| 174 | +# main demo function |
| 175 | +defdemo(pgcnx): |
| 176 | +inherit_demo(pgcnx) |
| 177 | +array_demo(pgcnx) |
| 178 | +demo_cleanup(pgcnx) |