日常工作中，有时候需要对数据进行分析。如果能把待分析的数据导入sqllite数据库，是种不错的分析手段。或者需要导入其他数据库中的数据，做数据迁移用，比如可以把其他数据库中的表数据导出为csv文件，再把csv文件导入到sqllite。
用Qt来操作sqlite，可以做些带界面和图表的分析，使用很方便。
这里记录下导入csv文件到sqlite数据库的操作方法及sqlite的封装，留作备忘。
浏览sqllite数据库的客户端工具，我常用的是SQLiteSpy，仅4M大小，小巧简单。
导入csv到sqlite
使用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操作sqlite封装
Qt中操作数据库，.pro文件中，需要添加上：
QT += sql
#ifndef SQLITEDB_H #define SQLITEDB_H #include <QDir> #include <QDate> #include <QDateTime> #include <QFileInfo> #include <QString> #include <QTime> #include <QSqlDatabase> #include <QSqlRecord> #include <QSqlQuery> #include <QVariant> 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<QHash<QString,QString>> &data);//执行sql语句，并获取结果 bool getData(QString dbName,QString tableName,QHash<QString,QString> &data,QString sqlWhere=""); //获取数据 bool getData(QString dbName,QString table,QList<QHash<QString,QString>> &data,QString sqlWhere=""); //获取数据 bool getData(QString dbName,QString tableName,QHash<QString,QString> columndata,QList<QHash<QString,QString>> &data,QString sqlWhere=""); //获取数据 bool addData(QString dbName,QString tableName,QHash<QString,QString> data);//增加 bool delData(QString dbName,QString tableName,QString sqlWhere);//删除 bool updateData(QString dbName,QString tableName,QHash<QString,QString> data,QString sqlWhere="");//修改 bool queryExec(QString sqlStr);//执行sql语句，不获取结果 bool queryExec(QString sqlStr,QList<QHash<QString,QString>> &data);//执行sql语句，并获取结果 bool getData(QString tableName,QHash<QString,QString> &data,QString sqlWhere=""); //获取数据 bool getData(QString table,QList<QHash<QString,QString>> &data,QString sqlWhere=""); //获取数据 bool getData(QString tableName,QHash<QString,QString> columndata,QList<QHash<QString,QString>> &data,QString sqlWhere=""); //获取数据 bool addData(QString tableName,QHash<QString,QString> data);//增加 bool delData(QString tableName,QString sqlWhere);//删除 bool updateData(QString tableName,QHash<QString,QString> 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<QHash<QString,QString>> &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<QString,QString> rowData; for(int i =0;i<rec.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<QString,QString> &data,QString sqlWhere) { data.clear(); QList<QHash<QString,QString>> 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<QHash<QString,QString>> &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<QString,QString> columndata,QList<QHash<QString,QString>> &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<QString,QString> data) { if(data.isEmpty()) return false; QString queryStr="insert into "+tableName+" "; QString fieldStr="(",valueStr="values("; QHash<QString,QString>::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<QString,QString> data,QString sqlWhere) { QString queryStr="update "+tableName+" "; QHash<QString,QString>::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<QHash<QString,QString>> &data) { if(databaseName.isEmpty()) return false; return queryExec(databaseName,sqlStr,data); } //获取数据 bool sqliteDb::getData(QString tableName,QHash<QString,QString> &data,QString sqlWhere) { if(databaseName.isEmpty()) return false; return getData(databaseName,tableName,data,sqlWhere); } //获取数据 bool sqliteDb::getData(QString table,QList<QHash<QString,QString>> &data,QString sqlWhere) { if(databaseName.isEmpty()) return false; return getData(databaseName,table,data,sqlWhere); } //获取数据 bool sqliteDb::getData(QString tableName,QHash<QString,QString> columndata,QList<QHash<QString,QString>> &data,QString sqlWhere) { if(databaseName.isEmpty()) return false; return getData(databaseName,tableName,columndata,data,sqlWhere); } //增加 bool sqliteDb::addData(QString tableName,QHash<QString,QString> 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<QString,QString> 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<QString,QString> data; data["name"] = "yang"; data["age"] = "10"; mydb.db->addData("tb_user",data); //......
注意事项
由于QSqlDatabase机制问题，不能跨线程访问，上述封装的连接和使用只能在同一个线程中使用。若需要放在多线程中使用，则需要改下query的实现方法，如：
//执行sql语句，并获取结果 bool sqliteDb::queryExec(QString dbName,QString queryStr,QList<QHash<QString,QString>> &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<QString,QString> rowData; for(int i =0;i<rec.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是文件级别的锁。多个线程可以同时读；但是多个线程同时读／写时：如读先发生，写会被阻塞直至读完毕，写才会继续执行；如写先发生，读会被阻塞直至写完毕，读才会继续执行。
Qt字节转换
#include "stringutils.h" #include <QDebug> #include <QTextCodec> //解决QBytearray中文乱码问题 #include <QDataStream> //将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<len; ) { 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
Qt使用sqlite数据库，查询数据卡顿导致其他数据偶尔插入失败_ouyang1988503的博客-CSDN博客
