peewee.ProgrammingError: column "id" does not exist

Question

Hi, I am following along the tutorial on my local machine and using PostgreSQL as my database. I have already created the database, and a table 'staff', with the corresponding fields username and points.

However, when I try to run my script, I get the following error:

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\peewee.py", line 3191, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: column "id" does not exist
LINE 1: ..." ("username", "points") VALUES ('yeyau', 10) RETURNING "id"
                                                                   ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\yeyau\Documents\tsc_exchange\staff.py", line 42, in <module>
    add_staff()
  File "C:\Users\yeyau\Documents\tsc_exchange\staff.py", line 25, in add_staff
    Staff.create(username=s_member['username'],points=s_member['points']) #Staff is our db, create adds a new record to the db
  File "C:\Python34\lib\site-packages\peewee.py", line 4127, in create
    inst.save(force_insert=True)
  File "C:\Python34\lib\site-packages\peewee.py", line 4287, in save
    pk_from_cursor = self.insert(**field_dict).execute()
  File "C:\Python34\lib\site-packages\peewee.py", line 2980, in execute
    cursor = self._execute()
  File "C:\Python34\lib\site-packages\peewee.py", line 2470, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "C:\Python34\lib\site-packages\peewee.py", line 3199, in execute_sql
    self.commit()
  File "C:\Python34\lib\site-packages\peewee.py", line 3048, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "C:\Python34\lib\site-packages\peewee.py", line 123, in reraise
    raise value.with_traceback(tb)
  File "C:\Python34\lib\site-packages\peewee.py", line 3191, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: column "id" does not exist
LINE 1: ..." ("username", "points") VALUES ('yeyau', 10) RETURNING "id"
                                                                   ^

My code is:

from peewee import *

db = PostgresqlDatabase(database='tsc_exchange', user='postgres', password='postgres')

class Staff(Model):
    username = CharField(db_column='username', max_length=255, unique=True)
    points = IntegerField(default=10)

    class Meta:
        database = db

staff = [
            {'username': 'yeyau', 'points': 10},
            {'username': 'crystal', 'points': 20},
            {'username': 'spacecadet', 'points': 30},
            {'username': 'marple', 'points': 40},
            {'username': 'sherlock', 'points': 50},
            {'username': 'doc', 'points': 60}
        ]


def add_staff():
    for s_member in staff:
        try:
            Staff.create(username=s_member['username'],points=s_member['points']) #Staff is our db, create adds a new record to the db
        except IntegrityError:
            s_member_record = Staff.get(username=staff['username'])

            if s_member_record['points'] != s_member[points]:
                s_member_record.points = s_member['points']
                s_member_record.save()


def top_staff():
    staff = Staff.select().order_by(Staff.points.desc()).get()
    return staff


if __name__ == '__main__':
    db.connect()
    db.create_tables([Staff], safe=True)
    add_staff()
    print("The staff member with the most points is {0.username}".format(top_staff()))

I tried to google the error message and perhaps the closest thing I could find was (I'm also curious as to which table it knows my Staff model is referring to in the db? My db currently has one table staff, but what if it had more?):

https://github.com/coleifer/peewee/issues/44

I tried to explicitly specify which column username refers to in my DB, but still the same error.

I'm not too sure how else to trouble shoot this - any help/ideas would be greatly appreciated!

Answer 1 · 2015-07-26T00:20:56Z

July 26, 2015 12:20am

Figured it out - I had actually not only created the database in postgresql beforehand, but also the table as well (without the id column), seems like when the script is run, an additional column of id is created in the db - hence where the error occurred.

Basically don't create the table beforehand, just the DB.

Answer 2 · 2015-07-25T18:22:10Z

July 25, 2015 6:22pm

Not sure I can help much with this, but one thought would be to start adding in test functions to make sure you have the right classes, column headers etc. Just adding print statements to check the value of things is a quick and dirty way to test things too. Or, you can also try the python debugger (pdb). Kenneth Love has nice lectures on testing and pdb. Good luck!!!

I also found this link where someone seemed to be having a similar issue that they were able to fix:

http://stackoverflow.com/questions/20168582/programmingerror-column-genre-id-of-relation-music-album-does-not-exist-w

Welcome to the Treehouse Community

Looking to learn something new?

Yeeka Yau

Yeeka Yau

peewee.ProgrammingError: column "id" does not exist

2 Answers

Yeeka Yau

Yeeka Yau

Frederick Pearce

Frederick Pearce

Yeeka Yau

Yeeka Yau

Frederick Pearce

Frederick Pearce

Frederick Pearce

Frederick Pearce

Yeeka Yau

Yeeka Yau