sqlite 如何在数据库中添加新行？

我正在创建一个用于修改SQLite数据库的简单应用程序。
我尝试在MainWindow::on_AddButton_clicked()方法中添加新行，但是当我使用MainWindow::on_reselectTable_clicked()方法重新选择SqlTableView时，所有新行都被删除了。
MainWindow.h：

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlTableModel>
#include <QDebug>
#include <QtSql>
QT_BEGIN_NAMESPACE
namespace Ui { class MainWindow; }
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private slots:
    void on_AddButton_clicked();

    void on_tableView_clicked(const QModelIndex &index);

    void on_DeleteButton_clicked();

    void on_comboBox_currentIndexChanged(int index);

    void on_submitButton_clicked();

    void on_revertButton_clicked();

    void on_reselectTable_clicked();

private:
    Ui::MainWindow *ui;

    QSqlDatabase db;
    QSqlTableModel* model;
    int currentRow;
};
#endif // MAINWINDOW_H

MainWindow.cpp:

#include "mainwindow.h"
#include "./ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("127.0.0.1");
    //db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("../db/cities.db3");

    if (db.open())
    {
        ui->statusbar->showMessage("Successful database connection: " + db.databaseName());
        model = new QSqlTableModel(this, db);
        model->setTable("cities");
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);

        model->select();

        ui->tableView->setModel(model);
        //rui->tableView->setColumnHidden(0, true);
        ui->tableView->setColumnHidden(1, true);
        ui->tableView->setColumnHidden(2, true);

        ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
        ui->tableView->setSortingEnabled(true);
    }
    else
    {
        ui->statusbar->showMessage("Connection error: " + db.lastError().databaseText());
    }
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_tableView_clicked(const QModelIndex &index)
{
    currentRow = index.row();
}

void MainWindow::on_comboBox_currentIndexChanged(int index)
{
    qDebug() << index;
    switch (index)
    {
    case 0:
        model->setFilter("population > 0");
        break;
    case 1:
        model->setFilter("population > 1000");
        break;
    case 2:
        model->setFilter("population > 10000");
        break;
    case 3:
        model->setFilter("population > 25000");
        break;
    case 4:
        model->setFilter("population > 50000");
        break;
    case 5:
        model->setFilter("population > 100000");
        break;
    case 6:
        model->setFilter("population > 250000");
        break;
    case 7:
        model->setFilter("population > 500000");
        break;
    case 8:
        model->setFilter("population > 1000000");
        break;
    case 9:
        model->setFilter("population > 2000000");
        break;
    case 10:
        model->setFilter("population > 20000000");
    default: // nop
        break;
    }
    model->select();
}

void MainWindow::on_AddButton_clicked()
{
    qDebug() << "inserting row:" << model->insertRow(model->rowCount());
}

void MainWindow::on_DeleteButton_clicked()
{
    qDebug() << "deleting row:" << model->removeRow(currentRow);
    model->select();
}

void MainWindow::on_submitButton_clicked()
{
    model->submitAll();
}

void MainWindow::on_revertButton_clicked()
{
    model->revertAll();
}

void MainWindow::on_reselectTable_clicked()
{
    model->select();
}

CMakeList.txt

cmake_minimum_required(VERSION 3.5)

project(cities VERSION 0.1 LANGUAGES CXX)

set(CMAKE_INCLUDE_CURRENT_DIR ON)

set(CMAKE_AUTOUIC ON)
set(CMAKE_AUTOMOC ON)
set(CMAKE_AUTORCC ON)

set(CMAKE_CXX_STANDARD 14)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

find_package(QT NAMES Qt5 COMPONENTS Widgets Sql REQUIRED)
find_package(Qt${QT_VERSION_MAJOR} COMPONENTS Widgets Sql REQUIRED)

set(PROJECT_SOURCES
        main.cpp
        mainwindow.cpp
        mainwindow.h
        mainwindow.ui
)
add_executable(cities ${PROJECT_SOURCES})

if(SQLite3_FOUND)
    include_directories(${SQLITE_INCLUDE_DIRS})
    target_link_libraries(cities, ${SQLITE_LIBRARIES})
endif(SQLite3_FOUND)

target_link_libraries(cities PRIVATE Qt${QT_VERSION_MAJOR}::Widgets Qt${QT_VERSION_MAJOR}::Sql)

从QSqlTableModel：：选择：

**注意：**调用select（）将恢复所有未提交的更改并删除所有插入的列。

因此，在尝试调用select()之前，您需要提交更改，但是如果您这样做而没有填充新添加的行，它仍然无法工作。
要检查原因，您可以使用：用途：

qDebug()<<model->submitAll();
qDebug()<<model->lastError();

这将输出：

false
QSqlError("", "No Fields to update", "")

范例：

#include <QApplication>
#include <QtWidgets>
#include <QtSql>

int main(int argc,char*argv[])
{
    QApplication a(argc, argv);

    QWidget w;
    QVBoxLayout l(&w);

    QSqlDatabase db;
    QSqlTableModel* model;
    QTableView tableView;
    QPushButton add("add");
    QPushButton submit("submit");
    QPushButton reselect("reselect");

    l.addWidget(&add);
    l.addWidget(&submit);
    l.addWidget(&reselect);
    l.addWidget(&tableView);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("cities.db3");

    if(db.open())
    {
        model = new QSqlTableModel(&w, db);
        model->setTable("cities");
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);

        model->select();

        tableView.setModel(model);
    }
    else
    {
        qDebug()<<"Could not open database file";

        return 0;
    }

    QObject::connect(&add,&QPushButton::clicked,[=]()
    {
        qDebug() << "inserting row:" << model->insertRow(model->rowCount());
    });

    QObject::connect(&submit,&QPushButton::clicked,[=]()
    {
        qDebug()<<model->submitAll();
        qDebug()<<model->lastError();
    });

    QObject::connect(&reselect,&QPushButton::clicked,[=]()
    {
        model->select();
    });

    w.show();

    return a.exec();
}

sqlite 如何在数据库中添加新行？

1条答案

相关问题

热门标签

最新问答