1
00:00:00,000 --> 00:00:01,990
There are many database choices available for Python.

2
00:00:02,000 --> 00:00:06,990
For our purposes, we're going to be using SQLite3 for a number of reasons.

3
00:00:07,000 --> 00:00:09,990
First of all, SQLite3 comes with Python.

4
00:00:10,000 --> 00:00:12,990
If you have Python installed and you're watching this course, then you have

5
00:00:13,000 --> 00:00:16,990
SQLite3 and that makes it easy for us for the purposes of teaching.

6
00:00:17,000 --> 00:00:20,990
SQLite3 is a perfect choice for a lot of applications.

7
00:00:21,000 --> 00:00:25,990
For most of the web site work that I do these days, I'm using SQLite3 instead of

8
00:00:26,000 --> 00:00:27,990
MySQL which I might have used a few years ago.

9
00:00:28,000 --> 00:00:31,990
It's reliable, it's simple, it doesn't require a separate database engine,

10
00:00:32,000 --> 00:00:36,990
it's self-contained, server less, zero configuration and fully transactional.

11
00:00:37,000 --> 00:00:40,990
It's a fully capable database engine in a driver and that makes it just

12
00:00:41,000 --> 00:00:42,990
incredibly easy to use.

13
00:00:43,000 --> 00:00:46,990
For our purposes, having something that's simple like this allows me to show

14
00:00:47,000 --> 00:00:51,990
you how databases work with Python without having to mess with the database a

15
00:00:52,000 --> 00:00:53,990
whole lot and spend our time in the Python.

16
00:00:54,000 --> 00:00:58,990
So, I'm going to go ahead and start by making a working copy of databases.py.

17
00:00:59,000 --> 00:01:02,990
Call it databases-working.py. Go ahead and load that up.

18
00:01:03,000 --> 00:01:07,990
You see our first line here says import sqlite3 and that simply imports the

19
00:01:08,000 --> 00:01:10,990
Python Library that supports SQLite3.

20
00:01:11,000 --> 00:01:20,990
And then here all we do is we say db = SQLite3.connect and the name of the file, test.db.

21
00:01:21,000 --> 00:01:25,990
And we save this and run it and we have created a database.

22
00:01:26,000 --> 00:01:29,990
We'll go ahead and refresh this file system here because Eclipse doesn't do that

23
00:01:30,000 --> 00:01:33,990
for us and you see that we now have empty file and we've created our database.

24
00:01:34,000 --> 00:01:37,990
So, we'll go ahead and populate the database and that's also very simple.

25
00:01:38,000 --> 00:01:44,990
We simply say db.execute and I'm going to give it some SQL here and say drop

26
00:01:45,000 --> 00:01:50,990
table if exists so that we can create a new table each time and call the table test.

27
00:01:51,000 --> 00:01:58,990
And then db.execute, create table tees, and give it a couple of fields.

28
00:01:59,000 --> 00:02:05,990
t1, it's a text field and i1 is an int and I've created a table inside my database.

29
00:02:06,000 --> 00:02:15,990
Now I'll just insert a little bit of data. db.execute insert into test (t1, i1,

30
00:02:16,000 --> 00:02:21,990
values (?, ?) and these are placeholders and that allows us to give it a

31
00:02:22,000 --> 00:02:26,990
tuple with the values one and 1.

32
00:02:27,000 --> 00:02:33,990
And I'll just make a few of these. Two and give that a 2.

33
00:02:34,000 --> 00:02:35,990
And a 3 and a 4.

34
00:02:36,000 --> 00:02:43,990
And 4 and a three and we have now inserted some data into our database.

35
00:02:44,000 --> 00:02:49,990
So this is all done with SQL and we'll say db.commit() because SQLite is a

36
00:02:50,000 --> 00:02:50,990
transactional database.

37
00:02:51,000 --> 00:02:54,990
It will buffer these values, in case you're going to be using it in

38
00:02:55,000 --> 00:02:55,990
a transactional mode.

39
00:02:56,000 --> 00:03:04,990
And then I say cursor = db.execute and select star from test order by t1.

40
00:03:05,000 --> 00:03:11,990
Again, standard SQL for row in cursor: print(row).

41
00:03:12,000 --> 00:03:15,990
So, now, I've inserted some data into the database and I'm going to print that

42
00:03:16,000 --> 00:03:16,990
data out from the database.

43
00:03:17,000 --> 00:03:20,990
Save it and run it and there we have it.

44
00:03:21,000 --> 00:03:25,990
There's our four records and they're sorted by the t1 field ,which is our text

45
00:03:26,000 --> 00:03:27,990
field, and that's getting read from the database.

46
00:03:28,000 --> 00:03:29,990
So, that's all there is to it.

47
00:03:30,000 --> 00:03:33,990
Using SQLite in Python is incredibly simple.

