Qt C++中SQLite数据库创建成功但未生成表及插入数据问题求助

Hey there! Let's figure out why your Qt C++ code can create a database but fails to make tables or insert data. Here are the most common issues and fixes to try:

1. You're not checking for errors in database operations #

Qt's QSqlQuery::exec() returns a boolean to indicate success, but skipping error messages means you're flying blind. Always capture and print the error details to pinpoint what's wrong:

QSqlQuery query;
if (!query.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")) {
    qDebug() << "Failed to create table:" << query.lastError().text();
}

2. The database connection isn't properly open when you run operations #

Sometimes after creating the database, the connection might drop, or you're not tying your queries to the correct connection. Make sure the database is open first, and pass the connection object to your QSqlQuery instances:

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

if (!db.open()) {
    qDebug() << "Couldn't open database:" << db.lastError().text();
    return;
}

// Tie the query to your open connection
QSqlQuery createTableQuery(db);

3. Your SQL syntax is incorrect #

Typos (like CREAT TABLE instead of CREATE TABLE), mismatched data types, or missing constraints can break table creation. Print your full SQL string and test it directly in a database tool (like SQLiteStudio for SQLite) to verify it works.

4. Transactions aren't being committed (if you're using them) #

If you started a transaction with db.transaction() but forgot to call db.commit(), all your changes will roll back. Either commit the transaction when you're done, or skip using transactions if you don't need them.

Example working code for reference #

Here's a complete, tested snippet that follows best practices for creating tables and inserting data:

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QDebug>

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

    // Set up database connection (adjust driver for your DB type)
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("user_data.db");

    if (!db.open()) {
        qDebug() << "Database open failed:" << db.lastError().text();
        return 1;
    }

    // Create Table 1: User Profiles
    QSqlQuery createProfileTable(db);
    QString profileSql = "CREATE TABLE IF NOT EXISTS user_profiles ("
                         "user_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                         "full_name TEXT NOT NULL,"
                         "age INTEGER)";
    if (!createProfileTable.exec(profileSql)) {
        qDebug() << "Profile table error:" << createProfileTable.lastError().text();
    } else {
        qDebug() << "Profile table created successfully";
    }

    // Insert data into Table 1
    QSqlQuery insertProfile(db);
    insertProfile.prepare("INSERT INTO user_profiles (full_name, age) VALUES (:name, :age)");
    insertProfile.bindValue(":name", "Amirhossein Daghigh Kia");
    insertProfile.bindValue(":age", 30);
    if (!insertProfile.exec()) {
        qDebug() << "Profile insert error:" << insertProfile.lastError().text();
    } else {
        qDebug() << "Profile data inserted";
    }

    // Create Table 2: User Preferences
    QSqlQuery createPrefsTable(db);
    QString prefsSql = "CREATE TABLE IF NOT EXISTS user_preferences ("
                       "user_id INTEGER,"
                       "notification_enabled BOOLEAN DEFAULT true,"
                       "FOREIGN KEY(user_id) REFERENCES user_profiles(user_id))";
    if (!createPrefsTable.exec(prefsSql)) {
        qDebug() << "Preferences table error:" << createPrefsTable.lastError().text();
    } else {
        qDebug() << "Preferences table created successfully";
    }

    // Insert data into Table 2
    QSqlQuery insertPrefs(db);
    insertPrefs.prepare("INSERT INTO user_preferences (user_id, notification_enabled) VALUES (:id, :enabled)");
    insertPrefs.bindValue(":id", 1);
    insertPrefs.bindValue(":enabled", false);
    if (!insertPrefs.exec()) {
        qDebug() << "Preferences insert error:" << insertPrefs.lastError().text();
    } else {
        qDebug() << "Preferences data inserted";
    }

    db.close();
    return a.exec();
}

Extra troubleshooting tips #

• Double-check your database driver is correctly installed (e.g., for MySQL, ensure the qsqlmysql.dll/.so file is in your app's path).
• For SQLite, verify the database file path: use qDebug() << db.databaseName(); to see where the file is actually created (it might be in your program's working directory, not where you expect).

内容的提问来源于stack exchange，提问作者Amirhossein Daghigh Kia