Jose: Hi guys, and welcome back.
In this video we're going to be working on
creating new polls, and on the returning key-wording sequel.
First of all though, I'm going to show how we could
create a poll, without the returning key-word
and then I'm going to show you what the returning
key-word really does, and why it can help us here.
Let's get started.
Here we've got the prompt to create
a poll in our application.
We're asking the user for the poll title that they want
to create, as well as who the owner is,
then we've got potentially a list of strings,
One string for each option text.
So for example, they might type "what's better
Python or Java?", the owner might be "Jose",
which is me, and then they might type two options,
"Python", "Java", and then an empty string
which would signal the end of this loop,
and then we would pass in here the connection,
"Python vs Java", "Jose", and then a list with
"Python and Java" in it.
Obviously Python is the right choice,
but you might want a poll for that in case you want to
share it with your friends who don't programme,
or something like that.
So then that would call "create poll", and here we
take the title, the owner, and the different options.
So with what we know so far we can implement this already,
but it's quite sub-optimal.
How might we do this at the moment?
Well right now we would first of all have to insert
into polls, so we would do something like
"cursor.execute" and then "insert into polls"
the values "%s", "%s", and then we would pass in here
the title and the owner.
This would create the poll.
Queries within a transaction, which is what happens
when you deal with connection are isolated
from the rest, but you still have access to the data
that this transaction produces
while running the transaction.
So even after running insert into polls,
this poll is now added to the database,
but only available to this transaction,
which means that we can now go and select from polls.
Why do we want to select from polls?
So we can grab the "poll ID" that is automatically generated
by the database, since we need that in order to
create the options in the options table.
So we would do "cursor.execute" "select id from polls order
order by id" in descending order "limit 1".
This would give us essentially, the latest ID
that has been added to the database.
So now that we've got that, we can say that "poll id"
is equal to "cursor.fetchone" and then access column 0,
which is really the only column returned,
then we could say something like
"option values" is equal to "option text poll id",
or "option text in options", that would give us
this list here which has one topple for each option,
and that topple contains the option text and the poll ID.
Notice that this is the data that we need
for the options table, we need the option text
and the poll ID, the ID is going to be
automatically generated, so this gives us everything
we need, and then we do "for option value in option values"
like that, "cursor.execute", "insert option"
with "option value" like that.
This is what we can do already with what we know.
As long as you remember how to do all of this stuff,
which is you don't, no problem, we've got the e-book
there for you to check out if you want,
or you can revisit the past lectures,
but clearly it's not great. Right?
We've got to do a bunch of stuff here, like
this secondary selecting, then we've got this loop here
to insert all of the different options.
We're going to do much better than that,
but before we can do that we need to learn
about the returning key-word, which is going to allow us
to get rid of this select statement.
Let's take a look.
So real quick, the returning keyword.
What's it for?
Well often when we're modifying a row,
we don't get anything back,
but sometimes it can be handy.
So when you're doing an insert, for example,
you can get the data back, and that means you're going to
see that auto-generated ID field.
So here's what it looks like.
Insert into polls the values of whatever returning ID,
and that can be comma separated columns,
or it can be the star for everything you've inserted
and so on.
So no secret here, nothing particularly tricky,
but nonetheless something worth knowing about.
Let's go back to Python.
All right, so now we know that can simply put
returning ID here, and now we no longer need
this separate select statement because running this
query will both insert into the table as well as
give us back a result set with one row and one column
so when we do "cursor.fetchone"
and we get this zero column there
it's going to give us that value back.
Now remember that something I forgot to do here
essentially is we do need to put the columns
that we want to enter data into, otherwise
we're going to try to put this data into the ID column,
which is not going to work,
and also we're going to be missing one column of data.
So that's just my bad there, but do remember
to put that in.
Then I'm going to copy that and go up to the top
and create a query for that insert poll return ID,
for example, and I'm going to paste that in there,
now I can copy that, come down again and replace it.
Just make sure that our queries are all defined at the top
so if we want to make any changes later on
it's really easy, we don't have to go looking through
these functions.
I also mentioned that I wasn't a big fan of
this for loop here, because we've got a lot of different
cursor executes running for all different pieces of data
and all that, and psycopg2 fortunately comes with
a better way of doing this.
So what we're going to do is go up to the top of the file
and we're going to do an import here.
We're going do "from psycopg2.extras import execute_values"
and now we can grab that, come down to the bottom
and what we're going to do is "execute_values"
and we have to pass in the cursor that we're going to use
to execute all of the different queries,
the query that we want to run, and also a list of topples
so that's our option values linked,
and this is simply a helper to do this.
It's going to take in each of the topples in this list
and pass them through the query
and run them with a cursor.
So it saves us a bit of typing,
and also it's a little bit better.
All right that's everything for this video,
thank you for joining me.
I hope you've learned something,
and I'll see you in the next one.