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.1 Relational Databases

Loading...

Using Databases with Python

Universidad de Míchigan

4.8 (12,892 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,892 calificaciones)

5 stars
10,567 ratings
4 stars
1,916 ratings
3 stars
332 ratings
2 stars
52 ratings
1 star
25 ratings

JG

Jun 17, 2016

This course was great! I enjoyed parsing different types of data: from documents and the web. This course is definitely (rudimentary) a must take course for anyone who wants to go into data science.

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

Basic Structured Query Language

We learn the four core CRUD operations (Create, Read, Update, and Delete) to manage data stored in a database.

15.1 Relational Databases13:49

15.2 - Using Databases12:27

15.3 - Single Table CRUD16:35

Worked Example: Counting Email in a Database13:38

Impartido por:

Charles Russell Severance
Professor

Transcripción

So now we're going to move into talking about databases.

And it may seem a little strange to be talking about databases in a programming

language, but we are using this programming language for

doing data analysis.

And so it turns out that it's quite often necessary when you're pulling data,

especially data you're pulling over a network, where you might be rate limited,

or you want to store the data in a database.

You have a process that reads the data and puts it in a database, and

then you can analyse it out of the database.

And it makes some of your processes go really fast.

It allows you to change your analysis, and

not lock your analysis into the retrieval of the information.

So it speeds up your overall workflow, but you have to learn a bunch of stuff so

that you can put the information into your database.

Now, the first task that you have is to install your database browser.

There's a couple of different ways to read and write these files.

We're going to use Python to read and write the files, but

we want a way to read and write the files directly.

And so this is code that you can download for the Mac, for Windows, for Linux.

There's also a Chrome plugin that you can get for SQLite,

that you could do everything that we're doing in the class.

And so I don't really care how you achieve the things you're going to achieve in

the class, because we're going to grade you, not so much about what, how you use things.

But instead, we're going to look at, you know, the databases that you produce, okay?

So stop now, download this, install it.

It should be simple, should be easy.

Check with the forums if you have some problems.

So relational databases are a whole subfield of computer science.

And it might be best to talk about what life was like before relational databases,

and you use relational databases thousands of times a day without even knowing it.

If you imagine something like a learning management system,

where there's hundreds of thousands of users, and terabytes or petabytes of data,

and you log in, and within a half a second it shows you what you're supposed to see.

You can't read a petabyte of data in a half a second, you just can't.

And in the early days, we used to have data that was small

and computers that didn't have a lot of storage in them.

So we tended to use tapes and

we would put the data like on a magnetic tape, and we would sort the data.

We would have old bank balances on one tape, we would have the transactions,

and then we would read one balance, check to see if that transaction had changed,

then we would store the balance.

And you would have like last night's bank balances, and tonight's bank balances.

And then the next day, you would put this over here, and

read the old bank balances and make changes.

And this is how, day after day, in the 60s and the 70s, we would update data.

If you look at old computer videos, you'll see these little spinning things.

Well those are tape drives, and

that's where most of the real data was stored, and it was.

But as computers got faster, as they got more memory, and

as we started to store data more in the memory of computers and

on the disk drives a whole different way, so

you didn't have to read to get through the account where it starts with the letter s.

You didn't have to read through all the data from a through s just to get to

my account that starts with s.

And you'd be like oh, wait a second.

We have a disk drive that we can skip all the way and skip back and skip over here.

And so the problem became how to make sure of this random access medium in

which we can store data in a way that's efficient and fast and clever.

So just because you could randomly access the data didn't mean it was fast.

You still had all the data to look at and if you just looked at it sequentially,

so relational databases were this applying cleverness

to how we would use random access data storage, mostly disk drives that spun.

And it really sort of emerged in the 60s and

the 70s, and whole companies were formed like Oracle.

Oracle exists because smart people figured this problem out

before other smart people did.

And so they got to form a company and

Oracle's the leading database vendor on the planet and much of its revenue

comes from its database product and things built on top of its database.

But literally before 1960, the concept of database really wasn't an idea.

It was something that happened as storage and what we wanted to do with computers.

So this is where, if you had to really read a tape to log in to a computer

it might take four hours to log in.

Which would be unacceptable in this modern day and age.

As I mentioned, the database is sort of this technology that emerged and

it emerged from a lot of theoretical analysis and the

underlying foundations of database have to do with some really powerful mathematics.

And so there's powerful mathematics and it still is present

in some of the terminology that some people use to describe databases.

And so there's really kind of two parallel terminologies that you will encounter.

You'll pick a book up and you'll kind of be able to read it and say oh,

this is using the highfalutin hoity-toity language, which is the more math-oriented.

So in the more math-oriented, we use the words relation, tuple, and attribute.

That's kind of the fancy way of speaking about it.

But sort of we programmers who just do our thing,

we would call it a table, a row, and a column.

Now, table and row and column is kind of the wrong way to think about it

if you're trying to understand the true underlying amazing mathematics.

The underlying mathematics, don't worry about the underlying mathematics.

Just be aware as you're reading,

don't be surprised when people lapse into this more fancy nomenclature.

So the idea is that you model data at a connection point rather than like,

here's data and we're starting here and we're reading through it.

The idea is if you model everything as a connection, like who a person is.

A person is a connection between this, that, and the other thing.

And so this notion of modeling stuff at a connection

is the underlying math that makes databases fast, but

when we programmers think about it we kind of think about it as rows and columns.

