树莓派OS中R连接SQLite数据库提示‘file is not a database’问题

问题概述 #

我正在开发一个网页抓取数据并自动生成报告的个人项目，计划通过cronjob在树莓派上部署自动化脚本，但遇到了Mac和Windows笔记本上未出现的异常：在R中读取基于文件的SQLite3数据库时，树莓派报错Error: file is not a database，相同代码在另外两个系统可正常运行。

问题详情 #

核心代码如下，在Mac和Windows运行正常，但树莓派（包括Raspi OS和Ubuntu 20.10）均执行失败：

library(DBI)
conn <- dbConnect(drv = RSQLite::SQLite(), dbname = "database")
rawdata <- dbReadTable(conn, "db")

复现步骤 #

1. 用到的SQLite文件数据库由Python脚本通过pandas的df.to_sql()方法写入生成
2. 数据清洗代码可参考项目内的data_cleaning.R第22-23行，但问题核心不在数据清洗环节，而是SQLite数据库的读取方式

已尝试的解决方法 #

• 通过Docker使用最新版R（4.2.2）验证是否为包版本问题，操作步骤如下：
  1. 克隆项目仓库
  2. 拉取r-base镜像并创建挂载项目目录的容器：

     docker pull r-base
     docker run -d -it --name renv -v /home/pi/repo/BerkeleyRSF_CrowdMeter:/home/repo r-base
     

  3. 进入容器shell：

     docker exec -it renv sh
     

  4. 运行R并测试读取SQLite3数据库，结果仍为Windows正常、树莓派报错
• 搜索Error: file is not a database相关问题，尝试过添加文件扩展名、排查SQLite版本差异等方法，均未解决问题

补充信息 #

Windows端会话信息 #

# From Windows Side
PS C:\Users\boyie> R.exe

R version 4.2.1 (2022-06-23 ucrt) -- "Funny-Looking Kid"
Copyright (C) 2022 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

[Previously saved workspace restored]

> library(DBI)
> library(RSQLite)
> sessionInfo()
R version 4.2.1 (2022-06-23 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.utf8
[2] LC_CTYPE=English_United States.utf8
[3] LC_MONETARY=English_United States.utf8
[4] LC_NUMERIC=C
[5] LC_TIME=English_United States.utf8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] RSQLite_2.2.15 DBI_1.1.3

loaded via a namespace (and not attached):
 [1] bit_4.0.4      compiler_4.2.1 fastmap_1.1.0  cli_3.4.1      memoise_2.0.1
 [6] Rcpp_1.0.8.3   bit64_4.0.5    vctrs_0.5.1    cachem_1.0.6   blob_1.2.3
[11] rlang_1.0.6

树莓派端会话信息 #

# Raspberry Pi Side
pi@raspberrypi:~ $ R

R version 4.0.3 (2020-10-10) -- "Bunny-Wunnies Freak Out"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: aarch64-unknown-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

[Previously saved workspace restored]

> library(DBI)
> library(RSQLite)
> sessionInfo()
R version 4.0.3 (2020-10-10)
Platform: aarch64-unknown-linux-gnu (64-bit)
Running under: Debian GNU/Linux 10 (buster)

Matrix products: default
BLAS:   /usr/local/lib/R/lib/libRblas.so
LAPACK: /usr/local/lib/R/lib/libRlapack.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C
 [9] LC_ADDRESS=C               LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] RSQLite_2.2.19 DBI_1.1.1

loaded via a namespace (and not attached):
 [1] bit_4.0.5      compiler_4.0.3 fastmap_1.1.0  cli_3.4.1      pbdZMQ_0.3-5
 [6] memoise_2.0.0  Rcpp_1.0.9     bit64_4.0.5    vctrs_0.5.1    cachem_1.0.4
[11] blob_1.2.1     rlang_1.0.6

内容的提问来源于stack exchange，提问作者BYC