Issue: db.create_tables causes exception in MySQLdb
Author: codylandry
State: closed
Created: 2016-10-18 13:20:10

After creating model classes, I attempted to run db.create_tables and received the following error:

```
Traceback (most recent call last):
  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 1580, in <module>
    globals = debugger.run(setup['file'], None, None, is_module)
  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 964, in run
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "/Users/codylandry/PycharmProjects/servicefusion/web2py/applications/servicefusion2/modules/create_db.py", line 197, in <module>
    pw_db.create_tables(tables, safe=True)
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/peewee.py", line 3772, in create_tables
    create_model_tables(models, fail_silently=safe)
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/peewee.py", line 5176, in create_model_tables
    m.create_table(**create_table_kwargs)
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/peewee.py", line 4857, in create_table
    db.create_table(cls)
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/peewee.py", line 3769, in create_table
    return self.execute_sql(*qc.create_table(model_class, safe))
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/peewee.py", line 3683, in execute_sql
    cursor.execute(sql, params or ())
  File "/Users/codylandry/PycharmProjects/servicefusion/venv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not enough arguments for format string
```

Tracing this to cursors.py I found that the _args_ keyword argument in the execute function is an empty tuple instead of _None_.  

```
    def execute(self, query, args=None):

        """Execute a query.

        query -- string, query to execute on server
        args -- optional sequence or mapping, parameters to use with query.

        Note: If args is a sequence, then %s must be used as the
        parameter placeholder in the query. If a mapping is used,
        %(key)s must be used as the placeholder.

        Returns long integer rows affected, if any

        """
        del self.messages[:]
        db = self._get_db()
        if isinstance(query, unicode):
            query = query.encode(db.unicode_literal.charset)
        if args is not None:
            if isinstance(args, dict):
                query = query % dict((key, db.literal(item))
                                     for key, item in args.iteritems())
            else:
                query = query % tuple([db.literal(item) for item in args])
        try:
            r = None
            r = self._query(query)
        except TypeError, m:
            if m.args[0] in ("not enough arguments for format string",
                             "not all arguments converted"):
                self.messages.append((ProgrammingError, m.args[0]))
                self.errorhandler(self, ProgrammingError, m.args[0])
            else:
                self.messages.append((TypeError, m))
                self.errorhandler(self, TypeError, m)
        except (SystemExit, KeyboardInterrupt):
            raise
        except:
            exc, value, tb = sys.exc_info()
            del tb
            self.messages.append((exc, value))
            self.errorhandler(self, exc, value)
        self._executed = query
        if not self._defer_warnings: self._warning_check()
        return r
```

If this is being caused by my model, I'm unclear as to how.  I ran the generated sql and was able to create the table the debugger had broke at, so I believe the model is written correctly.  I think this just a matter of passing None if there are no arguments to the execute function.

I found this in peewee.py.  I'd submit a PR to change it, but I can only test it against mysql.  

```
 def execute_sql(self, sql, params=None, require_commit=True):
        logger.debug((sql, params))
        with self.exception_wrapper:
            cursor = self.get_cursor()
            try:
                cursor.execute(sql, params or ())  # <== should be params or None ?
            except Exception:
                if self.autorollback and self.get_autocommit():
                    self.rollback()
                raise
            else:
                if require_commit and self.get_autocommit():
                    self.commit()
        return cursor
```


You neglected to share the only the thing I really need to debug this: the model definition!


AH, my bad.  Here you go!

```
class customer(BaseModel):
    entityId = ForeignKeyField(null=True, rel_model=entity, on_delete="SET NULL")
    parent = ForeignKeyField(null=True, rel_model='self', on_delete="SET NULL")
    billingAddressId = ForeignKeyField(null=True, rel_model=address, on_delete="SET NULL")
    customerType = EnumField(null=True, choices=('Customer', 'Lead'))
    accountNumber = CharField(null=True, max_length=16)
    name = CharField(null=True, max_length=32)
    active = BooleanField(null=True, default=True)
    taxable = BooleanField(null=True, default=False)
    membershipId = ForeignKeyField(null=True, rel_model=ACmembership, on_delete="SET NULL")
    taxItem = ForeignKeyField(null=True, rel_model=tax, on_delete="SET NULL")
    laborRateAmount = DecimalField(null=True, max_digits=10, decimal_places=3)
    laborChargeRate = EnumField(null=True, choices=('Flat Rate', 'Hourly'))
    billForDriveTime = BooleanField(null=True, default=False)
    discount = DecimalField(null=True, max_digits=10, decimal_places=3)
    discountType = EnumField(null=True, choices=('%', '$'))
    referralSourceId = ForeignKeyField(null=True, rel_model=ACreferralSource, on_delete="SET NULL")
    paymentTermsId = ForeignKeyField(null=True, rel_model=ACCpaymentTerms, on_delete="SET NULL")
    defaultPaymentMethodId = ForeignKeyField(null=True, rel_model=ACCpaymentMethod, on_delete="SET NULL")
    defaultCurrencyId = ForeignKeyField(null=True, rel_model=ACCcurrency, on_delete="SET NULL")
    lastUpdated = DateTimeField(null=True, default=datetime.utcnow())
```