And so here's just a screenshot of a spreadsheet that I made.

Along the bottom you see the names of the various subsheets,

tracks, albums, artists, genres, and titles.

And those are like the database tables.

And then each table, if you select it, has a row and a column.

So it's got these columns.

This has three columns in it and it has a bunch of rows.

The other thing, when you're doing stuff in a spreadsheet.

You just kind of come up with a data model, a schema, a strategy so

that you know that the first column is always the title, the second column is

the rating, because if you didn't label it, it would make no sense.

So we often do something where the first row of a spreadsheet

is kind of metadata about the columns, right?

Now, the spreadsheet doesn't exactly know that you're doing it.

Even though some of the things you do, like sometimes it sorts and says, oh,

check this tickbox to say the first row is title, so don't sort it.

So when you sort stuff you only sort like the bottom part of it, right?

And so it sort of knows it's the title.

But in a database this becomes what we call the schema.

All this, all these titles and

we have rules like these have to be integer numbers, this has

to be an integer number, this has to be string no more than 128 characters.

So we make strong contracts about the content that sort of at some level feels

very similar to what we´re doing when we´re writing these titles.

But in reality, as you'll see, it is far more complex and far more intricate.

Now, if you've ever tried to do something like we're going to do with this database,

and that is categorize your music collection, you find that a spreadsheet is

a really hard way to categorize your music collection because of so

much repeated data.

And we'll figure that out in databases.

So the idea that they came up with in the 60s and

the 70s is there was a way to represent data on a disk.

And it could be randomly accessed and

there was pointers that pointed to something else, and whatever.

And in the early days they figured out how to technically solve fast access

to lots of data by hopping in various ways, indexing, doing clever things.

And the early ways that we programmed these databases is we sort of revealed to us

application programmers, the low-level capabilities of read this thing,

then jump to this other thing, then read this other thing,

then jump to the other thing, read this thing, jump to this thing.

Which made our lives really difficult.

And so we had to write really sophisticated programs, but

when we did they could function very rapidly.

So what happened over time is instead of our code talking directly to the files or

the databases,

instead, a layer started to build up

that we call the database application, database.

I'm not doing a very good job drawing, right?

So here's our database, and it has lots of complexity in it.

And originally, we sort of just talked straight to that complexity, but

after a while we would have a database application.

And then our application, our code, would talk to the database application, and

the database would know all the magic stuff.

And it was what we call in computer science an abstraction,

which means that our job, this is us, and

our job was easier because the complexity could all be hidden here.

And so the question becomes, then

how do we talk to this really powerful piece of software that we call a database?

What is living here?

What is the way we communicate?

Sometimes we would call this an API, application program interface.

So this is our application.

This is actually another application, the database itself is an application.

And so we have to have an interface so

that our application can talk to the other application.

And just like in the previous lecture this is like a service.

It's taking care of it.

And ultimately, let me change the color here.

It's getting kind of messy, we can sort of think of this thing right here,

both the data and the software that understands the shape of the data,

as just a service in a service oriented architecture.

So once again, what we have to do is define, to use the service

oriented architecture, the cut point.

What is the cut point between our application and this magic stuff?

And this is Oracle. Right?

So it's lots and lots of money.

And it's amazing, it's just really good software. Right?

Oracle is really good.

Microsoft is really good.

So what happened was, we decided to create a standard at this point.

The industry did.

The folks got together with the National Institute of Standards and Technology,

NIST, and they agreed on a language

that was the API between an application and a database system.

And the name of that thing they came up with was SQL, or

the Structured Query Language, okay?

And so that means that, wow, I've made such a mess of this,

I have to start over again and draw another picture.

Right?

So that means that over here you could have Oracle.

Over here you could have Microsoft.

And here you could have APP1.

And here you could have APP2.

And APP1 could talk to Oracle, or it could talk to MySQL.

Or APP2 could talk to Oracle, or APP2 could talk to MySQL.

And so because they use the same communication between Oracle and

MySQL, that means that you can write an app that's portable

between different databases.

And, it turns out that this SQL is a beautiful language,

it's just a gorgeous language.

The reason I don't teach SQL as the very first programming language is you

would be ruined.

If you learned SQL you would never want to program in any other language.

I think it's the most beautiful language that I've ever encountered.

It's beautiful because of its simplicity and its expressiveness, and

its power, but then it's a very beautiful and elegant language.

The problem with SQL is, it depends on the data being pretty.

It's a great way to write code about really gorgeous data.

So part of what we're going to do is learn how to make the data look really good,

and then write really cool stuff.

Python, on the other hand, is a little rough around the edges.

But it also has no problem dealing with unstructured data, or

data that is imperfect, or whatever.

And you can just keep writing more Python.

And eventually you're like oh man I thought it wasn't as complex at this.

I got to add a little bit of if statement here and

add another if statement there and do another thing.

So Python handles unstructured rough data much better than databases and

that's why Python plus SQL is such a powerful thing.

Python cleans up the data.

SQL is a great way to store and retrieve data.

So what SQL does is it has four basic functions.

We call it CRUD.

Create, read, update, and delete.

Update is missing here.

CRUD.

So that's a database term.

It's CRUD. Create, read, update, and delete.

So up next, we're going to talk about how you as the application developer are going to

interact, both in large projects and in small projects.

Small projects are going to be the more typical thing that we use in this class.

So that's what we're going to talk about in a bit, and

how you use software to interact with databases.

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