Jose: Hi Guys, and Welcome back.
In this video, we're going to be changing our database.py
to use Postgres instead of sqLite.
Remember, one of the key benefits
of having database interactions
and user interactions in two separate files
is that app.py, we're not gonna touch it,
it shouldn't change at all.
However, we do have to make three changes in database.py.
The first one, as I mentioned earlier,
we can't do connection.execute,
we have to create our cursors.
Second thing, we can't do integer primary key in Postgres,
and expect auto incrementing values by default.
So whereas in sqlite, this was an allais for row id,
and automatically increase the value
every time we enter a new row.
In Postgres, we actually have to give it
a type of serial instead.
In the next video, we have more information
about what serial is and how it works behind the scenes,
but for now, this is gonna give you
an auto incrementing value.
So that's the first change there.
Then another change is that whereas in sqlite,
we were using question marks for the arguments,
in Postgres, we use percent s.
So that's just a change that we have to go
and copy percent s and find every question mark in here
and replace it for percent s.
So as you can see, pretty straightforward.
Just make sure to not miss any,
otherwise you're gonna get some errors later on.
But I think that's it.
You can always press Ctrl + F
or Command F, if you're on a Mac,
and find the question mark.
No matches, so we're good to go.
So the next thing is we have to create our cursors.
And I showed you earlier on
that you can just create a cursor
with cursor equal connection.cursor.
But actually, if you do with connection.cursor as cursor,
making sure to spell things correctly,
this is gonna be a little bit better.
Because that way, any resources associated with the cursor
are automatically released
at the end of this context manager.
This is a little bit different from what happened in sqlite,
where we didn't have to close cursors.
So just a couple of small changes there.
We're gonna have to do this for basically
every function that we've got here.
So I'm gonna copy this.
And then make sure to not miss any.
Here, as you can see, we're already creating a cursor
so we don't necessarily have to create our own,
but it's better to use the context manager,
so that we can release any resources that we already had.
And so we're just gonna do that.
Then here, we're gonna do this.
Oh, by the way, I'm forgetting to use the cursor,
we're gonna come back and do that in just a moment.
But we're gonna paste all that in.
Here we're gonna do the same.
This one is okay.
And this one is okay too.
Now, we have to go up to the top
where we are using connection.execute
and simply replace those by the cursor.
So in this function as well.
This one is okay because it was already using a cursor.
This one is not because it was using connection.execute,
and so is this one,
and the rest are okay.
So as you can see, pretty straightforward.
It's mostly mechanical changes
just because these two libraries behave
in slightly different ways.
The people who coded the libraries decided that
this is the way it should be done.
In this library, no approach is better than the other.
Although I do like the consistency here in psycopg2,
every function is essentially identical
with the first couple of lines.
So you may think this is a bit of duplication,
I like to think of it as set up a work
that we kinda need to do.
And not every function is gonna need its own cursor.
But for now they do in here.
There are ways to reduce this duplication,
but we're not gonna worry about them for now.
So you can see that app.py didn't have to change at all.
So now is the moment of truth.
We can run app.py and see what happens.
So I'm gonna go ahead and run it.
And you can see we get a small error here,
the column username referenced
in the foreign key constraint does not exist.
Now before you go and wonder
whether there's something wrong with the code.
I mean, you can probably double check the columns
and so forth, to make sure that they do match
in terms of the naming.
Remember that we did create a users table
earlier on in elephantSQL, or at least I did.
So that's why I'm getting this error.
If we go to elephantSQL and drop the users table
and then recreate it with our code,
then everything should work.
So I've come here to elephantSQL,
and we're gonna do drop table users.
And then just execute that.
That's gonna delete our table, there we go.
And then we can go back to Postgre and run this again.
And now it works.
So just make sure that not only can your code be wrong now,
your database structure can also be wrong.
So there's two things to check
and debug when you're doing database apps.
So we've got our code here, you feel free to test it out.
But, I mean, it's really just the same.
It's just sending some queries to a database.
And as long as these queries are not malformed,
and we've got all our percent "s"s and so forth,
this is gonna work just the same way.
So I'll let you guys try it out.
Then you can go to elephantSQL,
and do some selection there and see what's up.
And I'm sure everything is gonna work just fine.
Let's go into the next video.
Thank you for watching, and I'll see you there.