Issue: Cannot work with VIEWs in SQLite
Author: erakli
State: closed
Created: 2019-07-24 01:40:06

I tried to use peewee to select from view. I reflected view 

```python
>>> models = generate_models(db, include_views=True)
>>> globals().update(models)
>>> print_model(store_items_view)
store_items_view
  item_no TEXT
  color_id INT
  new_or_used TEXT
  completeness TEXT
  quantity DEFAULT
  unit_price INT
  last_date DEFAULT
```

Then I tried to get anything from it

```python
>>> store_items_view.get()
<store_items_view: (None, None, None, None, None, None, None)>
```

If we inspect fields of returned instance, we found that all of them are empty. More interesting that `t1` field added:

```python
>>> store_items_view.get().t1
'10039c01'
```

SQL generated for `store_items_view.select()`:

```sql
SELECT 
    "t1"."item_no", 
    "t1"."color_id", 
    "t1"."new_or_used", 
    "t1"."completeness", 
    "t1"."quantity",
    "t1"."unit_price",
    "t1"."last_date" 
FROM "store_items_view" AS "t1"
```

Maybe the problem is in quoted `"t1"` prefix?

peewee 3.9.6

My guess is you're using an ancient version of sqlite. Try this and check the output:

```python
>>> import sqlite3
>>> sqlite3.sqlite_version  # My version is quite new.
'3.29.0'
```

You need to upgrade your sqlite for peewee to properly infer the column names.

For example, the following works fine on my machine:

```python
db = SqliteDatabase(':memory:')

class Register(Model):
    key = TextField()
    value = IntegerField()
    class Meta:
        database = db

db.create_tables([Register])

Register.insert_many([('k%02d' % i, i) for i in range(100)]).execute()

db.execute_sql('create view "small_reg" as '
               'select id, key, value from register '
               'where value < 13')

class SmallReg(Register):
    class Meta:
        table_name = 'small_reg'

query = SmallReg.select()
for obj in query:
    print (obj.id, obj.key, obj.value)
```

Yes, I have SQLite 3.11.0.

What minimal version is supported by peewee?

> On 24 Jul 2019, at 05:02, Charles Leifer <notifications@github.com> wrote:
> 
> Closed #1975.
> 
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub, or mute the thread.


That's actually not as old as I would have thought. I'm compiling an older sqlite and am going to check it out some more.

So here's what's happening... Peewee makes use of database drivers implementing the db-api 2.0 spec (psycopg2, stdlib sqlite3, pymysql, etc). After executing a query using a cursor, the cursor exposes a `description` property that tells you the names of the selected columns.

In my above example, the `cursor.description` (as reported by the sqlite3 driver) is:

```python
>>> cursor.description                                                                                                              
(('t1', None, None, None, None, None, None), ('t1', None, None, None, None, None, None), ('t1', None, None, None, None, None, None))
```

The column names reported by the driver use the `sqlite3_column_name()` API. Seems like this behavior changed... I'll do some more digging.

I'm not sure why sqlite is behaving differently when using a view versus a regular table -- because it works as one would expect when querying a table. I suppose for now you can use a workaround of just explicitly aliasing the fields you select, or possibly try upgrading to a more recent sqlite?

Thanks for your advises and fast response, I appreciate this. 

I'll probably use aliases as a workaround because it would be more bulletproof. For now it works, thank you!

```python
>>> store_items_view.select(store_items_view.color_id.alias('color_id')).get()
<store_items_view: (None, 11, None, None, None, None, None)>
>>> print(store_items_view.select(store_items_view.color_id.alias('color_id')))
SELECT "t1"."color_id" AS "color_id" FROM "store_items_view" AS "t1"
```
Is there any short-cut to define aliases in a compact way?

FYI, SQLA always defines aliases for every column. Probably, peewee need to do the same?

I've patched this -- we use a more aggressive approach to slotting selected columns into the appropriate field slot. Tested it with sqlite 3.9 and verified it is working as expected.

> SQLA always defines aliases for every column

Should be no need to do this, honestly, except maybe for old versions of sqlite. I think I'll pass on that for now.

So, I could expect fixed behavior in next release?

> On 24 Jul 2019, at 17:35, Charles Leifer <notifications@github.com> wrote:
> 
> I've patched this -- we use a more aggressive approach to slotting selected columns into the appropriate field slot. Tested it with sqlite 3.9 and verified it is working as expected.
> 
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub, or mute the thread.


yea

Great! Thank you. 

> On 24 Jul 2019, at 17:57, Charles Leifer <notifications@github.com> wrote:
> 
> yea
> 
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub, or mute the thread.