use anyhow::Context;
use csv::Writer;
use rusqlite::{Connection, types::ValueRef};
use rust_xlsxwriter::Workbook;
fn value_to_string(v: Result<ValueRef, rusqlite::Error>) -> String {
match v {
Ok(ValueRef::Null) => String::new(),
Ok(ValueRef::Integer(i)) => i.to_string(),
Ok(ValueRef::Real(f)) => f.to_string(),
Ok(ValueRef::Text(s)) => String::from_utf8_lossy(s).to_string(),
Ok(ValueRef::Blob(_)) => String::new(),
Err(_) => String::new(),
}
}
fn write_csv_batch<W: std::io::Write>(
wtr: &mut Writer<W>,
batch: &[Vec<String>],
) -> anyhow::Result<()> {
for row in batch {
wtr.write_record(row)?;
}
Ok(())
}
pub fn export_sqlite_to_csv(
path: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
batch_size: usize,
) -> anyhow::Result<usize> {
let conn = Connection::open(path).with_context(|| format!("无法打开 SQLite 数据库: {path}"))?;
let mut stmt = conn.prepare(query)?;
let db_cols: Vec<String> = stmt.column_names().iter().map(|c| c.to_string()).collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let file = std::fs::File::create(output)?;
let mut wtr = csv::WriterBuilder::new().from_writer(file);
wtr.write_record(&headers)?;
let rows = stmt.query_map([], move |row| {
Ok((0..col_count)
.map(|i| value_to_string(row.get_ref(i)))
.collect::<Vec<_>>())
})?;
let mut batch = Vec::with_capacity(batch_size);
let mut total = 0usize;
for row in rows {
batch.push(row?);
if batch.len() >= batch_size {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
batch.clear();
}
}
if !batch.is_empty() {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
}
wtr.flush()?;
Ok(total)
}
pub fn export_sqlite_to_xlsx(
path: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
_batch_size: usize,
) -> anyhow::Result<usize> {
let conn = Connection::open(path).with_context(|| format!("无法打开 SQLite 数据库: {path}"))?;
let mut stmt = conn.prepare(query)?;
let db_cols: Vec<String> = stmt.column_names().iter().map(|c| c.to_string()).collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let mut workbook = Workbook::new();
let worksheet = workbook.add_worksheet();
for (col, header) in headers.iter().enumerate() {
worksheet.write_string(0, col as u16, header)?;
}
let rows = stmt.query_map([], move |row| {
Ok((0..col_count)
.map(|i| value_to_string(row.get_ref(i)))
.collect::<Vec<_>>())
})?;
let mut row_idx = 1u32;
for row in rows {
let r = row?;
for (col, cell) in r.iter().enumerate() {
worksheet.write_string(row_idx, col as u16, cell)?;
}
row_idx += 1;
}
let total = (row_idx - 1) as usize;
workbook
.save(output)
.with_context(|| format!("无法保存 XLSX 文件: {output}"))?;
Ok(total)
}
pub async fn export_mysql_to_csv(
url: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
batch_size: usize,
) -> anyhow::Result<usize> {
use mysql_async::prelude::*;
let opts = mysql_async::Opts::from_url(url)?;
let mut conn = mysql_async::Conn::new(opts).await?;
let mut result = conn.query_iter(query).await?;
let mut first = match result.next().await {
Ok(Some(row)) => row,
Ok(None) => {
let mut wtr = csv::Writer::from_path(output)?;
let h = custom_headers.unwrap_or_default();
wtr.write_record(&h)?;
wtr.flush()?;
return Ok(0);
}
Err(e) => return Err(e.into()),
};
let db_cols: Vec<String> = first
.columns()
.iter()
.map(|c| c.name_str().to_string())
.collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let mut wtr = csv::Writer::from_path(output)?;
wtr.write_record(&headers)?;
let mut batch: Vec<Vec<String>> = Vec::with_capacity(batch_size);
let mut total = 0usize;
batch.push(
(0..col_count)
.map(|i| {
let val: Option<String> = first.take(i);
val.unwrap_or_default()
})
.collect(),
);
loop {
match result.next().await {
Ok(Some(row)) => {
batch.push(
(0..col_count)
.map(|i| {
let val: Option<String> = row.get(i);
val.unwrap_or_default()
})
.collect(),
);
if batch.len() >= batch_size {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
batch.clear();
}
}
Ok(None) => break,
Err(e) => return Err(e.into()),
}
}
if !batch.is_empty() {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
}
wtr.flush()?;
Ok(total)
}
pub async fn export_mysql_to_xlsx(
url: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
_batch_size: usize,
) -> anyhow::Result<usize> {
use mysql_async::prelude::*;
let opts = mysql_async::Opts::from_url(url)?;
let mut conn = mysql_async::Conn::new(opts).await?;
let mut result = conn.query_iter(query).await?;
let mut first = match result.next().await {
Ok(Some(row)) => row,
Ok(None) => {
let mut workbook = Workbook::new();
workbook.add_worksheet();
workbook.save(output)?;
return Ok(0);
}
Err(e) => return Err(e.into()),
};
let db_cols: Vec<String> = first
.columns()
.iter()
.map(|c| c.name_str().to_string())
.collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let mut workbook = Workbook::new();
let worksheet = workbook.add_worksheet();
for (col, header) in headers.iter().enumerate() {
worksheet.write_string(0, col as u16, header)?;
}
let mut row_idx = 1u32;
for col in 0..col_count {
let val: Option<String> = first.take(col);
worksheet.write_string(row_idx, col as u16, &val.unwrap_or_default())?;
}
row_idx += 1;
loop {
match result.next().await {
Ok(Some(row)) => {
for col in 0..col_count {
let val: Option<String> = row.get(col);
worksheet.write_string(row_idx, col as u16, &val.unwrap_or_default())?;
}
row_idx += 1;
}
Ok(None) => break,
Err(e) => return Err(e.into()),
}
}
let total = (row_idx - 1) as usize;
workbook
.save(output)
.with_context(|| format!("无法保存 XLSX 文件: {output}"))?;
Ok(total)
}
pub async fn export_postgres_to_csv(
url: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
batch_size: usize,
) -> anyhow::Result<usize> {
use futures::StreamExt;
let (client, connection) = tokio_postgres::connect(url, tokio_postgres::NoTls).await?;
tokio::spawn(async move {
if let Err(e) = connection.await {
eprintln!("PG 连接错误: {e}");
}
});
let stmt = client.prepare(query).await?;
let db_cols: Vec<String> = stmt
.columns()
.iter()
.map(|c| c.name().to_string())
.collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let rows = client
.query_raw(
&stmt,
std::iter::empty::<&(dyn tokio_postgres::types::ToSql + Sync)>(),
)
.await?;
let mut wtr = csv::Writer::from_path(output)?;
wtr.write_record(&headers)?;
tokio::pin!(rows);
let mut batch: Vec<Vec<String>> = Vec::with_capacity(batch_size);
let mut total = 0usize;
while let Some(row_result) = rows.next().await {
let row = row_result?;
batch.push(
(0..col_count)
.map(|i| row.try_get::<_, String>(i).unwrap_or_default())
.collect(),
);
if batch.len() >= batch_size {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
batch.clear();
}
}
if !batch.is_empty() {
write_csv_batch(&mut wtr, &batch)?;
total += batch.len();
}
wtr.flush()?;
Ok(total)
}
pub async fn export_postgres_to_xlsx(
url: &str,
query: &str,
custom_headers: Option<Vec<String>>,
output: &str,
_batch_size: usize,
) -> anyhow::Result<usize> {
use futures::StreamExt;
let (client, connection) = tokio_postgres::connect(url, tokio_postgres::NoTls).await?;
tokio::spawn(async move {
if let Err(e) = connection.await {
eprintln!("PG 连接错误: {e}");
}
});
let stmt = client.prepare(query).await?;
let db_cols: Vec<String> = stmt
.columns()
.iter()
.map(|c| c.name().to_string())
.collect();
let headers = custom_headers.unwrap_or(db_cols);
let col_count = headers.len();
let rows = client
.query_raw(
&stmt,
std::iter::empty::<&(dyn tokio_postgres::types::ToSql + Sync)>(),
)
.await?;
let mut workbook = Workbook::new();
let worksheet = workbook.add_worksheet();
for (col, header) in headers.iter().enumerate() {
worksheet.write_string(0, col as u16, header)?;
}
tokio::pin!(rows);
let mut row_idx = 1u32;
while let Some(row_result) = rows.next().await {
let row = row_result?;
for col in 0..col_count {
let val: String = row.try_get::<_, String>(col).unwrap_or_default();
worksheet.write_string(row_idx, col as u16, &val)?;
}
row_idx += 1;
}
let total = (row_idx - 1) as usize;
workbook
.save(output)
.with_context(|| format!("无法保存 XLSX 文件: {output}"))?;
Ok(total)
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_sqlite_csv() {
let count = export_sqlite_to_csv(
":memory:",
"SELECT 1 AS num, 'hello' AS greeting",
None,
"test_out.csv",
500,
)
.unwrap();
assert_eq!(count, 1);
let content = std::fs::read_to_string("test_out.csv").unwrap();
assert!(content.contains("num,greeting"));
assert!(content.contains("1,hello"));
let _ = std::fs::remove_file("test_out.csv");
}
#[test]
fn test_sqlite_csv_custom_headers() {
let count = export_sqlite_to_csv(
":memory:",
"SELECT 1, 2",
Some(vec!["A".into(), "B".into()]),
"test_custom.csv",
500,
)
.unwrap();
assert_eq!(count, 1);
let content = std::fs::read_to_string("test_custom.csv").unwrap();
assert!(content.contains("A,B"));
let _ = std::fs::remove_file("test_custom.csv");
}
#[test]
fn test_sqlite_xlsx() {
let count = export_sqlite_to_xlsx(
":memory:",
"SELECT 1 AS num, 'hello' AS greeting",
None,
"test_out.xlsx",
500,
)
.unwrap();
assert_eq!(count, 1);
assert!(std::path::Path::new("test_out.xlsx").exists());
let _ = std::fs::remove_file("test_out.xlsx");
}
#[test]
fn test_sqlite_batch_boundary() {
let count = export_sqlite_to_csv(
":memory:",
"WITH RECURSIVE cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 1200) SELECT x FROM cnt",
None,
"test_batch.csv",
500,
)
.unwrap();
assert_eq!(count, 1200);
let _ = std::fs::remove_file("test_batch.csv");
}
}