Issue: IndexError: tuple index out of range when using db.execute_sql
Author: ymrzkrrs
State: closed
Created: 2019-05-06 09:15:18

I have function to create on PostgreSQL:
```
jsonb_flatten_function = """
create or replace function create_jsonb_flat_view
    (view_name text, table_name text, regular_columns text, json_column text)
    returns text language plpgsql as $$
declare
    cols text;
begin
    execute format ($ex$
        select string_agg(format('%2$s->>%%1$L "%2$s.%%1$s"', key), ', ')
        from (
            select distinct key
            from %1$s, jsonb_each(%2$s)
            order by 1
            ) s;
        $ex$, table_name, json_column)
    into cols;
    execute format($ex$
        drop view if exists %1$s;
        create view %1$s as 
        select %3$s, %4$s from %2$s
        $ex$, view_name, table_name, regular_columns, cols);
    return cols;
end $$;
"""
```
If try to execute the previous sql with 
```
db.execute_sql(jsonb_flatten_function)
```
it raises
```
Traceback (most recent call last):
  File "/home/user/init_pg.py", line 91, in <module>
    db.execute_sql(jsonb_flatten_function)
  File "/home/user/mainenv/lib/python3.6/site-packages/peewee.py", line 2940, in execute_sql
    cursor.execute(sql, params or ())
IndexError: tuple index out of range
```

However if I use a `psycopg2` curosr manually or use: 
```
cursor = db.cursor()
cursor.execute(jsonb_flatten_function)
```
It works without any issue. 

Removing `or ()` from `cursor.execute(sql, params or ())` seems to fix it, I'm not sure why it exists in the first place though.

I think your example is not correct or you're not providing all the information. Something doesn't make sense.

For example, this works fine:

```python
from peewee import *
db = PostgresqlDatabase('some_db')
print(db.execute_sql('SELECT current_timestamp').fetchone())
```

The `execute_sql()` function is a fundamental method in peewee and is used extensively.

I just re-run the example from another machine to make sure I did't miss anything. The query you provided `SELECT current_timestamp` works but the one I provided doesn't. Please kindly review again.

Here is a complete example :

```
from peewee import *
from playhouse.postgres_ext import *
from environs import Env
env= Env()
env.read_env()

testing_flatten_function = """
create or replace function testing_flatten_function
    (view_name text, table_name text, regular_columns text, json_column text)
    returns text language plpgsql as $$
declare
    cols text;
begin
    execute format ($ex$
        select string_agg(format('%2$s->>%%1$L "%2$s.%%1$s"', key), ', ')
        from (
            select distinct key
            from %1$s, jsonb_each(%2$s)
            order by 1
            ) s;
        $ex$, table_name, json_column)
    into cols;
    execute format($ex$
        drop view if exists %1$s;
        create view %1$s as 
        select %3$s, %4$s from %2$s
        $ex$, view_name, table_name, regular_columns, cols);
    return cols;
end $$;
"""

# This works without issue
import psycopg2
conn = psycopg2.connect("host={} dbname={} user={} password={}".format(env.str('PG_HOST'), env.str('PG_DB'),  env.str('PG_USER'), env.str('PG_PWD')))
cursor = conn.cursor()
cursor.execute(testing_flatten_function)

db = PostgresqlExtDatabase(env.str('PG_DB'),
                           user=env.str('PG_USER'),
                           password=env.str('PG_PWD'),
                           host=env.str('PG_HOST'))

# This works
print(db.execute_sql('SELECT current_timestamp').fetchone())

# This doesn't work
db.execute_sql(testing_flatten_function)
```

Output:
```
(datetime.datetime(2019, 5, 6, 14, 35, 55, 510756, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)
~/mainenv/lib/python3.6/site-packages/peewee.py in execute_sql(self, sql, params, commit)
   2938             cursor = self.cursor(commit)
   2939             try:
-> 2940                 cursor.execute(sql, params or ())
   2941             except Exception:
   2942                 if self.autorollback and not self.in_transaction():

IndexError: tuple index out of range
```

Perhaps it's a psycopg2 thing, where -- because your SQL query uses the same "%"-style placeholders as Python's string interpolation -- somewhere in the query handling, psycopg2 is trying to interpolate the empty params list.

I'd suggest this is probably something to take up with the psycopg2 folks. Alternatively,

```python
cursor = db.cursor()
cursor.execute(your_sql_here)
```

That's why I checked using psycopg2, which worked fine :
```
import psycopg2
conn = psycopg2.connect("host={} dbname={} user={} password={}".format(env.str('PG_HOST'), env.str('PG_DB'),  env.str('PG_USER'), env.str('PG_PWD')))
cursor = conn.cursor()
cursor.execute(testing_flatten_function)
```
The alternative you provided also works, but in my opinion the problem here is in `db.execute_sql` because in psycopg2 [docs](http://initd.org/psycopg/docs/cursor.html#cursor.execute),  `None` is the default value for `vars` in `cursor.execute(query, vars)`. 

Is there any reason it is `params or ()` instead of simply `params` here https://github.com/coleifer/peewee/blob/master/peewee.py#L2946 ?

> That's why I checked using psycopg2, which worked fine :

Yes and no. psycopg2 is doing some kind of interpolation behind-the-scenes, which to my thinking is a no-no. If you had done `cursor.execute(q, ())` you would again encounter the same error.

@ymrzkrrs Did you ever solve this issue?

@coleifer @ymrzkrrs Nevermind, simply included an extra % to escape the %:

```
and ((id %% 2)) = 0)
```