Instructor: Hi guys, and welcome back.
In this quick video I just wanted to tell you
about SQL String composition using Psycopg2.
Here's how we've been passing arguments
to a query for now.
We've done something like select star from users
where name equal percent S.
And then when we did cursor dot execute,
we passed in a value that would replace the percent S.
But what if you wanted to pass in a value
that would replace the table name,
so instead of selecting from users
you can select from something else that the user decided?
Can you do this?
Select star from percent S where name equal percent S?
What about select percent S from percent S
where name equal percent S?
No.
No, you cannot do that.
And the reason, I'm not quiet sure.
It's probably got to do with the fact
that values that you are passing in
are not the same concept as identifiers in SQL.
You know, column and table names.
So this is why we have to use SQL string composition.
This is how we do it.
First of all, from Psycopg2 we import SQL
and then let's say that we want to ask the user
for a table name, a column name, and a search value
that they're looking for.
So we just ask them with the input function.
And then we're going to construct our query,
and we do SQL dot SQL with uppercase,
and then we type in our query.
Select star from,
and then table where column equal percent S.
Note that the curly braces with table and column
are not Psycopg2 specific.
This is simply how we do formatting in Python.
Here we're gonna use the format function
to put values into those interpolation strings.
So then we do query equal query dot format,
and then for the table we're gonna pass in a SQL identifier
which is the table name.
And that is going to allow Psycopg2 to to construct
the identifier in a way
that will be understandable by Postgres.
Similarly with a column as well.
So at the end of the day,
what you've got is a query string
which is really a SQL object
and inside it you've got the values that you want
but it is safe to include them there.
So this protects you against any attacks
and also you can put in there your table and column names.
So at the end, you would simply do cursor dot execute,
query, and the search value.
Let's take a look at how to pass variable fields to a query.
We can ask the user the fields
that they want to retrieve from the table.
Then we're gonna grab them and put them into a list.
Let's say that they entered a comma separated list.
What we're doing is we're simply stripping any white space
from the start on the end, just in case they made a mistake,
and then we're splitting on the comma.
And then we're gonna construct a list of SQL identifiers
where each identifier is one field that they typed.
Then we'll construct our queries, select fields from users,
again using that format method syntax,
and at the end we are going to do a query dot format,
fields equal, and then SQL dot SQL with the comma.
Remember this is very similar to a string,
so what we're gonna do then
is we're gonna use the join method from strings
to join the SQL fields using the secure comma string there.
Just in case it had any, you know, any SQL injection attacks
or things like that is gonna escape things for you
and handle them so that it is safe.
So that's why we use the comma string there
but cast as a SQL object.
So really the key takeaway from this lecture
is that whenever you want to do any SQL string construction
using Psycopg2, you do not use plain strings.
You use the uppercase SQL class for your strings.
You use your SQL identifier for your columns and your tables
and there's also a couple more things you can use.
So, other module classes.
You've got Identifier
for tables and columns as we've discussed.
You've got SQL dot Literal for any values
that you wanna hard code into the query.
And finally you've got SQL dot Placeholder
for values to be passed in later
or you can just use percent S if you prefer.
All right, that's everything for this video.
Just wanted to tell you about it.
The official documentation is also really good,
so I would encourage you to give it a read as well.
That is linked in the resources section
of this video, as usual.
Thank you guys for joining me in this video,
thanks for watching, and I'll see you in the next one.