sqlite - 如何使Rust sqlx sqlite查询工作？

Question

main.rs:

#[async_std::main]
async fn main() -> Result<(),sqlx::Error> {
    use sqlx::Connect;
    let mut conn = sqlx::SqliteConnection::connect("sqlite:///home/ace/hello_world/test.db").await?;
    let row = sqlx::query!("SELECT * FROM tbl").fetch_all(&conn).await?;
    println!("{}{}",row.0,row.1);
    Ok(())
}

Cargo.toml:

[package]
name = "hello_world"
version = "0.1.0"
authors = ["ace"]
edition = "2018"

[dependencies]
async-std = {version = "1", features = ["attributes"]}
sqlx = { version="0.3.5", default-features=false, features=["runtime-async-std","macros","sqlite"] }

bash session :

ace@SLAB:~/hello_world$ sqlite test.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table tbl ( num integer, chr varchar );
sqlite> insert into tbl values (1,'ok');
sqlite> .quit
ace@SLAB:~/hello_world$ pwd
/home/ace/hello_world
ace@SLAB:~/hello_world$ export DATABASE_URL=sqlite:///home/ace/hello_world/test.db
ace@SLAB:~/hello_world$ cargo run
   Compiling hello_world v0.1.0 (/home/ace/hello_world)
error: failed to connect to database: file is not a database
 --> src/main.rs:8:12
  |
8 |     let row = sqlx::query!("SELECT * FROM tbl").fetch_all(&conn).await?;
  |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  |
  = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: aborting due to previous error

error: could not compile `hello_world`.

To learn more, run the command again with --verbose.
ace@SLAB:~/hello_world$ rustc --version
rustc 1.44.0 (49cae5576 2020-06-01)
ace@SLAB:~/hello_world$ uname -r
5.4.0-33-generic
ace@SLAB:~/hello_world$ cat /etc/os-release | head -2
NAME="Ubuntu"
VERSION="20.04 LTS (Focal Fossa)"
ace@SLAB:~/hello_world$ 

还尝试将DATABASE_URL“sqlite::memory:”(在环境变量和main.rs中)与系统表“sqlite_master”一起使用。得到了不同的错误:

error[E0277]: the trait bound `&sqlx_core::sqlite::connection::SqliteConnection: sqlx_core::executor::RefExecutor<'_>` is not satisfied

...但是它一定已经成功了，因为当我将表名称“Xsqlite_master”用于内存db时，它提示说没有这样的表。

尝试过“sqlite://home”(等)以及每隔零个斜杠(从零到4)。尝试了几百种其他方法。 :(

谢谢!

Answer 1

可能还有更多尝试的方法:

尝试sqlite3 /home/ace/hello_world/test.db再次验证数据库是否确实存在。确保在其中定义了tbl表.schema tbl

尝试使用单斜杠的数据库路径，即sqlite:/home/ace/hello_world/test.db

最后尝试使用query函数而不是macro https://docs.rs/sqlx/0.3.5/sqlx/fn.query.html来查看它是否有效。