COMP3311 24T2 Week 08
Python/Psycopg2 		Database Systems

  1. What is the difference between a connection and a cursor in Psycopg2? How do you create each?

    Answer:

    A connection provides authenticated access to a database. You create a connection as follows 

    connection = psycopg2.connect(ConnectionParameters)

    Typical connection parameters are: dbname or database (required), user and password for authentication, and host and port to specify the "location" of the PostgreSQL server.

    Some examples: 

    conn = psycopg2.connect("dbname=mydb")
conn = psycopg2.connect(database="mydb")
conn = psycopg2.connect("user=jas password=abc123 dbname=mydb")
conn = psycopg2.connect("host=db.server.org port=5432 dbname=mydb")

    A cursor provides a pipeline between the Python program and the PostgreSQL database. You create a cursor as follows: 

    cursor = connection.cursor()

    Cursors can be used to send queries to the database and read back rsults as in: 

    cursor.execute("SQL Query")
results = cursor.fetchall()

    where results is a list of tuples.


  2. [Question courtesy of Clifford Sesel] The following Python script (in a executable file called opendb) aims to open a connection to a database whose name is specified on the command line: 

    1. #!/usr/bin/python3
    2. import sys
    3. import psycopg2
    4. if len(sys.argv) < 2:
    5.     print("Usage: opendb DBname")
    6.     exit(1)
    7. db = sys.argv[1]
    8. try:
    9.     conn = psycopg2.connect(f"dbname={db}")
    10.     print(conn)
    11.     cur = conn.cursor()
    12. except psycopg2.Error as err:
    13.     print("database error: ", err)
    14. finally:
    15.     if conn is not None:
    16.         conn.close()
    17.     print("finished with the database")
    18.  

    When invoked with an existing database, it behaves as follows 

    $ ./opendb beers2
<connection object at 0x7fac401799f0; dsn: 'dbname=beers2', closed: 0>
finished with the database

    but when invoked with a non-existent database it produces 

    $ ./opendb nonexistent
database error:  FATAL:  database "nonexistent" does not exist

Traceback (most recent call last):
  File "./opendb", line 16, in 
    if conn :
NameError: name 'conn' is not defined

    rather than 

    $ ./opendb nonexistent
database error:  FATAL:  database "nonexistent" does not exist

finished with the database

    What is the problem? And how can we fix it?

    Answer:

    The scope of the connobject does not extend into the finally clause if conn is only initialised in the try clause. This is essentially what the error message is telling you: 

    NameError: name 'conn' is not defined

    Ask the Python developers why variable/object scope works this way.

    To fix the problem, you need to initialise conn in the outer scope. You can't simply move the connect() call there, because it would then be outside the try clause and the exception would be handled by the standard Python exception handler rather than our exception handler, e.g. 

    Traceback (most recent call last):
  File "./opendb", line 9, in 
    conn = psycopg2.connect(f"dbname={db}")
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  database "xyzzyyyy" does not exist

    The solution is to initialise conn outside the try clause: 

    db = sys.argv[1]
conn = None
try:
   ...

The following questions make use of the uni database used in lectures. A database dump for the uni database can be found in: 

/home/cs3311/web/23T1/lectures/week07-tuesday/exercises/uni.dump

The following gives enough of the schema to answer the questions below: 

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)
OrgUnit_Types(id, name)

  1. Using the uni database, write a Python/Psycopg2 script called courses-studied that prints the subjects that a specified student studied in a given term. There is no need to check whether the student was enrolled in the given term; simply print that they studied no courses in that term. Examples of use: 

    $ ./courses-studied
Usage: ./courses-studied studentID term

$ ./courses-studied 1234567 18s1
No such student

$ ./courses-studied 9300035 20T1
No such student

$ ./courses-studied 5137295 17s2
COMP1521 Computer Systems Fundamentals
COMP1531 Software Eng Fundamentals
MATH1231 Mathematics 1B

