Search code examples
pythondjangopymysql

The sql string from queryset of django cannot be executed in pymysql?

I'm using Django to make some param filtering, and then I got a filtered queryset like below:

sql_str = str(my_queryset.query)
# print(sql_str)

SELECT `market_accounts`.`agency`, `market_accounts`.`id`, `market_accounts`.`entity`, `market_accounts`.`related_entity`
, `market_accounts`.`type`, `market_accounts`.`our_side_entity`, `market_accounts`.`short_title`, `market_accounts`.`titl
e`, `market_accounts`.`settlement_type`, `market_accounts`.`ae`, `market_accounts`.`sale`, `market_accounts`.`medium`, `m
arket_accounts`.`is_pc`, `market_accounts`.`is_new_customer` FROM `market_accounts` WHERE (`market_accounts`.`entity` LIK
E %madbaby% OR `market_accounts`.`related_entity` LIKE %madbaby% OR `market_accounts`.`short_title` LIKE %madbaby% OR
`market_accounts`.`title` LIKE %madbaby%) ORDER BY `market_accounts`.`id` DESC

And for some reason I want to execute the sql above by pymysql, but error showed like :


# cursor.execute(sql_str)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MyS
QL server version for the right syntax to use near '%madbaby% OR `market_accounts`.`related_entity` LIKE %madbaby% OR `
market_acco' at line 1")
Get Data Error!

Why the sql string generated by django cannot be executed by pymysql?

The sql I put above is what I got from str(queryset.query) directly. It missed ' ' which confused me.

Solution

  • Django generates unquoted strings in str(qs.query) so you shouldn't feed those to a database. These are for debugging purposes, though I agree it's a bit sloppy that they aren't properly quoted, it is mentioned in the source:

        # django.db.models.sql.query.Query
    def __str__(self):
        """
        Return the query as a string of SQL with the parameter values
        substituted in (use sql_with_params() to see the unsubstituted string).

        Parameter values won't necessarily be quoted correctly, since that is
        done by the database interface at execution time.
        """
        sql, params = self.sql_with_params()
        return sql % params