Apostrophe in select with postgres/Python

Question

I am trying to see if I already have an item in the database. However I can't figure out how to select the name with an apostrophe(') in them.

I have tried using adapt, %s (which does not work at all). :item syntax does not work either.

The below select will result in

Exception has occurred: ProgrammingError
(psycopg2.errors.SyntaxError) syntax error at or near "s"
LINE 1: select * from items where name = 'Akunda's Bite'

# CODE
str_sql = text(f"select * from items where name = '{item_name}'")

results = self.conn.execute(str_sql).fetchone()

snakecharmerb · Accepted Answer · 2022-01-09 16:40:04Z

2

Rather than using (f-) string formatting, use the DB-API's parameter substitution method:

str_sql = text("select * from items where name = %s;")
results = self.conn.execute(str_sql, (item_name,)).fetchone()

Note that the value(s) must be passed as a sequence; specifically a tuple or a list. A string is a sequence, but each character in the string will be interpreted as a distinct value for the query, which will result in

TypeError: not all arguments converted during string formatting

being raised.

edited Jan 9, 2022 at 16:40

answered Aug 13, 2020 at 11:51

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

2 Comments

user2130951 Over a year ago

This does not need a tuple. str_sql = "select * from items where name = %s" results = self.conn.execute(str_sql, item_name).fetchone()

Adrian Klaver Over a year ago

Then item_name must be a tuple or list.

Collectives™ on Stack Overflow

Apostrophe in select with postgres/Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related