Skip to content

Commit 439643a

Browse files
bmomjianbmomjian
committed
Add missing python files.
1 parent f36e7ff commit 439643a

File tree

4 files changed

+826
-0
lines changed

4 files changed

+826
-0
lines changed

‎src/interfaces/python/tutorial/advanced.py

Lines changed: 178 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,178 @@
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+
from pg import DB
24+
import sys
25+
26+
# waits for a key
27+
def wait_key():
28+
print "Press <enter>"
29+
sys.stdin.read(1)
30+
31+
# inheritance features
32+
def inherit_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+
print pgcnx.query("SELECT * FROM cities")
77+
print "SELECT * FROM capitals"
78+
print pgcnx.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+
print pgcnx.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+
print pgcnx.query("""SELECT c.name, c.altitude
98+
FROM cities* c
99+
WHERE c.altitude > 500""")
100+
101+
# arrays attributes
102+
def array_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+
print pgcnx.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+
print pgcnx.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+
print pgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \
162+
"sal_emp.name = 'Bill'")
163+
164+
# base cleanup
165+
def demo_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+
def demo(pgcnx):
176+
inherit_demo(pgcnx)
177+
array_demo(pgcnx)
178+
demo_cleanup(pgcnx)

0 commit comments

Comments
 (0)