Helpful Resources

SQL

Here we have provided some SQL notation that you might find helpful. Depending on your solution, you may not need to use all of these:

· A case statement allow you to express a conditional expression (similar to if/else).

● You can have a select statement      with       hardcoded      values:

select     *

from(values   (1,'pie'),(2,'cake'),(3,'ice-cream'))as   desserts(id,   name);

● least and greatest can be used to find the minimum and maximum among a list of expressions respectively.

Date  and  Times  in  SQL

This assignment deals heavily with dates, times and timestamps. PostgreSQL offers an extensive set of functions and operators to work with these types. You can f ind

the full list here and we have included some of the notable ones below:

· You can extract the date portion of a time stamp by calling date.

csc343h-marinat=>select       date(tTime)From       Trip;

date

2023-05-03

(1 row)

· You  can  find  the  difference  between  two  dates.  The  returned  value  is  the number  of  days  (integer).

csc343h-marinat=>      select      '2023-05-04'::date      -'2023-05-03'::date      as      days;

days

1

(1 row)

· You can add an integer or an interval to a date.You can add an interval to a timestamp.

csc343h-marinat=>    select    '2023-05-04'::date    +    1    as    next  day;

next  day

2023-05-05

(1 row)

csc343h-marinat=>select     '2023-05-04'::date     +     interval'l     hour'     as     in  an  hour;

_   _

in  an  hour

_   _

2023-05-0401:00:00

(1  row)

csc343h-marinat=>select      '2023-05-0408:00'::timestamp      +      interval'1      day       1      hour’

csc343h-marinat->              as      tomorrow  in  an  hour;

_   _   _

tomorrow  in  an  hour

_   _   _

2023-05-0509:00:00

(1  row)

csc343h-marinat=>--    can't    add    an    integer    to    a    timestamp

csc343h-marinat=>select             '2023-05-0408:00'::timestamp             +5;

ERROR:    operator    does    not    exist:timestamp    without    time    zone    +    integer

LINE         1:select        '2023-05-0408:00'::timestamp        +5;

HINT:   No operator matches the given name and argument types. You might need to       add       explicit      type       casts.

· You can use the function overlap to check if two time periods overlap.

csc343h-marinat=>

csc343h-marinat->

csc343h-marinat->

overlaps

select('2023-04-0308:00'::timestamp,'2023-04-0413:00'::timestamp)

overlaps

('2023-04-0213:00’,’2023-04-0708:00');

t

(1  row)

● [NEW] You can make an interval using a given value for years, months, days, 钬②:

csc343h-marinat=>select                             make  interval(years=>2,months=>3,days=>2,hours=>5);

make  interval

2  years   3  mons  2   days   05:00:00

(1  row)

Psycopg2

psycopg2 is a Python library that provides an interface for manipulating and querying a PostgreSQL database.Psycopg22.9.3 is already installed on the   teaching lab machines.

T his section briefly covers the basic use case of the library. The documentation also offers a tutorial   that you  might find  helpful.

Making a connection

In order to issue commands to the database, you need to initialize a new database session. This can be done using the method connect that returns an instance of the  connection class, which allows you to interact with the database.

import   psycopg2   as   pg

