Issue: TypeError: not enough arguments for format string
Author: kelmore5
State: closed
Created: 2020-05-08 03:04:54

I believe there's some unexpected behavior that happens when using the `Database.execute_sql` command with an SQL statement but no parameters. The above error occurs in the pymysql project, but it's actually a problem with peewee (in my opinion).

When you run the following code:

```
sql = "INSERT INTO some_table (column_a, column_b) VALUES ('hello_my_name_is_inigo', 'odd_%_percent')
database.execute_sql(sql) # or database.execute_sql(sql, params=None)
```

you receive an error that looks like this:

```
Traceback (most recent call last):
    ...
    models.main_database.execute_sql(sql)
  File "/home/kyle/.local/lib/python3.7/site-packages/peewee.py", line 3057, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/kyle/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 171, in execute
    query = self.mogrify(query, args)
  File "/home/kyle/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 150, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string
```

* Quick note: Anyone reading this can fix the error by 'escaping' the % sign in your SQL statement, from '%' to '%%'

Technically, this happens because there's a percent sign in the SQL statement that needs to be escaped so the line `query = query % self._escape_args(args, conn)` in pymysql can run. However, this puts the burden on the user to figure that out.

I think the real culprit is the params argument in `database.execute_sql` which doesn't seem to be passed correctly into `cursor.execute` at line 3057 in peewee.py. In other words, the line `cursor.execute(sql, params or ())` seems to be the problem. I think we should consider changing the line to this:

`cursor.execute(sql, args=params or None)`

which is more in line with pymysql's documentation, seen [here](https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.execute).



To give some more context, the line failing in pymysql (within `cursor.execute`) looks like this:

```
# "pymysql/cursors.py", lines 149-150

if args is not None:
    query = query % self._escape_args(args, conn)
```

So passing in `params` as an empty tuple really just creates more trouble then it's worth. It should stay as `None`.

It would be different if I was passing in an SQL statement with the expectation that it was a string going to be formatted. Then the error `TypeError: not enough arguments for format string` would make sense, and I could debug it easily. However, I believe passing in only an SQL statement should just execute that SQL statement, without needing to think about formatting or `params` from `cursor.execute`.

Maybe I'm missing something, but is there a reason why `params` is defaulted to an empty tuple in the first place?

Finally, this issue isn't just prevalent [here](https://github.com/coleifer/peewee/issues/1394), but also [Django](https://code.djangoproject.com/ticket/20115), [SQLAlchemy](https://github.com/PyMySQL/PyMySQL/issues/367), and although correct behavior for the package, [pymysql](https://github.com/PyMySQL/PyMySQL/issues/562) itself. So I think it's definitely something that should be fixed as I'm sure more people are having trouble with it.

If anything, we should at least consider updating peewee's documentation for `execute_sql`, seen [here](http://docs.peewee-orm.com/en/latest/peewee/api.html?highlight=execute_sql#Database.execute_sql) to better explain how formatting works, like within pymysql's documentation [here](https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.Cursor.execute). Then the error might make more sense after looking it up.

Thanks

As you can see from the traceback:

```python
File "/home/kyle/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 150, in mogrify
    query = query % self._escape_args(args, conn)
```

The error is in pymysql, not peewee.

No.

The error is not because of pymysql. It's because peewee incorrectly passes in the `params` parameter into `cursor.execute`.

Even if it was, pymysql is not going to change their method, as stated in these issues [here](https://github.com/PyMySQL/PyMySQL/issues/562), [here](https://github.com/PyMySQL/PyMySQL/issues/507), and [here](https://github.com/PyMySQL/PyMySQL/issues/367). Seems easier just to correctly pass in the parameter.

Just curious, why r u using pymysql?

IMO mysql.connector works (and it has C extensions for better performance).

No reason really. Never thought about it.

I probably googled it at some point and picked the first thing that worked.

Passing `params=None` does not work with psycopg2.

Also I'd like to point out that the whole problem only arises because you are passing the parameters in literally, as part of the query, rather than using placeholders and a parameterized query. There are several reasons why this is bad: sql injection is the most obvious.

Hm, well that's fair-ish. Really I brought it up because it seemed like other people were having trouble with it.

> Passing `params=None` does not work with psycopg2.

And as long as there's a reason for it, I'm satisfied. Hopefully laying out the issue will help people in the future.