How to remove extra quotes in pymysql

Question

This code uses pymysql, however when i try to insert the variable title into the sql query it comes out with 'title' for example when i set title to = test the database created is 'test' is there a way to create the table without the extra quotes

   import pymysql
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='',
        db='comments',
    )
    c= connection.cursor()
    sql ='''CREATE TABLE IF NOT EXISTS `%s` (
      `comment_id` int(11) NOT NULL,
      `parent_comment_id` int(11) NOT NULL,
      `comment` varchar(200) NOT NULL,
      `comment_sender_name` varchar(40) NOT NULL,
      `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8; '''
    c.execute(sql, (title))

What's the use case for passing the table name as a bound variable? That sort of thing usually isn't allowed in prepared queries. — Powerlord, Sep 18 '18 at 23:35
Furthermore, it's a common mistake by new programmers trying to segment data. It virtually always indicates a database architecture problem. — ChrisGPT was on strike, Sep 18 '18 at 23:40
@Powerlord i was using it because the sql table is read in a seperate html file which looks for that specific name. Is there a better way to do this? — Aaron Mills, Sep 18 '18 at 23:59
@Chris what is the recommended way to lay out a database, not resulting in a problem? — Aaron Mills, Sep 19 '18 at 00:00
@AaronMills, you haven't shared enough information to answer that properly. But generally speaking you shouldn't have multiple tables containing the same data for different segments. Put it all in a single table and filter when necessary with a `WHERE` clause. What does `title` contain in your snippet above? — ChrisGPT was on strike, Sep 19 '18 at 00:04
@Chris the title is simply a string that the user inputs,so i would need one large table for all the current tables with different names i have at the moment? — Aaron Mills, Sep 19 '18 at 00:07
@AaronMills, probably. It's hard to say for sure without more context. — ChrisGPT was on strike, Sep 19 '18 at 00:10

Sun Lemuria · Accepted Answer · 2019-01-14T03:25:41.113

In my case I just rewrite the escape method in class 'pymysql.connections.Connection', which obviously adds "'" arround your string.

I don't know whether it is a bad idea, but there seems no better ways, if anyone knows, just let me know.

Here's my code:

from pymysql.connections import Connection, converters


class MyConnect(Connection):
    def escape(self, obj, mapping=None):
        """Escape whatever value you pass to it.

        Non-standard, for internal use; do not use this in your applications.
        """
        if isinstance(obj, str):
            return self.escape_string(obj)  # by default, it is :return "'" + self.escape_string(obj) + "'"
        if isinstance(obj, (bytes, bytearray)):
            ret = self._quote_bytes(obj)
            if self._binary_prefix:
                ret = "_binary" + ret
            return ret
        return converters.escape_item(obj, self.charset, mapping=mapping)


config = {'host':'', 'user':'', ...}
conn = MyConnect(**config)
cur = conn.cursor()

How to remove extra quotes in pymysql

1 Answers1

Linked