Creating tables, loading data and querying data

Loading...

來自 IBM 的課程

Databases and SQL for Data Science

45 個評分

IBM

Databases and SQL for Data Science

45 個評分

課程 4（共 4 門，Specialization Introduction to Data Science）

SQL is a powerful language which is used for communicating with databases. Before you step into the field of data science, it is vitally important that you set yourself apart by mastering the foundations of SQL. The purpose of this course is to introduce relational database concepts and help you learn and apply knowledge of the SQL language. Learners will also practice building and running SQL queries hands-on using a cloud-based environment as well as learn how to access databases from Jupyter notebooks using Python. If you choose to take this course and earn the Coursera course certificate, you will also earn an IBM digital badge upon successful completion of the course.

從本節課中

Week 3 - Accessing Databases using Python

After completing the lessons in this week, you will learn how to explain the basic concepts related to using Python to connect to databases and then create tables, load data, query data using SQL and analyze data using Python

How to access databases using Python?5:57

Writing code using DB-API4:46

Connecting to a database using ibm_db API2:05

Creating tables, loading data and querying data3:51

Analyzing data with Python9:29

與講師見面

Rav Ahuja
Data Science Program Manager
IBM Cognitive Class
Hima Vasudevan
Data Scientist
IBM Digital Business Group

Hello, and welcome to creating tables,

loading data, and querying data.

After completing this lesson,

you will be able to understand basic concepts related to creating tables, loading data,

and querying data using Python,

as well as demonstrate an example of how to perform these tasks using

the IBM DB2 Warehouse on Cloud database and Jupyter notebooks.

For this example, we will be using DB2 Warehouse as the database.

We first obtain a connection resource by connecting to the database

by using the connect method of the ibm_dbapi.

There are different ways of creating tables in DB2 Warehouse.

One is using the Web console provided by DB2 Warehouse,

and the other option is to create tables from any sequal or our Python environments.

Let's take a look at how to create tables in DB2 Warehouse from our Python application.

Here is a sample table of a commercial trucks database.

Let's see how we can create the trucks table in the DB2 Warehouse using Python code.

To create a table,

we use the ibm_db.exec_immediate function.

The parameters for the function are: connection,

which is a valid database connection resource that is returned from

the ibm_dbconnect or ibm_dbpconnect function, statement,

which is a string that contains the sequel statement,

and options which is an optional parameter that includes

a dictionary that specifies the type of cursor to return for results sets.

Here is the code to create a table called trucks in Python.

We use the ibm_dbexec_immediate function of the ibm_dbapi.

The connection resource that was created passes the first parameter to this function.

The next parameter is the sequel statement,

which is the create table query used to create the trucks table.

The new table created will have five columns,

serial_no will be the primary key.

Now let's take a look at loading data.

We use the ibm_dbexec_immediate function of the ibm_dbapi.

The connection resource that was created is passes the first parameter to this function.

The next parameter is the sequal statement,

which is the insert into query query used to insert data in the truck's table.

A new row will be added to the trucks table.

Similarly, we add more rows to the trucks table using the ibm_dbexec_immediate function.

Now that your Python code has been connected to

a database instance and the database table has been created and populated with data,

let's see how we can fetch data from the trucks table that we

created on DB2 Warehouse using Python code.

We use the ibm_dbexec_immediate function of the ibm_dbapi.

The connection resource that was created is passes the first parameter to this function.

The next parameter is the sequel statement,

which is the select from table query.

The Python code returns the output,

which shows the fields of the data in the truck's table.

You can check if the output returned by the select query shown is correct,

by referring to the DB2 Warehouse console.

Let's look at how we can use pandas to retrieve data from the database tables.

Pandas is a popular Python library that contains

high level data structures and

manipulation tools designed to make data analysis fast and easy in Python.

We load data from the trucks table into a data frame called DF.

A data frame represents a tabular spreadsheet like

data structure containing an ordered collection of columns,

each of which can be a different value type.

Thanks for watching this video.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2018 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取