温馨提示:本文翻译自stackoverflow.com,查看原文请点击:Swift/iOS - How to create a dictionary from data extracted from a sqlite database

Swift/iOS - 如何使用从sqlite数据库提取的数据创建字典

发布于 2020-03-28 23:30:39

我调用一个API,该API返回包含数据库的sqlite文件。连接正常,我可以读取数据表中的数据。

该数据库中的表都是相似的:

TableName = "id": 265946161, json: {key: value, key: value, key: value...}

我想提取字典中的所有表格,格式如下:

[String: [Int, JSON]] = [TableName: [id: json]]

这是我现在的代码:

   //Take all the table names
   var tablesName: [String] = []

   var queryStatement: OpaquePointer?
   var testStatement: OpaquePointer?
   let queryStatementString = "SELECT * FROM sqlite_master where type='table';"

   if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {

   if sqlite3_step(queryStatement) == SQLITE_ROW {
       while (sqlite3_step(queryStatement) == SQLITE_ROW) {

         let id = sqlite3_column_int(queryStatement, 0)
         guard let queryResultCol1 = sqlite3_column_text(queryStatement, 1) else {
           print("Query result is nil.")
           return
         }
         let tableName = String(cString: queryResultCol1)
         print("Query Result:")
         print("(tableName)")
         self.tablesName.append(tableName)
       } 
   } else {
       print("\nQuery returned no results.")
   }

   //Test query for one table
   for name in tableNames {
     let queryTest = "SELECT * FROM \(name);"

     if sqlite3_prepare_v2(db, queryTest, -1, &testStatement, nil) == SQLITE_OK {
        while (sqlite3_step(testStatement) == SQLITE_ROW) {
            let id = sqlite3_column_int(testStatement, 0)
            guard let queryResultCol1 = sqlite3_column_text(testStatement, 1) else {
              print("Query result is nil.")
              return
            }
            let json = String(cString: queryResultCol1)
            print("Query Result on \(name):")
            print("\(id) | \(json)")
            //create dictionary or maybe something else???
        }          
      }
   }
} else {
  let errorMessage = String(cString: sqlite3_errmsg(db))
  print("\nQuery is not prepared \(errorMessage)")
}
sqlite3_finalize(queryStatement)
      sqlite3_finalize(testStatement)

我想使用字典收集这样的数据:dict[tableName][id][json][key][value]在我的vc上,以避免每次都调用数据库。

如果您能在这条道路上或在实现此目标的更好方法上为我提供帮助,我就会陷入困境。

提前致谢

查看更多

查看更多

提问者
factragnar
被浏览
22
Joakim Danielson 2020-01-31 18:32

这是一种方法,首先创建主字典,然后在循环内部使用JSONSerialization为每个表创建一个新字典,以创建json数据的字典。

var tableData: [String: [Int: [String: Any]]]

for name in tableNames {
    let queryTest = "SELECT * FROM \(name);"

    if sqlite3_prepare_v2(db, queryTest, -1, &testStatement, nil) == SQLITE_OK {
        while (sqlite3_step(testStatement) == SQLITE_ROW) {
            var dict = [Int: [String: Any]]
            let id = sqlite3_column_int(testStatement, 0)
            guard let queryResultCol1 = sqlite3_column_text(testStatement, 1) else {
                print("Query result is nil.")
                return
            }
            let json = String(cString: queryResultCol1)
            let data = Data(json.utf8)
            do {
                 if let jsonDict = try JSONSerialization.jsonObject(with: data, options: []) as? [String: Any] {
                    dict[id] = jsonDict
                 }
             } catch let error as NSError {
                 print(error)            
             }
          }
          tableData[name] = dict
      }
 }

请注意,我无法编译或运行此文件,因此它可能包含一些问题,但是建议的解决方案应该很明确。

发布
问题

分享
好友

手机
浏览

扫码手机浏览