• Week 05 Wednesday
• Assignment FAQs
• Keep in mind for Assignments
• Programming with Databases
• Python in 10 mins
• Formatted Printing
• Python Interpreter
• Command-line Args
• Exercise: Simple Python Scripts
• Executable Scripts
• Psycopg2
• Psycopg2 in 10 mins
• Connecting to Databases
• Example: connecting to a database
• Operations on cursors
• Aside: SQL Injection
• Example: University Database
• Exercise: Scripts on Uni database

❖ Week 05 Wednesday

In today's lectures ...

• PL↔DB interface,   Python/psycopg2

Things to do ...

• Assignment 1 due before Friday 11:59pm
  (150 submissions so far; 450 more to go)
• Check your assignment on  vxdb2  before submitting
• Make sure it loads with no ERRORs using the commands:

  dropdb temp;  createdb temp;  psql temp -f .../ass1.dump
  psql temp -f ass1.sql    # your submission
  

❖ Assignment FAQs

You can define as many views and functions as you like

• but must preserve the Q1 ... Q9 interfaces in spec

Don't assume that names are unique

• Beers.name  is not unique in ass1.dump
• Breweries.name  is not unique in real life
• Ingredients.name  is unique in ass1.dump

Beers are distinguished by Beers.bid, not Beers.name

Think of some test cases of your own, and cross check

❖ Keep in mind for Assignments

If 100's of people are finishing the assignment last minute ...

• we get bombarded with 100's of emails, forum posts, etc.

  (it takes much longer to get your question answered)

• vxdb2  has 100's of PG servers running simultaneously

  (it runs s-l-o-w; queries take much longer to run)

Also, beware that  /localstorage  is not backed up ... put only your DB there

❖ Programming with Databases

Programming Language / DBMS archtecture:

❖ Programming with Databases (cont)

Consider this (imaginary) PL/DBMS access method:

--  establish connection to DBMS
db = dbAccess("DB");
query = "select a,b from R,S where ... ";
--  invoke query and get handle to result set
results = dbQuery(db, query);
--  for each tuple in result set
while (tuple = dbNext(results)) {
    --  process next tuple
    process(tuple['a'], tuple['b']);
}

Estimated costs:  dbAccess = 500ms,  dbQuery = 200ms,  dbNext < 1ms

❖ Python in 10 mins

Python identifiers and constants similar to C

12  3.141  'abc'  "it's fun"  "one line\n"

Data structures

• list:   a = [5,4,3,2,1],  a[0] == 5,  a[4] == 1
• tuple:   t = (5, "abc", 2.17),  t[1] == "abc",  x,y,z = t
• dictionary:   d = { "adam":65, "john":50 },  d["john"] == 50

Operators

and  or  not  +  -  /  *  **  ==  <  >  <=  

❖ Python in 10 mins (cont)

Keywords

None  def  if  elif  else  for  in  while  try  except

Assignment is = ... syntax like C, semantics slightly different

Variables are defined/typed by first assignment

The print function can print multiple objects

print( obj1, obj1, ... , sep=' ', end='\n', ...)

Python can convert most objects to a string representation

❖ Formatted Printing

print(a,b,c)

• converts each of a, b, c into a string
• concatenates strings, space-separated, printed on a line

print(f"string containing interpolated values")

• converts each value to a string, and inserts into result string

Examples:

>>> x = 42
>>> print(f"value of x = {x}")
value of x = 42
>>> print(f"value of x = |{x:5d}|")
value of x = |   42|
>>> print(f"value of x = |{x:<5d}|")
value of x = |42   |
>>> print(f"value of x = |{x:5.1f}|")
value of x = | 42.0|

❖ Formatted Printing (cont)

Python uses indentation to group statements

a = someValue
if a > 5:
   print("greater than 5")
else:
   print("at most 5")
i = 0
while i < a:
   print(i)
   i = i + 1

and : introduces a new statement group

Does not require semi-colon to complete/separate statements

❖ Formatted Printing (cont)

E.g. iteration over a list:

sum = 0
for x in [1,2,3,4,5,6,7]:
   sum = sum + x
print(sum)

E.g. defining a function:

def fac(n):
  f = 1
  i = 1
  while i <= n:
    f = f * i
    i = i + 1
  return f

❖ Formatted Printing (cont)

Python has an object oriented flavour:

Object.Method(args)   or   Module.Method(args)

Some functions are global, e.g.

len([6,4,7,5])  →  4
len("abcdefg")  →  7
print('Hello, world')

Some functions need to be imported from a module

import sys
import psycopg2

Python scripts are a sequence of statements and function definitions

❖ Python Interpreter

Python has an interactive shell (like psql)

