SQLite3 / python: results differ when choosing from VIEW instead of TABLE

Question

So, I was revising part of my code to use SQL views instead of repeating the same query all over the place. And suddenly, some of the unit tests begin to fail with messages whose requests do not have all the necessary attributes.

So, knowing that VIEW really should return the same structure (with only a few filters), this was strange. I was able to play it with the following code:

#!/usr/bin/env python3

import sqlite3

db = sqlite3.connect(':memory:')
db.row_factory = sqlite3.Row

db.execute('CREATE TABLE foo(id INT PRIMARY KEY, alpha TEXT, beta TEXT)')
db.execute('CREATE VIEW  foo_view AS SELECT * FROM foo')

db.execute("INSERT INTO foo(alpha, beta) VALUES ('hello', 'world')")

foo_res      = [dict(r) for r in db.execute('SELECT "f"."alpha", "f"."beta" FROM "foo"      "f"')]
foo_view_res = [dict(r) for r in db.execute('SELECT "f"."alpha", "f"."beta" FROM "foo_view" "f"')]

print(foo_res)
print(foo_view_res)

At startup, the output is as follows:

[{'alpha': 'hello', 'beta': 'world'}]
[{'f': 'hello'}]

In my opinion, this should lead to the same dictation. What's going on here?

There is also a difference between python3 and python2. When launched on python2, the output is as follows:

[{'alpha': u'hello', 'beta': u'world'}]
[{'"f"."beta"': u'world', '"f"."alpha"': u'hello'}]

Answer 1

, . .

, , select. , python 3:

>>> [dict(r) for r in db.execute("select f.alpha, f.beta from foo f")]
[{'alpha': 'hello', 'beta': 'world'}]
>>> [dict(r) for r in db.execute("select f.alpha, f.beta from foo_view f")]
[{'f.alpha': 'hello', 'f.beta': 'world'}]

(: ). select, :

>>> [dict(r) for r in db.execute("select 'f'.alpha, f.beta from foo_view as f")]
[{'f': 'hello', 'f.beta': 'world'}]

, :

>>> [dict(r) for r in db.execute('select f."alpha", f."beta" from foo_view as "f"')]
[{'f."alpha"': 'hello', 'f."beta"': 'world'}]

, , , . , , - select.

, .