• Week 05 Thursday
• Assignment FAQs
• Keep in mind for Assignment 2
• Programming with Databases
• Python in 10 mins
• Command-line Args
• Executable Scripts
• Psycopg2
• Connecting to Databases
• Operations on cursors
• Aside: SQL Injection
• Example: connecting to a database
• Exercise: Queries on Uni database

❖ Week 05 Thursday

In today's lectures ...

• PL↔DB interface,   Python/psycopg2

Things to do ...

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

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

❖ Assignment FAQs

You can define as many views and functions as you like

• but must preserve the Q1 ... Q12 interfaces

Don't assume that names are unique

• Beers.name  is not unique in the current database
• Breweries.name  is not unique in real life
• Ingredients.name  is most likely unique

Do not include the  Locations  constraint in  ass1.sql

❖ Keep in mind for Assignment 2

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)

• db2  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

❖ Programming with Databases (cont)

Example: find info about all marks for all students

query1 = "select id,name from Student order by id";
res1 = dbQuery(db, query1);
while (tuple1 = dbNext(res1)) {
    query2 = "select course,mark from Marks"
             + " where student = " + tuple1['id'];
    res2 = dbQuery(db,query2);
    while (tuple2 = dbNext(res2)) {
        --  process student/course/mark info
    }
}

E.g. 10000 students, each with 8 marks, ⇒ run 10001 queries

Cost = 10001 queries + 80000 tuple fetches

❖ Programming with Databases (cont)

Should be implemented as:

query = "select id,name,course,mark"
        + " from Student s join Marks m "
        + " on (s.id=m.student)"
        + " order by s.id"
results = dbQuery(db, query);
while (tuple = dbNext(results)) {
    --  process student/course/mark info
}

We invoke 1 query, and transfer same number of tuples.

Cost = 1 query + 80000 tuple fetches

❖ 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 =, like C.

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

❖ Python in 10 mins (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

❖ Python in 10 mins (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 just a sequence of statements

❖ Python in 10 mins (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

❖ 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)

❖ Executable Scripts

Can always run script  s.py  as

$ python3 s.py args...

To make  s.py  like an executable command

#!/usr/bin/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 (cont)

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

❖ Operations on cursors

cur.execute(SQL_statement, 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})

❖ 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>

❖ Exercise: Queries on Uni database

Consider the  uni  database

• 43 tables, e.g.  Students,  Courses
• ~5000 students, ~2600 course, ~67K enrolments

Some of the tables ...

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

Students(id)

Subjects(id, code, name, longname, uoc, offeredby, ...)

Courses(id, subject, term, homepage)

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

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

❖ Exercise: Queries on Uni database (cont)

Write Python/psycopg2 scripts to

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