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

Most simple active-record pattern ORMs need an id column to track object identity. PeeWee appears to be one of them (or at least I am not aware of any way to not use an id). You probably can't use PeeWee without altering your tables.

Your existing table doesn't seem to be very well designed anyway, since it appears to lack a key or compound key. Every table should have a key attribute - otherwise it is impossible to distinguish one row from another.

If one of these columns is a primary key, try adding a primary_key=True argument as explained in the docs concerning non-integer primary keys

date = DateField(primary_key=True)

If your primary key is not named id, then you must set your table's actual primary key to a type of "PrimaryKeyField()" in your peewee Model for that table.

You should investigate SQLAlchemy, which uses a data-mapper pattern. It's much more complicated, but also much more powerful. It doesn't place any restrictions on your SQL table design, and in fact it can automatically reflect your table structure and interrelationships in most cases. (Maybe not as well in MySQL since foreign key relationships are not visible in the default table engine.) Most importantly for you, it can handle tables which lack a key.

Answer 2

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

Answer 3

You have to provide a primary_key field for this model. If your table doesn't have a single primary_key field(just like mine), a CompositeKey defined in Meta will help.

primary_key = peewee.CompositeKey('date', 'team')

Answer 4

You need to us peewee's create table method to create the actual database table before you can call select(), which will create an id column in the table.