MySQL statement python escape single quote '

Question

I have below MySQL statement with python but there is a single quote ' in one of the values hence I got below error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near L at line 1

The value to be inserted is Regal INT'L

How to escape or to correct the MySQL statement?

MySQL statement

def query(self, item):

        return "INSERT INTO income_statement({columns}) VALUES ({values})".format(

            columns=', '.join(item.keys()),

            values=self.item_to_text(item)

        )

def item_to_text(self, item):
        return ', '.join("'" + str(v) + "'" for v in item.values()
        )

Khanal · Accepted Answer · 2018-06-22T16:17:04.147

3

Return a tuple of string template and tuple of variables and the cursor can execute (template, (v1,v2,..))

cursor.execute(‘insert into tablename (c, d) values (%s, %s)’, (v1, v2))

Based on the API Docs

Edit 2: A more complete example

def query(self, item):
  values = ', '.join(['%s']*len(item.keys()))
  stmt = "INSERT INTO income_statement({columns}) VALUES ({values})".format(
      columns=', '.join(item.keys()),
      values=values
  )
  # self.item_to_text(item) must be a tuple
  return (stmt, self.item_to_text(item))

# use it like so
cursor.execute(query(item))

Edit 3: I am pretty certain that if you really want to pass the statement as a single string you’d have to have a \ present in the string itself thus using INT\\’L

Edit 4:

def item_to_text(self, item):
    return ', '.join(item.values()) # assuming item.values() returns a list or a tuple

edited Jun 22 '18 at 16:17

answered Jun 22 '18 at 01:35

Khanal

788
6
14

I have not gotten a chance to test it yet, but it should work based on the api docs. – Khanal Jun 22 '18 at 11:53
Thank you Khnanal, I updated my question with the 'item_to_text' function, does the %s means to escape the ' ? In my updated question, where/how to put the escape in the join statement? – Nicholas Kan Jun 22 '18 at 16:06
Oh you don't need to escape it at all on the item_to_text, assuming it is a list of strings. basically %s would let mysql know that it is expecting a string and then it will escape the string it gets in. – Khanal Jun 22 '18 at 16:13
Thanks very much Khanal! – Nicholas Kan Jun 23 '18 at 09:58
Glad it helped you! – Khanal Jun 23 '18 at 11:35
would you also help to take a look at my this questions?https://stackoverflow.com/questions/51000140/mysql-on-duplicate-key-update-with-all-variables?noredirect=1#comment88994845_51000140 – Nicholas Kan Jun 23 '18 at 11:45

MySQL statement python escape single quote '

1 Answers1