我的不和谐.js机器人的 sql 没有/无法从我的 sqlite3 db 定义一个表

Question

我已经为此工作了大约3天，但我所尝试的一切都没有奏效。是的，我的代码很草率，但它似乎无法从我的data.db中获取表格。

那里至少有一个表有我的userid和其他东西，但它确定它是未定义的。

else{
const pr1 = new Promise( useless => {
sql.get(`SELECT * FROM data WHERE userid ="${message.author.id}"`)})
.then(row => {
if (!row) {
sql.run("INSERT INTO data (userid, rollsleft, registered) VALUES (?, ?, ?)", [message.author.id, 4, 1]);
db.close()
}
}).catch(error => {
console.log(error)
sql.run("CREATE TABLE IF NOT EXISTS data (userid INTEGER, rollsleft INTEGER, registered INTEGER)")
.then(() => {
sql.run("INSERT INTO data (userid, rollsleft, registered) VALUES (?, ?, ?)", [message.author.id, 4, 1]);
db.close()
});
});

我确实犯了一个错误。

Error: SQLITE_RANGE: column index out of range {errno: 25, code: 'SQLITE_RANGE', stack: 'Error: SQLITE_RANGE: column index out of range', message: 'SQLITE_RANGE: column index out of range'}

Answer 1

我想您对better-sqlite3的工作原理有点困惑。

首先定义您正在使用的npm包：

const database = require('better-sqlite3');

现在定义数据库和数据库名称——当你运行机器人程序时，这会在物理上创建一个空的数据库文件

const sql = new Database('foobar.db');

现在要向数据库文件中添加一个表。

let row = sql.get('SELECT * FROM tblExample WHERE id ="${message.author.id}"').run();
//runs a select query using the table name "tblExample" byval row "id"
if(!row) { //i.e if the table "tblExample" doesnt exist (or if there are no rows that match) - in this case if you have set up the table in the right way it will be because the table doesn't exist
sql.prepare('CREATE TABLE IF NOT EXISTS tblExample ("id", "columns_go_here")').run();
//creates the table if it doesnt already exist
};
//then you can populate the database by using an "INSERT" statement
sql.prepare('INSERT INTO tblExample VALUES ("1111", "example")').run();
//and if you need to pull data from it use the same statement as you first used
let row = sql.get('SELECT * FROM tblExample WHERE id ="1111"').run();
//then you should be able to do
let exampleData = row.columns_go_here;
//this exampleData variable should hold the value "example" - lets console.log() it
console.log(exampleData);
//if you see "example" in your console your database is working

如果你有任何问题，请留下评论，希望过多的评论能有所帮助。

查看这里的文档