Import a SQL VIEW from SQLite to Pandas
The "SQL As Understood By SQLite" documentation clearly states:
"The CREATE VIEW command assigns a name to a pre-packaged SELECT statement.
Once the view is created, it can be used in the FROM clause of another SELECT
in place of a table name."
http://www.sqlite.org/lang_createview.html
Most first level Python/SQLite packages (SQLite and APSW) support SQL VIEWS,
but SQAlchemy sometimes chokes when it attempts to parse a VIEW in Python
rather than simply passing the entire SQL SELECT statement to the
SQLite C API interface (see the SQLite mailing list thread
"Views as Virtual Tables -- Command line vs. Called Interface"
both the initial email and the code example by Keith Medcalf
where both the SQLite and APSW libraries are tested).
The SQLite command line shell interface ".tables" dot-command
returns both TABLEs and VIEWs:
The ".tables" command is similar to setting list mode then
executing the following query:
SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1
http://sqlite.org/cli.html
The code example, above is similar to the Python dbtools line:
cmd = "SELECT name FROM sqlite_master WHERE type='table'"
so, this suggests that, if this line were changed to:
cmd = "SELECT name FROM sqlite_master WHERE type IN ('table','view')"
dbtools would be able to import VIEWS as well as TABLES.
Although the SQL standard calls for VIEWS to be update-able, in reality
few implementations fully implement this complex and demanding feature
and SQLite3 documentation flatly states that, "Views are read-only in SQLite."
The CREATE VIEW command assigns a name to a pre-packaged SELECT statement.
Once the view is created, it can be used in the FROM clause of another SELECT
in place of a table name.
... You cannot DELETE, INSERT, or UPDATE a view.
Views are read-only in SQLite.
... Views are removed with the DROP VIEW command.
http://www.sqlite.org/lang_createview.html
Because, the SQLite3 documentation clearly states that,
"You cannot DELETE, INSERT, or UPDATE a view" we will
need IF statements preventing a VIEW from being passed
to DELETE, INSERT, or UPDATE methods in dbtools.
The IF statement test for view could be implemented as:
# if attempting to change a (read only) view, throw an error
# views are read-only in SQLite.
if type = 'view'
raise ValueError(
"Not a TABLE: %s\n\n"
"** SQLite3 VIEWS are read-only\n"
"** can only update base tables")
Import a SQL VIEW from SQLite to Pandas
The "SQL As Understood By SQLite" documentation clearly states:
Most first level Python/SQLite packages (SQLite and APSW) support SQL VIEWS,
but SQAlchemy sometimes chokes when it attempts to parse a VIEW in Python
rather than simply passing the entire SQL SELECT statement to the
SQLite C API interface (see the SQLite mailing list thread
"Views as Virtual Tables -- Command line vs. Called Interface"
both the initial email and the code example by Keith Medcalf
where both the SQLite and APSW libraries are tested).
The SQLite command line shell interface ".tables" dot-command
returns both TABLEs and VIEWs:
http://sqlite.org/cli.html
The code example, above is similar to the Python dbtools line:
so, this suggests that, if this line were changed to:
dbtools would be able to import VIEWS as well as TABLES.
Although the SQL standard calls for VIEWS to be update-able, in reality
few implementations fully implement this complex and demanding feature
and SQLite3 documentation flatly states that, "Views are read-only in SQLite."
Because, the SQLite3 documentation clearly states that,
"You cannot DELETE, INSERT, or UPDATE a view" we will
need IF statements preventing a VIEW from being passed
to DELETE, INSERT, or UPDATE methods in dbtools.
The IF statement test for view could be implemented as: