Why is peewee including the 'id' column into the mysql select query?

Question

I am trying to learn how to use peewee with mysql.

I have an existing database on a mysql server with an existing table. The table is currently empty (I am just testing right now).

>>> db = MySQLDatabase('nhl', user='root', passwd='blahblah')
>>> db.connect()


>>> class schedule(Model):
...     date = DateField()
...     team = CharField()
...     class Meta:
...             database = db

>>> test = schedule.select()
>>> test
<class '__main__.schedule'> SELECT t1.`id`, t1.`date`, t1.`team` FROM `nhl` AS t1 []
>>> test.get()

I get the following error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.6/site-packages/peewee.py", line 1408, in get
    return clone.execute().next()
  File "/usr/lib/python2.6/site-packages/peewee.py", line 1437, in execute
    self._qr = QueryResultWrapper(self.model_class, self._execute(), query_meta)
  File "/usr/lib/python2.6/site-packages/peewee.py", line 1232, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/lib/python2.6/site-packages/peewee.py", line 1602, in execute_sql
    res = cursor.execute(sql, params or ())
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 201, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 't1.id' in 'field list'")

Why is peewee adding the 'id' column into the select query? I do not have an id column in the table that already exists in the database. I simply want to work with the existing table and not depend on peewee having to create one every time I want to interact with the database. This is where I believe the error is.

The result of the query should be empty since the table is empty but since I am learning I just wanted to try out the code. I appreciate your help.

EDIT

Based on the helpful responses by Wooble and Francis I come to wonder whether it even makes sense for me to use peewee or another ORM like sqlalchemy. What are the benefits of using an ORM instead of just running direct queries in python using MySQLdb?

This is what I expect to be doing:

-automatically downloading data from various web servers. Most of the data is in xls or csv format. I can convert the xls into csv using the xlrd package.

-parsing/processing the data in list objects before inserting/bulk-inserting into a mysql db table.

-running complex queries to export data from mysql into python into appropriate data structured (lists for example) for various statistical computation that is easier to do in python instead of mysql. Anything that can be done in mysql will be done there but I may run complex regressions in python.

-run various graphical packages on the data retrieved from queries. Some of this may include using the ggplot2 package (from R-project), which is an advanced graphical package. So I will involve some R/Python integration.

Given the above - is it best that I spend the hours hacking away to learn ORM/Peewee/SQLAlchemy or stick to direct mysql queries using MySQLdb?

Answer 1

If your primary key column name is other than 'id' you should add additional field to that table model class:

class Table(BaseModel):
    id_field = PrimaryKeyField()

That will tell your script that your table has primary keys stored in the column named 'id_field' and that column is INT type with Auto Increment enabled. Here is the documentation describing field types in peewee.

If you want more control on your primary key field, as already pointed by Francis Avila, you should use primary_key=True argument when creating field:

class Table(BaseModel):
    id_field = CharField(primary_key=True)

See this link on non-integer primary keys documentation