QSqlQuery return "no such table" (but SQL code seems good)

Hoccau

Hello,

I am using QSqlQuery with Sqlite3 db. This code works perfectly when I execute it in sqlitebrowser. :

INSERT INTO repas(date, type_id, comment) VALUES('2017-04-01', 1, '26ieme');
INSERT INTO outputs(quantity, repas_id, product_id) VALUES(2.0, 26, 8)

But, with QT (python bindings), the following code fail :

from PyQt5.QtSql import QSqlDatabase, QSqlQuery

db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('aa.db')
opened = db.open()

query = QSqlQuery()
query.exec_("PRAGMA foreign_keys = ON")
query.exec_(
        "INSERT INTO repas(date, type_id, comment) VALUES('2017-04-01', 1, '24ieme')")
req = query.exec_("INSERT INTO outputs(quantity, repas_id, product_id) VALUES(2.0, 24, 8)")
print(req, ':', query.lastQuery())
print(query.lastError().text())

it's return the following error :

False : INSERT INTO outputs(quantity, repas_id, product_id) VALUES(2.0, 24, 8)
no such table: main.product Unable to execute statement

I don't understand, there is no table named 'main' in the database structure and it is not my query... What's wrong ?
Thanks!

SGaist

Hi,

Where is aa.db located exactly ?

It's likely something along the lines of opening a database file which path is relative to the executable and the real file is somewhere else.

Hoccau

hi,
aa.db is located in the same path as the script. I have tried with relative or absolute path but i still have the same error.

SGaist

Did you check that the opening succeeded ?

Do you have a product table ?

Hoccau

yes, db.open return True. I have no "product" table... The INSERT requests on other tables works. The problem occurs with this table only.

SGaist

Then were doesn't product_id come from ?

Hoccau

Ok, i have found the problem, it was a bad foreign key on an nonexistent table "product". Perhaps Sqlitebrowser doesn't have foreign key support activated by default, so it was working in it. Sorry, in fact it was a sql issue. Thank for your help!