$ ./courses-studied 5137295 20T1
COMP3231 Operating Systems
SENG3011 Software Eng Workshop 3

    Answer:

    Possible script for courses-studied: 

    1. #!/usr/bin/python3
    2. import sys
    3. import psycopg2
    4. if len(sys.argv) < 3:
    5.     print("Usage:",sys.argv[0],"sudentID term")
    6.     exit(1)
    7. student = sys.argv[1]
    8. stuName = None
    9. term = sys.argv[2]
    10. conn = None
    11. stuqry = """
    12. select p.fullname
    13. from   People p join Students s on p.id=s.id
    14. where  p.id = %s
    15. """
    16. query = """
    17. select s.code, s.name
    18. from   Subjects s
    19.        join Courses c on (c.subject=s.id)
    20.        join Terms t on (c.term=t.id)
    21.        join Course_enrolments e on (e.course=c.id)
    22.        join People p on (e.student=p.id)
    23. where  p.id = %s and t.code = %s
    24. order  by s.code
    25. """
    26. try:
    27.    conn = psycopg2.connect("dbname=uni")
    28.    cur = conn.cursor()
    29.    cur.execute(stuqry,[student])
    30.    stuName = cur.fetchone()
    31.    if not stuName:
    32.       print("No such student")
    33.       exit(0)
    34.    cur.execute(query, [student,term])
    35.    courses = cur.fetchall()
    36.    if len(courses) == 0:
    37.       print("No courses")
    38.       exit(0)
    39.    for subj in courses:
    40.       print(subj[0],subj[1])
    41. except psycopg2.Error as err:
    42.    print("database error: ", err)
    43. finally:
    44.    if conn :
    45.       conn.close()
    46.  

  2. Using the uni database, write a Python/Psycopg2 script called course-roll that takes two command-line arguments (subject code and term code) and produces a list of students enrolled in that course. Each line should contain: studentID, name. The name should appear as "familyName, givenNames" and the list should be ordered by familyName then by givenName. e.g.

    Some examples of use: 

    $ ./course-roll COMP1521
Usage: course-roll subject term

$ ./course-roll COMP1511 16s1
COMP1511 16s1
No students

$ ./course-roll COMP1151 16s1
COMP1151 17s1
No students

$ ./course-roll PSYC1022 19T0
PSYC1022 19T0
5154325 Georgas, Jose
5141408 Hordern, Priyanshi
5134420 Hulst, Stan
5159982 Madanimelak, Siavash
5163692 Watson, Carine

$ ./course-roll COMP3211 20T1
COMP3211 20T1
5167571 Ahmad Bakir, Nabilah
5160470 Bashir, Zeeshan
5132438 Bown, Yelgun
5147435 Chen, Manjiang
5137415 Hang, Zheren
5134504 Hiwilla, Libin
5182987 Krueger, Isabel
5149728 Lim, Sang
5160734 Maslin, Jocelyn
5160049 McTigue, Courtney
5159809 Murdani, Elsan
5171168 Nadol, Rowan
5169891 Niu, Zihui
5166832 Pong, Chi-Kuang
5161703 Schachat, Kalie
5215073 Shang, Chan
5173714 Strickland, Vanya
5129717 Styer, Sharon
5208366 Suo, Junjian
5178112 Tandan, Ray
5164849 Thattai, Kajalben
5168959 Virdi, Jawad

    Answer:

    Possible script for course-roll: 

    1. #!/usr/bin/python3
    2. import sys
    3. import psycopg2
    4. if len(sys.argv) < 3:
    5.     print("Usage:",sys.argv[0],"subject term")
    6.     exit(1)
    7. subject = sys.argv[1]
    8. term = sys.argv[2]
    9. conn = None
    10. query = """
    11. select p.id, p.family, p.given
    12. from   Subjects s
    13.        join Courses c on (c.subject=s.id)
    14.        join Terms t on (c.term=t.id)
    15.        join Course_enrolments e on (e.course=c.id)
    16.        join People p on (e.student=p.id)
    17. where  s.code = %s and t.code = %s
    18. order  by p.family, p.given
    19. """
    20. try:
    21.    conn = psycopg2.connect("dbname=uni")
    22.    cur = conn.cursor()
    23.    cur.execute(query, [subject,term])
    24.    students = cur.fetchall()
    25.    print(subject,term)
    26.    if len(students) == 0:
    27.       print("No students")
    28.       exit(0)
    29.    for stu in students:
    30.       print(stu[0],stu[1]+",",stu[2])
    31. except psycopg2.Error as err:
    32.    print("database error: ", err)
    33. finally:
    34.    if conn :
    35.       conn.close()
    36.  
    37.  


  3. The script in the previous question was a little bit sloppy in its error detection, and the messages did not distinguish between a real course with no students and a bad value for either the subject code or the term code, or a valid pair of codes for which there was no course offering. The script should also now print the long name of the subject. Improve the script so it behaves as follows: 

    $ ./course-roll1 COMP1151 18s1
