Sqlite with Qt - step by step
Posted in C++ with Qt, how to • Aug 28, 2015
Accessing SQL databases from C++ applications is very simple with Qt library. Here is some
short example that presents how to do it. I have chosen SQLite engine because it’s the easiest
engine to set up (it requires no server, no configuration…), still it’s suitable for the most of
possible applications.
Before you begin - sql drivers
Qt requires drivers to deal with SQL databases. Depending on your distribution, you can have
the drivers installed by default with your Qt or not. If you face problems like “QSqlDatabase:
QSQLITE driver not loaded” you need to install drivers manually. Error should be followed
by a console output that informs you which drivers you have installed: “QSqlDatabase:
available drivers:”. Read more about sql drivers….
Design and create the database
Let’s assume, for simplicity, that we need to store people data – only name and corresponding
id. With sqlite you can create such a simple database with two console commands. First line is
creating database people. Second one is creating the table people. It cointains a key (integer)
and name (text). To leave the sqlite console just type “.quit”.
$ sqlite3 people.db
sqlite> CREATE TABLE people(ids integer primary key, name text);
sqlite> .quit
Congrats, your database is ready. Now we can create C++ application to play with it.
Access database from Qt application
Create new Qt project. In .pro file you need to add:
QT += sql
It will link your project against QtSql module. If you don’t use QtCreator but another IDE, for
example Visual Studio, your project may need setting linker dependency to QtSql.lib. You
need to do it if your compilation fails with unresolved external symbol error. In VS linker
settings is located in Properties/Configuration properties/Linker/Input. In the
Additional Dependencies add Qt5Sqld.lib for Debug and Qt5Sql.lib for release.
Now you can create a class responsible for database access. Let’s call it DbManager.
class DbManager
{
public:
DbManager(const QString& path);
private:
QSqlDatabase m_db;
};
The constructor of DbManager sets up the database connection and opens it. The path
argument value is the path to your database file.
DbManager::DbManager(const QString& path)
{
m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName(path);
if (!m_db.open())
{
qDebug() << "Error: connection with database failed";
}
else
{
qDebug() << "Database: connection ok";
}
}
Define needed operations
For managing people resources we need the possibility to:
add new person,
remove person,
check if person exists in database,
print all persons,
delete all persons.
Actions can be implemented in following way: define QSqlQuery, then call prepare method.
Prepare argument can be raw SQL query string, or it can contain placeholders for variables —
placeholders must begin with colon. After prepare call bindValue to fill the placeholders with
proper values. When the query is ready, execute it.
bool DbManager::addPerson(const QString& name)
{
bool success = false;
// you should check if args are ok first...
QSqlQuery query;
query.prepare("INSERT INTO people (name) VALUES (:name)");
query.bindValue(":name", name);
if(query.exec())
{
success = true;
}
else
{
qDebug() << "addPerson error:"
<< query.lastError();
}
return success;
}
QSqlQuery::lastError() is very useful, you should not skip it in your implementation.
Rules of creating queries are simple, so I think you already know how removing person looks
like:
if (personExists(name))
{
QSqlQuery query;
query.prepare("DELETE FROM people WHERE name = (:name)");
query.bindValue(":name", name);
success = query.exec();
if(!success)
{
qDebug() << "removePerson error:"
<< query.lastError();
}
}
Print all persons:
QSqlQuery query("SELECT * FROM people");
int idName = query.record().indexOf("name");
while (query.next())
{
QString name = query.value(idName).toString();
qDebug() << name;
}
Check if person with specific name exists:
QSqlQuery query;
query.prepare("SELECT name FROM people WHERE name = (:name)");
query.bindValue(":name", name);
if (query.exec())
{
if (query.next())
{
// it exists
}
}
To remove all persons, just run:
QSqlQuery query;
query.prepare("DELETE FROM people");
query.exec();
You can find the whole source code on my github. All you need to do before running this
example is to create your SQLite database and write the valid path to it in main.cpp.
QT5 TUTORIAL MODELVIEW WITH QTABLEVIEW
AND QITEMDELEGATE - 2020
ModelView with QTableView and
QItemDelegate
In this tutorial, we will learn about
ModelView QTableView and QItemDelegate.
The QTableView class provides a default model/view implementation of a
table view. The QItemDelegate class provides display and editing facilities
for data items from a model.
In this example, we'll use Qt Gui application with QDialog:
As we discussed in other ModelView tutorials, Qt's MVC may not be the
same as the conventional MVC.
If the view and the controller objects are combined, the result is the
model/view architecture. This still separates the way that data is stored from
the way that it is presented to the user, but provides a simpler framework
based on the same principles. This separation makes it possible to display
the same data in several different views, and to implement new types of
views, without changing the underlying data structures. To allow flexible
handling of user input, we introduce the concept of the delegate. The
advantage of having a delegate in this framework is that it allows the way
items of data are rendered and edited to be customized.
However, it's worth investigating their approach of using "delegate" with
their Model/View pattern.
QItemDelegate can be used to provide custom display features and editor
widgets for item views based on QAbstractItemView subclasses. Using a
delegate for this purpose allows the display and editing mechanisms to be
customized and developed independently from the model and view.
The QItemDelegate class is one of the Model/View Classes and is part of Qt's
model/view framework. Note that QStyledItemDelegate has taken over the
job of drawing Qt's item views. We recommend the use of
QStyledItemDelegate when creating new delegates.
When displaying items from a custom model in a standard view, it is often
sufficient to simply ensure that the model returns appropriate data for each of
the roles that determine the appearance of items in views. The default
delegate used by Qt's standard views uses this role information to display
items in most of the common forms expected by users. However, it is
sometimes necessary to have even more control over the appearance of items
than the default delegate can provide.
In this tutorial, we'll use Model-Based TableView:
Since we finished layout, now is the time for coding.
Let's make our model in delegatetableviewdialog.h:
#ifndef DELEGATETABLEVIEWDIALOG_H
#define DELEGATETABLEVIEWDIALOG_H
#include <QDialog>
#include <QTableView>
#include <QItemDelegate>
#include <QStandardItemModel>
namespace Ui {
class DelegateTableViewDialog;
}
class DelegateTableViewDialog : public QDialog
{
Q_OBJECT
public:
explicit DelegateTableViewDialog(QWidget *parent = 0);
~DelegateTableViewDialog();
private:
Ui::DelegateTableViewDialog *ui;
// QStandardItemModel provides a classic
// item-based approach to working with the model.
QStandardItemModel *model;
};
#endif // DELEGATETABLEVIEWDIALOG_H
Move on to the implementation file, delegatetableviewdialog.cpp:
#include "delegatetableviewdialog.h"
#include "ui_delegatetableviewdialog.h"
DelegateTableViewDialog
::DelegateTableViewDialog(QWidget *parent) :
QDialog(parent),
ui(new Ui::DelegateTableViewDialog)
{
ui->setupUi(this);
// Create a new model
// QStandardItemModel(int rows, int columns, QObject * parent
= 0)
model = new QStandardItemModel(4,2,this);
// Attach the model to the view
ui->tableView->setModel(model);
// Generate data
for(int row = 0; row < 4; row++)
{
for(int col = 0; col < 2; col++)
{
QModelIndex index
= model->index(row,col,QModelIndex());
// 0 for all data
model->setData(index,0);
}
}
}
DelegateTableViewDialog::~DelegateTableViewDialog()
{
delete ui;
}
Let's run the code to see what we've done.
Looks good. We're on track!<
All data are set to zero.
Also note that Qt already provides SpinBox withing a cell of the TableView.
Let's customize the SpinBox.
Customizing the SpinBox using
QItemDelegate
To customize the SpinBox, we need to create a new class.
When a new instance of MyDelegate created, it does not have the methods
to use to make it functiioning (i.e. customizing the SpinBox). As we can see
from the table below, we need to re-implement couple of virtual methods
inherited from QItemDelegate class.
Type Description
virtual createEditor(QWidget * parent, const QStyleOptionViewItem & option, const
QWidget * QModelIndex & index) const
virtual void paint(QPainter * painter, const QStyleOptionViewItem & option, const
QModelIndex & index) const
virtual void setEditorData(QWidget * editor, const QModelIndex & index) const
setModelData(QWidget * editor, QAbstractItemModel * model, const
virtual void
QModelIndex & index) const
sizeHint(const QStyleOptionViewItem & option, const QModelIndex & index)
virtual QSize
const
updateEditorGeometry(QWidget * editor, const QStyleOptionViewItem & option,
virtual void
const QModelIndex & index) const
So, let's work on the header file (mydelegate.h) to make some prototypes of
those methods:
#ifndef MYDELEGATE_H
#define MYDELEGATE_H
#include <QItemDelegate>
#include <QModelIndex>
#include <QObject>
#include <QSize>
#include <QSpinBox>
class MyDelegate : public QItemDelegate
{
Q_OBJECT
public:
explicit MyDelegate(QObject *parent = 0);
// Create Editor when we construct MyDelegate
QWidget createEditor(QWidget *parent, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const;
// Then, we set the Editor
void setEditorData(QWidget *editor, const QModelIndex
&index;) const;
// When we modify data, this model reflect the change
void setModelData(QWidget *editor, QAbstractItemModel *model,
const QModelIndex &index;) const;
// Give the SpinBox the info on size and location
void updateEditorGeometry(QWidget *editor, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const;
signals:
public slots:
};
#endif // MYDELEGATE_H
So, the prototypes for the inherited virtual member functions are done. Now,
let our dialog know we now have a new delegate class:
Go to "delegatetableviewdialog.h"
#ifndef DELEGATETABLEVIEWDIALOG_H
#define DELEGATETABLEVIEWDIALOG_H
#include <QDialog>
#include <QTableView>
#include <QItemDelegate>
#include <QStandardItemModel>
#include "mydelegate.h"
namespace Ui {
class DelegateTableViewDialog;
}
class DelegateTableViewDialog : public QDialog
{
Q_OBJECT
public:
explicit DelegateTableViewDialog(QWidget *parent = 0);
~DelegateTableViewDialog();
private:
Ui::DelegateTableViewDialog *ui;
// QStandardItemModel provides a classic
// item-based approach to working with the model.
QStandardItemModel *model;
// Make a member pointer to a new MyDelegate instance
MyDelegate *myDelegate;
};
#endif // DELEGATETABLEVIEWDIALOG_H
Then, go to the dialog implementation file (delegatetableviewdialog.cpp):
#include "delegatetableviewdialog.h"
#include "ui_delegatetableviewdialog.h"
DelegateTableViewDialog
::DelegateTableViewDialog(QWidget *parent) :
QDialog(parent),
ui(new Ui::DelegateTableViewDialog)
{
ui->setupUi(this);
myDelegate = new MyDelegate(this);
// Create a new model
// QStandardItemModel(int rows, int columns, QObject * parent
= 0)
model = new QStandardItemModel(4,2,this);
// Generate data
for(int row = 0; row < 4; row++)
{
for(int col = 0; col < 2; col++)
{
QModelIndex index
= model->index(row,col,QModelIndex());
// 0 for all data
model->setData(index,0);
}
}
// Attach (tie) the model to the view
ui->tableView->setModel(model);
// Tie the View with the new MyDelegate instance
// If we don not set this, it will use default delegate
ui->tableView->setItemDelegate(myDelegate);
}
DelegateTableViewDialog::~DelegateTableViewDialog()
{
delete ui;
}
Implementing Delegate
Now, we need to work on the inherited virtual member functions.
Note that we've already written the prototypes in mydelegate.h.
Go to the implementation file, mydelegate.cpp.
#include "mydelegate.h"
MyDelegate::MyDelegate(QObject *parent) :
QItemDelegate(parent)
{
}
// TableView need to create an Editor
// Create Editor when we construct MyDelegate
// and return the Editor
QWidget* MyDelegate::createEditor(QWidget *parent,
const QStyleOptionViewItem
&option;,
const QModelIndex &index;)
const
{
QSpinBox *editor = new QSpinBox(parent);
editor->setMinimum(0);
editor->setMaximum(100);
return editor;
}
// Then, we set the Editor
// Gets the data from Model and feeds the data to Editor
void MyDelegate::setEditorData(QWidget *editor,
const QModelIndex &index;) const
{
// Get the value via index of the Model
int value = index.model()->data(index, Qt::EditRole).toInt();
// Put the value into the SpinBox
QSpinBox *spinbox = static_cast<QSpinBox*>(editor);
spinbox->setValue(value);
}
// When we modify data, this model reflect the change
void MyDelegate::setModelData(QWidget *editor, QAbstractItemModel
*model,
const QModelIndex &index;) const
{
// Give the SpinBox the info on size and location
void MyDelegate::updateEditorGeometry(QWidget *editor,
const QStyleOptionViewItem
&option;,
const QModelIndex &index;)
const
{
}
If we run it now, we'll have:
Most of the things seem to work. However, note that the SpinBox we see is
not properly located. That's because we haven't finished the Geometry for
Editor. Let's do that now. Also, after we changed the value in the SpinBox,
it's not put that value back to the Model.
Let's do the rest of the coding:
// When we modify data, this model reflect the change
// Data from the delegate to the model
void MyDelegate::setModelData(QWidget *editor, QAbstractItemModel
*model, const QModelIndex &index;) const
{
QSpinBox *spinbox = static_cast<QSpinBox*>(editor);
spinbox->interpretText();
int value = spinbox->value();
model->setData(index, value, Qt::EditRole);
}
// Give the SpinBox the info on size and location
void MyDelegate::updateEditorGeometry(QWidget *editor, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const
{
editor->setGeometry(option.rect);
}
Here is the file used in this tutorial: DelegateTableView.zip.
delegatetableviewdialog.h:
#ifndef DELEGATETABLEVIEWDIALOG_H
#define DELEGATETABLEVIEWDIALOG_H
#include <QDialog>
#include <QTableView>
#include <QItemDelegate>
#include <QStandardItemModel>
#include "mydelegate.h"
namespace Ui {
class DelegateTableViewDialog;
}
class DelegateTableViewDialog : public QDialog
{
Q_OBJECT
public:
explicit DelegateTableViewDialog(QWidget *parent = 0);
~DelegateTableViewDialog();
private:
Ui::DelegateTableViewDialog *ui;
// QStandardItemModel provides a classic
// item-based approach to working with the model.
QStandardItemModel *model;
// Make a member pointer to a new MyDelegate instance
MyDelegate *myDelegate;
};
#endif // DELEGATETABLEVIEWDIALOG_H
delegatetableviewdialog.cpp:
#include "delegatetableviewdialog.h"
#include "ui_delegatetableviewdialog.h"
DelegateTableViewDialog
::DelegateTableViewDialog(QWidget *parent) :
QDialog(parent),
ui(new Ui::DelegateTableViewDialog)
{
ui->setupUi(this);
myDelegate = new MyDelegate(this);
// Create a new model
// QStandardItemModel(int rows, int columns, QObject * parent
= 0)
model = new QStandardItemModel(4,2,this);
// Generate data
for(int row = 0; row < 4; row++)
{
for(int col = 0; col < 2; col++)
{
QModelIndex index
= model->index(row,col,QModelIndex());
// 0 for all data
model->setData(index,0);
}
}
// Attach (tie) the model to the view
ui->tableView->setModel(model);
// Tie the View with the new MyDelegate instance
// If we don not set this, it will use default delegate
ui->tableView->setItemDelegate(myDelegate);
}
DelegateTableViewDialog::~DelegateTableViewDialog()
{
delete ui;
}
mydelegate.h:
#ifndef MYDELEGATE_H
#define MYDELEGATE_H
#include <QItemDelegate>
#include <QModelIndex>
#include <QObject>
#include <QSize>
#include <QSpinBox>
class MyDelegate : public QItemDelegate
{
Q_OBJECT
public:
explicit MyDelegate(QObject *parent = 0);
// Create Editor when we construct MyDelegate
QWidget* createEditor(QWidget *parent, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const;
// Then, we set the Editor
void setEditorData(QWidget *editor, const QModelIndex
&index;) const;
// When we modify data, this model reflect the change
void setModelData(QWidget *editor, QAbstractItemModel *model,
const QModelIndex &index;) const;
// Give the SpinBox the info on size and location
void updateEditorGeometry(QWidget *editor, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const;
signals:
public slots:
};
#endif // MYDELEGATE_H
mydelegate.cpp:
#include "mydelegate.h"
MyDelegate::MyDelegate(QObject *parent) :
QItemDelegate(parent)
{
}
// TableView need to create an Editor
// Create Editor when we construct MyDelegate
// and return the Editor
QWidget* MyDelegate::createEditor(QWidget *parent, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const
{
QSpinBox *editor = new QSpinBox(parent);
editor->setMinimum(0);
editor->setMaximum(100);
return editor;
}
// Then, we set the Editor
// Gets the data from Model and feeds the data to delegate Editor
void MyDelegate::setEditorData(QWidget *editor, const QModelIndex
&index;) const
{
// Get the value via index of the Model
int value = index.model()->data(index, Qt::EditRole).toInt();
// Put the value into the SpinBox
QSpinBox *spinbox = static_cast<QSpinBox*>(editor);
spinbox->setValue(value);
}
// When we modify data, this model reflect the change
// Data from the delegate to the model
void MyDelegate::setModelData(QWidget *editor, QAbstractItemModel
*model, const QModelIndex &index;) const
{
QSpinBox *spinbox = static_cast<QSpinBox*>(editor);
spinbox->interpretText();
int value = spinbox->value();
model->setData(index, value, Qt::EditRole);
}
// Give the SpinBox the info on size and location
void MyDelegate::updateEditorGeometry(QWidget *editor, const
QStyleOptionViewItem &option;, const QModelIndex &index;) const
{
editor->setGeometry(option.rect);
}
