CODEWITHSUNDEEP
python
Dan
Dan

Reputation: 623

Way to add single quote character in string in constructing oracle query in python

I have this method that creates the query and passes two string parameters. But when I test this it has escape characters '' before the single quote '''.

The query can only accept native queries in string form

I also tried string.replace method but doesnt work

replace('\\', '')

Here is the code

def update_query(self, status, row_id):
    return '''UPDATE TABLE SET STATUS = {0} WHERE ID = {1}'''.format(status, row_id)

Here is the sample output:

'UPDATE TABLE SET STATUS = 'Success' WHERE ID = 1'

Thank you

Upvotes: 1

Views: 843

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

I think you absolutely should be using prepared statements here, which the other answers don't seem to be recommending (for whatever reason). Try using something along these lines:

sql = "UPDATE TABLE SET STATUS = :status WHERE ID = :id"
cursor.prepare(sql)
cursor.execute(None, {'status':status, 'id':row_id})

One advantage of using prepared statements here is that it frees the user from having to worry about how to properly escape the literal placeholders in the query. Instead, we only need to bind a variable with the correct type to the statement, and Oracle will handle the rest.

Upvotes: 3

Vaebhav
Vaebhav

Reputation: 5032

You can also use f-string for formatting your string


def update_query(self,status, row_id):
    return f"UPDATE TABLE SET STATUS = '{status}' WHERE ID = {row_id}"

>>> update_query("Success",1)
"UPDATE TABLE SET STATUS = 'Success' WHERE ID = 1"

Upvotes: 2

billz
billz

Reputation: 45410

you need to add \ in the code

def update_query(self, status, row_id):
    return '''UPDATE TABLE SET STATUS = \'{0}\' WHERE ID = {1}'''.format(status, row_id)

Upvotes: 1

Related Questions