日常工作中,有时候需要对数据进行分析。如果能把待分析的数据导入sqllite数据库,是种不错的分析手段。或者需要导入其他数据库中的数据,做数据迁移用,比如可以把其他数据库中的表数据导出为csv文件,再把csv文件导入到sqllite。
用Qt来操作sqlite,可以做些带界面和图表的分析,使用很方便。
这里记录下导入csv文件到sqlite数据库的操作方法及sqlite的封装,留作备忘。
浏览sqllite数据库的客户端工具,我常用的是SQLiteSpy,仅4M大小,小巧简单。

使用sqlite3.exe命令行工具。这个sqllite数据库带的有这个工具,可以直接下载使用。
操作的步骤:
1.打开数据库文件(前提存在db文件,且里面有相应的表结构)
.\sqlite3.exe .\test.db
2.命令行操作
- //查看数据库
- .databases
- // 查看数据
- select * from tb_user;
- 1|yang|10
- //查看表
- .tables
3.导入csv文件到sqlite
这其中有个特别需要注意的问题是:csv文件的编码问题,必须为utf-8格式,utf-8-bom不行。
- // 必须修改为,分隔符
- .separator ","
-
- // 查看表结构
- .schema tb_data
-
- // 导入csv数据到表中 (前提 结构必须一致)
- .import dbo_data.csv tb_data
以上操作即完成了csv文件数据入库的操作。
Qt中操作数据库,.pro文件中,需要添加上:
QT += sql
- #ifndef SQLITEDB_H
- #define SQLITEDB_H
-
- #include
- #include
- #include
- #include
- #include
- #include
- #include
- #include
- #include
- #include
-
- class sqliteDb
- {
- public:
- sqliteDb();
- ~sqliteDb();
- public:
- bool setDbDir(QString dirPath);//设置数据库存放路径
- bool creatDbFile(QString dbName);//生成一个db文件
- bool reOpenSql(QString dbName);//打开连接
- bool closeSql();//关闭连接
- bool queryExec(QString dbName,QString sqlStr);//执行sql语句,不获取结果
- bool queryExec(QString dbName,QString sqlStr,QList
> &data) ;//执行sql语句,并获取结果 - bool getData(QString dbName,QString tableName,QHash
&data,QString sqlWhere="") ; //获取数据 - bool getData(QString dbName,QString table,QList
> &data,QString sqlWhere="") ; //获取数据 - bool getData(QString dbName,QString tableName,QHash
columndata,QList> &data,QString sqlWhere="") ; //获取数据 - bool addData(QString dbName,QString tableName,QHash
data) ;//增加 - bool delData(QString dbName,QString tableName,QString sqlWhere);//删除
- bool updateData(QString dbName,QString tableName,QHash
data,QString sqlWhere="") ;//修改 -
- bool queryExec(QString sqlStr);//执行sql语句,不获取结果
- bool queryExec(QString sqlStr,QList
> &data) ;//执行sql语句,并获取结果 - bool getData(QString tableName,QHash
&data,QString sqlWhere="") ; //获取数据 - bool getData(QString table,QList
> &data,QString sqlWhere="") ; //获取数据 - bool getData(QString tableName,QHash
columndata,QList> &data,QString sqlWhere="") ; //获取数据 - bool addData(QString tableName,QHash
data) ;//增加 - bool delData(QString tableName,QString sqlWhere);//删除
- bool updateData(QString tableName,QHash
data,QString sqlWhere="") ;//修改 - bool transaction();
- bool commit();
- QString getErrorSql();//获取错误的数据库语句
- private:
- QSqlDatabase db;
- QString dbFilePath;//数据库路径
- QString dbDir; //数据库文件夹
- QString databaseName;//数据库名字
- QString errorSqlText;//错误语句
- private:
- void errorSql(QString sql);//错误打印
- };
-
- #endif // SQLITEDB_H
-
- #include "sqlitedb.h"
-
- sqliteDb::sqliteDb()
- {
- dbDir = QDir::currentPath() ;
- }
- sqliteDb::~sqliteDb()
- {
-
- }
- //设置数据库存放路径
- bool sqliteDb::setDbDir(QString dirPath)
- {
- QDir dir(dirPath);
- if(dir.exists())
- {
- dbDir = dirPath;
- return true;
- }
- else
- {
- return false;
- }
- }
- //打开连接
- bool sqliteDb::reOpenSql(QString dbName)
- {
- QString fileName = (dbDir + "/"+dbName + ".db");
- if(!QFile::exists(fileName))
- {
- qWarning("error,db not exist");
- return false;//数据库不存在
- }
- QFileInfo file(fileName);
- if(file.suffix() != "db")
- return false;
- db = QSqlDatabase::database(dbName);
- if(!db.isValid())
- {
- db = QSqlDatabase::addDatabase("QSQLITE",dbName);
- db.setDatabaseName(fileName);
- if (!db.open())
- {
- return false;//打开失败
- }
- }
- dbFilePath = fileName;
- databaseName = dbName;//数据库名字
- return true;
- }
- //关闭连接
- bool sqliteDb::closeSql()
- {
- if(databaseName.isEmpty())
- return true;
- if(!QFile::exists(dbFilePath))
- {
- return false;//数据库不存在
- }
- db = QSqlDatabase::database(databaseName);
- if(!db.isValid())
- {
- return true;
- }
- db.close();
- db = QSqlDatabase::database();
- QSqlDatabase::removeDatabase(databaseName);
- databaseName = "";
- dbFilePath = "";
- return true;
- }
- //生成一个db文件
- bool sqliteDb::creatDbFile(QString dbName)
- {
- QString fileName = (dbDir + "/"+dbName + ".db");
- QFileInfo fileInfo(fileName);
- if(fileInfo.isFile()){
- qWarning("db已存在,创建失败");
- return false;
- }
- QFile file(fileName);
- if(file.open(QIODevice::WriteOnly))
- {
- file.close();
- }
- else
- {
- return false;
- }
- return true;
- }
- //错误打印
- void sqliteDb::errorSql(QString sql)
- {
- errorSqlText = sql;
- qCritical("%s",qPrintable(errorSqlText));
- //QString("数据库执行错误:%1 ")+sql.toUtf8().constData();
- }
- //获取错误的数据库语句
- QString sqliteDb::getErrorSql()
- {
- if(databaseName.isEmpty())
- {
- return "db not setting";//数据库未设置
- }
- return errorSqlText;
- }
- //执行sql语句,不获取结果
- bool sqliteDb::queryExec(QString dbName,QString queryStr)
- {
- if(databaseName.isEmpty())
- {
- if(!reOpenSql(dbName))
- {
- return false;
- }
- }
- QSqlQuery query(QSqlDatabase::database(dbName, true));
- if(!query.exec(queryStr))
- {
- errorSql(queryStr);
- return false;
- }
- return true;
- }
- //执行sql语句,并获取结果
- bool sqliteDb::queryExec(QString dbName,QString queryStr,QList
> &data) - {
- data.clear();
- if(databaseName.isEmpty())
- {
- if(!reOpenSql(dbName))
- {
- return false;
- }
- }
- QSqlQuery query(QSqlDatabase::database(dbName, true));
- if(!query.exec(queryStr))
- {
- errorSql(queryStr);
- return false;
- }
- QSqlRecord rec = query.record();
- while(query.next())
- {
- QHash
rowData; - for(int i =0;i
count();i++) - {
- QVariant::Type ty = query.value(i).type();
- if( QVariant::Type::Date == ty)
- {
- QDate temp = query.value(i).toDate();
- rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd");
- }
- else if( QVariant::Type::Time == ty)
- {
- QTime temp = query.value(i).toTime();
- rowData[rec.fieldName(i)]=temp.toString("hh:mm:ss");
- }
- else if( QVariant::Type::DateTime == ty)
- {
- QDateTime temp = query.value(i).toDateTime();
- rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd hh:mm:ss");
- }
- else
- rowData[rec.fieldName(i)]=query.value(i).toString();
- }
- data.append(rowData);
- }
- return true;
- }
- //获取数据
- bool sqliteDb::getData(QString dbName,QString tableName,QHash
&data,QString sqlWhere) - {
- data.clear();
- QList
> dataList; - if(!getData(dbName,tableName,dataList,sqlWhere))
- {
- return false;
- }
- if(dataList.count() > 0)
- {
- data = dataList[0];
- }
- return true;
- }
- //获取数据
- bool sqliteDb::getData(QString dbName,QString tableName,QList
> &data,QString sqlWhere) - {
- QString queryStr="select * from "+tableName;
- if(!sqlWhere.isEmpty())
- queryStr+=" "+sqlWhere;
- if(!queryExec(dbName,queryStr,data))
- {
- return false;
- }
- return true;
- }
- //获取数据
- bool sqliteDb::getData(QString dbName,QString tableName,QHash
columndata,QList> &data,QString sqlWhere) - {
- QString colunmStr;
- if(columndata.count() == 0)
- colunmStr = "*";
- else
- {
- QStringList keys = columndata.keys();
- for(auto key : keys)
- {
- QString column = QString("%1 AS `%2`").arg(key).arg(columndata[key]);
- if(!colunmStr.isEmpty())
- colunmStr += ",";
- colunmStr += column;
- }
- }
- QString queryStr = QString("SELECT %1 FROM %2 %3").arg(colunmStr).arg(tableName).arg( sqlWhere);
- if(!queryExec(dbName,queryStr,data))
- {
- return false;
- }
- return true;
- }
- //增加
- bool sqliteDb::addData(QString dbName,QString tableName,QHash
data) - {
- if(data.isEmpty())
- return false;
- QString queryStr="insert into "+tableName+" ";
- QString fieldStr="(",valueStr="values(";
- QHash
::iterator it; - for ( it = data.begin(); it != data.end(); ++it )
- {
- fieldStr+=it.key()+",";
- valueStr+="'"+it.value()+"',";
- }
- fieldStr=fieldStr.left(fieldStr.length()-1);
- valueStr=valueStr.left(valueStr.length()-1);
- fieldStr+=")";
- valueStr+=")";
- queryStr+=fieldStr+" "+valueStr;
- if(!queryExec(dbName,queryStr))
- {
- return false;
- }
- return true;
- }
- //删除
- bool sqliteDb::delData(QString dbName, QString tableName, QString sqlWhere)
- {
- QString queryStr="delete from "+tableName;
- if(!sqlWhere.isEmpty())
- queryStr+=" "+sqlWhere;
- if(!queryExec(dbName,queryStr))
- {
- return false;
- }
- return true;
- }
- //修改
- bool sqliteDb::updateData(QString dbName,QString tableName,QHash
data,QString sqlWhere) - {
- QString queryStr="update "+tableName+" ";
- QHash
::iterator it; - QString setStr="set ";
- for ( it = data.begin(); it != data.end(); ++it )
- {
- setStr+=it.key()+"='"+it.value()+"'";
- setStr+=",";
- }
- setStr=setStr.left(setStr.length()-1);
- queryStr+=setStr;
- if(!sqlWhere.isEmpty())
- queryStr+=" "+sqlWhere;
- if(!queryExec(dbName,queryStr))
- {
- return false;
- }
- return true;
- }
- bool sqliteDb::transaction()
- {
- if(databaseName.isEmpty())
- return false;
- return db.transaction();
- }
- bool sqliteDb::commit()
- {
- if(databaseName.isEmpty())
- return false;
- return db.commit();
- }
- //执行sql语句,不获取结果
- bool sqliteDb::queryExec(QString sqlStr)
- {
- if(databaseName.isEmpty())
- return false;
- return queryExec(databaseName,sqlStr);
- }
- //执行sql语句,并获取结果
- bool sqliteDb::queryExec(QString sqlStr,QList
> &data) - {
- if(databaseName.isEmpty())
- return false;
- return queryExec(databaseName,sqlStr,data);
- }
- //获取数据
- bool sqliteDb::getData(QString tableName,QHash
&data,QString sqlWhere) - {
- if(databaseName.isEmpty())
- return false;
- return getData(databaseName,tableName,data,sqlWhere);
- }
- //获取数据
- bool sqliteDb::getData(QString table,QList
> &data,QString sqlWhere) - {
- if(databaseName.isEmpty())
- return false;
- return getData(databaseName,table,data,sqlWhere);
- }
- //获取数据
- bool sqliteDb::getData(QString tableName,QHash
columndata,QList> &data,QString sqlWhere) - {
- if(databaseName.isEmpty())
- return false;
- return getData(databaseName,tableName,columndata,data,sqlWhere);
- }
- //增加
- bool sqliteDb::addData(QString tableName,QHash
data) - {
- if(databaseName.isEmpty())
- return false;
- return addData(databaseName,tableName,data);
- }
- //删除
- bool sqliteDb::delData(QString tableName,QString sqlWhere)
- {
- if(databaseName.isEmpty())
- return false;
- return delData(databaseName,tableName,sqlWhere);
- }
- //修改
- bool sqliteDb::updateData(QString tableName,QHash
data,QString sqlWhere) - {
- if(databaseName.isEmpty())
- return false;
- return updateData(databaseName,tableName,data,sqlWhere);
- }
- //
新建一个dbhelper类,作为具体的操作使用。
- #include "dbhelper.h"
- #include "cglobal.h"
- Dbhelper::Dbhelper()
- {
- db = new sqliteDb();
- init_table_0 = R"(
- CREATE TABLE IF NOT EXISTS tb_user (
- id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- name CHAR (50) NOT NULL,
- age INTEGER
- );)";
- }
-
- Dbhelper::~Dbhelper()
- {
- if(db!= nullptr){
- db->closeSql();
- delete db;
- }
- }
-
- int Dbhelper::createTable()
- {
- db->setDbDir(DATA_PATH);
- bool ret = db->creatDbFile(DB_NAME);
- if(!ret){
- return -1;
- }
- ret = db->reOpenSql(DB_NAME);
- if(ret){
- db->queryExec(QString::fromStdString(init_table_0));
- db->queryExec(QString::fromStdString(init_table_1));
- db->queryExec(QString::fromStdString(init_table_2));
- }
-
- return 0;
- }
简单使用:
- Dbhelper mydb;
- //若库不存在则自动创建库和表
- mydb.createTable();
- //打开数据库
- auto ret = mydb.db->reOpenSql(DB_NAME);
- if(!ret){
- qCritical("打开数据库失败");
- }
- //插入一条测试数据
- QHash
data; - data["name"] = "yang";
- data["age"] = "10";
- mydb.db->addData("tb_user",data);
- //......
由于QSqlDatabase机制问题,不能跨线程访问,上述封装的连接和使用只能在同一个线程中使用。若需要放在多线程中使用,则需要改下query的实现方法,如:
- //执行sql语句,并获取结果
- bool sqliteDb::queryExec(QString dbName,QString queryStr,QList
> &data) - {
-
- if(QSqlDatabase::contains("default")){
- qDebug() << "query error,now in use";
- return false;
- }
-
- data.clear();
-
- {
- QSqlDatabase mdb;
- {
- mdb= QSqlDatabase::addDatabase("QSQLITE","default");
- mdb.setDatabaseName("./liuliang.db");
- auto ret = mdb.open();
- if(!ret){
- qCritical("error,open",qPrintable(errorSqlText));
- }
- }
-
- QSqlQuery query(mdb);
- if(!query.exec(queryStr))
- {
- qDebug() << mdb.lastError();
- errorSql(queryStr);
- return false;
- }
- QSqlRecord rec = query.record();
- while(query.next())
- {
- QHash
rowData; - for(int i =0;i
count();i++) - {
- QVariant::Type ty = query.value(i).type();
- if( QVariant::Type::Date == ty)
- {
- QDate temp = query.value(i).toDate();
- rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd");
- }
- else if( QVariant::Type::Time == ty)
- {
- QTime temp = query.value(i).toTime();
- rowData[rec.fieldName(i)]=temp.toString("hh:mm:ss");
- }
- else if( QVariant::Type::DateTime == ty)
- {
- QDateTime temp = query.value(i).toDateTime();
- rowData[rec.fieldName(i)]=temp.toString("yyyy-MM-dd hh:mm:ss");
- }
- else
- rowData[rec.fieldName(i)]=query.value(i).toString();
- }
- data.append(rowData);
- }
- }
-
- qDebug() << "query ok";
- QSqlDatabase::removeDatabase("default");
- return true;
- }

