Exemple d'application de base de données

ExempleModèle

1
CREATE TABLE philosopher (
2
surname TEXT PRIMARY KEY,
3
givenname TEXT,
4
century INTEGER);

ExempleVue

1
CREATE VIEW v_philosopher (name, century) AS
2
SELECT
3
  COALESCE(givenname,'') ||
4
  CASE WHEN givenname IS NOT NULL THEN ' ' ELSE '' END
5
  || surname,
6
  century || 'e' 
7
FROM philosopher;

ExempleContrôle (menu.py)

1
#!/usr/bin/python3
2
3
import psycopg2
4
import forms
5
import reports
6
7
HOST = "localhost"
8
USER = "me"
9
PASSWORD = "secret"
10
DATABASE = "mydb"
11
12
conn = psycopg2.connect("host=%s dbname=%s user=%s password=%s" % (HOST, DATABASE, USER, PASSWORD))
13
14
choice = '1'
15
while choice == '1' or choice == '2':
16
  print ("Pour ajouter un philosophe à la base, entrez 1")
17
  print ("Pour voir la liste des philosophes, entrez 2")
18
  print ("Pour sortir, entrez autre chose")
19
  choice = input()  
20
  if choice == '1':
21
    forms.addPhilo(conn)
22
  if choice == '2':
23
    reports.printPhilo(conn)
24
  print(choice)
25
26
conn.close()
ExempleFormulaire
1
#!/usr/bin/python3
2
3
def quote(s):
4
  if s:
5
    return '\'%s\'' % s
6
  else:
7
    return 'NULL'
8
9
def addPhilo(conn):
10
  surname = quote(input("Surname : "))
11
  givenname = quote(input("Given name : "))
12
  century = int(input("Century : "))
13
  # Connect, execute SQL, close
14
  cur = conn.cursor()
15
  sql = "INSERT INTO philosopher VALUES (%s, %s, %i)" % (surname, givenname, century)
16
  cur.execute(sql)
17
  conn.commit()
ExempleÉtat (report.py)
1
#!/usr/bin/python3
2
3
def printPhilo(conn):
4
  # Connect and retrieve data
5
  cur = conn.cursor()
6
  sql = "SELECT name, century FROM v_philosopher"
7
  cur.execute(sql)
8
  # Fetch data line by line
9
  raw = cur.fetchone()
10
  while raw:
11
    print ("- %s (%s)" % (raw[0], raw[1]))
12
    raw = cur.fetchone()