Explore
For Enterprise
Join for Free
Log In

artes y humanidades
Negocios
Ciencias de la Computación
Ciencia de Datos
Tecnología de información
salud
Matemáticas y Lógica
Desarrollo Personal
Ciencias Físicas e Ingeniería
Ciencias sociales
Aprendizaje de un idioma
Grados
Certificados

Explorar todo lo que Coursera tiene para ofrecer

Explorar
Buscar
For Enterprise
Inicia Sesión
Únete de forma gratuita

15.6 - Inserting Relational Data

Loading...

Using Databases with Python

Universidad de Míchigan

4.8 (12,870 calificaciones) | 170K estudiantes inscritos

Curso 4 de 5 en Python para todos Programa Especializado

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.

Destrezas que aprenderás

Python Programming, Database (DBMS), Sqlite, SQL

Revisiones

4.8 (12,870 calificaciones)

5 stars
10,549 ratings
4 stars
1,914 ratings
3 stars
330 ratings
2 stars
52 ratings
1 star
25 ratings

GB

Jan 14, 2018

It's an excellent course. Thanks for the oportunity to learn. Thanks to Coursera, to the University of Michigan and of course to Dr. Chuck, a very good an enthusiastic teacher! All the best for you!

RS

Mar 06, 2019

This course is well designed to get fast launching in programming & talking to data with python. As the name suggests " Python For Everybody ", courses in this specialization really signifies that.

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.

15.4 - Designing a Data Model13:58

15.5 - Representing a Data Model in Tables11:55

15.6 - Inserting Relational Data9:24

15.7 - Reconstructing Data with JOIN12:11

Worked Example: Tracks.py (Chapter 15)13:58

Impartido por:

Charles Russell Severance
Professor

Transcripción

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.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play

Coursera
Acerca de
Liderazgo
Empleo
Catálogo
Certificados
Certificados MasterTrack™
Grados
For Enterprise
For Government

Comunidad
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
Youtube
Blog de Tecnología

Más
Términos
Privacidad
Ayuda
Accesibilidad
Prensa
Contacto
Directorio
Afiliados