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?

Solution 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.

Solution 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

Solution 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')

Comments

[codingknob]

Will that create the table in my actual database? I already have that table in the database. I'm very confused as the peewee documentation is not very friendly for newbies.

[codingknob]

Also - is there a step by step guide/examples on how to do something like a) connect to db, b) insert records stored in a list object into a table, c) perform complicated query (with joins, group-by etc.), and d) store results of query in a python list object which then can be used for other purposes. The peewee documentation does not properly explain the end use case of the package and how to walk through the work flow I describe from a to d for example.

[codingknob]

peewee.readthedocs.org/en/latest/peewee/example.html. Every model has a create_table() classmethod which runs a CREATE TABLE statement in the database. It will create the table, including all columns, foreign-key constaints, and indexes. Usually this is something you’ll only do once, whenever a new model is added.. How can this package not work with an existing table? Am I missing something? Appreciate the help.

[Wooble]

Check out peewee.readthedocs.org/en/latest/peewee/… -- you need to use the pwiz utility to generate models for a legacy database; the default usage is to define the models and let the framework handle the database side.

[codingknob]

Thank you for the detailed reply. As it turns out the existing table in the database does have a PRIMARY KEY (date). Do I need to use pwiz as indicated by Wooble to provide this information to peewee? Appreciate the help.

[Francis Avila]

You don't need to--pwiz is just a code generator and does nothing you couldn't do yourself. Probably just include primary_key=True argument as explained in the docs describing non-integer primary keys.

[codingknob]

Thank you for that Wooble! I will take a look. However, I made an edit to my original post with a follow-up question if you have any guidance on that. I appreciate it. Thank you.

[codingknob]

I'm investigating SQLAlchemy as peewee will not do what I want in the future.

[coleifer]

Sorry to hear peewee isn't doing what you need -- if you could provide any suggestions or things I could do to improve it I'd be grateful.

[ySJ]

I've update the answer, it's clear now.Just found a way to make a CompositeKey.

[OzzyTheGiant]

this worked for me, also do not confuse primary_key for unique, if you're new to databases.