This course will introduce students to the basics of the Structured Query Language (SQL) as well as basic database design for storing data as part of a multi-step data gathering, analysis, and processing effort. The course will use SQLite3 as its database. We will also build web crawlers and multi-step data gathering and visualization processes. We will use the D3.js library to do basic data visualization. This course will cover Chapters 14-15 of the book “Python for Everybody”. To succeed in this course, you should be familiar with the material covered in Chapters 1-13 of the textbook and the first three courses in this specialization. This course covers Python 3.
15.6 - Inserting Relational Data
Loading...
Del curso dictado por University of Michigan
Using Databases with Python
9877 calificaciones
De la lección
Data Models and Relational SQL
In this section we learn about how data is stored across multiple tables in a database and how rows are linked (i.e., we establish relationships) in the database.
Conoce a los instructores
Charles SeveranceAssociate Professor
School of Information
So before lunch we developed a logical model,
after lunch we developed a physical model.
And sort of implemented it in the database and then we went home for the night.
And now it's the next morning and it's time to insert some data into our tables.
Now, you've got to remember all the keys and fields and stuff like that.
Just like we're inserting data from kind of like the outward simple things
to the more central ones, we're going to do the same thing when we insert data.
Okay?
And so we're using the INSERT INTO.
And this is a good time to point out that the keywords inside of SQL like
insert into, the case doesn't matter.
The names of the tables, the names of the columns, may or may not matter, and
the actual data you put in that's case sensitive because that's your data.
So I'm going to type INSERT INTO Artist.
And the key thing that is important here is we're not specifying the id field.
So if you recall, in the Artist table we have an id field and a name field.
But if you look over here, the id field is an auto-generated field.
It's not really showing up very well because we don't have a type.
So the id field is auto-generated.
So when I write this SQL
I don't need to specify the id because as part of our contract we made a contract
with a database that says, you assign that.
So I'm going to run this now, INSERT INTO Artist with a name of Led Zeppelin.
And if then we Browse Data and we go look at the artist, it's added the number 1.
Kind of predictable. You can kind of guess where the next one's going to be,
so the next one thing we're going to do is we're going to insert another artist in,
we're going to insert AC/DC in.
And, we inserted that.
Now, if we go take a look at the data again, we got 2.
And if we did a new record here, it would automatically provide this for us, right?
I mean, it would let us change this, but it would want to pick the next number 3.
So what we've done here is both inserted the record,
but we've also established for within this database a new number.
1 means Led Zepplin, and 2 means AC/DC. Okay?
So then, the next thing we're going to do is insert in the Genre,
kind of working outward in, and insert the value Rock and
the value Metal, and now I'm going to show you a little trick here.
You can actually, in this user interface of most of these things,
not just this one, you can usually put more than one command in as long as you
put a semicolon at the end of it.
So I'm going to, if I did that right, semicolon at the end of it.
So I'm going to do an INSERT INTO Genre of Rock and
an INSERT INTO Genre of Metal and I'm going to run the SQL.
Oh, I wonder if I did it twice.
I did.
So I'll just delete this record.
Good bye.
Delete that record.
Good bye.
So I did two inserts and I hit the botton twice, so
that's how I ended up with two copies of it.
So Rock for the rest of this lecture is the value 1 and Metal is the value 2.
And again now when we're making the Track table we don't have to put Rock, Rock, Rock,
Rock, Rock, we just put 1, 1, 1, 1, 1.
Because 1 is not a string but it's a number, that turns out to be okay.
So now we're going to take a look at the album and
this is the first situation where we've got to do a primary key, a foreign key, right?
So we got an Album table.
And the Album has a primary key which is going to be automatically generated for us.
And a foreign key called artist_id.
Because the album points to the artist.
And we only add data at the starting point of these arrows and so
that's what we're doing.
So this one we have to put in title but we don't have to put in id so we don't have
to put that in but we do have to explicitly put in artist_id. So when you insert
into Album you're going to have a title and then we're going to have an artist_id.
This number we have to remember that 2 was AC/DC and 1 was Led Zeppelin.
Now when we're doing this by hand it's a little tricky and
you find yourself flipping back and forth to remember like,
oh which one was Led Zeppelin, which one was AC/DC?
And it seems like in the short term it'd be easier just to put the string in, but
that's the whole thing we're trying to avoid. Okay?
So for foreign keys we have to put the numbers in explicitly
because we or the code we write has got to remember them.
So remembering what these numbers are, the 2 and the 1, is a lot easier for
a program to remember than it is for you to remember doing this by hand.
Now normally you wouldn't insert this stuff,
you would have code that inserts it, and it's really easy for it to say,
oh I just inserted Genre, and just I inserted Rock, and I got back a 2.
So actually you can get that, and so you the human don't have to remember this.
So let's run these.
This is our first one that's doing,
and I'll put a semicolon here so we can do it all at once.
And I'll only push the button once.
Okay, and if we do Browse Data and we look at the Album,
the id was auto-generated and artist_id was something that we chose and
title was something that we put in, so now we're in pretty good shape.
Now the last thing we're going to do is probably the most complex.
But it's not.
We have and id's come in for free, title is data,
the length, rating, and count, that's just data.
And then we have two arrows, right, pointing to Album and Genre.
Album and Genre, and so we have two foreign keys.
Now here is where we have replication now.
Those two are going to the same album, and these two are going to the same album.
Same genre, same genre.
It's okay to have replication as long as they're numbers.
That's really the essence of what we've done.
We just like went in a big circle.
Took replicated strings and turned them into replicated numbers.
So these are okay.
And so then what you're going to do,
is you're just going to take each of these rows that you know.
Well, we don't need that.
Clear.
Take each of these rows, and you know most of the data.
We're going to put in the title, we're going to put in the album_id, the length,
the rating, the count.
Put all those things in
explicitly, this comes automatically, and we're going to do that.
title, rating, length, count, album_id, genre_id.
And then we have the values, and then these are the two foreign key values.
And they point to those rows.
And again this is the point in time where you sort of have written down on a piece of paper
what these little numbers were if you were doing it by hand.
It won't be too bad.
I luckily have them cut and pasted, so they are all easy for me.
And if you are following along, then you just cut and paste these guys.
And I'll be super lazy, and I'll copy all four SQL statements, and
run over here, paste them in, and then I am going to put a semicolon,
carefully, not on each line, but at the end of the values thing,
So I'll put a semicolon on each of these guys.
Forgot to put a semicolon on the last one.
And I'm going to hit the run, and I'll look at the data.
And I'll look at the Track data.
And there we are.
It's all there.
We can kind of move this over.
And that's the Track data and again we specified all those things.
So if you look at all these tables, we've constructed them outwardly in.
We started like at the Genre and the Artist and worked our way in so
that we could establish those numbers so then we could link
to those numbers as we put things in the Album and then we established numbers.
And so the foreign keys are the starting points, the primary keys the ending points
and we worked in. And so we have replication of numbers in this column but
we don't have replication of strings.
So the word Rock, if you look at all this data.
Okay that's a bad example, forget that.
The word Metal is better.
That doesn't count, that's just a title, that's not really the word Rock.
The word Metal as a genre only appears once in the entire
database.
And the thing about it is like what if there were a million of these, right?
One million.
You say okay, what's the difference?
The word metal verses whatever.
Versus the number 2.
If there's millions of them it's not just that it takes up storage,
it's it has to do with how much has to be scanned.
So part of what you're trying to do in databases is reduce the amount of data that
has to be scanned to get to a particular piece of information.
And so the difference between Metal, which might actually be 128 characters, and
a number, which is actually four characters, that turns out to be different. And it's not that
we can't start on disk drives. Disk drives are big. It's just that we can't scan past
it fast enough and we can't built indexes so we can jump past it even faster, okay?
So that's what we have achieved in this section,
is we've inserted all these data and we've linked things together and
we've modeled relationships and connection points rather than replicating data.
Explora nuestro catálogo
Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.
Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.
© 2018 Coursera Inc. Todos los derechos reservados.
