Does PeeWee support interaction with MySQL Views?

Question

I am trying to access pre-created MySQL View in the database via. peewee treating it as a table [peewee.model], however I am still prompted with Operational Error 1054 unknown column.

Does PeeWee Supports interactions with database view ?

Answer 1

Peewee has been able to query against views when I've tried it, but while typing up a simple proof-of-concept I ran into two potential gotcha's.

First, the code:

from peewee import *

db = SqliteDatabase(':memory:')

class Foo(Model):
    name = TextField()
    class Meta: database = db

db.create_tables([Foo])
for name in ('huey', 'mickey', 'zaizee'):
    Foo.create(name=name)

OK -- nothing exciting, just loaded three names into a table. Then I made a view that corresponds to the upper-case conversion of the name:

db.execute_sql('CREATE VIEW foo_view AS SELECT UPPER(name) FROM foo')

I then tried the following, which failed:

class FooView(Foo):
    class Meta:
        db_table = 'foo_view'

print [fv.name for fv in FooView.select()]

Then I ran into the first issue.

When I subclassed "Foo", I brought along a primary key column named "id". Since I used a bare select() (FooView.select()), peewee assumed i wasnted both the "id" and the "name". Since the view has no "id", I got an error.

I tried again, specifying only the name:

print [fv.name for fv in FooView.select(FooView.name)]

This also failed.

The reason this second query fails can be found by looking at the cursor description on a bare select:

curs = db.execute_sql('select * from foo_view')
print curs.description[0][0]  # Print the first column's name.
# prints UPPER(name)

SQLite named the view's column "UPPER(name)". To fix this, I redefined the view:

db.execute_sql('CREATE VIEW foo_view AS SELECT UPPER(name) AS name FROM foo')

Now, when I query the view it works just fine:

print [x.name for x in FooView.select(FooView.name)]
# prints ['HUEY', 'MICKEY', 'ZAIZEE']

Hope that helps.