Quiz
- 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 (themysql
program we used in the first few assignments). Themysql
client talks to themysqld
server over a network connection.The
PyMySQL
module allows our Python programs to talk to themysqld
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 thedbi.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 themysqld
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:
- 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: