ach account number and then you sort those transactions And then what you do is what we call a sequential master update and that is you would write a program That would read the first transaction and hold on to it. So, okay, this is count 45 Then it would read the first count like one and it would copy one and then it read two and read like seven eight 42 43 then we'd read like 44 And then we'd read 45 But it would now it would change that and write the new 45 and read the next thing And so this might be 60 and it would read a bunch of stuff and copy a bunch of stuff And then would finally get to 60 and it would merge the add their subtract And so the the old balance ended up here and the new balance did here And you had to only make one pass through the data. So it was super efficient So we had all these mechanisms to sort We used to do punch cards and have sorters and all these things and then these things would run for hours And if you watch old tv shows these tapes are spinning and these things are running back and forth These are simply reading and writing tapes And that's how we did a lot of data processing because we could store far more On a tape drive than we could on a disc and with the racks of tape drives We could scale the storage that our computers had And so that's the way we did data processing But it meant that you the only way you knew what the old balance was Was it was the balance as of this morning before your bank started? You don't know what the balance was for the day and that led to things like You can never return You can never withdraw more than a hundred dollars a day or something like that because you you don't know what the old balance was Or you might go withdraw a hundred dollars at a couple of different branches And and so they they didn't they weren't able to look your stuff up right away now It didn't take long until the disc drives got better and better and better And you could store the entire accounts all the accounts and their current balances on computers And then the problem becomes is what happens if sort of in the middle of the afternoon you want to update a balance? Well, do you want to read all your data and then write a brand new one and that's say that takes like 10 minutes that means for that 10 minutes only one person can be updating their bank balance and so because We could randomly access this data. We didn't have to read it all sequentially The trick was is how do you spread the data out and then how do you make it so you can change a balance This is of course second nature today, but how do you make it so you change the balance here without Changing the balance there and you can have multiple people going simultaneously to these things and make sure that you can't say withdraw money at two different locations simultaneously and somehow have your bank balance get corrupted by that so there's a lot of debate on how to do that and In early days, we just did sequential master update But increasingly we wanted to make better use of the random nature of our computers And and our storage and so that's what led to databases databases are the science of how you make use of Rotating random access data permanent data in a way that allows you to read modify and update that Simultaneously from many different locations and yet keep the data completely consistent And so this led to a study of a thing called relational databases and there's relational databases are not the only databases That that happened we had many other kinds of databases and there was a debate and I remember in the 70s in the 80s There was a folks that says oh no no there you can do index sequential That's the way to do it and relational databases weren't popular Weren't all that popular the first time that that I saw them. I I didn't like relational databases The relational databases had an inherent advantage because they were based on some really powerful mathematics The interesting thing is is early on the relational databases were slower But eventually they figured out how to sort of bring all the cleverness to bear to make relational databases fast And so relational databases are a pretty advanced technology and their companies like oracle that are very very wealthy And their primary Product for many many years was nothing more than a clever database product a clever piece of software That was really good at solving this problem. And that's how important this problem was to computing if you read about databases, you're going to see two sets of terminology one set of terminology comes from the mathematical background and Has to do with the underlying math things like relations tuples and attributes that's kind of like the fancy math version of it and uh programmers kind of think of them as rows and columns inside of a table and so if you look at sort of fancy theory you'll see Words that look like this and they're just full of this and the connection now All this is important and true and if you really want to get good You sort of begin to understand the nature that we model data at connections rather than At sort of intersection points rather than just modeling data as a flat file the way we do But for now we're we're going to as programmers think of this as just like oh It's like a super fast spreadsheet The super fast part is the math for us the rows columns and tables are spreadsheets So it thinks of thinking a spreadsheet of sheets sheet sheet sheet And that's like a table a named thing like tracks or albums artists or genres And then there is rows and each row has a different kind of data And then there's columns and we sort of specialize the first column in many spreadsheets to say what's in there This is not really the data. This is like meta data It's like the titles in this first column. That's not really the data and the data starts here And we have different kinds of data like strings and numbers etc etc for each of the rows and literally you can Get away with this is sort of about 80 of databases is just a really super cool spreadsheet, but Under the covers it is far more powerful than that so one of the early arguments that Happened was again what the programming model for this was and A lot of folks wanted a programming model that reflected how the data was actually stored The notion of structured query language came about in a way to express what you want it to happen And allow that to be sort of a very abstract expression Select all records that meet this criteria not read read read read read And so structured query language is a not a procedural language It is a it is a Imperative language where you're simply saying what you want and then somebody writes the loop the database actually does the loop But it's a a way for you to avoid actually writing the loop now That turns out to be the power of databases Because the cleverness in how to write the loop is a way that you Would probably never figure out how to be most supremely optimal when it comes to writing the writing the loop as you'll see toward the end of Joining many tables together and selecting and throwing a ray and getting down or count or whatever Someone has figured out how to do that really really well. So the idea was is you would express You know, we're going to create some data. We're going to retrieve some data. We're going to insert and delete it create read crud crud Create read update and delete crud And so that's what this does. It's a language that does this very simply Now the applications that we're going to use This for are more of a data analysis application We've been doing data analysis for through the whole course And the kinds of things that we'll see in the remaining chapters is we'll take some raw data file These might actually come across the network and we'll write some python programs to Play with that data parse it clean it up make sense of it You know and then write it into a database and this might be a slow processor This might be really nasty and this might be a way to have very clean data And then we'll write another python program to sort of read this Read through it and it's all efficient and pretty and then we can produce files And maybe we'll visualize it or do work further analysis in our excel or or a javascript visualization framework And so in this situation you will be the person who is Both sort of writing the programs database administrator And you can using sql browser play and look at the database kind of in a raw way And the first part of this we are mostly going to be using sql i browser Just to talk straight to a database later We'll write python programs that read and write data and and visualize the data So so this is what we're going to do first and then second we're going to do this part right here That's the second thing we're going to do Now another really common use of applications and something that if you continue learning more about programming is that you will want to write a An online application like amazon or a company or a or twitter that's got a website and it stores dynamic data in databases And so the picture for that is similar but different than the picture We're going to start out with and so the way this usually works is that You the end user uses a web browser talks to the application And the developer writes the application software and that application software stores its data in a database And inside that database we talk to the database using sql And all the data is actually stored here and the magic happens the data server is that database software that's so precious and valuable And then there is another person often called the database administrator Who has access to the direct access to the data and these roles in medium and large projects are kept separate mostly because the Mostly because the um the production While it's running and live the developer leaves the data alone and works on say the next version of the software And then the developer has a test version of the application that they run on their computer Where they're doing all that stuff and so this database administrator is a is a role in a large Project where we have to run production and keep production careful Keep production in good shape So the database administrator has this responsibility for the production aspects of the data And you may be working in a situation where that you're not actually controlling the data the database servers on different computers You have a little special access and you write programs to sort of read the data um And so the database administrator is the person who is Asked by the organization to administer that data The data that we develop and we'll do this in the second part of these lectures Um conforms to a data model. That's the metadata. Is this an integer? Is this a string? You know, how many columns is this and the data model turns out to be very very important There's a lot of science to building an effective data model that leads to really good performance And it's a it's a collaborative activity between the the application developers And the database administrator to make it so it's efficient runs in production Etc etc etc. There's a lot of products out there That you may encounter and we're going to be using sql light sql light's a little tiny database server And it's built into so many things and that's why we like it But if you're going to work at a large organization, you can easily run into oracle, which is the number one commercial product Microsoft has a thing called sql server, which is a commercial product and it's also very popular and very effective The more popular open source There's things called postgres. There's my sql And my sql recently was sort of bought by oracle and there is a a copy of that called mariah db That doesn't belong to oracle mariah db and so you Most of the sql that we're going to learn is common across these database because database systems because sql is a standard But then there are parts that weren't part of the original standard where each database vendor has done things a little bit different But there is a core common subset that does the basic create read update and delete operations So sql light is a very popular you probably have it in your cell phone 10 12 times your web browser has a database engine in it your car has a few databases in it And so sql light is what's called an embedded database system Python comes built in with with it. You just import sql light 3 and away you go And so it's a very very popular because it's free. It's open source And it is such a tiny little piece of software That you just included in other pieces of software and use it to solve the data management problems of those pieces of software Like your browser might use sql light to store your bookmarks Now you think oh, there's only how many bookmarks can you have? But what if there you need it to be fast and what if there's like people that have 10 000 bookmarks? There probably are do you still want it fast? Do you want to be able to search? And so you get all that by using a database like sql light And so again, we're going to encourage you to download the sql light browser So you can follow along with what we're going to do coming up next And so here is the sql light browser. Here's what it looks like and it's just a desktop application and Coming up next we'll start playing with this desktop application and see how it works So now we're going to make a database. We're going to use sql light browser Hopefully you've downloaded so you can follow along And I've got this handout this basic database handout that saves you from having to type all these things So bring that up in your web browser And so that gives you all of the commands that I'm going to type now And so you could pull them out of the either the web page of the You can pull them out of the slides or you can pull them out of that out of that so I'm going to bring up the database browser here Database browser now the thing that's going to happen. You'll see this happen on my desktop I'm going to make a new database and you have to store it somewhere and so I'm going to put it on my desktop and I'm going to call it py4efund And so we should see a new file on my database right there py4efund now that's a file that you don't want to edit with a text editor or anything like that. This is A database that you're this this is a file that's to be read by sql light browser and nothing else Okay, so we're going to create a table and I'm going to make a table called users With a column called name. That's a text and a column called email So I'm going to it's already asking me to make a table. I'm going to call this users And I'm going to add a field that is called name And I'm going to add a text And I'm going to add another field called email I'm going to make that be text now the key thing here is as we are in effect making columns and Rendering an opinion as to exactly what that column is supposed to be used for and we're not allowed to violate that It's not like oh, we'll do whatever you want because the database is optimizing its storage based on our In a contract that we're we're effectively making contract ourselves We could make these columns anything we wanted But we're just going to we have to we're going to contract with ourselves And you can see it's kind of small here You can see there's a create table and that's on the slide and that's the The the sql way of doing that this user interface is just helping us write sql So now I'm going to just say okay, and if you take a look you can see That I now have a table users and I can look at my database structure and the table users and Away we go and so so now that's that is creating it and like I said here in the slides is the create statement or On the web page. There's the create statement that could have done it now We can insert some data Let's add a new record to this database users And we'll call this guy Name Charles Csev So now we have a record so it's kind of like a database Spreadsheet now that's not the sql way to do it. There's sql sort of going on in the background But if we really want to do this using sql, we're going to use the insert statement And the insert statement Looks like this The sql syntax sometimes has extra words insert into is actually an s to sql keywords the name of table the columns And then the word values and then one-to-one correspondence between The values and in parenthesis. It looks kind of like a a tuple in python, but we're nowhere near python right now. Okay, and so Uh, that's what we're going to do And so i'm going to grab this christen And i'm going to go over here to my sql browser and say s execute sql. So now I can say paste that in And then hit this little run button And that's going to submit the sql to sql light and then Update that file and it says query executed successfully and away we go So if I go back now and I look at the data I see that there's two two things in here and now I can actually insert all the rest of these Let's go back to my little bit of stuff here. Let's put all these other rows in it turns out that If I go into the execute sql and I want to do more than one More than one command at a time I can put a semicolon at the end of each one of these things And then I can run them all for that all at the same time I mean one after another actually is what's going on here So boom boom boom and I'll take a look at the data and look I've got all those things in there now Eventually the thing that's going to generate that sql is a program not us This is we're being the database administrator. So we're sort of doing things manually Once things get going you write programs do that insert over and over and over again in python Or a web language like php or something like that. And so that is the insert Now we can get rid of data And so I'm going to say delete from that's the keyword users is the name of a table Where is a where clause will have lots of where clauses in sql Which is it's not like an if it in effect the delete is going towards the whole table And being turned on and off by this where clause So delete from users if you didn't put the where clause on will actually delete all the rows But where ted equals email equals ted at umich.edu Well, that one is going to make it so it only applies to those to the rows that where that is true So I'm gonna go over here and sql and I'll say delete from Users where email equals ted at umich.edu and then I'm going to run it because it's only one I don't need a semicolon at the end of it. And now if I go back and I look at the data Ted is gone okay update so the update says Updates keyword users is the name of the table set as a keyword And then this is column equals new value and then a where clause again This update if we didn't have a where clause would change every row in the table And so where email equals csev at umich.edu Oh, I got to change that because I already got the name to be charles. So you see the name is already charles So I'll just execute here Make this be chuck. So we see it And then I run it And then you take a look at the data and it's changed That's it. That's an update statement We're doing you're doing great. You're doing great and so The next thing we're going to do is we're going to take a look at how we retrieve data Now this is the select statement select star You have a list of columns and star means all columns from is a keyword and then the name of a table So this select star from users is the kind of thing you type all the time As a matter of fact, it's what SQLite browser is doing internally to cause this to happen But we can do it by hand by saying Select star from users and then run it and so then we get A little record set that is those four records that are sitting there We can also throw a where clause on the end of it. So we say select star from users where email equals csev at umich.edu and that Again, the select star from users goes at the whole table and the where clause goes at the whole table And then filters out all of the things except one record. So the where clause is Send it to the table, but then filter based on on whatever and so it it only shows us that Okay, we're cruising right along here You can also put an order by clause on there So we can say select star from users Order by email. So that's a column Select star from users order by email And so that orders by email Or we can change it by to name and we can say descending So that's the name and and descending order Sorting and selecting are good things that databases are really good at So this is the summary of what I've told you I said the databases do create read update and delete crud And we've done all those things except we did create delete update read. That's what we did That's the summary of sql and so You might be saying why did I take so long to learn such a simple and elegant and beautiful language because it's not really exciting It's a extremely simple language. That's a very predictable And you're like, well, that's pretty easy and it turns out that some of you May have been using sql in situations Maybe with microsoft access or something or actually type in this stuff and you you just kind of typed it And you never realized that you were learning a programming language That's why I like sql and that's a very declarative language and it's very straightforward. It's much harder to learn It's I mean it's much easier to learn sql than enough to learn python because in python you have to figure out how loops work and how iteration variables work And you'll notice there's none of that and so the but the key is is we've only started to understand the power that That's the simple ability to move around and update data and read data Randomly using using these simple sets of commands, but up what next we're going to look at how you do this With data models and relationships and really multiple tables Hello, and welcome to a code walkthrough in this bit of code. We're talking about the email db.py. This is a beautiful little example and then it sort of reduces Talking to the database to kind of it's a it's pure essence And so we'll start out this code and we import the s to a light three just to get the library there We make a connection and the in databases we sort of end up with an open that's two steps That there's the connection to the database which checks access to the file and the cursor is kind of like our handle We it's not as simple as you just open it and read it But you open it and then you send sql commands through the cursor and then you get your responses through that same cursor So c ur here is the variable that we're interested in and the first thing that we're going to do is we're going to We we've got this file. It will either create this file and right now this file doesn't exist It's going to be in the same director directory Oops email Yeah, there's no There's no email db. So this is actually going to create the file when it runs Um, and then the first thing we're going to do is drop the table if it exists drop table is a bit of sql If exists just keeps this from blowing up if we start with a fresh database and in this case There is no file there. So we are starting with a fresh database. So this will accomplish absolutely nothing Which is just fine Now when you're using triple quotes here, I'm just kind of using that to make this a little bit easier to read I probably could pull those lines up a bit Um, this one's uh, this one's actually small enough that I could maybe I'll just do that Let's do that. Let's bring that baby right up and turn this into a single quote That's short enough, right But triple code is just this one here is a little longer. So I use triple quotes So we're going to drop table. That's going to do nothing first time through then we're going to do a create table Now sometimes your application will have like a read me or something says go run these commands to set the database up But we're able to just set this database up in this particular application We'll see later ones where we're going to leave the database and not start it fresh and in this one We can do the same And so but this one in this one we could but then we're just going to start fresh by dropping the table. So we'll create it We're going to have a email and an account We're going to just basically what we're doing here is we're really going to pretend that this is a dictionary If you recall when I said dictionary dictionaries like an in-memory database. Well Now we're using a database to do a database But the first thing we're going to do here is pretend it's a dictionary. So that's a little crazy So these next lines of code, hopefully are pretty familiar to you, right? You get a file name Um loop through it. Um check to see if it's if it's you know, grab inbox short by default So we can press the enter key and then loop through it, right? And so this little part right here. This is our basic This is our basic loop that we're doing And so uh, uh, you know that that is pretty normal And if we look at this line right here that line right there is the line that is um That line right there make sure that we can Uh only get the front lines. We've done that a bunch of times and we're going to split it We're not going to strip the right because the split's going to take care of that And then we're going to grab the email address, which of course in the from line is the second part Um, and uh, and then uh, we will have that So now we're going to do some database. So the first thing we're going to do this This bit right here is kind of like the dictionary part So the first thing that we're going to do is we're going to select count from our database That is an integer where email equals and this part right here bears some explaining This is going to be c7umich.edu or whatever now it is dangerous to put Those strings especially from user entered entered data Into your sql you technically could I could make this be a Email equals single quote c7umich.edu. I'd have to skate the quotes and stuff But this question mark is a placeholder And this is a way to basically make sure that we don't allow sql injection go google sql injection To get a sense of what that is It's more it's more of an issue in online Applications, but in this application. We're just being good And so the way this works is this is a placeholder in this sql That will ultimately be replaced by this now you could have several question marks We only have one in here and so you give a tuple And if we just put email it won't turn into a tuple. This is a one tuple basically this little weird parentheses email comma parentheses that is a tuple with only one thing in it and that's just the weird python syntax It's rare that I apologize for python syntax, but that's a little bit Less than pretty but it's okay It's a tuple and normally there if there were like two of these then there would be email name dota dota dot okay, so So this cur.execute is actually not really retrieving the data In a way, it's looking at the sql and making sure that maybe it might verify that the table name is right Or if there's any syntax there's et cetera, et cetera. So this this actually is not really reading the data So but we have prepared this cursor. This is kind of like The opening of a file, but what we're opening is a record set We're opening a set of records that are going to You know Be this wherever it's true. So it's like we're going to read this like a file Now later things will loop through this, but we're only going to say hey grab that first one Right, we could have even put maybe a limit clause on there or something grab the first one And give it back in row and so row is going to be a Row is going to be the information that we get from the database and so that is If there are no records that meet this then row is going to be none So here's kind of again like the the get here's like the get where if the row wasn't there Because the way we're doing this is we're going to end up with this row in the database Here is this database and there's going to be two columns and there's a bunch of rows and then here's going to be csev four and gen three and Six right so these are the counts and so we're grabbing this variable out if it's csev that we're grabbing And that's going to come into here right that's going to show up in here and that row that row is actually It turns out that the row is a list But we're only getting one thing and what we really are doing is if we if we search through and we got through and there was nothing Then row is none means that there was no and we're seeing like Like chens for the first time and we have to insert it. So if row is none We're going to run an insert statement insert into counts Email count now we've got to set it to one because it's the first time we've seen it So values and then again the question mark the question mark basically says Hey, I'm going to have a value in this tuple and there's an ordering to the tuple And so there's only one question here one question mark placeholder here and then one is the initial count So email question mark count one away we go and so then then we have again We have a tuple that gives to this execute statement Just like in that execute statement the corresponding sort of strings or integers That that are to be replaced by each of the questions So when this runs there's going to be a new record and there's going to be a one that's put in there into that new record If on the other hand we pull back a row that exists. We're going to get this four number And you might think we want to take this four number and add it But in databases, it's always better to do an update because There might be multiple applications that are talking to this database at the same time So no matter what update does is in a single Atomic operation it turns whatever this number is into one higher and we don't have to worry about Other pieces of code potentially modifying now in this case We don't have to worry about that because we're the only piece of code But using update to increment something is way better than reading the value and then doing an update to adding one inside of python And then updating the new value, which is that's two sql statements, but it's also not atomic. Okay So if the row is uh, none If the row exists, we just know that it exists and when they just want to add one to the number We don't we do have the number sitting here in the row variable, but we don't need it. And so we're going to say update counts Set count equals count plus one column name where email equals and then another placeholder and then another tuple For the question mark. Okay, and so that's what this little bit of code does That is kind of the the read it parse it check to see if it's there if it's not insert it if it is update it And so then we see this con commit and this con commit Basically the way it works is that the database Is efficiently keeping some of the information in memory and at some point it has to write all that stuff out to disk So you can choose at times where you put this commit I'm right now We're going to commit every time through this loop But you might commit every tenth time through the loop because the the commit will take some time because it Forces everything to be written to disk and these can run really fast and the commit is the slowest part here So sometimes we do things like commit every tenth record or every hundredth record If it's an online system, which is not what this is You you have to commit at the end of every sort of screen paint, but For this kind of a system because we're putting so much in this is kind of a bulk insert We might come up with a thing where we you know every one every tenth time we do a commit But ultimately what this will do when this is running is it will build up slowly But surely adding new records and then one one and then it'll be able two and a three and all these things and add another one That'll be one It'll do this thing right and then at the end of the day that is what's going to be in the database now So now we're so let's take a look what's in the database and now we can actually read the database And so in the database we're going to run a select And we're going to say we're going to select the email and account from counts order by count descending So look at that isn't that cool We're getting in the top 10 because databases are good at sorting and they're good at all these other things So we're going to then execute this And then we're going to ask for the rows one at a time and the rows are going to be Rows of a tuple and row sub zero will be email and row sub one will be count So we run all this stuff and then we close the connection and away we go. Okay, so Let's go ahead and run this Let's go ahead and run all this stuff Python three email dv the dot py It asks for a file name m box Short now I can hit enter right and buck short And that's it and it looks just like that and it counts it and away we go Now the difference is at this point We have a file email dv dot sql light and we can run the sql light browser And we can then open this database And we can see what's in there. So here we go. It has made an sql light database We have a table of counts and then we can take a look at the data and there we go We've got the data in a way and we can do this And so let me close this it's it's important At times when you you don't want necessarily to have Well, let's see if we can cause it to lock up. Let me run this again And it's going to drop this table. So I'm going to run the code again But this time I am going to do the full one m box dot txt Now we'll see what happens here But it ran and now so what what we have to do then to see this date Is from the previous run, but if we want to get the most recent one We hit refresh and then away we go and so we can see this stuff And so this is just a real simple start To see how you can connect some of the stuff that we've been doing But store the data in a database, but the nice thing about the database is that it can Um Store this stuff from run to run even though in this case we're dropping the table every time In later things we will see how we can store data from run to run to give ourselves more restartable processes Cheers We're going to do some code walkthrough and if you want to follow through with the code you can download the sample code From python for everybody And so the code that we're going to play with is the twitter spider Code that is both talking to the twitter api and talking to the To the database and so what we're going to be doing is we're going to Run code that's going to hit the twitter api much like we did in a previous chapter And we're going to retrieve the data, but we're going to remember the data So we don't have to retrieve it again Okay, and so we're going to keep track of people's friends and what we're doing here is sort of illicitly Pulling down slowly but surely based subject to our rate limit. We're pulling down Who our friends are? And so let's take a look We're going to use url lib and url lib error Twitter url which was code that augments my url to do all the oauth calculation We're going to get json data back. We're going to make a database and we have to import sql because of the way python doesn't trust any certificates no matter how good they are And so this is our url to talk to the twitter api We're going to make a database and again the way sql light works is if this s spider dot sql light doesn't exist It creates it And we get herself a cursor And we're going to do a create table This if not exists Some sqls but sql light 3 does this create table if it doesn't exist We want to start this over and over unlike the the the tracks example I want to start this over and over and not lose data I want to and this is a spidering process and we'll see a lot of these we want a restartable process where we use a database So if we're starting with nothing and there's no and there's no file a spider sql light It creates this table and it's the name of the person Whether we retrieved it or not and how many friends this person has that we know of in our database Now this little bit is to deal with the ssl certificate errors The certificates are totally fine, but python doesn't trust any certificates by default Which is frustrating, but whatever so here. We're going to have a loop We're going to ask for a twitter account We have to type quit to quit if we had enter in this case We're going to actually read from the database an unretrieved Twitter person and then grab all that person's friends okay, and And so then we're going to we're going to if we We're going to do a fetch one get one and that's going to get the name of the first person the sub zero If we had more things than name here as sub zero is the first of those Fetch one means get one row from the database and sub zero means the first column of that first row And if this fails then we've retrieved all the twitter accounts um And so you know We're going to augment this twitter url using this makes you can look at the twurl.py code um This basically uh neat requires the hidden.py file Which has your keys and secrets in it. You got to get hidden.py updated I've got it updated, but i'm not going to show you because it has my keys and secrets in it And so we're only going to take the first five which means we're probably not going to find friends of friends of friends It's only the most five recent ones. We could run this with a much higher number To get to the so we have more than one friend We'll show the url while we treat it. We will do our url open We'll do a read and then we'll do a decode to make sure that this utf This will give us data in utf 8 and then decode will give us data in unicode Which is what we need inside of python. We will ask for the headers From the the the connection We'll say give me the headers give me a dictionary of the headers and The x-rate limiting header from the twitter api Tells us when we're going to be told we can't use this api anymore because this is one of those things And then we're going to parse and load the data that we got from From twitter and get a uh I think it was I think it's a list Yeah, it's a list and then we could dump this if you want and yours you can undo that And then what we're going to do is we've just retrieved This person's screen name and their friends And so the first thing we want to do is update the database And change the retrieve from zero to one and that's because we want we're going to use this to know about unretrieved So retrieve being one means we've already retrieved it and we did retrieve it So for that account we've retrieved it And then what we're going to do is we're going to parse that and so this is the similar to the twitter code We did previously in the web services chapter. We're going to go through all the users We're going to find their screen name. We're going to print the screen name out okay, and um Then what we're going to do is see if Let's see So we're going through all the users who are the friends of this person and we're going to say, oh, okay Let's select the friends from twitter where the name is the friend person And What we're going to do is we're going to if we're going to do a curve fetch one Of this this twitter the name of the friends This is the friend screen name, right? So we're going to say, oh, okay If we get this We're going to get that friend screen name and we're going to get how many how many friends this particular screen name has If we find a url We find it in there We're going to do an update statement and add one to their friend count how many friends they have and then Keep track this count here is not in the database. It's just so I can print it out at the end if there is no record For this particular friend We're going to insert them into it new and we're going to say here's the new person that we just saw Here that's their name. We're going to set retrieve to zero And we're going to say that they have one friend okay And then we're going to commit the we're going to commit the transaction and then we're going to close this at the End. Okay, so Let's go ahead and run this the first time it's going to create an empty database So I'm going to say python 3 tw spider. So ls star sql light Nothing there python 3 Oops, that's because I removed it python 3 twspider.py Okay, so I'm going to start with a twitter account Dr. Chuck And so it's doing this retrieval and don't worry showing the token and the signature is not dangerous Because you don't have the keys or the token. I mean the secrets and the token secrets So don't get all too worried. So I have 11 calls left. So I got I hope this all works One of my friends is Stephanie teesley and I do these are in reverse order. So let's grab Stephanie and Ask for Stephanie's friends So now we just retrieve Stephanie's friends and here are Stephanie's most recent friends And then I can just hit enter and it'll randomly pick Let's see if I can in the database. Let's open this up file open database Hope I don't lock myself sometimes. It's a little scary when you look at the database And you're just checking so this is what my database looks like we retrieve Stephanie and She has this is how many people So these these are the these are the friends of uh, Stephanie and me and these are how many I'm not in there So we retrieve Stephanie which was a friend. So let's go grab Oh, I don't know. Let's grab tim mckay You can get that one remaining 10. I don't have too many of these Tim mckay Right. So there we go remaining nine And so if I do a refresh on this Then you see I've got some more folks if I hit enter here. It will retrieve it'll pick one randomly based on Retrieve being zero. So it won't pick Stephanie or Tim because they're zero But we have lots of other folks to pick randomly and we'll hit enter. So it picked uh, and um, Who did it pick it picked? Screen name live edu tv which is ironic because I'm recording this on live edu tv right now And so we can keep hitting refresh and away we go. So I'm going to stop now because I only have eight remaining And uh, and so I'm going to type quit and so we will see We'll see how that works. So that's how it works now. Remember that you've got to Edit the hidden dot py file to make this work because we are talking to the twitter api if you don't edit that file Um It won't work for you. Okay, so I hope you find this useful cheers So now we're going to take a look at how we deal with more than one table multiple tables because the Real power of sql and the power of database performance has to do with when you start connecting tables together If you go back to that original mathematics it models data at the intersections between the row and the columns And these intersections are the magical bits um And so breaking an application to use multiple tables is an art form. It takes a while There are some simple basic things that you can learn and we'll teach you here And so it's not too hard to learn the basics, but then it's much more complex to be super Skilled at it and and in general advanced databases in my mind. It's hard To teach advanced databases because they're always so contextually grounded You know something like a twitter or a or google the databases are so specialized by the time you make Everyone can do small to medium-sized databases using the basic techniques But at some point once you escape medium-sized databases You end up in these sort of narrow things and optimize each database very separately And so I just tell people, you know, learn the basics really really well Write programs and then go do real work but Database design is the act of figuring out The data that your application is going to want to store and spreading that across multiple tables But we don't just do it randomly. We do it very much Cleverly and if you look at a data model, this is what it looks like and what we're showing here in this data model Is we are showing Five tables and this is a kind of a calendar kind of a system And we're seeing the the columns that are in each of the tables And then we're seeing the relationships between the tables And even in these relationships, there's kind of a little bit of code And when you have an arrow that looks like that, there's many of those to one and this is a many to one relationship Many to one relationship. We'll talk all about that stuff But if you go into an organization and you have a really large and complex data application They might have something printed out on the wall that looks about like this Which shows the database tables and connections, etc, etc And they might say, oh your job is to go down and then this little corner add one column field there And then do this and then connect it with this thing over there and then make a A screen that shows all these things that pulls from this table this table this table and that table And that's your job if you're a programmer on a large software development project these database models become sort of like the core backbone of the knowledge that applications are managing and using So the idea is is that you take your application. We're going to start really simple We're going to take your application and you have to draw a picture and the basic rule And literally you could spend Course upon course learning about database normalization But i'm going to i'm going to distill it into one basic rule and that is never put the same string data in twice so My name charles severance If i have build a database well, you should go into that database and you'd say, okay The words charles severance, which is the name of a person me in that database only shows up once And what we do instead is we connect things together and model my name as a connection to the record that has my actual name in it Rather than putting my name all these other places And so the idea is to pull duplicate data out and make only one copy of it So there is the there is the users and in there is the user's name and the user name shows up only here And everything else points to the particular user entry. So That's the idea And so here is our first application We are Working as a startup. We just quit all of our jobs and we are going to build a music management application I mean, what a great idea.  And so we have mocked up and we have figured out that this is what our music management application. We want to track people's tracks Know something about what artists and albums and genre they are and have ratings and how many times we played them And how long they are. Well, that's that's the data that our application needs to represent And we've done testing on this and and wireframes and everyone loves this a great user interface And so this is how it's got to look But we're going to have billions and billions of tracks and these things And so we want to come up with an efficient database to handle this And so we're going to take a look at this and look at each of the columns And we're going to ask ourselves is this column part of one of our existing Objects our existing tables or is this got a this object Have to create a new table and then once we've just find those different objects We connect the tables together and model of connections now A little trick to kind of make it a little easier on ourselves is we can look in these columns And look in the columns that have duplicate information vertically that string information So rating is just a number like zero through five So we don't worry too much about integers and numbers and that kind of stuff or or whatever But we do look for strings and the problem here is we got like these strings A core many times and so these are the problems and so we we have to put these things where there is replication of string data kind of in the vertical dimension We have to put those in different tables And so we'll start out Now the first question that you have to ask yourself When you're going to draw this picture of how this data is in multiple tables and connect it together Is what is the first one that you're going to write down? And this is an interesting debate and often people are sitting in a conference room and people who have experience Kind of know what to do. Usually if it's a multi user system like a learning management system The users might be the central concept. Perhaps the courses might be the central concept This is a single user system. And so you can think well, what is really this application about? It's not about people. It's one person But it is about tracks and so we can say, okay Here it will take the the track is probably the sort of most foundational notion of this application And then we can take and say, okay now that we've decided that Tracks are the foundational notion which of these columns are simply an attribute of the track not really And the cheaping the cheating way and the easy way and this particular one is like these numbers All these numbers like this number and these numbers not that one They just go along with track and so we'll put that in we got the track title Rating length and count and we put that in And then the question is we've got the remaining things are we've got the artists We've got the album and we've got the genre and so we can say, okay Well, we can't we've got some vertical duplication. So we're going to say, okay, this track probably Belongs to an album. So let's pull out the album into its own table Oops Pull the album out into its own table and so Pull the album out into its own table And so that pulls that out and then you say, okay What would be the next thing that we're going to pull out? So we pulled out the track We've got this taking care of this taking care of that taken now We've got the album. Well albums belong to artists. So let's take out the artist And then we'll pick where the genre belongs and we'll just say that the genre belongs to the track And so because there might be albums with more than one different genre So each album is not necessarily a rock album It could have a rock track and a country track etc etc etc And so now what we've got is we've got four tables, right? We've got a track table We've got an album table an artist table and a genre table And if we sort of double check all of the columns that had vertical duplication in them now have their own little table So we can we can eliminate the next thing we'll do is to show how we're going to eliminate this vertical data vertical data replication By showing how you represent these relationships that we just created inside of the database Now we're going to represent these relationships in the database And again what we're trying to solve here is this notion of database normalization third normal form There is so much theory, right? But in in this lecture, I'm just going to condense this down to don't replicate string data And use what are called keys use integer keys To point at those things and we're going to use these integers then to point So assign each row an integer and then we're going to point from one row to another using those integers And so we're going to add these special key columns to each of the tables and help the in the database will even give us help Managing those now and so we still need to keep track of you know Who is the creator of the album which album a track belongs to we've got to Create these relationships and we have to come up with ways to store those relationship And so the idea is is we're going to have a column in one table, which is the key column And we're going to call this the id column and so this is a row it might have many bits of data here But in this case, it's just the name of an artist So this album is going to belong to an artist And we're going to assign a number inside the database and so that led zeppelin Is one and acdc is two and so we have this key This is called a primary key and then later when we want to say that the who made who album really was done by acdc We put the number two in and so the difference here is instead of saying acdc in this Record we just put the number two once we've established this number So we assign keys and then we have these pointers that point back And so that's how we model a relationship We with these small integer numbers And so there are three basic kind of keys that we use one is the primary key And that is that little id column that is just a number But once we give led zeppelin the number one led zeppelin is the number has got the key one for the rest of that database The logical key is the text area that we use that you might look up So the title of the band or the title of the album That's a logical key and then the foreign key is one of these keys that is really pointing to the primary key of another row So that's called a foreign key And it's you might think that you want to use something like an email address as the primary key for a user table Or something like that the logical key should always be separate and there should always be a primary key that integer number Because things like logical keys do change people do get new email addresses And if you've got that email address as a foreign key pointing all over the place It doesn't work out so well And so that's why you use these small integer numbers that have no meaning outside So sometimes if you're on a system and you see a url and you see some number like 422 2016 you're like, oh that turns out to probably be my primary key in their database So sometimes you can look in a url and you can see these primary keys in the url But they don't mean anything outside of that particular system So like I said a foreign key is a key that is really pointing at a row in a different table And so so we have the album has a primary key for it But the artist underscore id points to a row in the artist table as we will soon see I have a naming convention and in my naming convention on this lecture I use id for the primary key and then artist underscore id I use uppercase for the table names And then artist underscore id says this is a key This is just a key that points to the id key of the artist table And so that's what I do So you'll see and all my stuff i'll use that it's a convention It's not something sql forces you to do But you will find when you go to organizations and work on their databases These conventions are very important So I can do something and you can understand the rules in which I created Some of these you'll find this used by some people You'll find completely different conventions and that'll be okay Whatever convention your organization uses learn that convention So now we're going to talk about how we put these keys in And then how we actually make the connections from one row to another row So now that we know what a primary key logical key and foreign key are We are going to actually start putting these together and creating tables That have these kind of values in them So when we were done we drew this picture that was sort of a logical model of How our data would be spread across four tables and how those tables are connected Now we have to take this and we have to map it in a way That leads to the column row the columns and the needed columns in each of our database tables And so here's what we do We basically have to take and for each of these When we're going to build a track table, we're going to build a track table We add a primary key So we just added an id field to every one of these things And that's so we have a place to store the sequence number of this particular row We have logical keys We've just marked those those are strings and then we things like You know rating length and count they just kind of go in here And now we have to model a relationship So we do is we in the table that the relationship starts from We put one more column in and this is the one I will name album ID And that just is an integer column that's going to record the album ID So there might be this might be 16 and then 16 goes in here So there's one of these columns that's a foreign key that points to this and that's why it's foreign This is a key that's not in the track table This is a key in the album table that we're pointing to and so there's a foreign key And that's what we have to do and we just do that over and over and over again And we quickly convert that picture that was a logical picture to having every table has a primary key And every time we have a starting point we have a foreign key foreign key and then foreign key And then we mark these things as logical key logical key logical key and we'll see how we do that And so that's the picture now we have a picture of exactly how we're going to lay these tables out in the fields that we need in these tables So we're going to do a create table statement And I've got this create table statement sitting there And so this one's going to be a little bit different We're going to say create table artist And the ID field is integer And it's we're going to add all of this stuff This is adding to the column to tell it additional stuff It's a primary key, which means we're going to use it to look up a lot It's automatically incremented, which means the database is actually going to provide this number for us as we insert records We it's not it's not allowed to be null. It's not allowed to be empty And it's supposed to be unique and then it's going to have the artist is going to have Um a name column the name column. That's just text So let's do that We already have our users and this is now we're going to do a create table in this sql and you can do that That's okay. That's totally fine and we have to get this right and we say away we go And so now if I take a look at database structure, I've got a users table as well as that that users table we were playing with before and uh this artist table Let me go ahead and delete this users table just to say goodbye Okay, so now we have the artist table and we take a look and it's got an id and it knows all about this stuff. Okay So That created the table We're going to keep doing this the next thing that we're going to show here is we're going to show The foreign key right so artist id is just an integer in some database languages like my sql and oracle You would put more stuff here to say this is a foreign key blah blah blah But in sql light we keep it simple and just say that is an integer column That's a foreign key the album table has a primary key and a foreign key and then the title So we'll go back and we'll grab that text out of my little page Let's create table Go back to execute sql And then run that And we'll continue with just the genre table has an id on it and um Primary key auto you'll just copy and paste these That whole thing you do that over and over and over again. So we'll go in here And run that one And so the last one we're going to do Is the track table and the only thing that's kind of weird about the track table is it's got two foreign keys Right it's got an album id and a genre id Once you draw the picture you just sort of literally translate these things It's got two foreign keys and a primary key. That's pretty much just like all those other primary keys And you know integer counts an integer and lengths an integer all that stuff And now we we've got it. So we take a look at our database structure We're going to see that our album genre and track are all set up And these are no columns that we just made with those create statements Okay So now let's some insert some data this first insert statement is kind of important to take a look at So insert into by the way the keywords can be upper or lower case table name Columns now this table has two columns. It has id and name But we told the database that id was auto increment. So it's going to actually give us the number We're going to it's going to assign the number rather than make us assign We could make it be one two three, but we say hey database. You're good at this Why don't you make it one two three? And so there is going to be a record that it adds led zeppelin. So let's take a look at that So We'll insert led zeppelin Oops Over to sql Insert led zeppelin and run it. So now if I look at database structure And I look at the let's look at browse data and look at the artist database You will see that I put led zeppelin in but this id field here was auto incrementing And so it it's it was put there by the database And now when we do the next insert Which is acdc And we take a look at the data We'll see that acdc is two now if you're writing this in a program If you're going to write this in a program You can get these numbers back from the database in your program But I'm not writing this in a program. So I have to remember that one is zeppelin And two is acdc So I'm gonna keep myself a little cheat sheet here to remember that Because everywhere else in the program that we want to say led zeppelin I got to say one now because the artist the uh the artist id of one means led zeppelin in those rows And so now we're going to go back and we're going to take a look at the next one And now we're going to put the genre in if you think about it We're working from the leaves out where the track will be the last table that we'll update because I have to define the keys For things like rock and metal and led zeppelin and all those other things and again Even though the genre table has two columns id and name We're only going to specify the name and let the database assign the value So i'm going to insert both of these and use a semi the semicolon trick Put a semicolon here and a semicolon there and run that And so if I take a look at my uh browse data and I look at the genre It's assigned one to rock and two to metal and i'm going to write that down one rock two metal I should have done something like rock and country because I can't even tell the difference between rock and metal but whatever that's My musical skill is not what's at issue in this class so now We're going to put an album in the album is the first thing that has a foreign key So if you remember the thing is the album points to artist and so that means it has a foreign key of artist id And so we have to explicitly say this because we're the system doesn't know Which artist who made who is but we know that who made who is acdc? And that's two and so we know to put artist id in so we'll say insert into album title artist id And so we have to know what this two number is And of course because we have our have the handy little handy little cheat sheet We can go over to execute and run that And I'll put a semicolon there and a semicolon there and run it and so now we have in the um In the album field we now have this and so these this was assigned and so who made who still have to write down that um Who made who is album one? And album two is led zeppelin four That makes it even more complex because the name of the album is at roman numeral four. I'm sure I can figure that out okay so The next thing that we're going to do is we're going to insert the track record Now if you think about the track record the track has two foreign keys and um And it's got a lot of stuff. It's got the title It's got the rated length count, but then we got the two foreign keys And so we have to know these numbers. So this this two one This two one This one two is the genre We're specifying the genre and the album that this track is from by those numbers now again We have to use this cheat sheet but if this was a program the program would know that one was zeppelin and um You know our one was who made who and two was led zeppelin four And so that the programs this kind of stuff is easier for the program to understand Then for us to keep track of and understand, but just just so we can get through these few records Um, and that's why I rely so heavily on my cheat sheet. So here we are all With all these numbers the the foreign keys are the tricky part here. Everything else is really quite straightforward So now I'm going to insert four records into my track table And then run that Okay, so if I look at browse data and I look at my track table This column here this id that's the primary key of the track table and then here are the two foreign keys Now now the interesting thing is now there is replication in these columns But the numbers are what's being replicated and that's okay Went a long time just not to put led zeppelin four in twice We could have made this a string but by making this an integer it saves tons of storage and makes it super fast That turns out to be One of the key things that makes databases super fast is using these integers So we take a look at all this stuff. We see that in a sense by using these little numbers We are pointing to rows and other tables the foreign keys are always pointing. They always point to their id So these foreign keys are out here This is the primary key up here and they always point to a row and another table And so we have modeled all those relationships and you will notice that in this entire database the who made who Only appears once the word rock only appears once the word ac dc only appears once What we have is we have duplication In our data, but we are duplicating the relationships i.e these little integer numbers Not duplicating the data itself and and in something that's the small it seems irrelevant But if you have billions of records or hundreds of millions of records, it's very relevant very very relevant So the next thing we're going to do is take a look at how you actually reconnect all this stuff together once we've sort of blown it out in these uh using these foreign keys and hand constructing all these relationships now how we bring it back together to show the data to the user So now that we've carefully constructed our relationships and the tables we need to reconstruct the data to show our users And you can kind of see how you would go pull this stuff together But there's a wonderful capability in relational databases called join that brings this all back together And so we have done this for efficiency of storage efficiency of scanning etc but we do need to traverse these foreign keys at times and The database software will do this for us automatically So the join operation basically is a way to specify in a select statement that you want to pull data out of more than one table And then specifying using what's called the on clause exactly how you want that data pulled out And so here we go we already have a A table an album table to the artist table And the foreign key and we want to in effect Pull data from both the album and the artist the album title and the artist name And we want to show that and so we we're going to say select which is the same select statement Here's a little different syntax. This is the list of fields. This is table dot field So it's the album title and the artist dot name comma there from the album And I always start with where the little arrow starts from album joined with So that is going to walk down this connection from album to artist album joined with artist Don't say with I just say it on and then this is the conditions upon which that join is going to happen When the albums artist id which is this column here albums albums artist id Matches think of that as is equal to or matches the artist id And so it it only connects the rows here when there is a match between these two tables And so if we look at this and we see that You know this one matches this one and this one matches that one and so it's the join connects Uh conditionally and it connects when the on clause is satisfied And so when this whole join runs, this is what we get So you select all this stuff now. This is an abstraction. Are you writing a loop? Are you doing two nested loops? How are you exactly bringing all this data again? We don't care about that because that's the beauty of sql That's the beauty of how we do this in a database So now if we we can just run this command. So let's grab this command Select track title genre name from track join genre that exact query case case of keywords doesn't matter And we go over here and we run this as sql And we run it And we get oops I got I went I got too far Let's do this one. So let's do that one there select artist name I have to add that one to my little cheat sheet the next time you see the cheat sheet. It'll be right So the title so this is coming from one table and that's coming from another table Okay And so that's one So here is something we can do that gives us a little more detail on that We can say So so this is where the connection and so you can think of the join is sort of spreading one table and connecting it to the other table And so what we're going to show here is it's exactly the same thing We're going to do is we're going to add these two columns so you can see where the match happens And so this this is one table This is another table and these are the these are the kind of columns in common even though they're not They're the columns that match. This is where the on clause is happening, right? We we have taken this table joined with this table on These two things connecting with each other so you can almost in some language some variants of sql This would even be a where clause so you connect these two rows, but only connect them when those two numbers match So so you can see I mean if we run this I'll just run this And again, you just see these this is where it connects Okay now interestingly We can see what happens and what the purpose of the on clause is If we omit it so this is exactly the same as that previous query Except there's no on clause so it's select all four of those fields from the track joined with the genre And so it's basically taken the track table And the genre with a join but no on clause. So it's not filtering for matches This is a match. This is a match That's a match. That's a match But we don't have an on clause so the matchiness doesn't matter And so you're going to get all possible combinations and literally if there were you know 10 on one side and 30 on the other side you would get 300 rows in that join So it'd be all combinations except the on clause reduces the combinations and you might think Whoa, this is really inefficient and I will say that's what my first reaction was when I first saw this but It's not inefficient. That's the beauty of abstraction. That's the beauty of sql. You say do it and and it just figures that out so um Let me grab this and you will see that we can run this one as well And that kind of gives you Why the on clause is important because now we have a whole bunch of these things and the on clause Just filters that out. So if we would just add the on clause back in Then that would only show the ones we showed on the previous slide So that's why the on clause is important. The join is like all possible combinations of all pairs of rows Between these two tables on is oh, but only where these two things match And you might think that it's inefficient, but the on clause turns out to be the way it becomes efficient Okay, so now we're going to Do the same thing where we're just going to take the track title in the genre and going to connect that to better together So we select this we're going to we need to join from one table join to the genre table with an on clause and so we're going to make those connections and the only thing we're going to look at is the title and the genre name and then run that And so we got this title and genre name now the thing you'll notice Is for the first time we now have replication of string data in a vertical dimension That's okay Because the data is not replicated in the database. The data is now replicated as a result of the join And so we are going to reconstruct what the user wants to see which the user originally all the way back to the beginning Wanted to see the duplicate information in the vertical axis, but now we're reconstructing it We didn't waste the space or performance in our database, but we still have to show them And so now the next thing we're going to do Is a monster We are going to reconstruct across all four tables And you might think this is really hard and and and sure it's going to be a little tricky But as long as you follow the naming convention and the naming convention makes sense We're going to do a select from the tracks title the artist name the albums title in the genre name From the track join genre join the album joined artists And so the joints follow the little arrows, right and then the on clause Qualifies each of those arrows when to follow the arrow and then this becomes pretty easy It's a foreign key the tracks genre id. That's a foreign t Equals the genre dot id the primary that's primary key That's a foreign key because I name it that way And I know that this goes to that genre table because I name it that way And tracks album id is equal to the album's id foreign key primary key And albums artist id is equal to artist id After a while you can type these pretty fast as long as you follow a naming convention and and you know the naming convention So this looks like it's really hard to do, but after a while it's really just a pattern So let's go ahead and run that one And it will Assuming we've done everything right Replicate all the data. So there's all kinds of vertical data now being replicated every column has vertical data Again, it's not in the database the select in the join and reconstructing Vertical data as it needs to be shown to the user And so if you've been following along Probably a couple hours later now We started with a picture that was our mock-up of what we wanted our user interface to look like and it had vertical stuff And we're like, oh, we can't put that in a database model And then we carefully build a database model that didn't have the data and then we're like, ah, we got to reconstruct it So we use join to reconstruct it And so after all that we went here with a clean little model with four tables all beautifully connected together And then we had to join it all back together. So join reconstructs it and again the key is The storage is efficient. The scanning is efficient And we still use the join to produce the output that we ultimately want with all the sort of vertical representate rep The vertical replication that our users really want to see so So one more kind of relationship. This is that was called a one to many relationship That was actually three one to many relationships and the other major relationship is what's called a many to many relationship We're going to do some code walkthroughs actually running some code And if you want to follow along with the code the sample code is here in the materials of my python for everybody website So you can take a look at that. So the the code we're going to look at is from the database chapter And we're going to look at tracks dot py So a lot of the lectures that I give in this database chapter are just about sql And this is really about sql and python. So this i'll go through this in some detail So the the code that i'm going through is in tracks. Uh, there's also tracks dot zip that you can grab that has these two things It's got this um library dot xml file, which you can export from your If you have itunes you can export this or you can just play with my itunes And so this is also going to review how to read xml. So we're going to actually pull all this data And this xml is In that that apple produces out of itunes is a little weird in that it's kind of key values And so you see key value pairs and it even uses the word dictionary And so it's like i'm going to make a dictionary that has this then a dictionary within a dictionary This to me would be so nice if it was json because it's really a list of dictionaries This is a dictionary then another dictionary then another dictionary and then the key for that dictionary and it's It's a weird weird Format but we'll write some python to be able to read it and so So you export that from Itunes and you you can use my file or you can use your file. It might be more fun to use your file And so here's tracks.py Um, we're going to do some xml and so we import that Uh, we're going to import sqlite 3 because we want to talk to the database And then we're going to make a database connection and in this once we run this you'll see that that file will exist And so right now if i'm in my tracks data That file doesn't exist But what we'll see is this is going to actually create it now remember that we have a cursor Which is sort of our like a file handle. It's really a database handle as it were And uh and and in order to sort of bootstrap this nicely We are going because this code is going to run all the time It's going to run and read all of library ducks xml and later things will we won't wipe out the database every time And so this i'm executing a script which is a series of sql commands separated by semicolons So i'm going to throw away the artist table album table and track table Very similar to the stuff we covered in lecture Um, and then i'm going to do the create table and i'm doing this all automatically And so and you'll notice this is a triple quoted string. So this is just one big long string here And it happens to know that it's sql. I'll thank you adam for that And so it creates all these things now It's not quite as rich as the data model we built because there's no genres in here And so it's artist album track and then there's a foreign key for album id and a foreign key for artist id Which it's it's sort of a subset of A subset of what we're doing And so that's that when that's done that actually creates all the tables and we'll see those in a moment Once we run the code then ask for a file name for the xml, right? And so that's what that is Um And we're going to i wrote a function that that does a look up that it's it's really weird because if you look at these files the Um, like in this dictionary, there is a key, right? And so the key of this dictionary This really could should have been a key value pair But so i had it there's this weird thing where the key for an object is inside of the object And so we're going to grab Uh For all that we're going to loop through all the children in Uh This outer dictionary and find a child Tag that has a particular key. And so you'll see how this works. And this was something I was going to use over and over again um And so the first thing we're going to do is we're going to just parse the string and this is the string And then this of course is an xml Et object And then we're going to say we're going to do a find all and so this shows how the find all We're going to go the third level dictionaries. We want to see all of the tracks And so we have a dictionary and a dictionary and a dictionary. And so what we want Is all of these guys Right all those guys right there right track id So we're going to get a list of all those that'll be the first one This will be the second one because the find all says go to the Find the dictionary key then a dictionary key a dictionary tag within that and a dictionary tag So it's and then we'll tell how many things we got And then we're going to loop through an entry is going to iterate through Each of these and see we'll get our name and our artist another one bites the dust queen And away we go and then the next next time to the loop will hit this one. Okay, so So then what we're going to do if is we're going to go through all those entries And if there is no track id and if that that's this track id field, where are you hiding? Track id We don't have that. We're going to continue And then we're going to look up the name artist album play count rating and total time Okay And And so Here they are play count a lot of those things that we had In the sample lecture that I did and we're going to look those things up And we're going to do some sanity checking if we didn't get a name or an artist or an album We're going to continue we're going to print them out and then We are going to Ask forget remember how you have to get the primary key of a row so you can use it So the way we're going to do this is we're going to do an insert or ignore And so this or ignore basically says Because I said that the artist name go up here I said the artist name is unique Which means if I try to attempt to insert the same artist twice it will blow up Okay, because I put this constraint on that Except when I say insert or ignore that basically says hey If it's already there don't insert it again So what I'm doing here is insert or ignore into artist So this is putting a new row into the artist table unless there's already a row in that artist table and This syntax right here You know the question mark is sort of the where this artist Variable goes and this is a tuple, but I have to sort of put this comment to force it to be a tuple So this is a way you have a one tuple And then what I need to know is I need to know the primary key of this particular artist row Now if this line may or may not have been actually done the insert And so I need to know what the id for that particular artist is so I do a select id from artist where name equals Now it either was already there or I'm getting it fresh and brand new So I do an artist id equals I fetch one row and it's going to be the first thing Given that I only selected id and so this artist id is going to be the id now Now I have the foreign key for the album title, right? And and so now I'm going to insert into the title artist id This is the foreign key to the artist table And I got this value that I just moments ago retrieved and I got the album title But this also is insert or ignore because now if you look I have unique on the album title Yep, unique on the album title. So that'll That'll do nothing. It doesn't blow up. It or ignore says don't blow up Just do nothing because this next line is going to select it and I grab the albums foreign key For either the existing row or the new row And then I'm going to insert or replace and so what this basically says is if the unique constraint would be violated This turns into an update Now not all sql's have this but sql light has this that basically says insert or replace Some sqls are totally standard some things we do like this is this this select statement is a totally standard a part of sql Then they insert is totally standard But insert or replace and insert or ignore is not totally standard But that's okay. It works for sql light, which is what we're doing and so we have the title album id length rating and count And then we have a tuple that does all that stuff and of course the The title is unique All right, the title is unique in the track table as well. And so we've inserted that so the clever bit here is Both dealing with new or existing names in these three lines and we see that pattern twice here Where we're doing that okay, so there's not much left to do except run this code Hopefully it runs python 3 tracks dot py and library dot xml whoosh Okay, so that is my um So so we we found 404 of those dictionaries 3d dictionaries and now it's starting to insert them insert them insert them And we can take a look at so we do an ls minus l Or dur on windows. We'll see that we made a a track database We extracted the data from this library and we made a track database and we have all these foreign keys So let's go and take a look at the sql light browser file open database track dbsql light And come on up. Where'd you hide? I've got it minimized. So there you go. Let's look at the database structure We have an album. This is the structure Artist in track. We don't have no genre And this is all like we did it by hand except python did all this work for us If we take a look at the data and we start from the outside end We have the artist names and their primary keys Right, there's the artist names and primary keys And then we have the albums And we have the artist IDs See the artist IDs how nice those are So we have the primary key here and the foreign key there and then we have the title and if we get to the track Uh, we have the album id in a way we go So if I was clever, I could be able to type some sql Oh great If I was smart, I'd had this in a paste buffer so uh select select track dot title album dot title artist Dot name, I think Artist has names and albums have titles. Yes Okay, so I can do that from track Join album Oops album Join Make that a little bigger Album, track, join, album, join, artist I need to on clause and I can say track Dot album ID equals album notice how I know the name that I named these things and album Dot artist This is so great when you use a naming convention artist ID Really, I think that might work So let's just see what we get when we type that into the sql box here execute sql run Yay, I got it right the first time Right, so that's basically my nice little joined up track list Oh, I'm so happy that I got that right the first time Okay, well you can so you can play with this yourself um Play with this tracks maybe make an export of your own uh iTunes library and run it with that and so I uh, I hope that you found this particular, uh, bit of code useful. Okay Cheers So our last major topic is called many to many relationships and up till now everything that we've done is what's called a one to many relationship And that is there are many tracks associated with one album There are many albums associated with one artist There are many tracks associated with one genre And you can think of labeling and as you look at data models They put little labels on each arrow that tell you which end of the arrow is the many And which end of the arrow is the one And so in this case the foreign key is pointing to There are many of these rows over here many rows that point to one row over here So it's a many to one relationship There are various ways sometimes sometimes I'll put two arrows at this end and one arrow at that end But whatever it is This kind of thing we've been showing is a many to one relationship and that's probably the most common thing But there are times when you just can't model things with the one to many relationship Um, so like if you have a mother and children Well, that's uh, that's a many to one relationship and it's just fine and that works fine But sometimes You have a many to many relationship and that there might be many books One book has many authors and uh, each author has many books and so you don't have like the one side There's no one and so you have to end up building a table that what we call I call it a connector table They call it a junction table on wikipedia But we need a little table that allows us to break a many to many relationship into an effect Too many to one relationships and a connector table and so this is a connector table So you could think of this as you know, there are many many links here But we don't have a way to model the many over here to here And so what you do is you basically say, oh, there's a lot of these things There's many that go to the one the many that go to the one and then in here You sort of create that manyness that you want to create So it's probably just as easy to look at a sample of this So let's imagine a learning management system Where you're taking a class and there are some people that are teachers and some people that are students and many students are members of many classes A student can be part of many classes and a class has many students in it So you can't really find the one End and so what we do is we make a table called membership and in that table of membership We actually often don't put a primary key in at all. We simply put in two foreign keys And if we're going to put a uniqueness constraint, we put a combination of the two Foreign keys as the uniqueness constraint. So we say There could be duplicate user IDs and duplicate course IDs, but there can only be You know, user ID course ID combinations. That has to be unique. So you can make unique be more than one One column And so if you imagine a course table and a user table, there's a user ID the name and email and the course has a title and an ID and then we have this little table that just is the connector table that shows The points out and so we can expand this membership. So let's take a look at how that works so we're going to create some tables and the These are very classic tables because these are the one end of it. So these are the one end of it So it has a primary key a title a logical key email There's a primary key for course and then there's text. So we add this unique to kind of indicate that it's a logical key We're not going to allow ourselves to put any duplicates in here. Now that the connector database here is A table member and it has two foreign keys user ID and artist course ID And you can each model some data here. So I'm going to model role Which is going to be zero equals student and one equals instructor And then I'm going to indicate that the primary key or uniqueness constraint is the combination of the user ID and a course ID Now when we say the primary key It it both limits our ability to insert duplicates But it also allows the database to optimize it scanning because it knows that that combination Is always unique and so it can organize its disk structure and storage structure To understand how to look things up more efficiently knowing that once it's found a user ID course ID combination It doesn't have to look any farther because they're unique And so all of these contracts that we add speed things up Save storage and makes things more efficient. But in ways we don't always know exactly how they happened And so let's go ahead and make these Let's go ahead and make these guys I think I will start with a new database I'm going to call it LMS for learning management system No, I don't really want to do that one And so I'm going to not create the table. I'm going to do everything in sql And so let me see if it's in my cheat sheet. Nope, that's not in my cheat sheet So I'll have to fix the cheat sheet again for you by the time you see a cheat sheet All these things will be in there. So I'm going to go in here and I'm going to grab create table user Actually, I'm going to grab them all watch this Grab them all highlight all these Go over to sql browser Blast them all in And then I'll put a semicolon at the end of each one of the statements And I want to run them. So did I look does it look good? Yep. Yep. Yep. So I got a course I got membership to foreign keys and I got user so that all looks good Okay, so now we're going to have to insert some data in And we're going to insert from the outside in and so we're going to just put the name and email The id will be automatically assigned for the users And we're going to do the same thing and the id and the courses will be automatically assigned So let me just grab all this stuff Go into sql that has the semicolons at the end already for me. Thank you very much Now I'm going to run it And if I take a look at my data now, I've got primary keys for the courses and I've got Premier keys for the users and I've got nothing in the membership table And I have to I of course have to remember what these values are because Jane is one and Ed is two and Sue is three, right? And Python is one sql is two is three. And so when I go into membership I've got two foreign keys here and a roll and they just have to be for the course person combination And so it's a little tricky to figure all this stuff out But again, these are just numbers and if you look at these numbers user id course id roll Well user id one is in course one user id is in course as the teacher User id two is in course one as the student, etc, etc, etc So I'm making these connections by just putting these little numbers in and once again conveniently I have all my semicolons perfectly in place So I go to sql And then I run that And then I take and I look at my membership data and there it is So two foreign keys and a bit of data modeled at the connection. That's the way we say that the roll is modeled at the connection So now we build all this stuff up. We can write some queries that take a look at this And so what we're going to do is we're going to look at who's in what course and what role are they? And we're going to Sort this in a nice way. So let's just take a quick look at the code of writing We're going to do a select from three tables the username the member roll the course title So we're in effect. We're not showing any of the foreign keys or the primary keys We're going to go from the user table joined to the member table joined to the course table This is pretty easy to write. You know, there are three tables you want to go across the on clause Is also very easy to write right the on clause models each of these connections where the members user id is equal to the user's id and Where the member's course id is equal to the course id So we're going to connect and we're going to concatenate all three of these tables together But we're going to only keep rows that where it matters now that this is not this Roll doesn't participate, but we're going to print that out And we're going to order it by the course title First and then the member roll second and the name third and so let's run that So we've reconnected it So ed's the teacher of the php class sue is the student the php class Jane is the teacher in the python class adds a student and sewer students of the python class Ed's the teacher in the sql class and jane is the student in the sql class and so we have Many people are in there are many students in many classes there and so we have modeled that But we model that with this sort of table And if you look at a piece of software that i've written called sugi, which is a standalone learning management system That's built with learning tools. You will see in anything we're in membership where we have a We have a user table We have a context which is also the course table and then we have a membership table and you look here's these foreign keys It's kind of like that's the many side. That's the one side many to one And so this you know, this is now a an effect of many to many Court between these two But then it's modeled as a series of many to one many to one Relationships and you see this all the time in all kinds of things Where membership or other kinds of things are necessary many to one are many to many so With all that there's so much to learn It's it's both easy and complex at the same time It's easy when someone shows you how to do it. But at some point you will learn how to build database models and you realize Oh, it wasn't so bad. It takes a while to get used to them. Um, this really just as a quick walk what the the bottom line is The what we just did Seems like it was while that's nice Do you really have to do that and the answer is if you're going to scale at all? You absolutely have to because you simply can't read and write data sequentially You can't read through a update one little piece of data in a file by reading all the way through and then writing a new copy the file that could take seconds and In a system like an online system you get a hundredth of a second to do something like that and the databases make it So that happens in a thousandth of a second. So you ultimately you simply have to take advantage of this. You just Can't if you're going to modify it you can read data from flat files But even if you're going to read a lot of data if it's big it slows down terribly So it might seem like there's a trade-off that you could debate whether this is worth it But if you're going to deal with a lot of data, it's you've got no choice. It's really not as much a trade-off as you think So this has been a quick romp through databases. We talked a little bit about indexes There are constraints. We talked a little bit the not null stuff. We've talked about that uniqueness. That's a constraint Another whole area is what's called transactions and that's the locking of little areas So you can read an area then lock it and then update it to make sure no one else reads it And and so they make sure they either get the the version before You looked at it or before you change it or after you change it. And so that's how You make sure that you can't do things having to do with bank account Balances and gets yourself in trouble. So these are a lot of sql. It's really fascinating sql is a fascinating thing to use and learn and performance tune and enjoy So Relational databases are cool. This gets us started The the big thing is don't allow replication vertically of string data pull that out into a separate table Establish a primary key and then have foreign keys that point to that primary key It is not just how much data you store It's sort of a compression way as a way of compressing data You might think strings take no data, but they do numbers take a lot less data and it's both how much data that's stored but also how much data has to be scanned and That way joins work. That's part of the magic of why oracles such as such a successful company It's a bit of art form and it's something that you can work your whole life And always get better at Hello and welcome to our code walk through on the roster code So the the learning objective of this is to do a many to many table And so the idea is 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 have a 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 So 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 we're going to take advantage of that in a moment And so you'll see how we take advantage of that So 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 so we need the json library in the sql library We make a database connection and we get a cursor The connection the cursor is they 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 to so you can have 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 And so this some people would just give this to a unit text file and have you cut and paste this and then go run that In your sql. I 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 roster db dot sql light and if I look where I'm at 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'm going to 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 um 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 roll and I guess this and again This is straight from the lecture um 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 So we're going to Oh, that should be roster data sample. That's okay to oops fix a bug Save that roster data sample And so that's just this file and 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 And so so once we do load s on json We're parsing it and then this is going to be an array of arrays and so For entry in json data So entry is going to be one of these things. So entry itself Is a row So an 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 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 tried 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 an effect Always attempt to insert it and if it's been there once then it's all set And so this insert or ignore is a super powerful mechanism. Um, I use it all the time and We have a uh placeholder in the form of a question mark And then we have so one of these days we'll have uh two things that we're asking for as a 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 uh sql injection So this runs it may or may not insert a new record, but if the this chuck or whomever the name is Is not there it will give us 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 and that primary key is going to be auto and auto constructed for us And 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 Because we're going to fetch one record from the cursor because 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 one in there But that'd be kind of redundant because it is the name is a unique key And then the sub zero just means if there were more than one thing that I was selecting which we'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 a sign Or if we're coming through later for chuck, you know 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 and so part of this works by having both a Logical key and a primary key the primary keys auto generated But the name is a logical key and it's unique And so that's our trick to get get that Assigned thing before we just looked at it in the user interface of escalate 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 And uh, and so we're going to get the user id course id And then what we're going to do is we are going to insert a replace So this is it basically if they're remember that this user id course id combination Is the primary key for this member table If there is a duplicate If this is this combination is already there this becomes effectively an update statement And we have these two number values now. What's missing here is the role is not there Um, 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 is Turns out that 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 If I do python three roster dot pi Hopefully I can hit enter. So you'll notice by the way that this sql light 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's no data So 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 And 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 so 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 It's just going to run it really fast. So I'll 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 it 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 constraint Sush primary key is the combination of these things and I haven't put anything in roll 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 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 roll in but I just wanted to kind of give you a bit of a walkthrough of 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 Or to get the old id you can sort of sort and then insert or replace Which is a combination of a insert and an update So I hope you found this this example useful and can Can apply it and basically create many to many tables We are doing some code walkthroughs if you want to follow along with the code you can download the source code From python for everybody dot the python from everybody website. Okay So the code we're playing with today is twfriends dot py And this is a step beyond the Simple tw spider. It is a restartable spider, but we're going to data model things a little bit differently We're going to have two tables and we're going to have a Many to many relationship except that it's sort of a many to many relationship between the same table, which is okay um friends is a twitter friends are a directional relationship and so uh So we start out here in twfriends dot py remember that the file hidden dot py I'll show it to you, but i'm not going to open it because i've got my keys and secrets in it So this hidden dot py file you got to edit that and you got to go to apps dot twitter dot com and get your keys and put them in there Otherwise these things won't work But if you have twitter and you set your api keys up and you put them in hidden dot py Then all these things will work. It's kind of fun actually and impressive Not hard to do actually So The twitter url. That's my library that reads hidden dot py and augments the url and does all the oauth stuff json and ssl because twitter doesn't i mean because python doesn't accept any certificates even if they're good certificates, so we kind of crush that Here's our friends list that we're going to hit. We're going to make a database friends dot sql light Um now here we're doing create table if not exists So what this really is saying is I want this to be a restartable process and I don't want to lose the data We're starting out We do not have sql light any sql light files And so this is going to create the database and create these tables But the second time we run it we're not going to recreate the tables We're not going to we're going to be able to restart this because we're going to run out of um We're going to run out of a rate limit before we finish this But so we just have to wait however long the rate that template takes to reset and we'll watch the rate limit go down And so we're going to have a people table and we're going to have an idea a primary key in the name The name is going to be unique and whether or not we've retrieved it and that's kind of from a previous one But then there's the who follows who? Um the from id to to id and so this is a direction And we're going to put a uniqueness constraint in just like we do in many to many's that basically says The combination of from id and to id has got to be unique. We don't allow ourselves to Put duplicates of the combination So from id can be one in many records and two id can be one in many records But one one is only allowed once And this is the crud we have to do to convince python to accept the twitter certificate And so this is similar to some of the other stuff that we've done We're going to enter a twitter account or quit and if we hit enter by itself Then we will actually go and retrieve a record that was not yet retrieved And now we're actually pulling out two values id and name And so we will we will grab fetch one is going to give us a two tuple basically And we're going to store that in id and account of course That's like this is this is coming back with a two tuple First of which is the id from the database limit one means we're only going to get one of these Or zero of these if there are zero of these that means there are no un retrieved twitter accounts retrieved equals zero Well, you'll see in a second that all the new Accounts we put in are the ones for which we haven't retrieved and again given that our rate limit We want to know which ones we've retrieved Okay, and And so what we're going to do next is we're going to check to see if the person that We just checked which means the length of the account is greater that we just were entered We're going to check to see if they're already there, okay And we're going to select id from people where name equals. So that's the one we just entered And we're going to fetch one and grab the first thing because we only we only got one thing in the select statement here um And if this person that we just asked to see Uh is not in the table that means this is going to fail We're going to do an insert or ignore this or ignore is kind of redundant because we just checked to see if it was there But we'll put that in just to be safe And we're going to put the name in for as the new the new account that we're looking at And we're indicating that retrieved is zero so that we will we will know that we haven't retrieved it yet You'll see that we'll update that in a second. We commit it so that later selects will see this so that So you got to do the commit This later select wouldn't see the one we just inserted And we're going to ask how many rows were affected and if it's not equal to one Then we're going to complain about we inserted it and we are going to do this thing. We're going to ask. Hey Remember there was an id up there Do do do Right here id integer primary key and we did not insert this here But we want to know what that id is and every time I was showing you that in lectures I was saying it's really easy in python to do this and that's what we're saying This cursor did the insert But one of the things happens is after the insert we're going to grab the last row id Which is the primary key that was assigned by sql Okay, and so that means that one way or another coming through this code here in line 45 one way or another We're either going to know the id of the user that was there before or we just inserted one And so we're going to know the primary key of the current user and you'll see why we need that So id is the primary key of the current user that we entered right here Okay And now we're going to do is do the twitter url augment with the oauth and all the keys and the secrets and hidden dot py Instead we're going to go through let's count 1000 let's go count But what the heck let's go 200 up to 200 friends Save no, let's do 100. We'll keep it that way and then we're going to retrieve it and We're retrieving the account. We're not going to print the nasty url out. We could Then we're going to open the url with the connection and then we're going to read that and we're going to get the utf8 data from this and then we're going to decode that And we're going to have the unicode data so the data string is a internal python string With all that data representing all the wonderful characters And of course, we're going to ask url open to give us back the headers As a dictionary using this call and we can see what the how many we have left For the remaining right what's the remaining rate limit that we have Okay, and so then we're going to parse the data with json load s if uh Oh wait, I need to continue in here continue Okay, save um If we are going to parse this data we'll print it out, right? So that means that this this died which means it's not syntactically correct json basically And who knows if we're ever going to see that but at least when it blows up it'll print this data out We'll have to catch it and then it'll continue. Actually, I'll make this a break Because if that's blowing up that bad we should quit Now We don't I don't yet know what happens when this rate limit says you can't have it And so but I do know that I expect when it's successful that there will be a key of users in this outer dictionary that we're going to get and if this outer dictionary that we're going if we if users is Not in the parse dictionary Then I'm going to dump out this data so that at least I can debug what happens when I've got some broken json, so the difference between um This code this code is going to fail when the json is syntactically bad meaning a curly brace isn't right or whatever This code will trigger when I get good json, but I don't have a user's key in it. Okay So then Once we've retrieved it we we're pretty happy with it We're going to update for our account that we are retrieving. We're going to set this is one of our retrieved accounts Okay, and then what we're going to do is write a loop that goes through all the friends of This particular user that we're asking and gets their screen name prints it out and then we're going to check to see if This one is already in our people database because this is a spider. We're grabbing accounts And uh, and so we'll do a friend id And do a fetch one grab the sub zero thing And if that works if if this person's not in there this fetch one is going to blow up Which means we're going to drop down to the accept code But if it does work, we have friend id is the You know that we they they're there and they're already in our database, right? They just weren't retrieved Okay, and so Now if we the friend id wasn't there we're going to do an insert into setting retrieve to zero And then we're going to commit right now Remember row count is how many rows were affected by this last transaction curve dot row count And we're going to die if that doesn't insert doesn't work. This is unlikely Unless somehow we've ran out of disk drive or something and we're going to grab the friend id as the as the key The last row that was inserted. We're only going to insert one row So it's basically the primary key of the row that we just inserted. So if you look at this code right here It comes out the bottom one way or another with friend id successful Right friend id is either they're already in our database Or they're not and if we insert them then we have it and so now This count new and count old is just so I can print out a nice print out Now we are going to insert into the friend table, which is called the follows table in this case From id and to id those are the those are the two outward outward pointing uh foreign keys And we have the id of the account that we are retrieving the friends of and then this particular friend And so we're inserting the connection from this person to that person And then we commit it we want to commit these again so that later selects when the loop goes back up Later selects get all of that data that's going on Okay, so we do want to commit from time to time and then we close the cursor at the very end. Okay So let's run this and see what happens Okay, so python twfriends Dot py Oh Of course I am a refugee from python 2 so I always forget to type python 3 Okay, so we're going to start If we take a look right now, I'm going to start another tab over here and ls minus l star sql light Now that sql light file is there right and it's actually made the tables if you go up here It ran all this stuff create the tables yada yada, and we're sitting right here at this line As a matter of fact, I think without causing too much trouble. I can open that database And get into this database right here and there is no data in the follows table And there is no data in the people table. It's completely empty. Okay, so we're waiting for the first one And I'll go with mine dr. Chuck So it's retrieving the 100 friends and they all were brand new they're all inserted right Um, and so now if I hit refresh We will see that dr. Chuck has retrieved um Who follows so these are all the people I follow for one follows two So if we look at here, we see that dr. Chuck follows Stephanie teesley because we grabbed the follower as a dr Chuck, you know, we're going to have a record in all of the follows For all the ones that I did right so these are all the people I followed And we put them in So we can go back and We can let's see grab somebody. Let's go grab Stephanie teesley And let's pull out her friends So we grabbed 100 of her folks. I got 14 left. That's my x-ray limit. So I did Stephanie teesley. So let's go back here So you'll notice there's 101. There's probably going to be oh 182 That's interesting. So we've retrieved dr. Chuck and Stephanie teesley And let's go take a look in the friends table the follows table Okay So we have all of the people I follow now all the people Stephanie follows Okay, so there we go. So let's go ahead and do somebody else. Um, let's see. I think we both follow Tim a k. Where's tim a k? Yeah, let's follow tim a k. Let's see what who tim follows see if we can get like an overlap Oh, we revisited some let's see if we can see this in the follows See people So we've got dr. Chuck retrieved and tim a case somewhere down here It might take us a while before we Get any really good overlaps Let's see Let's do a database call Let's see. Let's do a database sql select Count Okay, so let's just run this some more. It's clearly working now. One thing I can do here is I can hit enter And it will just pick one randomly So it grabbed live edu tv and now I can and let's see how many I got left We got 12 left and now I can hit enter again and it picks another one That was the next one. I was kind of picking them in order. Is it picking them in order? Let's go to people Yeah, it's picking these so it's gonna we can see that it's going to just do the first unretrieved person Who's Nancy? Let's let it retrieve Nancy. So it grabbed Nancy new So we're finding some and this table is getting really big and so if we look at the people table, we now have 455 people And we have 467 following records And so there we go Oops Hit enter it does another one And away we go. So you get the idea. I can type quit to finish And just to give you a A little interesting Bit of code to show you how to do selects. I'm going to do this tw join now. You'll notice that we're not talking Oh, let's show you one thing um ls by nacelle friends Star sql light So this database has it so I can restart this process And run it again and the database is still there. And so we just grab swear track um And so we can keep doing this and and so this data it keeps extending and so this is a restartable Restartable process I can run it and then tell it to grab the next unretrieved one. And so away we go right and um So that's part of it. So so I can if I run out of my Uh, I've got eight left. Oh, how many I've left really let's keep going How many do I got left? I got five left Okay, wait. Oh, I guess we'll just run it out. So I got four left You know, I should do is I should I can't change the code I guess I can't change the code I can stop the code and I can quit the code So what I'm going to do is I'm going to change this code a little bit really quick And I'm going to print the headers of rate limiting at the beginning And at the end So now I can run it again. I changed the code. Hopefully didn't make a python error Tell to go get another one and in devarro. And so I got three left We'll see what happens when I run out of rate limit Run out of rate limit So we have one left hit enter Hit ctrl k Open source org so we have zero left that worked Now let's see what happens. I don't know what happens next Oh, we blew up too many requests So we got an ht to be error 429 So that means that Going for mark cuban Uh That was in line 48. So the right thing to do would be in line 48 Um We should really put this in a try try accept blocked Try accept block Because it gives us an error print Oh fiddlesticks How do I print the exception message? I always am forgetting print Failed to retrieve Okay, so we'll put that in Now if I run it And then I have to put a break here because that's not good break Failed to retrieve not got to figure out. Oh, I see I never know how to print out the error message Yeah So I have to I never remember see that's the weird thing about stuff is that I don't ever remember enough I don't remember the syntax what I say here To print the error message out So I'm going to go to google and I'm going to say print out the exception message in python Print out the exception message in python. Oh python 3. Hello Okay, so let's go find it here in the documentation Is this it? Is this what I say? I just want to print out the message Ah, that's it accept Let's try this So this is part of python programming is like for me at least because I'm just not like a genius expert at this stuff This is what thing I like about python Is you can guess stuff and sometimes you guess right so there we go We got the error We got the nice little error message and we see error 429 too many requests So that cleans that up nicely So we are we have run out of requests And on that it is a good Uh good time to to say thanks for listening and uh, I hope that you found this valuable And welcome to our final chapter Retrieving and visualizing data in this chapter. We are going to basically Bring this all together databases web services code loops Logic and and we're going to solve a problem that is a multi-step data analysis We're going to find some data on the internet might be html might be an api or whatever And we're going to write a relatively slow process that's going to Pull data slowly because these are all rate limited. This is a slow and restartable process So you have can be a start this and what we're going to do is we're going to have a database That's going to hold the data that we're pulling and so this might take several days actually If you really have to do it and then you'll build up your data in your database and then What you tend to do is you tend to produce two databases. One is kind of a raw database that You know is you really it's all of its data Columns are aimed at helping you figure out what you've got to retrieve yet and what you haven't retrieved yet So that's kind of a crawling spidering process And then you find that the data is kind of nasty and ugly and you find that before you're going to do any analysis You probably want to clean and process it so you in a lot of these you're going to go from a raw database to a clean one And this is going to be really large and this is going to be really small And and you're going to do this sort of once but slowly and you'll do this as many times you need changing this program Cleaning the data up over and over and over again And then you'll end up with really clean data and that's relatively small And you might run programs that'll loop through this to do visualizations or analysis or some things Or whatever and so you'll actually sort of use this database as a source of information okay, so That's the basic pattern of what we're going to work with now This is what I call personal data mining and if you're going to do this Seriously python is used in lots of data mining activities But if you're going to do data mining seriously with really really large data sets, we're doing small to medium sized data sets As you might do sort of for an individual personal research versus like an organizational research where you're processing The logs of a web server or something like that And there's lots and lots of wonderful technology and what's really cool is This technology just keeps getting better and better because the whole data and mining data analysis Natural language processing field is just so hot right now. It's so awesome we're going to keep it simple and do stuff for ourselves for now and And and I gave you a bunch of sample code It's going to make it so that you can adapt this sample code to solve the problems that you need to solve So like I said, this is more of a programming exercise data mining might be a lot more complex If you're doing simple research, this might actually model what you do pretty well So the first thing that we're going to do is what's called use the google's uh json api for geocoding And uh, there are two versions of this one version requires a key and one version It doesn't require a key Google used to make all this data available for free but with just a rate limit But now they're making increasingly requiring a key So I give you code in this zip file that kind of does both If you really wanted to do something in production of taking User entered