Python sqlite3 cannot view table views

Question

Through python, I open the SQLite database and try to access the view of the table view. Sorry, I am getting an error. The following is the Python code:

import sqlite3 conn = sqlite3.connect("test.db") mydb = conn.cursor() mydb.execute("CREATE TABLE TestTbl (MRTarget_id int, Fullmodel text)") mydb.execute("CREATE TABLE TestTbl2 (Other_id int, Othermodel text)") mydb.execute("CREATE VIEW TestView AS SELECT m.ROWID, m.MRTarget_id, m.Fullmodel, t.Othermodel FROM TestTbl m, TestTbl2 t") mydb.execute("CREATE VIEW TestView2 AS SELECT m.Fullmodel, m.Othermodel FROM TestView m") mydb.close() 

Python spits out the error "sqlite3.OperationalError: there is no such column: m.Fullmodel" after trying to create TestView2. However, from the Sqlite3 prompt, the above SQL statements can be executed without any problems. Since my database contains table view views, I wonder if it is even possible to access this programmatically through Python.

Answer 1

I had the same problem - and I found a solution. I believe your problem is that in your initial “TestView”, the attribute names are actually m.ROWID , m.Fullmodel , etc. Instead of just ROWID , Fullmodel , etc.

A casual look at the views through the SQL manager will not show m. attached to the front of each field name. If you run the Pragma PRAGMA table_info TestView , attribute extensions will be expanded.

So, change your TestView request to

 CREATE VIEW TestView AS SELECT m.ROWID as ROWID, m.MRTarget_id as MRTarget_id,... etc 

and your second Create View request should succeed - at least that was in my application.

Answer 2

Your code works fine for me.

You can try to create the first view and the second:

 conn.commit()