Python Postgresql query with text string

Question

In PgAdmin, I can do the following query successfully:

select * from "Faces" where "Face_Name" = 'Alex'

However, when I try to do the exact same query in python, I get endless syntax errors. I am trying to write the line like this:

cursor.execute('SELECT * from "Faces" where ("Face_Name" = 'Alex')

I understand the table and column names need to be in double quotes, and the whole query needs to be in single quotes. Also seems the string (in this case 'Alex') that I am searching for needs to be in single quotes.

How do I put all this together into a single line?

The database I am using is Postgresql 12

Wasabe
– Wasabe

2020-07-17 05:53:15 +00:00
Commented Jul 17, 2020 at 5:53 — Wasabe
– Wasabe, Commented Jul 17, 2020 at 5:53
what errors are you getting?

ewokx
– ewokx

2020-07-17 05:54:37 +00:00
Commented Jul 17, 2020 at 5:54 — ewokx
– ewokx, Commented Jul 17, 2020 at 5:54

Tim Biegeleisen · Accepted Answer · 2020-07-17 06:14:35Z

1

Assuming you did need to escape the table and column names, you could use double quotes. In that case, just escape the double quotes inside the Python SQL string:

sql = "SELECT * FROM \"Faces\" WHERE \"Face_Name\" = 'Alex'"
cursor.execute(sql)

edited Jul 17, 2020 at 6:14

answered Jul 17, 2020 at 5:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Laurenz Albe Over a year ago

I think the first part of your answer is misleading: if the double quotes were unnecessary, the query wouldn't work with quotes in pgAdmin. The second part of your answer is fine.

Tim Biegeleisen Over a year ago

@LaurenzAlbe Are you saying that placing double quotes around a DB object name is invalid unless that object name must be escaped?

Jiří Baum Over a year ago

The double quotes are mandatory if the table name actually has upper-case letters (that is, if the CREATE TABLE command used double quotes). By default, without the double quotes, PostgreSQL turns everything lower-case, but that doesn't appear to be the case here.

Tim Biegeleisen Over a year ago

@sabik That's not the only reason to use double quotes. The object name might also be a reserved keyword, etc.

Jiří Baum Over a year ago

Sure, but neither "Faces" nor "Face_Name" is in fact a keyword, or contains spaces or other special characters; they need to be quoted because of the upper-case letters.

|

Jiří Baum · Accepted Answer · 2020-07-17 06:29:39Z

1

There are two issues here:

As others already wrote, you need to be careful not to mix up the Python and SQL quotes; depending on the field name you may need to have both in the query, and either escape one of them or use """ for the Python string.
If the name "Alex" comes from a variable in Python, rather than being a constant, you should use a placeholder and pass it separately. This will help avoid security problems (SQL Injection) and is a good habit to get into whether or not it's required in this particular case.

Putting these together, the query should be:

cursor.execute('SELECT * from "Faces" where "Face_Name" = %s', ('Alex',))

edited Jul 17, 2020 at 6:29

answered Jul 17, 2020 at 6:00

Jiří Baum

6,9882 gold badges19 silver badges19 bronze badges

3 Comments

Tim Biegeleisen Over a year ago

No need for a prepared statement if the value 'Alex' happens to come internally from the application, and does not come from the outside.

Jiří Baum Over a year ago

It's a good idea to get into the habit... and presumably this is the first iteration of code which will eventually handle other names.

Jiří Baum Over a year ago

Reasoning added to the answer; even a vetted, trusted, fully-internal list of names will sooner or later contain someone named "O'Brien".

Collectives™ on Stack Overflow

Python Postgresql query with text string

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related