Quiz

1. When would you want to use the same cursor vs create a new one?

   Mostly as a matter of convenience. For example, suppose I want to check the number of seats remaining for a concert, and, if there's one available, I want to reserve it:

   
       with dbi.cursor(conn) as curs:
           curs.execute('''select seats_left from concert
                           where cid=%s and date=%s''',
                        [cid, date])
           left = curs.fetchone()[0]
           if left == 0: 
                print('no seats left, sorry')
                return  # this will also close the cursor
           curs.execute('''insert into reservations 
                           where cid=%s and pid=%s and date=%s''',
                        [cid, pid, date])
           conn.commit()
   
   

   So, here, I wanted to do two things, one after the other, and reusing a cursor seems reasonable.

   (Later in the course, we'll revisit the example above in the context of concurrency, but for now, I think it's pretty intuitive. )

2. Could we go over how a cursor works? Is it a built in object, does it have a set of properties we can look up?

   It's a Python object defined by the PyMySQL class, which is a pure-Python implementation of an API to MySQL. Since it's pure Python, you can read the source code, which you have a copy of in your venv.

   There's also a link to the official docs in the reading: references

3. How does pymysql know what type of value can match %s?

   Great question! It finds out the datatype from MySQL and chooses the best matching Python datatype. Thanks to this question, I wrote a little demo:

   
   use cs304_db;
   
   create table pytypes(
       a int,
       b int unsigned,
       c decimal(5,2)  # integer with 5 digits and 2 after the decimal point
   );
   
   insert into pytypes values (1, 2, 3.45);
   
   
   /* load these into pymysql like this:
   import cs304dbi as dbi
   dbi.conf('cs304_db')
   conn = dbi.connect()
   curs = dbi.cursor(conn)
   curs.execute('select a,b,c from pytypes')
   (a,b,c) = curs.fetchone()
   print(a,b,c)
   print(type(a), type(b), type(c))
   */
   
   

   Notice how unsigned int isn't a thing in Python.

   Did you know that Python has a special Decimal datatype? See decimal type

   Still, you probably want to stick to things that easily convert, like INT

4. what makes a query prepared vs parameterized? I don't understand what you mean by "parameterized query"

   Those terms are interchangeable. "Parameterized" should remind you of parameters to a function, so is meant to be more intuitive: it means that the query requires some values that are supplied at run-time, the same way that we do in python (see below).

   The way we do those in MySQL is "prepared" queries. To "prepare" a query is analogous to compiling a Python function. The following function can be compiled without knowing the values of the arguments.

   
       def hyp(x,y):
           return Math.sqrt(x*x + y*y)
   
   

5. N/A but learning about queries' potential for cybersecurity vulnerability has been interesting.

   I'm glad! I agree that it's interesting.

6. None! / none. All is clear! / Everything makes sense so far!

   Yay!