system views not reflectable via metadata.reflect(only=['sqlite_master'])

[sqlalchemy-bot]

Issue created by Anonymous

---

The special sqlite_master metadata table is not reflectable via sqlalchemy.

Try:

import sqlalchemy
from sqlalchemy import Table, Column, MetaData, Integer
conn = sqlalchemy.create_engine('sqlite:///:memory:')
meta = MetaData()
meta.bind = conn
t = sqlalchemy.Table('test',meta,Column('a',Integer))
t.create()
meta.reflect(only=['sqlite_master']('sqlite_master'))

Basically the table is not listed itself in sqlite_master, but is in principle introspectable. The sqlite driver should maybe add sqlite_master to the list of available tables. Or one could argue thats more a view and that it isn't introspectable per se.

[sqlalchemy-bot]

jek (jek) wrote:

---

The system views are reflectable if named explicitly:

>>> t = Table('sqlite_master', MetaData('sqlite:///'), autoload=True)
>>> print t.c
['sqlite_master.name', 'sqlite_master.tbl_name', 'sqlite_master.rootpage', 'sqlite_master.sql']('sqlite_master.type',)

They aren't reflected in the table name list though. I think most of the dialects omit system-owned views from the tables list, and I'll put a stick in the ground and say that sqlite should continue to as well, at least for now. metadata.drop_all() shouldn't try to drop sqlite_master, which is what would happen today if metadata.reflect() pulled that in as well. Future work on view reflection could eliminate that hiccup.

Allowing only=['sqlite_master']('sqlite_master') to load an identifier that's not present in the full list or available to the callable variant of only wigs me out a little... It would be more convenient than the explicit alternative, though.

Poking into this revealed that we weren't considering temporary tables with reflect(), and that's now in 5320a47a14177e48a993e5333851888252a2d691 along with improved handling of sqlite_master on attached databases.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

no action is going to be taken here as metadata.reflect_all() is about the user-defined tables.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

---

[sqlalchemy-bot]

Changes by jek (jek): changed title from "sqlite_master not reflectable" to "system views not reflectable via metadata.reflect(only=['sqlite_master'])"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): set state to "wontfix"

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek): set milestone to "blue sky"