SQLiteDatabse实质上是将数据写入一个文件,我们可以得知SQLiteDatabse是文件级别的锁。多个线程可以同时读;但是多个线程同时读/写时:如读先发生,写会被阻塞直至读完毕,写才会继续执行;如写先发生,读会被阻塞直至写完毕,读才会继续执行。
- #include "stringutils.h"
- #include
- #include
//解决QBytearray中文乱码问题 - #include
- //将1-9 a-f字符转化为对应的整数
- char ConvertHexChar(char ch)
- {
- if((ch >= '0') && (ch <= '9'))
- return ch-0x30;
- else if((ch >= 'A') && (ch <= 'F'))
- return ch-'A'+10;
- else if((ch >= 'a') && (ch <= 'f'))
- return ch-'a'+10;
- else return (-1);
- }
- //将字符型进制转化为16进制
- QByteArray QString2Hex(QString str)
- {
- QByteArray senddata;
- int hexdata,lowhexdata;
- int hexdatalen = 0;
- int len = str.length();
- senddata.resize(len/2);
- char lstr,hstr;
- for(int i=0; i
- {
- hstr=str[i].toLatin1(); //字符型
- if(hstr == ' ')
- {
- i++;
- continue;
- }
- i++;
- if(i >= len)
- break;
- lstr = str[i].toLatin1();
- hexdata = ConvertHexChar(hstr);
- lowhexdata = ConvertHexChar(lstr);
- if((hexdata == 16) || (lowhexdata == 16))
- break;
- else
- hexdata = hexdata*16+lowhexdata;
- i++;
- senddata[hexdatalen] = (char)hexdata;
- hexdatalen++;
- }
- senddata.resize(hexdatalen);
- return senddata;
- }
-
- //将接收的一串QByteArray类型的16进制,转化为对应的字符串16进制
- QString Hex2QString(QByteArray str)
- {
-
- QDataStream out(&str,QIODevice::ReadWrite); //将str的数据 读到out里面去
- QString buf;
- while(!out.atEnd())
- {
- qint8 outChar = 0;
- out >> outChar; //每次一个字节的填充到 outchar
- QString str = QString("%1").arg(outChar&0xFF,2,16,QLatin1Char('0')).toUpper() + QString(""); //2 字符宽度
-
- buf += str;
- }
- return buf;
- }
- QString ShowStr(QByteArray arr)
- {
-
- //QDataStream out(&str,QIODevice::ReadWrite); //将str的数据 读到out里面去
- //QString buf;
- //buf.prepend(str);
-
- // while(!out.atEnd())
- // {
- // qint8 outChar = 0;
- // out >> outChar; //每次一个字节的填充到 outchar
- // QString str = QString("%1").arg(outChar&0xFF,2,16,QLatin1Char('0')).toUpper() + QString(" "); //2 字符宽度
-
- // buf += str;
- // }
- QTextCodec *tc = QTextCodec::codecForName("GBK");
- QString tmpQStr = tc->toUnicode(arr);
- return tmpQStr;
- }
- //将接收的一串QByteArray类型的16进制,每2个16进制转化为1个字的16进制的字符串
- QString Convert4Hex(QByteArray str)
- {
-
- QDataStream out(&str,QIODevice::ReadWrite); //将str的数据 读到out里面去
- QString buf;
- while(!out.atEnd())
- {
- qint16 outChar = 0;
- out>>outChar; //每次一个字节的填充到 outchar
- QString str = QString("%1").arg(outChar&0xFFFF,4,16,QLatin1Char('0')).toUpper() + QString(" "); //2 字符宽度
-
- buf += str;
- }
- return buf;
- }
引用
Qt中操作SQLite数据库_龚建波的博客-CSDN博客_qt sqlite