48
00:03:34,000 --> 00:03:37,990
Our first line here connects to the database and that actually creates the file

49
00:03:38,000 --> 00:03:39,990
if the file didn't already exist.

50
00:03:40,000 --> 00:03:44,990
And then, from there on, we're using db.execute because db is the database

51
00:03:45,000 --> 00:03:48,990
object that we got back from the connect statement and we simply interact with

52
00:03:49,000 --> 00:03:50,990
the database using SQL.

53
00:03:51,000 --> 00:03:56,990
Be sure to do a commit after you change any data in the database and then we can

54
00:03:57,000 --> 00:04:02,990
do a select and use the cursor object that's returned by db.execute and simply

55
00:04:03,000 --> 00:04:06,990
step through the cursor object as an iterator and print the data.

56
00:04:07,000 --> 00:04:11,990
The data comes back, you'll notice, in tuples and they're in the order that you

57
00:04:12,000 --> 00:04:13,990
specify things in your SQL.

58
00:04:14,000 --> 00:04:19,990
So, if I want it in the i1 first followed by t1, I simply change my SQL so that

59
00:04:20,000 --> 00:04:23,990
specifies an order, because the order that was in was the order that we define

60
00:04:24,000 --> 00:04:25,990
them in the create table text and then int.

61
00:04:26,000 --> 00:04:30,990
So, if I want int and then text, I simply change in my SQL and it'll come back

62
00:04:31,000 --> 00:04:33,990
in that order and obviously if I want to order it by the integer instead of

63
00:04:34,000 --> 00:04:38,990
by the text, I simply change my SQL and it comes ordered by the integer

64
00:04:39,000 --> 00:04:39,990
instead of by the text.

65
00:04:40,000 --> 00:04:44,990
There's one more thing I'd like to show you in this context and that is the row

66
00:04:45,000 --> 00:04:45,990
factory that comes with SQLite.

67
00:04:46,000 --> 00:04:51,990
The SQLite interface is actually incredibly rich and very full featured in

68
00:04:52,000 --> 00:04:58,990
Python and there is a lot of options and a lot of methods that you can override

69
00:04:59,000 --> 00:05:02,990
and a tremendous amount of power there for working with databases.

70
00:05:03,000 --> 00:05:05,990
We are going to keep it simple for our purposes here but there is one thing that

71
00:05:06,000 --> 00:05:07,990
I want to show you and that's what's called a row factory.

72
00:05:08,000 --> 00:05:15,990
We'll say db.row_factory = sqlite3.Row.

73
00:05:16,000 --> 00:05:22,990
So, what the row factory does is it allows you to specify how rows will be

74
00:05:23,000 --> 00:05:26,990
returned from the cursor and the built-in row factory that's provided,

75
00:05:27,000 --> 00:05:32,990
sqlite3.Row, is very powerful and very suitable for most purposes.

76
00:05:33,000 --> 00:05:36,990
So, when I save this and run it, you'll notice the only change I made was to add

77
00:05:37,000 --> 00:05:39,990
that row factory there after the connect.

78
00:05:40,000 --> 00:05:45,990
You'll notice now we get row objects instead of those tuples and the row objects

79
00:05:46,000 --> 00:05:51,990
can be looked at as tuples if we'd like or it can be looked at as dictionaries,

80
00:05:52,000 --> 00:05:54,990
which I find particularly useful.

81
00:05:55,000 --> 00:06:00,990
So, if I say dictionary like that, I'm creating a dictionary object based on a

82
00:06:01,000 --> 00:06:03,990
iterable because row is an iterable.

83
00:06:04,000 --> 00:06:08,990
So, if I save this and run it, now I get dictionary objects and so they

84
00:06:09,000 --> 00:06:10,990
are completely indexed.

85
00:06:11,000 --> 00:06:17,990
If I want to, I can say, rows up t1 and I'll get my t1 objects.

86
00:06:18,000 --> 00:06:25,990
I can say row.t1, row.i1 and get all the data. Save that and run it.

87
00:06:26,000 --> 00:06:29,990
The built-in row factory from SQLite is very flexible.

88
00:06:30,000 --> 00:06:32,990
I tend to use it in the dictionary mode because I find that very convenient, but

89
00:06:33,000 --> 00:06:37,990
it has a lot of other options and they're all documented on the SQLite3 page in

90
00:06:38,000 --> 00:06:39,990
the Python documentation.

91
00:06:40,000 --> 00:06:44,990
So, as you can see, accessing a database from Python is very simple.

92
00:06:45,000 --> 00:06:47,990
Most databases have an interface very similar to this one.

93
00:06:48,000 --> 00:06:52,990
For most simple database applications, SQLite3 is going to be a great choice and

94
00:06:53,000 --> 00:07:03,000
you can see that it's very simple to use in Python.