1 00:00:00,980 --> 00:00:03,182 Time to start building out our database. 2 00:00:03,182 --> 00:00:06,400 Create a file called models.py. 3 00:00:06,400 --> 00:00:08,420 This is where we will start. 4 00:00:08,420 --> 00:00:12,611 It's also a typical pattern to create a database 5 00:00:12,611 --> 00:00:16,298 model inside of a file called models.py. 6 00:00:16,298 --> 00:00:21,367 At the top of the file, we'll need to add our first import 7 00:00:21,367 --> 00:00:26,944 from sqlalchemy import create_engine. 8 00:00:30,373 --> 00:00:35,510 Add a few spaces, and we'll need to create a variable called engine. 9 00:00:37,450 --> 00:00:42,520 And you can probably guess we're going to set it equal to create_engine(). 10 00:00:44,050 --> 00:00:48,364 Inside of our parentheses, 11 00:00:48,364 --> 00:00:58,270 we need a string that has ('sqlite:///users.db'). 12 00:00:58,270 --> 00:01:05,681 Oops, not use, users.db, there we go. 13 00:01:05,681 --> 00:01:07,811 , echo=True, 14 00:01:13,937 --> 00:01:17,860 This will create a local database on your computer. 15 00:01:17,860 --> 00:01:21,347 In production, a live website or 16 00:01:21,347 --> 00:01:27,200 application would have a database accessible by a URL, 17 00:01:27,200 --> 00:01:31,220 instead of this sqlite/users.db. 18 00:01:31,220 --> 00:01:36,080 This tells SQLAlchemy where your database is going to be created and accessed. 19 00:01:38,390 --> 00:01:43,364 echo=True will add some additional information in the console when 20 00:01:43,364 --> 00:01:45,330 the file is run. 21 00:01:45,330 --> 00:01:47,920 This will be helpful for now to see what's happening. 22 00:01:49,740 --> 00:01:52,030 Next, we're going to create a model. 23 00:01:52,030 --> 00:01:56,934 A model tells SQLAlchemy the name of our table, and the names and 24 00:01:56,934 --> 00:02:00,000 contents of each column. 25 00:02:00,000 --> 00:02:01,000 At the top of the file, 26 00:02:01,000 --> 00:02:03,750 we'll need to add another import 27 00:02:03,750 --> 00:02:13,097 from sqlalchemy.ext.declarative 28 00:02:13,097 --> 00:02:17,917 import declarative_base. 29 00:02:17,917 --> 00:02:20,125 Then below our engine, 30 00:02:20,125 --> 00:02:25,410 we'll need to create a new variable called Base with a capital B. 31 00:02:27,000 --> 00:02:30,420 The B is capitalized, because it equals a class, 32 00:02:30,420 --> 00:02:34,086 and in Python, classes are usually capitalized. 33 00:02:34,086 --> 00:02:38,671 It equals declarative_base(). 34 00:02:39,850 --> 00:02:45,979 This base, maps our models to the database. 35 00:02:45,979 --> 00:02:50,737 We'll pass this base into the model class we create that lays out what one 36 00:02:50,737 --> 00:02:52,640 table looks like. 37 00:02:52,640 --> 00:02:53,507 Let's do that now. 38 00:02:56,693 --> 00:03:03,004 Create a class called User(), and pass in our base. 39 00:03:04,832 --> 00:03:07,640 Inside of our class, we need to pick a table name. 40 00:03:07,640 --> 00:03:12,576 You set it with __tablename__. 41 00:03:14,503 --> 00:03:17,980 And let's set it = 'users'. 42 00:03:20,050 --> 00:03:22,390 Always make sure to create a table and 43 00:03:22,390 --> 00:03:26,240 class names that describe the content within them. 44 00:03:26,240 --> 00:03:32,934 A class called data, and a table name called table is not really helpful for 45 00:03:32,934 --> 00:03:36,874 others to know what this class is creating. 46 00:03:39,126 --> 00:03:41,480 Next, we need to create some columns. 47 00:03:41,480 --> 00:03:46,535 At the top, add a Column, Integer, and 48 00:03:46,535 --> 00:03:51,610 String to our imports from SQLAlchemy. 49 00:03:53,650 --> 00:03:59,931 The first column will be called id = Column, 50 00:04:02,160 --> 00:04:08,702 Integer, primary_key =True. 51 00:04:10,542 --> 00:04:13,420 Let's break down our first column. 52 00:04:13,420 --> 00:04:18,370 We imported Column at the top, which creates a column in our table. 53 00:04:18,370 --> 00:04:23,031 We passed in Integer, which tells SQLAlchemy what the data type is for 54 00:04:23,031 --> 00:04:25,480 the content in this column. 55 00:04:25,480 --> 00:04:27,082 It'll be integers. 56 00:04:27,082 --> 00:04:30,730 Lastly, we set it as the primary key. 57 00:04:31,730 --> 00:04:36,598 This means that each entry in our table will have a unique ID number 58 00:04:36,598 --> 00:04:41,570 associated with it, which will help you locate specific values. 59 00:04:42,590 --> 00:04:46,950 When creating a new table, you need at 60 00:04:46,950 --> 00:04:51,864 least a table name and a primary key field. 61 00:04:51,864 --> 00:04:54,902 Let's create the rest of our columns for this practice table. 62 00:04:54,902 --> 00:05:04,023 We'll have a column called name, which is a Column(String). 63 00:05:04,023 --> 00:05:07,000 We'll have a column of 64 00:05:07,000 --> 00:05:08,714 fullname, which is also 65 00:05:08,714 --> 00:05:14,276 a Column(String). 66 00:05:14,276 --> 00:05:17,936 And we'll have a nickname column, 67 00:05:17,936 --> 00:05:22,820 which is a column of, you guessed it, string. 68 00:05:23,920 --> 00:05:26,750 One more thing to add to our class that will help with 69 00:05:26,750 --> 00:05:31,000 viewing individual users in the console. 70 00:05:31,000 --> 00:05:40,145 Add a dunder repr method, __repr__. 71 00:05:40,145 --> 00:05:42,580 It will take self, because we are in a class. 72 00:05:44,640 --> 00:05:50,325 And then we'll return a formatted string with our user's information. 73 00:05:56,432 --> 00:06:04,235 Name, fullname ={self.fullname}, 74 00:06:04,235 --> 00:06:11,394 and nickname ={self.nickname}. 75 00:06:14,333 --> 00:06:17,655 Wonderful, now we need to create the table. 76 00:06:17,655 --> 00:06:21,787 Below our model, create a dunder main statement. 77 00:06:26,831 --> 00:06:31,801 If __name__ == '__main__'. 78 00:06:36,341 --> 00:06:43,673 And we'll call Base.metadata.create_all, 79 00:06:43,673 --> 00:06:47,170 and pass in our engine. 80 00:06:48,580 --> 00:06:55,295 This will connect our engine with our model class to create our database table. 81 00:06:55,295 --> 00:06:57,760 In the console, run the file. 82 00:07:00,180 --> 00:07:02,292 Don't forget to save your work. 83 00:07:02,292 --> 00:07:09,173 Run the file Python, and I'm gonna do 3, because I'm on a Mac, models.py. 84 00:07:11,207 --> 00:07:15,150 And ta-da, you can see your database was created. 85 00:07:15,150 --> 00:07:18,975 In the console, because of our echo=True, 86 00:07:18,975 --> 00:07:24,520 you can also see the echo of what just happened in SQL code as well. 87 00:07:28,330 --> 00:07:29,774 If you're in the workspace, 88 00:07:29,774 --> 00:07:33,650 you'll need to refresh the files to see your database appear. 89 00:07:33,650 --> 00:07:39,050 Right click > Refresh, ta-da. 90 00:07:39,050 --> 00:07:43,700 I also wanted to show you that your database is actually a table. 91 00:07:43,700 --> 00:07:46,840 This is the DB browser for SQLite. 92 00:07:46,840 --> 00:07:50,013 I won't be using it much throughout the course, but you can find the link 93 00:07:50,013 --> 00:07:52,780 in the teacher's notes below if you would like to download it. 94 00:07:53,820 --> 00:08:00,887 At the top, I'm going to click on Open Database, 95 00:08:00,887 --> 00:08:07,282 and navigate to my users.db file inside of my 96 00:08:07,282 --> 00:08:12,690 project folder, users.db > Open. 97 00:08:12,690 --> 00:08:18,127 You can see it says Tables > Users > Browse Data. 98 00:08:18,127 --> 00:08:23,226 Now that I have my database open, I can see I have one, two, three, 99 00:08:23,226 --> 00:08:29,870 four columns, named id, name, fullname, and nickname, just like what we created. 100 00:08:30,930 --> 00:08:34,520 And that's it, you've successfully created a model and 101 00:08:34,520 --> 00:08:36,870 table using SQLAlchemy, nice work.