1
00:00:00,000 --> 00:00:02,680
Hello, and welcome to creating tables,

2
00:00:02,680 --> 00:00:04,935
loading data, and querying data.

3
00:00:04,935 --> 00:00:06,730
After completing this lesson,

4
00:00:06,730 --> 00:00:11,390
you will be able to understand basic concepts related to creating tables, loading data,

5
00:00:11,390 --> 00:00:13,800
and querying data using Python,

6
00:00:13,800 --> 00:00:17,780
as well as demonstrate an example of how to perform these tasks using

7
00:00:17,780 --> 00:00:22,735
the IBM DB2 on Cloud database and Jupyter notebooks.

8
00:00:22,735 --> 00:00:27,500
For this example, we will be using DB2 as the database.

9
00:00:27,500 --> 00:00:31,385
We first obtain a connection resource by connecting to the database

10
00:00:31,385 --> 00:00:36,115
by using the connect method of the ibm_db api.

11
00:00:36,115 --> 00:00:40,095
There are different ways of creating tables in DB2.

12
00:00:40,095 --> 00:00:43,775
One is using the Web console provided by DB2,

13
00:00:43,775 --> 00:00:48,885
and the other option is to create tables from any SQL, R, or Python environments.

14
00:00:48,885 --> 00:00:54,020
Let's take a look at how to create tables in DB2 from our Python application.

15
00:00:54,020 --> 00:00:57,465
Here is a sample table of a commercial Trucks database.

16
00:00:57,465 --> 00:01:02,790
Let's see how we can create the Trucks table in the DB2 using Python code.

17
00:01:02,790 --> 00:01:04,295
To create a table,

18
00:01:04,295 --> 00:01:09,140
we use the ibm_db.exec_immediate function.

19
00:01:09,140 --> 00:01:12,170
The parameters for the function are connection,

20
00:01:12,170 --> 00:01:15,570
which is a valid database connection resource that is returned from

21
00:01:15,570 --> 00:01:22,150
the ibm_db.connect or ibm_db.pconnect function statement,

22
00:01:22,150 --> 00:01:24,890
which is a string that contains the SQL statement,

23
00:01:24,890 --> 00:01:26,550
and options which is an optional parameter that includes

24
00:01:26,550 --> 00:01:31,885
a dictionary that specifies the type of cursor to return for results sets.

25
00:01:31,885 --> 00:01:35,810
Here is the code to create a table called Trucks in Python.

26
00:01:35,810 --> 00:01:44,285
We use the ibm_db.exec_immediate function of the ibm_db api.

27
00:01:44,285 --> 00:01:49,550
The connection resource that was created is passed as the first parameter to this function.

28
00:01:49,550 --> 00:01:51,255
The next parameter is the SQL statement,

29
00:01:51,255 --> 00:01:55,250
which is the create table query used to create the Trucks table.

30
00:01:55,250 --> 00:01:58,285
The new table created will have five columns,

31
00:01:58,285 --> 00:02:02,550
serial_no will be the primary key.

32
00:02:02,550 --> 00:02:04,930
Now let's take a look at loading data.

33
00:02:04,930 --> 00:02:12,030
We use the ibm_db.exec_immediate function of the ibm_db api.

34
00:02:12,030 --> 00:02:16,375
The connection resource that was created is passed as the first parameter to this function.

35
00:02:16,375 --> 00:02:19,910
The next parameter is the SQL statement,

36
00:02:19,910 --> 00:02:23,345
which is the insert into query used to insert data in the Trucks table.

37
00:02:23,345 --> 00:02:26,930
A new row will be added to the Trucks table.

38
00:02:26,930 --> 00:02:34,890
Similarly, we add more rows to the Trucks table using the ibm_db.exec_immediate function.

39
00:02:34,890 --> 00:02:36,320
Now that your Python code has been connected to

40
00:02:36,320 --> 00:02:41,080
a database instance and the database table has been created and populated with data,

41
00:02:41,080 --> 00:02:44,040
let's see how we can fetch data from the Trucks table that we

42
00:02:44,040 --> 00:02:47,885
created on DB2 using Python code.

43
00:02:47,885 --> 00:02:55,820
We use the ibm_db.exec_immediate function of the ibm_db api.

44
00:02:55,820 --> 00:03:00,560
The connection resource that was created is passed as the first parameter to this function.

45
00:03:00,560 --> 00:03:03,175
The next parameter is the SQL statement,

46
00:03:03,175 --> 00:03:05,465
which is the select from table query.

47
00:03:05,465 --> 00:03:07,975
The Python code returns the output,

48
00:03:07,975 --> 00:03:11,250
which shows the fields of the data in the Trucks table.

49
00:03:11,250 --> 00:03:15,315
You can check if the output returned by the select query shown is correct,

50
00:03:15,315 --> 00:03:18,370
by referring to the DB2 console.

51
00:03:18,370 --> 00:03:23,290
Let's look at how we can use pandas to retrieve data from the database tables.

52
00:03:23,290 --> 00:03:26,520
Pandas is a popular Python library that contains

53
00:03:26,520 --> 00:03:28,730
high level data structures and

54
00:03:28,730 --> 00:03:34,660
manipulation tools designed to make data analysis fast and easy in Python.

55
00:03:34,660 --> 00:03:38,710
We load data from the Trucks table into a data frame called DF.

56
00:03:38,710 --> 00:03:40,670
A data frame represents a tabular spreadsheet like

57
00:03:40,670 --> 00:03:43,800
data structure containing an ordered collection of columns,

58
00:03:43,800 --> 00:03:46,255
each of which can be a different value type.

59
00:03:46,255 --> 00:03:49,070
Thanks for watching this video.

60
00:03:49,070 --> 00:03:54,000
(Music)