从 SQLite 3 导出数据 [英] Exporting data from SQLite 3

问题描述

我需要一种简单的方法来从包含多个表的 SQLite 数据库中导出数据，然后将它们导入到另一个数据库中.

这是我的场景.我有 5 个表:A、B、C、D、E.

每个表都有一个主键作为第一列，称为 ID.我想要一个 Unix 命令，它只会以可以导入另一个数据库的格式从主键转储行中的数据.

我知道我可以做一个

sqlite3 db .dump |插入

但这给了我表格中的所有数据.我不是数据库专家，我正在尝试使用所有可以编写 shell 脚本的 unix 命令来执行此操作，而不是编写 C++ 代码来执行此操作(因为人们告诉我这是最简单的方法).我只是拒绝编写 C++ 代码来完成一项可能在命令行中用 4-5 条语句完成的任务.

有什么建议吗?

解决方案

这可能看起来有点奇怪，但你可以尝试一下:

创建文本文件并在其中放置以下语句:

.mode 插入.输出插入.sqlselect * from TABLE where STATEMENT;-- 将需要的选择查询放在这里.输出标准输出

将此文件提供给 sqlite3:

$ sqlite3 -init initf DATA.DB .schema >模式.sql

结果你会得到两个文件:一个带有简单的插入"(insert.sql)，另一个带有数据库架构(schema.sql).>

I need a simple way to export data from an SQLite database of multiple tables, then import them into another database.

Here is my scenario. I have 5 tables: A, B, C, D, E.

Each table has a primary key as the first column called ID. I want a Unix command that will dump ONLY the data in the row from the primary key in a format that can be imported into another database.

I know I can do a

sqlite3 db .dump | grep INSERT

but that gives me ALL data in the table. I'm not a database expert, and I'm trying to do this with all unix commands in which I can write a shell script, rather than writing C++ code to do it (because that's what people are telling me that's the easiest way). I just refuse to write C++ code to accomplish a task that possible can be done in 4-5 statements from the command line.

Any suggestions?

解决方案

This may look a little weird, however you may give it a try:

Create text file and place following statements there:

.mode insert
.output insert.sql
select * from TABLE where STATEMENT; -- place the needed select query here
.output stdout

Feed this file to sqlite3:

$ sqlite3 -init initf DATA.DB .schema > schema.sql

As the result you will get two files: one with simple "inserts" (insert.sql) and another with db schema (schema.sql).

这篇关于从 SQLite 3 导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！