$ python3
Python 3.9.1 (default, Mar  3 2021, 10:01:06) 
[GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> 1 + 2
3
>>> print("Hello, world")
Hello, world
>>> "Hello, world"
'Hello, world'
>>> Hello, world
Traceback (most recent call last):
  File "", line 1, in 
NameError: name 'Hello' is not defined
>>> quit
quit
Use quit() or Ctrl-D (i.e. EOF) to exit
>>> quit()

❖ Command-line Args

In C, we have  main(int argc, char *argv[])

In Python, we have  sys.argv   (a Python list of strings)

To access command-line args

import sys

argc = len(sys.argv)

# access via index
print(sys.argv[0])

# access via iteration
for arg in sys.argv:
   print(arg)

❖ Exercise: Simple Python Scripts

Write Python scripts to

• add two numbers supplied on command line

  $ python3 add.py 27 42
  69
  

• behave like the Unix echo command

  $ python3 echo.py abc 1 2 3
  abc 1 2 3
  

• compute factorial for number on command-line

  $ python3 fac.py 5
  120
  

❖ Executable Scripts

Can run Python scripts e.g.  s.py  via interpreter

$ python3 s.py args...

To make  s.py  like an executable command

#!/usr/bin/env python3

code for script

And then

$ chmod 755 s.py
$ ./s.py args...

❖ Psycopg2

Psycopg2 is a library for Python ↔ PostgreSQL interaction.

Where psycopg2 fits in the PL/DB architecture

❖ Psycopg2 in 10 mins

Summary of Psycopg2 functions:

• conn = psycopg2.connect(DB_connection_string)
• conn.close()
• conn.commit()
• cur = conn.cursor()
• cur.execute(SQL_statement, Values)
• cur.mogrify(SQL_statement, Values)
• list = cur.fetchall()
• tup = cur.fetchone()
• list = cur.fetchmany(nTuples)

❖ Connecting to Databases

conn = psycopg2.connect("dbname=dbName")

• attempts to connect to database called dbName
• if succesful, conn holds a handle for db access
• if unsuccesful, conn has the value None

Most common action after connecting: make a cursor

cur = conn.cursor()

Use cursor to interact with database

❖ Example: connecting to a database

Simple script that connects and then closes connection

import psycopg2

try:
   conn = psycopg2.connect("dbname=mydb")
   print(conn)  # state of connection after opening
   conn.close()
   print(conn)  # state of connection after closing
except Exception as e:
   print("Unable to connect to the database")

which, if mydb is accessible, produces output:

$ python3 ex1.py
<connection object at 0xf67186ec; dsn: 'dbname=mydb', closed: 0>
<connection object at 0xf67186ec; dsn: 'dbname=mydb', closed: 1>

❖ Operations on cursors

cur.execute(SQL_statement_template, Values)

• if supplied, insert values into the SQL statement
• then execute the modified SQL statement

Examples:

# run a fixed query
cur.execute("select * from R where x = 1");

# run a query with values inserted
cur.execute("select * from R where x = %s", (1,))
cur.execute("select * from R where x = %s", [1])

# run a query stored in a variable
query = "select * from Students where name ilike %s"
pattern = "%mith%"
cur.execute(query, [pattern])

❖ Aside: SQL Injection

If using values from an external source

• be careful how they're inserted into an SQL query

The following are dangerous

qry = f"select * from R where x = {value}"
qry = "select * from R where x = " + value
qry = "select * from R where x = '%s'" % value

Need to use something like the following:

qry = "select * from R where x = %s"
cur.execute(qry, [value])
or even
qry = "select * from R where x = %(val)s"
cur.execute(qry, {'val':value})

❖ Aside: SQL Injection (cont)

Typical database access pattern in Psycopg2

qry = 'select a,b,c from R where d = %s'
try:
   db = psycopg2.connect('dbname=mydb')
   cur = db.cursor()
   cur.execute(qry, [6])
   for tup in cur.fetchall():
      x,y,z = tup  # extract a,b,c values into x,y,z
      ... do something with x, y, z ...

except Exception as err:
  print("DB error: ", err)

finally:
  if db:
    db.close()

❖ Example: University Database

Consider the  uni  database ...

People(id, family, given, fullname, birthday, origin)
Students(id)

Subjects(id, code, name, uoc, offeredby, ...)
Courses(id, subject, term, homepage)
Streams(id, code, name, offeredby, stype, ...)
Programs(id, code, name, uoc, offeredby, ...)

Terms(id, year, ttype, code, name, starting, ending)

Course_enrolments(student, course, mark, grade)
Stream_enrolments(part_of_prog_enr, stream)
Program_enrolments(id, student, term, program, ...))

Orgunits(id, utype, name, longname, unswid)

~5000 students, ~2600 course, ~67K enrolments

❖ Exercise: Scripts on Uni database

Write Python/psycopg2 scripts to

1. get a list of subject codes+names
   
   
2. get a list of subjects offered in a given term
   
   
3. get a list of subjects whose codes match a pattern
   
   
4. display info about a subject given by a code
   
   
5. get a list of students whose name matches a pattern
   
   
6. display a transcript for a student, given a zID