diff options
Diffstat (limited to 'src/interfaces/python/tutorial/advanced.py')
| -rwxr-xr-x | src/interfaces/python/tutorial/advanced.py | 198 |
1 files changed, 0 insertions, 198 deletions
diff --git a/src/interfaces/python/tutorial/advanced.py b/src/interfaces/python/tutorial/advanced.py deleted file mode 100755 index 41a5bc457a3..00000000000 --- a/src/interfaces/python/tutorial/advanced.py +++ /dev/null @@ -1,198 +0,0 @@ -#! /usr/bin/env python -# advanced.py - demo of advanced features of PostGres. Some may not be ANSI. -# inspired from the Postgres tutorial -# adapted to Python 1995 by Pascal Andre - -print """ -__________________________________________________________________ -MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL - -This module is designed for being imported from python prompt - -In order to run the samples included here, first create a connection -using : cnx = advanced.DB(...) - -The "..." should be replaced with whatever arguments you need to open an -existing database. Usually all you need is the name of the database and, -in fact, if it is the same as your login name, you can leave it empty. - -then start the demo with: advanced.demo(cnx) -__________________________________________________________________ -""" - -from pg import DB -import sys - -# waits for a key -def wait_key(): - print "Press <enter>" - sys.stdin.read(1) - -# inheritance features -def inherit_demo(pgcnx): - print "-----------------------------" - print "-- Inheritance:" - print "-- a table can inherit from zero or more tables. A query" - print "-- can reference either all rows of a table or all rows " - print "-- of a table plus all of its descendants." - print "-----------------------------" - print - print "-- For example, the capitals table inherits from cities table." - print "-- (It inherits all data fields from cities.)" - print - print "CREATE TABLE cities (" - print " name text," - print " population float8," - print " altitude int" - print ")" - print - print "CREATE TABLE capitals (" - print " state varchar(2)" - print ") INHERITS (cities)" - pgcnx.query("""CREATE TABLE cities ( - name text, - population float8, - altitude int)""") - pgcnx.query("""CREATE TABLE capitals ( - state varchar(2)) INHERITS (cities)""") - wait_key() - print - print "-- now, let's populate the tables" - print - print "INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)" - print "INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)" - print "INSERT INTO cities VALUES ('Mariposa', 1200, 1953)" - print - print "INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')" - print "INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')" - print - pgcnx.query("INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)") - pgcnx.query("INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)") - pgcnx.query("INSERT INTO cities VALUES ('Mariposa', 1200, 1953)") - pgcnx.query("INSERT INTO capitals VALUES ('Sacramento',3.694E+5,30,'CA')") - pgcnx.query("INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')") - print - print "SELECT * FROM cities" - print pgcnx.query("SELECT * FROM cities") - print "SELECT * FROM capitals" - print pgcnx.query("SELECT * FROM capitals") - print - print "-- like before, a regular query references rows of the base" - print "-- table only" - print - print "SELECT name, altitude" - print "FROM cities" - print "WHERE altitude > 500;" - print pgcnx.query("""SELECT name, altitude - FROM cities - WHERE altitude > 500""") - print - print "-- on the other hand, you can find all cities, including " - print "-- capitals, that are located at an altitude of 500 'ft " - print "-- or higher by:" - print - print "SELECT c.name, c.altitude" - print "FROM cities* c" - print "WHERE c.altitude > 500" - print pgcnx.query("""SELECT c.name, c.altitude - FROM cities* c - WHERE c.altitude > 500""") - -# arrays attributes -def array_demo(pgcnx): - print "----------------------" - print "-- Arrays:" - print "-- attributes can be arrays of base types or user-defined " - print "-- types" - print "----------------------" - print - print "CREATE TABLE sal_emp (" - print " name text," - print " pay_by_quarter int4[]," - print " pay_by_extra_quarter int8[]," - print " schedule text[][]" - print ")" - pgcnx.query("""CREATE TABLE sal_emp ( - name text, - pay_by_quarter int4[], - pay_by_extra_quarter int8[], - schedule text[][])""") - wait_key() - print - print "-- insert instances with array attributes. " - print " Note the use of braces" - print - print "INSERT INTO sal_emp VALUES (" - print " 'Bill'," - print " '{10000,10000,10000,10000}'," - print " '{9223372036854775800,9223372036854775800,9223372036854775800}'," - print " '{{\"meeting\", \"lunch\"}, {}}')" - print - print "INSERT INTO sal_emp VALUES (" - print " 'Carol'," - print " '{20000,25000,25000,25000}'," - print " '{9223372036854775807,9223372036854775807,9223372036854775807}'," - print " '{{\"talk\", \"consult\"}, {\"meeting\"}}')" - print - pgcnx.query("""INSERT INTO sal_emp VALUES ( - 'Bill', '{10000,10000,10000,10000}', - '{9223372036854775800,9223372036854775800,9223372036854775800}', - '{{\"meeting\", \"lunch\"}, {}}')""") - pgcnx.query("""INSERT INTO sal_emp VALUES ( - 'Carol', '{20000,25000,25000,25000}', - '{9223372036854775807,9223372036854775807,9223372036854775807}', - '{{\"talk\", \"consult\"}, {\"meeting\"}}')""") - wait_key() - print - print "----------------------" - print "-- queries on array attributes" - print "----------------------" - print - print "SELECT name FROM sal_emp WHERE" - print " sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]" - print - print pgcnx.query("""SELECT name FROM sal_emp WHERE - sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]""") - print - print pgcnx.query("""SELECT name FROM sal_emp WHERE - sal_emp.pay_by_extra_quarter[1] <> sal_emp.pay_by_extra_quarter[2]""") - print - print "-- retrieve third quarter pay of all employees" - print - print "SELECT sal_emp.pay_by_quarter[3] FROM sal_emp" - print - print pgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp") - print - print "-- retrieve third quarter extra pay of all employees" - print - print "SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp" - print pgcnx.query("SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp") - print - print "-- retrieve first two quarters of extra quarter pay of all employees" - print - print "SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp" - print - print pgcnx.query("SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp") - print - print "-- select subarrays" - print - print "SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE" - print " sal_emp.name = 'Bill'" - print pgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \ - "sal_emp.name = 'Bill'") - -# base cleanup -def demo_cleanup(pgcnx): - print "-- clean up (you must remove the children first)" - print "DROP TABLE sal_emp" - print "DROP TABLE capitals" - print "DROP TABLE cities;" - pgcnx.query("DROP TABLE sal_emp") - pgcnx.query("DROP TABLE capitals") - pgcnx.query("DROP TABLE cities") - -# main demo function -def demo(pgcnx): - inherit_demo(pgcnx) - array_demo(pgcnx) - demo_cleanup(pgcnx) |