# RETURNING data from modified rows
Whenever we execute INSERT, UPDATE, or DELETE statements, we don't get anything back. That's just how it works: when you're modifying, you're not retrieving.
However, sometimes it can be handy to insert something, and then get back what you inserted. For example, if you want to use what you inserted immediately after, or print it out.
When we create a poll, we want to immediately use that new poll's id value so that we can create the poll options. With what we know so far, we would have to do this (assume the connection and cursor have been created):
cursor.execute("INSERT INTO polls VALUES (%s, %s);", (title, owner))
cursor.execute("SELECT id FROM polls ORDER BY id DESC LIMIT 1;")
poll_id = cursor.fetchone()[0]
option_values = [(option_text, poll_id) for option_text in options]
for option_value in option_values:
cursor.execute(INSERT_OPTION, option_value)
This is suboptimal because we're inserting into the table and then immediately going into a SELECT to retrieve exactly what we inserted.
Remember we must do this because the id is generated by the database, we don't have access to it before inserting.
# RETURNING to get data back when inserting
Instead of doing that though, we can ask the database to give us back the data in a column of what it has just inserted[1]:
INSERT INTO polls VALUES (%s, %s) RETURNING id;
With that, we do both queries in one! We no longer need to execute a separate SELECT.
The final code ends up like this:
cursor.execute("INSERT INTO polls (title, owner_username) VALUES (%s, %s) RETURNING id;", (title, owner))
poll_id = cursor.fetchone()[0]
option_values = [(option_text, poll_id) for option_text in options]
for option_value in option_values:
cursor.execute(INSERT_OPTION, option_value)
TIP
You can do RETURNING * to return all the columns. You can also comma-separate columns just like in SELECT: RETURNING id, title.
# Code modifications
Let's modify our database.py to make use of this query.
First of all, we'll create the query at the top:
+ INSERT_POLL_RETURN_ID = "INSERT INTO polls (title, owner_username) VALUES (%s, %s) RETURNING id;"
Then we'll also fill in the create_poll() function to make use of it:
def create_poll(connection, title, owner, options):
with connection:
with connection.cursor() as cursor:
+ cursor.execute(INSERT_POLL_RETURN_ID, (title, owner))
+
+ poll_id = cursor.fetchone()[0]
+ option_values = [(option_text, poll_id) for option_text in options]
+
+ for option_value in option_values:
+ cursor.execute(INSERT_OPTION, option_value)
# psycopg2's execute_values
Instead of that for loop at the end:
for option_value in option_values:
cursor.execute(INSERT_OPTION, option_value)
We can make use of a function in psycopg2 that is more efficient and does the looping for us: execute_values[2].
To use it we'll need to import it:
+ from psycopg2.extras import execute_values
And then we'll also need to use it instead of the for loop!
The execute_values function takes in three arguments:
- The cursor that will execute the queries.
- The query that will be executed for each value.
- A list of tuples, where each tuple is what goes into one of the queries.
All in all, the change looks like this:
- for option_value in option_values:
- cursor.execute(INSERT_OPTION, option_value)
+ execute_values(cursor, INSERT_OPTION, option_values)
# Wrapping Up
That's about it for the RETURNING keyword. It's something we can use here in our project, and it can be really handy!
We've also learned about fast execution helpers in psycopg2 as a bit of an extra.