phpmyadmin Question posted in
The official documentation can be found here.

Python -> MySQL "select * from brand WHERE text='L'Orial'" (quote inside search text) – Phpmyadmin

From Python 3.9 i’m trying to do a MySql query like this
select * from brand WHERE text=’L’Orial’.
It works fine from phpMyAdmin but fails from python for all text including quote "’"

brand = "L'Orial"
where = f"text='{brand}'"
brand_pk_id = self.getPrimaryKeyIfExistInTable('brand', where)

def getPrimaryKeyIfExistInTable(self, table, where, key='id'):
   try:
      sql = f"SELECT {key} FROM {table} WHERE {where}"
      self.cursor.execute(sql)
      result = self.cursor.fetchone()
      return result[key if self.bUseDictCursor else 0] if result else None
   except pymysql.MySQLError as e:
      logging.error(e)
   return None

I can see that python escapes all quotes, which probably causes the problem, but can not figure out how to handle it properly !!

If I turn it around and use query LIKE with underscore( _ ) as wildcard:

brand = "L_Orial"
sql = f"SELECT {key} FROM {table} WHERE text LIKE '{brand}'"

It works fine, but this is not what I want !!

Tags:

2

Answers


  1. 0

    If I am understanding your question correctly, your problem is as follows:

    Your query must exactly read:

    SELECT * from brand WHERE text='L'Orial'

    But you are currently getting something like this, when you use python to execute the query:

    SELECT * from brand WHERE text='L'Orial'

    If this is indeed the issue, you should be able to resolve this by simply escaping the backslash that you need to have in the query. The complete python string for your query would be:

    # Python String:
"SELECT * from brand WHERE text='L\'Orial'"
# Resulting Query
SELECT * from brand WHERE text='L'Orial'

    If you wanted to automatically fix this issue for all brands that might include a ', you can simply replace the ' with \' before making the query. Example:

    brand = "L'Orial"
brand = brand.replace("'", "\'")

# New Python string:
#   "L\'Orial"
# Output in SQL
#   "L'Orial"
    Login or Signup to reply.
  2. 0

    Had to fire up my local instance just to make a point.

    First, some prep work…

    import pymysql

table = 'ps_carrier'
key = 'id_carrier'

mysql = {
        "charset": "utf8",
        "database": "mystore",
        "host": "localhost",
        "password": "secret",
        "user": "justin"
    }

    As somebody suggested in the comments, the following

    sql = "SELECT %s FROM %s WHERE %s"
where = "name='UPS'"
with pymysql.connect(**mysql) as conn:
    with conn.cursor() as cur:
        cur.execute(sql, (key, table, where))

    Raises an error as expected since all the (string) params are quoted, even the table name!

    Traceback (most recent call last):
  File "<stdin>", line 3, in <module>

...

  File "C:Python38site-packagespymysqlerr.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''ps_carrier' WHERE 'name=\'UPS\''' at line 1")

    If you can trust the inputs for the table name, the key, and the column name(s) then perhaps a simple query builder can help.

    params = {'name': 'UPS'} # add more key--value pairs here

# use backticks in case we need to escape reserved words (OP uses MySQL)
where = " AND ".join(f"`{k}` = %s" for k in params.keys())  # .keys() just to be explicit
args = tuple([v for v in params.values()])

# backticks again
sql = f"SELECT `{key}` FROM `{table}` WHERE {where}"

print(sql)
print(args)

with pymysql.connect(**mysql) as conn:
    with conn.cursor() as cur:
        cur.execute(sql, args)
        print(cur.fetchall())

    If you need something more elaborate, there are a few modules such as Mysql Simple Query Builder and PyPika – Python Query Builder that you may want to look at (I’ve not used any of these.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search