QSqlDatabase用法(插入图片，取出图片)，linux系统，sqlite3

主要代码如下
初始化sql，在info.dat中创建三个table

    QSqlDatabase warning_db = QSqlDatabase::addDatabase("QSQLITE", "warning_init");
    warning_db.setDatabaseName("info.dat");
    if (!warning_db.open()){
        qDebug() << warning_db.lastError().text();
        return;
    }
    // 执行SQL创建表
    warning_db.exec("CREATE TABLE IF NOT EXISTS WARNING (\
                    time  TEXT NOT NULL, \
                    id    TEXT NOT NULL, \
                    type  TEXT NOT NULL, \
                    data  TEXT)"
    );
    warning_db.commit();
    warning_db.close();

    QSqlDatabase invironment_db = QSqlDatabase::addDatabase("QSQLITE", "inv_init");
    invironment_db.setDatabaseName("info.dat");
    if (!invironment_db.open()){
        qDebug() << invironment_db.lastError().text();
        return;
    }
    //环境数据刷新速率限制在1HZ以下
    invironment_db.exec("CREATE TABLE IF NOT EXISTS INVIRONMENT (\
                        time  TEXT NOT NULL, \
                        id    TEXT NOT NULL, \
                        rain  INT, \
                        temp  INT, \
                        humi  INT, \
                        wdir  INT, \
                        PRIMARY KEY (time,id))"
    );
    invironment_db.commit();
    invironment_db.close();

    QSqlDatabase picture_db = QSqlDatabase::addDatabase("QSQLITE", "picture_init");
    picture_db.setDatabaseName("info.dat");
    if (!picture_db.open()){
        qDebug() << picture_db.lastError().text();
        return;
    }
    picture_db.exec("CREATE TABLE IF NOT EXISTS PICTURE (\
                    time  TEXT NOT NULL, \
                    id    TEXT NOT NULL, \
                    pic   BLOB)"
    );
    picture_db.commit();
    picture_db.close();

删除连接

    QSqlDatabase::removeDatabase("picture_init");
    QSqlDatabase::removeDatabase("inv_init");
    QSqlDatabase::removeDatabase("warning_init");

初始化数据库

	QDateTime dateTime;
	QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", info.connectionName);
	qDebug() << QSqlDatabase::connectionNames();
	db.setDatabaseName(info.dbName);
	db.open();
	if (!db.open()){
	    qDebug() << db.lastError().text();
	    qDebug() << QString("thread [%1] exit").arg(pthread_self());
	    return NULL;
	}
	QSqlQuery query = QSqlQuery(db);

插入图片数据

	//图片数据以qbytearray形式保存
	QByteArray arr;
	dateTime = QDateTime::currentDateTime();
	query.prepare(QString("INSERT INTO PICTURE(time, id, pic) VALUES('%1', '%2', :imageData)")
	         .arg(dateTime.toString("yyyy-MM-dd hh:mm:ss"))
	         .arg(QString("%1-%2").arg(info.index).arg(info.index)));
	//get qbytearray
	query.bindValue(":imageData", arr);
	if(!query.exec()){
	    qDebug() << "inserting image into table :" << query.lastError();
	}

取出图片数据并保存到QList picarr中

QString qtime = QString("%1-%2-%3 %4:")
            .arg(ui->pyear->text())
            .arg(ui->pmonth->text().toInt() > 10 ? ui->pmonth->text() : ("0" + ui->pmonth->text()))
            .arg(ui->pday->text().toInt() > 10 ? ui->pday->text() : ("0" + ui->pday->text()))
            .arg(ui->phour->text().toInt() > 10 ? ui->phour->text() : ("0" + ui->phour->text()));
    QString monitor = ui->pmonitor->text();
    QString camera = ui->pcamera->text();
    QSqlDatabase db;
    if(QSqlDatabase::contains("picture_select")){
        db = QSqlDatabase::database("picture_select");
    }else{
        db = QSqlDatabase::addDatabase("QSQLITE", "picture_select");
        db.setDatabaseName("info.dat");
    }
    db.open();
    if (!db.open()){
        qDebug() << db.lastError().text();
        return;
    }
    QSqlQuery query = QSqlQuery(db);
    query.prepare("SELECT * FROM PICTURE");
    if(!query.exec()){
        qDebug() << query.lastError();
        return;
    }
    QSqlRecord rec = query.record();
    int row = 0;
    QMap<int, QStringList>valuemap;
    QStringList list;
    QList<QByteArray> picarr;
    while(query.next()){
        rec = query.record();
        int time = rec.indexOf("time");
        int id = rec.indexOf("id");
        int pic = rec.indexOf("pic");
        QString value1 = query.value(time).toString();
        QString value2 = query.value(id).toString();

        if (value2.split("-").at(0) == monitor &&
            value2.split("-").at(1) == camera  &&
            value1.contains(qtime))
        {
            qDebug() << "time:" << value1 << "id:" << value2;
            list << value1 << value2;
            valuemap.insert(row, list);
            list.clear();
            picarr << query.value(pic).toByteArray();
            ++row;
        }
    }
    qDebug() << row;
    ui->ptab->setRowCount(row);
    QMapIterator<int, QStringList>iter(valuemap);
    while(iter.hasNext()){
        iter.next();
        qDebug() << iter.value().size();
        for (int i = 0; i < iter.value().size(); i++) {
            ui->ptab->setCellWidget(iter.key(), i, new QLabel(iter.value().at(i)));
        }
        QPushButton *btn = new QPushButton("显示");
        btn->setProperty("index", picarr.at(iter.key()));
        connect(btn, &QPushButton::clicked, this, [=](){
            QPixmap pix;
            pix.loadFromData(sender()->property("index").toByteArray());
            ui->plabel->setPixmap(pix.scaledToWidth(ui->plabel->width()));
        });
        ui->ptab->setCellWidget(iter.key(), 2, btn);
    }
    db.close();

最终效果