Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Use SQLAlchemy to create a model for your database.
SQLite Browser Application
If you’re working on this project locally, you can use the SQLite Browser application to view your database table. Download the application here.
SQLAlchemy Doc Links
Time to start building out our database.
0:00
Create a file called models.py.
0:03
This is where we will start.
0:06
It's also a typical pattern
to create a database
0:08
model inside of a file called models.py.
0:12
At the top of the file,
we'll need to add our first
0:16
import from sqlalchemy
import create_engine().
0:21
Add a few spaces, and we'll need to
create a variable called engine.
0:30
And you can probably guess we're
going to set it = create_engine().
0:37
Inside of our parentheses,
0:44
we need a string that has
('sqlite:///users.db').
0:48
Oops, not use, users.db, there we go.
0:58
, echo=True,
1:05
This will create a local
database on your computer.
1:13
In production, a live website or
1:17
application would have
a database accessible by a URL,
1:21
instead of this sqlite/users.db.
1:27
This tells SQLAlchemy where your database
is going to be created and accessed.
1:31
Echo =True will add some additional
information in the console when
1:38
the file is run.
1:43
This will be helpful for
now to see what's happening.
1:45
Next, we're going to create a model.
1:49
A model tells SQLAlchemy the name
of our table, and the names and
1:52
contents of each column.
1:56
At the top of the file,
2:00
we'll need to add another import
2:03
from sqlalchemy.ext.declarative
2:07
import declarative_base().
2:13
And then below our engine,
2:17
we'll need to create a new variable
called Base with a capital B.
2:20
The B is capitalized,
because it equals a class.
2:27
And in Python,
classes are usually capitalized.
2:30
It = declarative_base().
2:34
This base,
maps our models to the database.
2:39
We'll pass this base into the model
class we create that lays out what one
2:45
table looks like.
2:50
Let's do that now.
2:52
Create a class called User(),
and pass in our base.
2:56
Inside of our class,
we need to pick a table name.
3:04
You set it with __tablename__.
3:07
And let's set it = 'users'.
3:14
Always make sure to create a table and
3:20
class names that describe
the content within them.
3:22
A class called data, and a table name
called table is not really helpful for
3:26
others to know what
this class is creating.
3:32
Next, we need to create some columns.
3:39
At the top, add a Column, Integer, and
3:41
String to our imports from SQLAlchemy.
3:46
The first column will
be called id = Column,
3:53
Integer, primary_key =True.
4:02
Let's break down our first column.
4:10
We imported Column at the top,
which creates a column in our table.
4:13
We passed in Integer, which tells
SQLAlchemy what the data type is for
4:18
the content in this column.
4:23
It'll be integers.
4:25
Lastly, we set it as the primary key.
4:27
This means that each entry in our
table will have a unique ID number
4:31
associated with it, which will
help you locate specific values.
4:36
When creating a new table, you need at
4:42
least a table name and
a primary key field.
4:46
Let's create the rest of our columns for
this practice table.
4:51
We'll have a column called name,
which is a Column(String).
4:54
We'll have a column of
4:57
fullname, which is also
5:02
a Column(String).
5:08
And we'll have a nickname column,
5:14
which is a column of,
you guessed it, string.
5:17
One more thing to add to our
class that will help with
5:23
viewing individual users in the console.
5:30
Add a dunder repr method, __repr__.
5:35
It will take self,
because we are in a class.
5:40
And then we'll return a formatted
string with our user's information.
5:44
Name, fullname ={self.fullname},
5:56
and nickname ={self.nickname}.
6:04
Wonderful, now we need
to create the table.
6:14
Below our model,
create a dunder main statement.
6:17
If __name__ == '__main__'.
6:26
And we'll call Base.metadata.create_all,
6:36
and pass in our engine.
6:43
This will connect our engine with our
model class to create our database table.
6:48
In the console, run the file.
6:55
Don't forget to save your work.
7:00
Run the file Python, and I'm gonna do 3,
because I'm on a Mac, models.py.
7:02
And ta-da,
you can see your database was created.
7:11
In the console, because of our echo=True,
7:15
you can also see the echo of what
just happened in SQL code as well.
7:18
If you're in the workspace,
7:28
you'll need to refresh the files
to see your database appear.
7:29
Right click > Refresh, ta-da.
7:33
I also wanted to show you that
your database is actually a table.
7:39
This is the DB browser for SQLite.
7:43
I won't be using it much throughout
the course, but you can find the link
7:46
in the teacher's notes below if
you would like to download it.
7:50
At the top,
I'm going to click on Open Database,
7:53
and navigate to my
users.db file inside of my
8:00
project folder, users.db > Open.
8:07
You can see it says Tables
> Users > Browse Data.
8:12
Now that I have my database open,
I can see I have one, two, three,
8:18
four columns, named id, name, fullname,
and nickname, just like what we created.
8:23
And that's it,
you've successfully created a model and
8:30
table using SQLAlchemy, nice work.
8:34
You need to sign up for Treehouse in order to download course files.
Sign up