connection     =     pg.connect(

dbname='csc343h-marinat',user='',password=’’,

options="-c search path=waste wrangler"

The     above     will     return     a     connection     to     the     database,     as     well     as     set     the     search  path     to

waste  wrangler.

Note you will need to change the value for dbname to your own personal

database: csc343h-(user  name>.If you are working on your own machine, the default values for dbname,user and password are postgres, postgres, password respectively.

At the end of your session, you need to close that connection:

connection.close()

It is good practice to ALWAYS close the connection to the database. Consequently, you should make use of try/except blocks to ensure that the connection is always

closed even if an error occured.You can see an example of that in the main block of a2.py.

Cursors

Once you have established a connection to the database, you can use cursors to issue commands to the database and examine results of queries.

You can create as many cursors as you want from a single connection. Here, I have created two:

cursorl       =     connection.cursor()

cursor2           =           connection.cursor()

You can use cursors to issue queries to the database:

>>>#      Get     all     trucks

>>>       cursor1.execute("select       *       from       Truck")

>>>#     rowcount     attributes     checks     the     number     of     rows     in     the     result

>>>           cursorl.rowcount

7

You can also use the cursor to inspect the result set of the issued query. You can do that by using one of the following:

· Using the fetchone method. This method will return the next row in the result

as a tuple, or None if the next row doesn钬撾exist.

>>> cursor1.execute("select * from Truck where tID = 1")

>>>             cursor1.fetchone()

(1,'A’,23.0)

>>>             cursor1.fetchone()     #    returns    None    since    there    are    no    more    rows

· Using a for loop to iterate over the result.At each iteration, the current row will be returned as a tuple.

>>>      cursor1.execute("select      *      from      Truck      where      tID      =      1")

>>>     for     row     in     cursorl:

print(row)

···

(1,'A',          23.0)

· Using fetchall that returns the entire result set as a list of tuples.

In the above examples, each row is returned as a tuple, where the item at the it h

index corresponds to the value of the ith attribute (column). Alternatively, each r ow

can be returned as a dictionary where the keys are the name of the columns, and

the values are the corresponding values of these columns as in this example .

Sanitizing user input

In most cases, you will be incorporating client钬楚input in your queries e.g., in a real application, the user will probably provide the value for the truck ID tID. It is

very important to sanitize your inputs to prevent against any SQL injection attacks. Luckily, psycopg2 provides a way for doing that:

· In your queries, you need to use the placeholder%s in place of where user input will be used. While this may look very similar to Python string

formatting, it is NOT the same. Also note that the placeholder %s is used in place of any value, irrespective of its type.

· In the call to execute, pass the values of these placeholders as a list, where their order in the list corresponds to their order of appearance in the SQL   command

tid    =    3

cursorl.execute("select         *         from         Truck         where         tID         =%s",[tid])

psycopg2 also provides a way to name your placeholders using the notation%

(name  of  placeholder)s. In that case, the second argument of execute is a dictionary that maps the name of each placeholder to its value.

tid    =    3

cursor1.execute("select         *        from        Truck        where        tID        =%(tid)s",    {'tid':tid})

Dealing with Exceptions

If an error occurs as a result of a SQL query/update, an Exception is raised. None of the cursors generated from this connection can be used, unless the connection is

rolled back. Rolling back the connection resets the state of the connection so it can accept more commands (more about that below).

>>>#      This      raises      an      Exception      because      there      is      no      Trucks      relation

>>>         cursor1.execute("select         *         from         Trucks")

Traceback(most           recent           call           last):

File           "<stdin>",line           1,in           <module>

psycopg2.errors.UndefinedTable:relation                   "trucks"does                   not                   exist

LINE    1:   select      *      from      Trucks

>>>#I     can't     use     the     cursor     without     rolling     back     the     connection

>>>      cursor1.execute("select      *      from      Truck")

Traceback(most        recent        call        last):

File        "<stdin>",line        1,in        <module>

psycopg2.errors.InFailedSqlTransaction:           current          transaction           is           aborted,

commands     ignored    until     end     of    transaction    block

>>>#    I    can't    even    use    the    other    cursor

>>>      cursor2.execute("select      *      from      Truck")

Traceback(most        recent        call        last):

File        "<stdin>",line        l,in        <module>

psycopg2.errors.InFailedSqlTransaction:current              transaction              is              aborted,

commands     ignored    until     end     of    transaction    block

>>>#    and   Not    even    a   new    cursor

>>>      cursor3      =      connection.cursor()

>>>      cursor3.execute("select      *      from      Truck")

Traceback(most        recent        call        last):

File        "<stdin>",line        1,in        <module>

psycopg2.errors.InFailedSqlTransaction:current              transaction              is              aborted,

commands     ignored    until     end     of    transaction    block

>>>#    Rolling    back    the    connection    allows    me    to    use    cursorl

>>>#(or     any     of    the     other     cursors)

>>>            connection.rollback()

>>>      cursor1.execute("select      *      from      Truck")

Committing to changes

Executing SQL DML commands is very similar to issuing queries:

1. you need to use the execute method of class cursor:

Similar to executing queries, if the values used in the command are o btained   from the user, you need to sanitize the input by making use of the second argument to

execute:

>>>     cursorl.execute("insert     into     Truck     values     (%s,%s,%s)",[tid,truck  type,capacity])

2.  Unlike queries, calling execute will  not actually change the database  instance i.e., if you simultaneously open psql, you won钬撾see the new row we have

just added.  In order to do that, you need to call the commit method of class

connection. Calling commit issues any uncommitted changes to the database i.e., once you call connection.commit(), the new row will be reflected in the database.

Commit     and     Rollback

You might have noticed that connection.commit()and connection. rollback() have exactly

the opposite functions from each other:

·   connection.commit():commits  any  uncommitted  changes  to  the  database. ·   connection.  rollback():undoes   any   uncommitted   changes.

We usually use them in a try/except block as follows (the exact structure may vary):

#     You     can     create     the     cursor     here     or     under     the     try     clause.

cursor            =            connection.cursor()

try:

#     use     the     cursor     here     to     query     or     manipulate     the     db.

# You     can     commit     the     changes     here     or     under     the     else

# clause.

except             pg.Error:

#     An     error     occurred     so     we     need     to     rollback     the     connection.

connection.rollback()

else:

#     No     error     has     occured.

finally:

#      This      code      will      always      execute.

if       cursor        and       not        cursor.closed:

cursor.close()

Creating views

It is recommended to use views to break up your SQL commands into smaller more manageable pieces.

You can create views in psycopg2 using execute. However, note that cursor.rowcount will  NOT return the actual  number of rows contained  in the view.

>>>             cursor1.rowcount

-1

Python

Date and time in Python

The  datetime  module  in  Python  provides  classes  for  storing  and  manipulating  dates

(date),  times  (time),  and  timestamps  (datetime).

Here are some examples for using the different classes provided by the module:

>>>     import     datetime     as     dt

>>>                     dt.date(2023,5,4)     #      The      date      2023-05-04

datetime.date(2023,5,4)

>>>dt.datetime(2023,5,4,13,10)                                       #        The        timestamp        2023-05-041:10pm