Invalid subject COMP1151

$ ./course-roll1 COMP1511 88xx
Invalid term 88xx

$ ./course-roll1 COMP3211 20T2
No offering: COMP3211 20T2

$ ./course-roll1 MATH2601 20T2
MATH2601 20T2 Higher Linear Algebra
5194739 Boddam-Whetham, Billi
5160543 Chao, Sixiang
5188339 Ghebrial, Christina
5220431 Lopez Castro, Feyza
5196254 McWilliam, Lianne

$ ./course-roll1 COMP3821 17s1
COMP3821 17s1 Extended Algorithms and Programming Techniques
5143563 Aurik, Rumana
5128387 Barsoum, Rona
5128243 Bartlett, Gregg
5133001 Black, Samim
5126171 Demiri, Florida
5128296 Donney, Lisita
5133189 Essam, Swisszanah
5135130 Ghislain, Thien-Ngan
5143373 Howard, Alanna
5141297 MAI, Tiancong
5132763 MAI, Xueqi
5143439 Manolios Reichert, Janette
5141860 Matsuo, Ryann
5125264 Mock, Na-Ima
5128275 Mohd Hassan, Xiao
5147571 Morsalin, Amir
5129681 Neo, Fenzhi
5128164 Park, Toshiki
5142705 Rabelo Castello, Tamara
5142618 Saldanha, Sai
5143532 Sethuramasamy, Tanvi
5141585 Twine, Hughie
5142688 Wenzel, Sohaib
5145043 Wijeratne, Joyce
5127997 Zhen, Kaijian

    Answer:

    Possible script for course-roll1:

    Can't find code file code/class-roll1

  4. Using the uni database, write a Python/Psycopg2 script called nsubjects that takes the name (or part of the name) of a School and prints the number of subjects offered by that school. Use the command-line argument as a pattern, and do case-insensitive matching. If more than one school matches the pattern, indicate that multiple schools match.

    Examples of usage: 

    $ python3 nsubjects 'Computer Science'
School of Computer Science and Engineering teaches 138 subjects

$ python3 nsubjects 'Chem'
Multiple schools match:
School of Chemical Engineering
School of Chemistry

$ python3 nsubjects 'Chemistry'
School of Chemistry teaches 11 subjects

$ python3 nsubjects 'Math'
School of Mathematics & Statistics teaches 85 subjects

    Answer:

    Possible script for nsubjects: 

    1. #!/usr/bin/python3
    2. import sys
    3. import psycopg2
    4. if len(sys.argv) < 2:
    5.     print("Usage:",sys.argv[0],"SchoolName")
    6.     exit(1)
    7. schoolName = sys.argv[1]
    8. conn = None
    9. schoolq = """
    10. select id,longname from Orgunits where longname ~* %s
    11. """
    12. ncoursesq = """
    13. select count(distinct s.code)
    14. from   Subjects s
    15.        join Courses c on (c.subject = s.id)
    16.        join Orgunits u on (s.offeredby = u.id)
    17. where  u.id = %s
    18. """
    19. try:
    20.    conn = psycopg2.connect("dbname=uni")
    21.    cur = conn.cursor()
    22.    cur.execute(schoolq, [schoolName])
    23.    schools = cur.fetchall()
    24.    if len(schools) == 0:
    25.       print("No such school")
    26.    elif len(schools) > 1:
    27.       print("Multiple schools match:")
    28.       for school in schools:
    29.          print(school[1])
    30.    else:
    31.       school = schools[0]
    32.       cur.execute(ncoursesq, [school[0]])
    33.       nc = cur.fetchone()
    34.       print(f"{school[1]} teaches {nc[0]} subjects")
    35. except psycopg2.Error as err:
    36.    print("database error: ", err)
    37. finally:
    38.    if conn :
    39.  
    40.       conn.close()
    41.