Worked Example- roster.py (Chapter 15)

Hello and welcome to our code walk through on the roster code. The learning objective of this is to do a many-to-many table, and so the idea is that we’re going to, just like we talked about in lecture, we’re going to have a set of users, we’re going to set of courses, and then we’re going to have a connector table or a many-to-many table that basically has two foreign keys. We are going to use the integer not null primary key auto increment unique as the way to get auto assignment of the primary keys in the user table and the course table. And then, we’re going to say that the name, which is like a logical key, and then the course title, we’re going to mark those as unique. And we’re going to take advantage of that in a moment. And so you’ll see how we take advantage of that. What unique means is if you try to insert the same string into this column, you know like chuck twice, then it’s going to fail the second time because it’s going to refuse to create a new record. And so if we just kind of like take a look, we’re going to get our roster data from this sample JSON, which is just an array of arrays. And this is the person’s name, the class that they’re in, and whether they are a teacher or a student. And so we’re going to read that. We need the JSON library and the SQLite library. We make a database connection and we get a cursor. The cursor is the kind of more like the file handle. You send SQL commands to the cursor and then you read the cursor to get the data back. The connection can create more than one cursor too so you can add more than one set of commands. But the cursor is generally like the file handle to the database server. And we are going to execute a big script and you’ll notice this is a triple-quoted string that goes all the way down to here. Some people would just give this to you in a text file and have you cut and paste this, and then go run that in your SQLite browser to create them. But that’s okay because what we’re going to do is we’re going to set this up. It will either reconnect to existing file name rosterdb.sqlite. And if I look where I’m at and I do an ls, we find that that file is not there. So the first time I run it it’s going to create it, but I want this to start fresh every time, so I’m going to wipe out the tables if they exist. That way you can run it over, and over, and over again, in case you make a mistake here. Now, I don’t have a mistake, or hopefully I don’t have a mistake on this. So we’re going to create, we’re going to drop three tables and we’re going to create three tables. And here, we’re going to create the table that has two foreign keys, user id, course id, that are sort of going outwards from the member table. And then, we’re going to model a little bit of the data at the role. Again, this is straight from the lecture. And the primary key is actually a composite primary key because we’re going to look up and it’s going to force this to be the combination of user id and course id to be unique. But there can be many user IDs and many course IDs, but only one particular combination of a value for user id and course id. And so that’s what we’re basically saying. You can be a member of a course, but you can only do that once. You can’t be like a member of the course a bunch of times. We’re going to, that should be roster data sample. That’s okay. Oops. Fix a bug. Save that, roster data sample. And so that’s just this file. It’s really just an array and then each row is an array. And it’s a way for us to get this roster data in. Once we do loads on JSON, we’re parsing it. And then, this is going to be an array of arrays. for entry in JSON data, so entry is going to be one of these things. So entry itself is a row. So and entry sub zero is the name and entry sub one is the title. Name, that’s the sub zero and that’s the sub one of the particular entry that we’re looking at. And we’re going to print it out just for yucks as a tuple. So we make, that’s what the two parentheses are. This inner thing is a two-tuple. And we’re then going to take the person, and we’re going to do an insert, and this is new, or ignore. So what the or ignore means is if this insert would cause an error, please don’t blow up. Don’t, just ignore that I try to insert it. And so this is our trick and it’s a beautiful trick. It’s like a gorgeously beautiful trick. Here, if we insert the name Chuck twice, or ignore will just mean that nothing happens. Meaning it’s already there. Okay, so if it’s already there. If it’s not there, it’ll put it in. And the unique will guarantee that it only goes in once. So we just in effect always attempt to insert it. If it’s had been there once, then it’s all set. And so this insert or ignore is a super powerful mechanism. I use it all the time. And we have a placeholder in the form of a question mark. And then we have so one of these days we’ll have two things that we’re asking for. As matter of fact, here it is, there’s a tuple down here. But this is kind of a tuple with one item in it, name. And that name is then going to substitute in for there without, while avoiding SQL injection. So this runs. It may or may not insert a new record, but if it’s Chuck or whomever that name is not there, it will give us a new record. And then we are going to get back the ID. And so this is the logical key and this is the primary key. That primary key is going to be auto constructed for us. So we need to know what it is. So we say select id from user where name equals and then that same name. So that’s Chuck. And so that gives us one. And then, what we do is we’re going to fetch one record from the cursor, cause that’s a select and it gives us back a cursor. There’s only, hopefully, one record there because it’s unique. I could put a limit 1 in there but that would be kind of redundant because it is, name is a unique key. And then, the sub zero just means if there were more than one thing that I was selecting, which you’ll see in a bit, the sub zero is just the first thing. And so this is going to give us the integer, user id, that was assigned, or if we’re coming through later for Chuck, Chuck later, Charlie later, that will be the old one. So this is inserted if it doesn’t exist, and this is get the newly created ID field or the original ID field. Part of this works by having both a logical key and a primary key. The primary key is auto generated, but the name is a logical key and it’s unique and so that’s our trick to get that assigned thing. Before we just looked at it in the user interface of SQLite browser and wrote it down, but this is how we do it in code. So we need to know what that key is, whether it was new or not. And then, we do the exact same pattern for the course except we’re inserting the course title. So that’s no big deal. We’re going to get the user id, course id. And then what we’re going to do is we are going to insert or replace. This is, it basically if there, remember that this user id, course id, combination is the primary key for this member table. If there is a duplicate, if this combination is already there, this becomes effectively an update state. And we have these two number values. Now what’s missing here is the role is not there. And so user id, course id, this is the SQL bit. And now we have a tuple with two items in it and that’s because we have two question marks. And then we commit it. And as I mentioned before, sometimes you want to commit every time through the commit. It turns out that these things are less costly, but that’s because it’s not always writing all the way to disk. Whereas, when you enter the commit, it’s going to go and write everything to disk, pause until it’s complete, and then your program doesn’t continue. So sometimes we don’t run this every single time through. Okay? So let’s just go ahead and run this. The only thing we’re going to see is the output of the name and the title as it’s running. So python3 roster.py. Hopefully, I can hit enter. So you’ll notice, by the way, that this SQLite now exists, right? And it has no data in it. So let me see if I can open this database and see it. So you see that there is no data. So we’re the code, we’ve run this code in effect up to this point. So we’ve done all the create tables and all that stuff. So the create tables are there. So all this data is here, it did it. We haven’t started putting any data into it yet because if we look at browse data, we’re not finding anything in here. Okay? There’s no data to browse. Now, hopefully we won’t have locked ourselves because we are sitting right here. And when I hit enter over here, then it’s going to go and it’s going to run really fast. So I hit enter, it’ll read it, and so it inserted all of those things. And now it’s been changed. And if I hit refresh over here, we will see in the user, it just sort of assigned user IDs, right? Columns auto assigned. We will find in the course that those courses are all auto assigned. There’s the courses. And there’s no duplicates because this is unique, right? And so these are the newly created things. But then, membership is user id, course id. And so again, the primary key as it were the unique constraints as primary key is the combination of these things. And I haven’t put anything in role. And so if you scroll through these, you see all of the users who are members of the courses that they’re part of. Okay? So there you go. And I’ll leave it up to you to come up with the join. I’ll leave it up to you to figure out how to put the role in. But I just wanted to kind of give you a bit of a walk through this code base and in particular, the tricks of the uniqueness keys, the auto increment keys, the logical key uniqueness, and then this kind of composite primary key, and then the trick of insert or ignore, and then the quick select that comes right afterwards to get the newly generated ID or to get the old ID. And then insert or replace which is a combination of a insert and an update. I hope you found this example useful, and can apply it, and basically create many-to-many tables.