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
Create and read database entries with SQLAlchemy.
Installing SQLite and Accessing it on Windows
Follow the directions listed out in the answer on this forum post
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.
User List
[User(name='Grace', fullname='Grace Hopper', nickname='Pioneer'), User(name='Alan', fullname='Alan Turing', nickname='Computer Scientist'), User(name='Katherine', fullname='Katherine Johnson', nickname='') ]
SQLAlchemy Doc Links
Creating Database Tables - create_all()
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Now that we have our database created,
it's time to start making some entries.
0:00
First, if you would like to clear up the
console a bit you can set echo to False.
0:05
This will remove a lot of the additional
console messages when running the file.
0:12
You're more than welcome to leave
this message turned on if you'd like.
0:17
Our first entry will be you.
0:22
You can also copy my code and
add me to your database if you'd rather.
0:26
Let's jump down here to the bottom,
add a space and
0:32
create your variable with
your name underscore user.
0:37
Set it = User, and inside we'll
have name=, and then your name.
0:43
fullname = and then your full name.
0:54
And then nickname =, and your nickname.
1:00
Since our ID is our primary key,
we don't need to pass in a value.
1:07
One will be created for this entry
when it's added to the database.
1:12
Now let's print out our name,
1:20
meg_user.name, and let's do id too,
1:24
meg_user.id, run the file,
1:31
python3 for me cuz I'm on a Mac.
1:36
And we have our name and
then none for the ID.
1:42
This entry hasn't been
added to our database yet.
1:47
We need to add sessions to do that.
1:51
At the top of the file we'll add an import
1:56
from sqlalchemy.orm import sessionmaker.
2:01
Then after our engine,
we'll need to create two variables,
2:08
the first,
session with a capital S = sessionmaker,
2:14
inside we'll have bind=engine.
2:20
This binds our sessions to the database.
2:25
Next we'll add another
variable called session with
2:30
a lowercase s and
it we'll call our Session, great.
2:35
Let's add a few more things before
I get into what session is doing.
2:41
Trust me,
it will all make more sense in a minute.
2:45
Jump back down to where we
created our first entry.
2:48
Remember, it hasn't been added to
the database yet, id is still none.
2:54
Below let's add session, oops,
2:59
not sessionmaker,
session.add and add your user.
3:03
This adds our new user to our session
which communicates with the database,
3:09
but it's not in the database yet.
3:14
It's kind of like putting items in
your cart when shopping online.
3:19
You've added them to be purchased but
you haven't committed to them yet.
3:23
You can change items, you can remove
items and when you're ready to check out,
3:29
you give a final Place Order to
finally commit to ordering them.
3:35
So right now our user is in
this placed in cart stage.
3:41
We haven't committed to adding
it to the database yet.
3:45
We can see that it's inside of our session
3:49
by printing out, session.new.
3:54
Run the file and there's our user.
3:58
Let's finally add this
entry to our database.
4:04
Replace this print statement
with session.commit.
4:07
This will add our user to the database.
4:16
We can check by printing out our id again,
4:19
meg_user.id, run the file and
4:25
our id goes from none to 1.
4:30
That means it was added to our database.
4:35
We can also add multiple users at once.
4:38
You can use a session.add_all.
4:41
Since we've already added this user to
our database, let's comment this out.
4:49
Cmd or Ctrl+/ will comment out
a group of code all at once.
4:55
Now inside of this function,
we're going to pass in a list of users.
5:05
I have a list in the teachers
notes if you wanna copy and
5:10
paste them or
create a list of users of your own, and
5:14
paste and we have our users Grace,
Catherine and Alan.
5:19
You can also make this
list a variable instead.
5:24
And then pass in
the variable into add_all.
5:39
Also need a session.commit, and
5:48
let's loop through our users so
we can see our id.
5:53
So for user in our new_users list,
I want to print
6:15
their id after we've committed
them to the database.
6:20
Run the file, Oops and
6:26
I forgot a comma, comma save.
6:32
Let's try that again.
6:37
There we go and we see 2, 3 and 4.
6:39
So now our database has
a total of four users.
6:42
Another way to check if
your entries have been
6:47
added to the database is to check
via the sqlite shell on a Mac or
6:52
in workspaces type sqlite3 users.db.
6:59
On Windows, you'll type sqlite3.exe
users.db to open your shell.
7:03
But you'll also need to download SQlite
first, check the teacher's notes for
7:11
a list of steps.
7:16
Once you have the shell opened,
type .tables and hit Enter.
7:18
You'll see our users table.
7:24
Next we'll need to use a sql statement
to see all users in this table.
7:27
SELECT * for all, FROM users, and
7:32
then don't forget that ; to
finish out the statement.
7:36
And they're all of our users.
7:43
Using DB Browser for SQlite,
7:46
you can now see all four
users in our table.
7:49
A lot of knowledge dropped in this video.
7:56
So let's do a quick recap.
7:58
You create an entry to the database
by calling the model class and
8:02
passing in the values for
each column except id,
8:06
which will be created for you when
the entry is added to the database.
8:10
We use sessions to keep
track of our additions like
8:15
a cart keeps track of the items
you want to purchase.
8:19
You then commit these additions to
the database by calling session.commit.
8:23
Nothing is added to our database
until we call session.commit.
8:28
Until then,
it's just sitting inside of our session.
8:33
You can also add a group of users
all at once by using add_all.
8:36
Nice job.
8:45
Take some time to review the code so far,
and ask yourself what each line is doing.
8:46
Review the videos as
many times as you need.
8:53
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up