Question

我正在利用Axum和Sqlx建造一个皮端点,该点采用表格名称,并以json的形式将表中的所有数据退回。栏目及其类型没有事先知道。实现这一目标的最佳途径是什么。

我写了以下法典:

 let query = format!("SELECT * FROM {}", table);
 let result = sqlx::query(&query).fetch_all(&db).await.unwrap();
 for row in result.iter() {
   for col in row.columns() {
       let value = row.try_get(col.name()).unwrap();
    }
 }

当然,价值需要一种类型,我尝试利用这块宝石的 en子和ctor子储存这些价值,但可能这样做。任何朝着正确方向发展的趋势都会受到赞赏。

Answer 1

在过去几天里,我一直在努力解决这一问题。我是Rust的新手。

I don t have a solution yet, but I have made tiny "progress" toward the solution -- I believe.

请允许我介绍我迄今所做的工作。

所使用的数据库一是。 Blake Corporation MySQL测试数据。

我正在利用以下储存的程序调查这一问题:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `DemoStoredProc1`( pm_dept_no varchar(4) )
    READS SQL DATA
begin
  select * from departments where dept_no = pm_dept_no;
  select * from dept_manager where dept_no = pm_dept_no;
end$$
DELIMITER ;

www.un.org/Depts/DGACM/index_french.htm

CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The structure of the table dept_manager:

CREATE TABLE `dept_manager` (
  `emp_no` int NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这是我的检验方法:

async fn do_run_stored_proc(pool: &sqlx::Pool<MySql>, dept_no: &str) {
    let result = sqlx::query("call DemoStoredProc1(?)")
    .bind(dept_no)
    .fetch_all(pool).await;

    match result {
        Err(e) => {
            println!("Error select data for department number: {}", dept_no);
            println!("Error message: [{}].
", e.to_string());
        }

        Ok(query_result) => {
            for (rindex, row) in query_result.iter().enumerate() {
                println!("
* Row number: {}", rindex+1);

                println!("* Total columns: {}
", row.columns().len());

                for (cindex, col) in row.columns().iter().enumerate() {
                    println!(">> {}.", cindex+1);

                    println!(">> {:#?}", col.type_info());
                    println!(">> Name: {}. Type Name: {}.", col.name(), col.type_info().name());
                }
            }
        }
    }
}

它印刷了以下产出:


* Row number: 1
* Total columns: 2

>> 1.
>> MySqlTypeInfo {
    type: String,
    flags: ColumnFlags(
        NOT_NULL | PRIMARY_KEY | NO_DEFAULT_VALUE,
    ),
    char_set: 224,
    max_size: Some(
        16,
    ),
}
>> Name: dept_no. Type Name: CHAR.
>> 2.
>> MySqlTypeInfo {
    type: VarString,
    flags: ColumnFlags(
        NOT_NULL | UNIQUE_KEY | NO_DEFAULT_VALUE,
    ),
    char_set: 224,
    max_size: Some(
        160,
    ),
}
>> Name: dept_name. Type Name: VARCHAR.

* Row number: 2
* Total columns: 4

>> 1.
>> MySqlTypeInfo {
    type: Long,
    flags: ColumnFlags(
        NOT_NULL | PRIMARY_KEY | NO_DEFAULT_VALUE,
    ),
    char_set: 63,
    max_size: Some(
        11,
    ),
}
>> Name: emp_no. Type Name: INT.
>> 2.
>> MySqlTypeInfo {
    type: String,
    flags: ColumnFlags(
        NOT_NULL | PRIMARY_KEY | MULTIPLE_KEY | NO_DEFAULT_VALUE,
    ),
    char_set: 224,
    max_size: Some(
        16,
    ),
}
>> Name: dept_no. Type Name: CHAR.
>> 3.
>> MySqlTypeInfo {
    type: Date,
    flags: ColumnFlags(
        NOT_NULL | BINARY | NO_DEFAULT_VALUE,
    ),
    char_set: 63,
    max_size: Some(
        10,
    ),
}
>> Name: from_date. Type Name: DATE.
>> 4.
>> MySqlTypeInfo {
    type: Date,
    flags: ColumnFlags(
        NOT_NULL | BINARY | NO_DEFAULT_VALUE,
    ),
    char_set: 63,
    max_size: Some(
        10,
    ),
}
>> Name: to_date. Type Name: DATE.

* Row number: 3
* Total columns: 4
...

我可以提取一栏的类型名称。例如:

>> Name: dept_no. Type Name: CHAR.
...
>> Name: to_date. Type Name: DATE.

我确实理解,这不是你所期待的,我也不理解。

但是,对于我来说,这仍然是一项微小的进展,我认为这只是分享。

Answer 2

你们所希望的充满活力的景象确实需要一刀切,而且对于除装入json之外的任何东西都不会真正有用。我设法在 s子里得到 s的支持,为你们工作:

 use serde_json::json;
 use sqlx::{Column, Row, TypeInfo};
 use std::collections::HashMap;

 . . . .

 let query = format!("SELECT * FROM {}", table);
 let result: Vec<_> = sqlx::query(&query).fetch_all(&db).await.unwrap()
   .into_iter()
   .map(|row| {
     json!(row
       .columns()
       .into_iter()
       .map(|column| {
         let index = column.ordinal();
         (
           column.name().to_string(),
           match column.type_info().name() {
             "TEXT"    => json!(row.get::<String, _>(index)),
             "INTEGER" => json!(row.get::<i64, _>(index)),
             "BOOLEAN" => json!(row.get::<bool, _>(index)),
             "REAL"    => json!(row.get::<f64, _>(index)),
             // probably missed a few other types?
             _         => json!(format!("UNPROCESSED TYPE {}", column.type_info().name())),
           },
         )
       })
       .collect::<HashMap<_, _>>()
     )
   })
   .collect();

This should get you an in memory vector of your db recordset that is json-ish.

友情链接