And here's the EnumField def as well:

```
class EnumField(Field):
    db_field = "enum"

    def coerce(self, value):
        if value not in self.choices:
            raise Exception("Invalid Enum Value `%s`", value)
        return str(value)

    def get_column_type(self):
        return "ENUM"

    def get_modifiers(self):
        return ['"%s"' % choice for choice in self.choices]
```


I believe it's the EnumField which has a "%" character. Sqlite gets upset if you explicitly pass in `None`, so that's the inverse of this particular issue (which would be solved by passing in `params or None` instead of `params or ()`). I could move this out into a db-specific parameter, I suppose...but what if you tried passing in a double percent instead of a single percent?  Also if you could share the relevant ddl-generation portions of `EnumField` that'd be awesome.


Here's what _query_ looks like at this point:

``` python
# in MySQLdb/cursor.py/
    def execute(self, query, args=None):
        del self.messages[:]
        db = self._get_db()
        if isinstance(query, unicode):
            query = query.encode(db.unicode_literal.charset)
        if args is not None:
            if isinstance(args, dict):
                query = query % dict((key, db.literal(item))
                                     for key, item in args.iteritems())
            else:
                # <--- sql below is what query looks like here --->
                query = query % tuple([db.literal(item) for item in args])
```

``` sql
CREATE TABLE `customer` (
  `id`                        INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `entityId_id`               INTEGER,
  `parent_id`                 INTEGER,
  `billingAddressId_id`       INTEGER,
  `customerType`              ENUM ("Customer", "Lead"),
  `accountNumber`             VARCHAR(16),
  `name`                      VARCHAR(32),
  `active`                    BOOL,
  `taxable`                   BOOL,
  `membershipId_id`           INTEGER,
  `taxItem_id`                INTEGER,
  `laborRateAmount`           NUMERIC(10, 3),
  `laborChargeRate`           ENUM ("Flat Rate", "Hourly"),
  `billForDriveTime`          BOOL,
  `discount`                  NUMERIC(10, 3),
  `discountType`              ENUM ("%", "$"),
  `referralSourceId_id`       INTEGER,
  `paymentTermsId_id`         INTEGER,
  `defaultPaymentMethodId_id` INTEGER,
  `defaultCurrencyId_id`      INTEGER,
  `lastUpdated`               DATETIME,
  FOREIGN KEY (`entityId_id`) REFERENCES `entity` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `customer` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`billingAddressId_id`) REFERENCES `address` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`membershipId_id`) REFERENCES `acmembership` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`taxItem_id`) REFERENCES `tax` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`referralSourceId_id`) REFERENCES `acreferralsource` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`paymentTermsId_id`) REFERENCES `accpaymentterms` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`defaultPaymentMethodId_id`) REFERENCES `accpaymentmethod` (`id`)
    ON DELETE SET NULL,
  FOREIGN KEY (`defaultCurrencyId_id`) REFERENCES `acccurrency` (`id`)
    ON DELETE SET NULL
)
```


Well, in the implementation of the EnumField, I wonder if it'd be better to parameterize the values it accepts...is this possible? i.e. it would generate DDL like `SQL('ENUM (%s))' % ', '.join('%s' for _ in values), values)`.

WDYT?


How is that different from what it does now?  The generated sql is correct and making it all the way to the execute method without issue.  If this was an issue, wouldn't it cause problems in generating the field sql instead when trying to execute?  By the time the generated sql gets to the db, the string formatting for the fields has been done.  


I verified that double % works.  Sorry to waste your time.  It wasn't obvious to me at first why that would fail. Thanks for looking into it though.


Its completely non obvious! Glad we got it fixed.


I have an application that must support both Sqlite and MySQL. How can I make the above `EnumField` compatible with `SqliteDatabase`? When I use the option `field_types={"ENUM": "TEXT"}`, the produced DDL is not valid for Sqlite:

    customerType TEXT("Customer", "LEAD")

Instead I want `customerType TEXT`.