在R中使用DBI与RSQLite执行多语句SQL时的执行异常及解决方案咨询

Great question! The warning you're seeing is an intentional safety measure in DBI and RSQLite—by default, multi-statement queries are blocked to reduce SQL injection risks. But there are two straightforward solutions for your use case:

Solution 1: Use RSQLite's multi.statement parameter #

RSQLite's implementation of dbExecute() includes a dedicated multi.statement argument that lets you run multiple semicolon-separated statements in one call. You just need to set it to TRUE:

library(DBI)
library(RSQLite)

conn <- DBI::dbConnect(RSQLite::SQLite(), "test.sqlite")

# Your multi-statement SQL (or read from file as you originally planned)
sql <- "CREATE TABLE `bemovi_mag_25__mean_density_per_ml` ( `timestamp` NUMERIC, `date` NUMERIC, `species` NUMERIC, `composition_id` NUMERIC, `bottle` NUMERIC, `temperature_treatment` NUMERIC, `magnification` NUMERIC, `sample` NUMERIC, `density` NUMERIC ); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle);"

# Execute all statements at once
DBI::dbExecute(conn, sql, multi.statement = TRUE)

DBI::dbDisconnect(conn)

This will run every statement without warnings, and return a vector showing the number of rows affected by each command (0 for CREATE TABLE/INDEX since they don't modify existing rows).

Solution 2: Split and execute statements manually (cross-database compatible) #

If you need code that works across different database backends (not just SQLite), splitting the statements is a more portable approach. Your initial code is on the right track, but you should handle empty strings that often result from strsplit() (e.g., if your SQL ends with a semicolon):

library(DBI)
library(RSQLite)

conn <- DBI::dbConnect(RSQLite::SQLite(), "test.sqlite")

sql <- "CREATE TABLE `bemovi_mag_25__mean_density_per_ml` ( `timestamp` NUMERIC, `date` NUMERIC, `species` NUMERIC, `composition_id` NUMERIC, `bottle` NUMERIC, `temperature_treatment` NUMERIC, `magnification` NUMERIC, `sample` NUMERIC, `density` NUMERIC ); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timetamp on bemovi_mag_25__mean_density_per_ml(timestamp); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_bottle on bemovi_mag_25__mean_density_per_ml(bottle); CREATE INDEX idx_bemovi_mag_25__mean_density_per_ml_timestamp_bottle on bemovi_mag_25__mean_density_per_ml(timestamp, bottle);"

# Split statements, clean up whitespace, and remove empty entries
sql_statements <- strsplit(sql, ";")[[1]]
sql_statements <- trimws(sql_statements) # Trim leading/trailing whitespace
sql_statements <- sql_statements[sql_statements != ""] # Remove empty strings

# Run each statement individually
lapply(sql_statements, function(s) DBI::dbExecute(conn, s))

DBI::dbDisconnect(conn)

Bonus: Clean up SQL from files #

When reading SQL from a file, you'll want to remove comments and empty lines to avoid errors:

sql_lines <- readLines("test.sql")
# Filter out comment lines (starting with -- or #) and empty lines
sql_lines <- sql_lines[!grepl("^\\s*(--|#|$)", sql_lines)]
# Combine into a single string
sql <- paste0(sql_lines, collapse = " ")

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