What are the benefits of using PyMySQL vs MySQL?

We are using both! MySQL is the DBMS. It consists of a server (mysqld) and a client (the mysql program we used in the first few assignments). The mysql client talks to the mysqld server over a network connection.

The PyMySQL module allows our Python programs to talk to the mysqld server, sending queries and getting results.

What actually is an API? Is it a collection of predefined methods?

Yes, that's a pretty good working definition.

Any time two pieces of software have to talk to one another, we have an API: a collection of functions, classes, objects and methods that they can use.

Here, the API is what allows our Python program to talk to the mysqld server.

do we just edit the same dbi.conf('wmdb') to change the database or do we add another line of code that uses dbi.conf?

The dbi.conf() is a function call in your code. If you want your program to connect to a different database (e.g. WMDB versus WW123_db), you would edit that line.

If your program needs to connect to one, then another, you could call dbi.use(new_database), since you don't need to re-read the dbi.conf file and such.

Can you explain more about how authentication works?

Sure. Your python program reads your username/password from your credentials file (~/.my.cnf) and sends them to the mysqld server to set up your connection.

This is plain old password authentication. There are other methods, like SSH keys, but this will do.

could you talk more about how to create the credentials file?

Actually, the sysadmin (me) already did that for you. It's there in your account. I created it at the beginning of the semester. It's ready by the mysql client program.

Even if tuples are better in terms of data storage, doesn't having a maximum column number of 1-2 limit its use?

Oh, there's no practical limit on the number of columns! If you want to read 10 or more columns from a table, you are welcome to do so:


    conn = dbi.connect()
    curs = dbi.cursor(conn)
    # these are column names
    curs.execute('select a,b,c,d,e,f,g,h,i from table1 where id = 123')
    # these are variable names
    a,b,c,d,e,f,g,h,i = curs.fetchone()
    ...

What is the difference between connection and cursor objects?

The connection object is created when we first connect and authenticate (like starting the mysql program).

The cursor object represents a particular query.

I think I still do not fully understand what cursors are and how we can use cursors to execute queries. Is each cursor usually attached to each query?

Yes, each cursor corresponds to a query. You can re-use a cursor if you want, but it's just a small data structure, like an object in a Java program, so you're not saving a lot of space by re-using one. But it doesn't hurt, either.

How exactly are cursors cheap and what does it mean to request one from the connection?

The cursor needs to know where to send the SQL code, and it gets that information from the connection object.

Then, it has some instance variables to store the result and such.

Can you go over tuple cursors vs dictionary cursors?

Sure. It's just an issue of whether you want to get the results (the rows) as tuples or as dictionaries. My rule of thumb:

If I'm going to destructure right away, I use tuples.
If I'm sending the data elsewhere, I use dictionaries, so that the other code doesn't have to remember the order of the columns.

I really try to avoid code like the following:

    
        x = row[0]  # what the heck is this value?