Issue: pwiz breaks with a table named `servers` on mysql
Author: g3rv4
State: closed
Created: 2014-02-16 09:11:01

If you have a table named `servers` on mysql (and a FK to it), pwiz fails with

```
Traceback (most recent call last):
  File "env/bin/pwiz.py", line 540, in <module>
    print_models(options.engine, database, tables, **connect)
  File "env/bin/pwiz.py", line 444, in print_models
    models, table_to_model, table_fks, col_meta = introspect(db, schema)
  File "env/bin/pwiz.py", line 419, in introspect
    table_columns[rel_table][rel_pk].field_class = PrimaryKeyField
KeyError: 'id'
```

but if you don't have FK, pwiz just generates

```
from peewee import *

database = MySQLDatabase('db', **{'user': 'user', 'passwd': 'pwd'})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Servers(BaseModel):
    db = CharField(db_column='Db', max_length=64)
    host = CharField(db_column='Host', max_length=64)
    server_name = IntegerField(db_column='Server_name')
    username = CharField(db_column='Username', max_length=64)

    class Meta:
        db_table = 'servers'
```

Which is also wrong. I'd add a list of RESERVED_TABLE_NAMES as you currently have RESERVED_WORDS for the columns.


Are you sure the problem is the foreign key to `servers`? Can you create a test-case?


I'd love to. Do you have an example of a test case? I'm just getting started with python


I don't see `servers` listed in mysql's reserved words list: https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html


I don't think that's a reserved word in mysql, sorry if I gave you that impression. I think it's something in pwiz.py that doesn't let it work with a table named `servers`. In order to replicate, I created a new database with this table

```
CREATE TABLE `servers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(50) NULL,
  PRIMARY KEY (`id`));
```

Created a new project and installed

```
PyMySQL==0.6.1
peewee==2.2.1
```

Then, I ran `python env\Scripts\pwiz.py -e mysql -u user -P pass peewee` inside the virtualenv, and this is what it returned

```
from peewee import *

database = MySQLDatabase('peewee', **{'user': 'user', 'passwd': 'pass'})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Servers(BaseModel):
    db = CharField(max_length=64, db_column='Db')
    host = CharField(max_length=64, db_column='Host')
    server_name = IntegerField(db_column='Server_name')

    class Meta:
        db_table = 'servers'
```

You can see that pwiz created a class named Servers, but it doesn't have the `ip` field that's on the table `servers`.

If you also have a FK to your `servers` table, pwiz just breaks... to show this, I created another table and added a FK to `servers` running this query

```
CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `server_id` INT NULL,
  `name` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `server` (`server_id` ASC),
  CONSTRAINT `server`
    FOREIGN KEY (`server_id`)
    REFERENCES `servers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);
```

Now, when I execute `python env\Scripts\pwiz.py -e mysql -u user -P pass peewee` I get

```
Traceback (most recent call last):
  File "env\Scripts\pwiz.py", line 540, in <module>
    print_models(options.engine, database, tables, **connect)
  File "env\Scripts\pwiz.py", line 444, in print_models
    models, table_to_model, table_fks, col_meta = introspect(db, schema)
  File "env\Scripts\pwiz.py", line 419, in introspect
    table_columns[rel_table][rel_pk].field_class = PrimaryKeyField
KeyError: 'id'
```

I was able to replicate this in an Ubuntu as well.


I spent some time messing around with this and you're totally right. Apologies -- I never use MySQL and needed to install it and test to see what exactly was going on.


No need to apologize at all :) let me know if I can give you a hand. I'm totally new at Python but your project has helped me a lot already


There was a bug in the introspection of primary keys w/mysql. Fixed in 20329564fc740bc94e0751a94cd6def88b36da5b