我正在制作一个使用Python Flask和Sqlite3的API。大部分都有效。具体来说:
但是,其余的文章/插入into不起作用。它们都有相同的sqlite3.OperationalError消息:
no such table: main.source
这很奇怪,因为没有一个查询使用名为“source”或主要来源". 我在execute
之前打印查询,并尝试将查询复制/粘贴到sqlite3命令提示符中。当我这样做时,查询没有问题。在
另一个奇怪的地方是,所有的INSERT-INTO查询都调用同一个函数来创建实际的查询(反过来调用函数来运行查询。。。大多数查询都可以使用)。只有一些INSERT into会导致此错误。在
一些潜在的有用信息:
节选创建数据库.sql在
^{pr2}$Python打印的insert/在execute
中引发错误:
INSERT INTO transactions (status, fee, description, source, seller, currency, amount, buyer) VALUES ('initiated', '1', 'nada', '1', '2', 'USD', '1000', '1');
还有一些来自Sqlite的提示:
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
作为参考,下面是一个POST命令,尽管使用了大多数相同的东西,但没有错误:
INSERT INTO users (session, balance, name, firebaseToken) VALUES ('ABCDEFG', '0', 'Mr Miyagi', 'ABCDEFG');
SO上有很多类似的问题,但下面是为什么它们不是重复的:
flask/sqlalchemy - OperationalError: (sqlite3.OperationalError) no such table是的,我在使用表之前创建了这些表。
Why am I suddenly getting "OperationalError: no such table"?我的Flask应用程序能够很好地找到数据库(而且大多数查询都能正常工作)。为了安全起见,我将connect
中的DB设置为绝对路径。没有效果。
Error: sqlite3.OperationalError: no such table: main.m我不做任何奇怪的索引+如果我这样做,复制粘贴将无法工作
Python Sqlite3 - Data is not saved permanently:我确实在@app.teardown_appcontext
中调用commit。我还尝试在每次查询后调用commit。没有效果。
我考虑过但排除的其他问题:
transactions
与transaction
相近,但不一样。source
不在列表中。在我相信这最终会是一个愚蠢的混乱,但任何关于在哪里寻找的想法都将是非常感谢的。我也试着在google上搜索这个错误,但没有看到任何有用的东西。在
更多代码---
这是数据库.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()
这是从中选择的部分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)
注:这个问题的目的不是讨论实施,但如果你想留下评论,我可以。在
---回应评论--
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
)
目前没有回答
相关问题 更多 >
编程相关推荐