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

Question

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

Tim Biegeleisen · Accepted Answer · 2021-01-12 08:41:45Z

3

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.

answered Jan 12, 2021 at 8:41

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

Dan Over a year ago

Hi Tim, I am using a helper class to execute db queries that only accepts native queries in string form.

Vaebhav · Accepted Answer · 2021-01-12 08:39:24Z

2

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"

answered Jan 12, 2021 at 8:39

Vaebhav

5,0921 gold badge17 silver badges37 bronze badges

1 Comment

Dan Over a year ago

I am using an older version of python, this is not applicable.

billz · Accepted Answer · 2021-01-12 08:36:34Z

1

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)

answered Jan 12, 2021 at 8:36

billz

45.6k9 gold badges87 silver badges103 bronze badges

2 Comments

Roy Cohen Over a year ago

the backslash (\) isn't necessary.

Dan Over a year ago

adding backslash will not recognize the first bind variable

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related