I am making an API that uses Python Flask and Sqlite3. Most of it works. Specifically:
- all GET endpoints/SELECT queries work
- two POST endpoints/INSERT INTO queries work
However, the remaining POSTs/INSERT INTOs do not work. They all have the same sqlite3.OperationalError with the message:
no such table: main.source
This is weird because none of the queries use a table called "source" or "main.source". I print the queries before I execute them, and I have tried copy/pasting the queries into the sqlite3 command prompt. The queries have no issues when I do that.
The other weird thing is that all the INSERT INTO queries call the same function to create the actual query (which in turn calls the function to run queries ... used by ALL queries most of which work). Only some of the INSERT INTOs cause this error.
Some potentially useful information:
An excerpt from createdb.sql
CREATE TABLE transactions (
id INTEGER PRIMARY KEY,
buyer INTEGER NOT NULL,
seller INTEGER NOT NULL,
amount INTEGER NOT NULL,
currency VARCHAR(6) NOT NULL,
fee INTEGER NOT NULL,
source INTEGER NOT NULL,
description TEXT NOT NULL,
status VARCHAR(40) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
The insert into that Python prints/that throws an error in execute:
INSERT INTO transactions (status, fee, description, source, seller, currency, amount, buyer) VALUES ('initiated', '1', 'nada', '1', '2', 'USD', '1000', '1');
And some stuff from Sqlite's prompt:
sqlite> .tables
conversations sources users
messages transactions withdrawals
sqlite> SELECT id, description FROM transactions;
1|hella mulah
2|payback
3|woohoo
sqlite> INSERT INTO transactions (status, fee, description, source, seller, currency, amount, buyer) VALUES ('initiated', '1', 'nada', '1', '2', 'USD', '1000', '1');
sqlite>
sqlite> SELECT id, description FROM transactions;
1|hella mulah
2|payback
3|woohoo
4|nada
For references, here is a POST command that has no error despite using most of the same stuff:
INSERT INTO users (session, balance, name, firebaseToken) VALUES ('ABCDEFG', '0', 'Mr Miyagi', 'ABCDEFG');
There are a lot of similar questions on SO but here's why they are not duplicates:
flask/sqlalchemy - OperationalError: (sqlite3.OperationalError) no such table Yes, I created the tables before using them.
Why am I suddenly getting "OperationalError: no such table"? my Flask app is able to find the database no problem (and most of the queries work without a hitch). To be safe I made my DB in
connectan absolute path. No effect.Error: sqlite3.OperationalError: no such table: main.m I don't do any weird indexing + if I did this the copy pasting wouldn't work
Python Sqlite3 - Data is not saved permanently: I do call commit in
@app.teardown_appcontext. I also tried calling commit after every query. No effect.
Other stuff I considered but ruled out:
- There is a list of disallowed names, but these are not included http://www.sqlite.org/lang_keywords.html.
transactionsis close totransactionbut not the same.sourceis not on the list.
I'm sure this will end up being some kind of silly messup but any ideas on where to look would be much appreciated. I also tried Googling this error but I didn't see anything helpful.
--- more code ---
This is database.py
import sqlite3
import flask
import backend
def dict_factory(cursor, row):
output = {}
for idx, col in enumerate(cursor.description):
output[col[0]] = row[idx]
return output
def get_db():
if not hasattr(flask.g, 'sqlite_db'):
flask.g.sqlite_db = sqlite3.connect("/my/absolute/path/var/data.db"
)
flask.g.sqlite_db.row_factory = dict_factory
flask.g.sqlite_db.execute("PRAGMA foreign_keys = ON;")
return flask.g.sqlite_db
def query(query, args=(), islast=False):
print(query) # this is where the print from before is
cur = get_db().execute(query, args)
rowvector = cur.fetchall()
if islast:
cur.close()
return rowvector
@backend.app.teardown_appcontext
def close_db(error):
if hasattr(flask.g, 'sqlite_db'):
flask.g.sqlite_db.commit()
flask.g.sqlite_db.close()
This is selected sections from apiimpl.py
QUERY_INSERT = "INSERT INTO"
QUERY_SELECT = "SELECT"
QUERY_UPDATE = "UPDATE"
def queryhelper(*args, **kwargs):
sqltxt = None
selectstr = None
if kwargs["action"] == QUERY_INSERT:
sqltxt = "{} {} ({}) VALUES ({});".format(
QUERY_INSERT,
kwargs["table"],
", ".join(["{}".format(x) for x in kwargs["cols"]]),
", ".join(["'{}'".format(x) for x in kwargs["vals"]]),
)
# pretty sure this next bit is not relevant but here it is anyway
selectstr = "SELECT * FROM {} WHERE ROWID=(SELECT last_insert_rowid());".format(
kwargs["table"],
)
elif kwargs["action"] == QUERY_SELECT:
# not relevant
elif kwargs["action"] == QUERY_UPDATE:
# not relevant
else:
assert(kwargs["action"] in [QUERY_INSERT, QUERY_SELECT, QUERY_UPDATE,])
try:
rv = db.query(sqltxt) # this is where the error is thrown
if selectstr:
return db.query(selectstr)
else:
return rv
except sqlite3.OperationalError as e:
# this is where the error is caught
return api_error("SQL error (1): {}", str(e), code=500)
def append(tablename, args):
tabledata = TABLES().tablenamemap[tablename]
print("tablename: " + tablename) # "tablename: transactions"
# a bunch of error detection
rv = queryhelper(
action=QUERY_INSERT,
table=tablename,
cols=args.keys(),
vals=args.values(),
)
# not shown: potentially returning json.dumps(rv)
return rv
def transactions_post(req):
# a lot of stuff to turn req into validargs
# printed validargs: {'status': 'initiated', u'fee': u'1', u'description': u'nada', u'source': u'1', u'seller': u'2', u'currency': u'USD', u'amount': u'1000', u'buyer': u'1'}
return append("transactions", validargs)
@backend.app.route("/transactions", methods=["GET", "POST", "PUT"])
def transactions_route():
return {
"GET": transactions_get, # get list of transactions
"POST": transactions_post, # initiate a transaction
"PUT": transactions_put, # change transaction status
}[flask.request.method](flask.request)
P.S. the purpose of this question is not to discuss the implementation, but if you want to leave a comment that's ok with me.
--- in response to comment --
sqlite> SELECT * FROM sqlite_master WHERE type="table" AND name="transactions";
table|transactions|transactions|4|CREATE TABLE transactions (
id INTEGER PRIMARY KEY,
buyer INTEGER NOT NULL,
seller INTEGER NOT NULL,
amount INTEGER NOT NULL,
currency VARCHAR(6) NOT NULL,
fee INTEGER NOT NULL,
source INTEGER NOT NULL,
description TEXT NOT NULL,
status VARCHAR(40) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (buyer) REFERENCES users(id), -- do not want to delete on CASCADE
FOREIGN KEY (seller) REFERENCES users(id), -- do not want to delete on CASCADE
FOREIGN KEY (source) REFERENCES source(id) -- do not want to delete on CASCADE
)
