Python strings problem storing single quote

Question

I am trying to execute mysql query from python. I want the output

query = "UPDATE 'college_general' SET 'fees' = '180000' WHERE ('college_id' = '2')"

Below is the snippet of the code

def update(table, column, value):
    return f"UPDATE '{table}' SET '{column}' = '{value}' WHERE ('college_id' = '{id}')"

query = update("college_general", "fees", fee)
cursor.execute(query)

Instead Python is storing it like

query = 'UPDATE \'college_general\' SET \'fees\' = \'180000\' WHERE (\'college_id\' = \'2\')'

which is causing the script to fail. How can I achieve the desired output? Thanks in advance!

Muhammad Umar · Answer 1 · 2021-04-14T06:59:04.673

2

You can replace the identifiers single quotes with backticks. For more detailed answers visit this question.

There are two types of quotes in MySQL:

' for enclosing string literals
` for enclosing identifiers such as table and column names

edited Apr 14 '21 at 06:59

answered Apr 11 '21 at 11:47

Muhammad Umar

130
1
10

score 0 · Answer 2 · answered Apr 11 '21 at 11:18

raw string is the simplest solution to your problem.

I believe the code below will achieve what you wanted.

def update(table, column, value):
    return fr"UPDATE '{table}' SET '{column}' = '{value}' WHERE ('college_id' = '{id}')"

query = update("college_general", "fees", fee)
cursor.execute(query)

score 0 · Accepted Answer · answered Apr 11 '21 at 12:17

There are multiple issues here:

First, I suspect that the string handling bit of your program is actually working, but you are being confused by the external representation of strings. For example, if you do

x = "O'Reilly"

Python will, in some circumstances, display the string as

'O\'Reilly'

Second, I think you are using the wrong kind of quotes. Single quotes in SQL are for strings; MySQL uses backticks for names when necessary, while other SQL implementations usually use double quotes for this.

Third, AND THIS IS IMPORTANT! Do not use string manipulation for building SQL queries. The database library almost certainly has a feature for parametrized queries and you should be using that. Your query should look something like this:

query = 'UPDATE college_general SET fees = ? WHERE college_ID = ?'
cursor.execute(query, [180000, '2'])

but the details will depend on the DB library you are using. For example, some use %s instead of ?. This saves you from all kinds of headaches with quoting strings.

Python strings problem storing single quote

3 Answers3