1
00:00:00,600 --> 00:00:02,210
- [Instructor] Now so
far we've been looking

2
00:00:02,210 --> 00:00:06,070
just at selecting existing
data from a database,

3
00:00:06,070 --> 00:00:07,480
next up we're going to look at

4
00:00:07,480 --> 00:00:10,440
inserting new data into a database,

5
00:00:10,440 --> 00:00:12,210
and to do that we're going to be

6
00:00:12,210 --> 00:00:14,990
working with from the sqlite3 module,

7
00:00:14,990 --> 00:00:18,020
a cursor object, and that's the class

8
00:00:18,020 --> 00:00:21,040
that we will use to
interact with the database

9
00:00:21,040 --> 00:00:24,450
to insert new data, update existing data

10
00:00:24,450 --> 00:00:27,650
and delete data from the database as well.

11
00:00:27,650 --> 00:00:29,480
So the first thing that we need to do

12
00:00:29,480 --> 00:00:31,520
in order to obtain the cursor object

13
00:00:31,520 --> 00:00:34,610
is get it from our connection object,

14
00:00:34,610 --> 00:00:37,010
so we'll first obtain the cursor,

15
00:00:37,010 --> 00:00:41,130
and then the cursor object
has an execute method

16
00:00:41,130 --> 00:00:45,780
to which you can pass structured
query language statements

17
00:00:45,780 --> 00:00:48,220
that insert data into the database,

18
00:00:48,220 --> 00:00:49,900
update data in the database

19
00:00:49,900 --> 00:00:53,040
or remove data from the database as well.

20
00:00:53,040 --> 00:00:56,680
So as you can see here,
we're in this case,

21
00:00:56,680 --> 00:00:59,670
going to insert into the authors table

22
00:00:59,670 --> 00:01:02,180
a new first name and last name,

23
00:01:02,180 --> 00:01:04,500
and the values we're going to use for that

24
00:01:04,500 --> 00:01:08,070
are Sue and Red respectively.

25
00:01:08,070 --> 00:01:10,060
Notice we're not saying anything about

26
00:01:10,060 --> 00:01:13,037
the ID number because that is going to be

27
00:01:13,037 --> 00:01:15,260
an auto incremented value,

28
00:01:15,260 --> 00:01:18,010
so up to this point we have five authors

29
00:01:18,010 --> 00:01:20,531
in the database but
now if we go and select

30
00:01:20,531 --> 00:01:24,790
the ID, first and last
name for every author

31
00:01:24,790 --> 00:01:27,430
from the authors table we can see

32
00:01:27,430 --> 00:01:29,820
that we now have six IDs and that

33
00:01:29,820 --> 00:01:34,650
our new author Sue Red was
auto incremented to number six

34
00:01:34,650 --> 00:01:36,700
because up until this point we've only

35
00:01:36,700 --> 00:01:40,050
dealt with five authors so far.

36
00:01:40,050 --> 00:01:42,580
Now by the way, if we were to delete

37
00:01:42,580 --> 00:01:44,810
a record from this table that does not

38
00:01:44,810 --> 00:01:47,900
modify any of the existing author IDs

39
00:01:47,900 --> 00:01:51,395
and the next insert will
still be the ID seven

40
00:01:51,395 --> 00:01:53,413
no matter what at that point.

41
00:01:55,650 --> 00:01:58,660
Now one quick note about strings

42
00:01:58,660 --> 00:02:00,450
that contain single quotes,

43
00:02:00,450 --> 00:02:03,750
so as you've seen up above SQL delimits

44
00:02:03,750 --> 00:02:05,690
strings with single quotes,

45
00:02:05,690 --> 00:02:09,140
so if you have a name like O'Malley

46
00:02:09,140 --> 00:02:12,320
with a quote in it, you
have to use two quotes

47
00:02:12,320 --> 00:02:15,853
in that position to act
as an escape sequence

48
00:02:15,853 --> 00:02:20,073
in the context of a SQL query of any kind.