如何转储一些SQLite3表的数据？

Question

如何转储数据库的某些 SQLite3 表（不是所有表）的数据，并且仅转储数据，而不转储架构？ 转储应该采用 SQL 格式，因为稍后应该可以轻松地将其重新输入到数据库中，并且应该从命令行完成。 类似于

sqlite3 db .dump

但不转储架构并选择要转储的表。

Answer 1

您没有说明您希望如何处理转储的文件。

获取 CSV 文件（可以导入几乎所有内容）

.mode csv 
-- use '.separator SOME_STRING' for something other than a comma.
.headers on 
.out file.csv 
select * from MyTable;

获取 SQL 文件（可以重新插入到不同的 SQLite 数据库中）

.mode insert <target_table_name>
.out file.sql 
select * from MyTable;

Answer 2

您可以通过获取 .schema 和 .dump 命令的差异来执行此操作。 例如，使用 grep:

sqlite3 some.db .schema > schema.sql
sqlite3 some.db .dump > dump.sql
grep -vx -f schema.sql dump.sql > data.sql

data.sql 文件将仅包含没有模式的数据，如下所示：

BEGIN TRANSACTION;
INSERT INTO "table1" VALUES ...;
...
INSERT INTO "table2" VALUES ...;
...
COMMIT;

Answer 3

您可以为 .dump 特殊命令指定一个或多个表参数，例如sqlite3 db ".dump 'table1' 'table2'"。

Answer 4

这不是最好的方法，但至少不需要外部工具（除了 grep，无论如何，这是 *nix 盒子上的标准），

sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'

但您确实需要为您要查找的每个表执行此命令。

请注意，这不包括架构。

Answer 5

任何建议使用 grep 排除 CREATE 行或仅从 sqlite3 $DB .dump 输出中获取 INSERT 行的答案都将严重失败。 CREATE TABLE 命令每行列出一列（因此排除 CREATE 将无法获得全部内容），并且 INSERT 行上的值可以嵌入了换行符（因此您不能只获取 INSERT 行）。

for t in $(sqlite3 $DB .tables); do
    echo -e ".mode insert $t\nselect * from $t;"
done | sqlite3 $DB > backup.sql

在 sqlite3 版本 3.6.20 上测试。

如果您想排除某些表，您可以使用 $(sqlite $DB .tables | grep -v -e one -e 2 -e 3) 来过滤它们，或者如果您想获取特定的子集将其替换为一二三。

Answer 6

作为对 Paul Egan 答案的改进，可以按如下方式完成：

sqlite3 database.db3 '.dump "table1" "table2"' | grep '^INSERT'

--或者 --

sqlite3 database.db3 '.dump "table1" "table2"' | grep -v '^CREATE'

当然，需要注意的是您必须安装 grep。

Answer 7

在 Python 或 Java 或任何高级语言中，.dump 不起作用。 我们需要手动编写转换为 CSV 的代码。 我举一个Python的例子。 其他人，示例将不胜感激：

from os import path   
import csv 

def convert_to_csv(directory, db_name):
    conn = sqlite3.connect(path.join(directory, db_name + '.db'))
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    for table in tables:
        table = table[0]
        cursor.execute('SELECT * FROM ' + table)
        column_names = [column_name[0] for column_name in cursor.description]
        with open(path.join(directory, table + '.csv'), 'w') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(column_names)
            while True:
                try:
                    csv_writer.writerow(cursor.fetchone())
                except csv.Error:
                    break

如果您有“面板数据”，换句话说，许多带有 id 的单独条目将其添加到 with 外观中，并且它还会转储摘要统计信息：

        if 'id' in column_names:
            with open(path.join(directory, table + '_aggregate.csv'), 'w') as csv_file:
                csv_writer = csv.writer(csv_file)
                column_names.remove('id')
                column_names.remove('round')
                sum_string = ','.join('sum(%s)' % item for item in column_names)
                cursor.execute('SELECT round, ' + sum_string +' FROM ' + table + ' GROUP BY round;')
                csv_writer.writerow(['round'] + column_names)
                while True:
                    try:
                        csv_writer.writerow(cursor.fetchone())
                    except csv.Error:
                        break 

Answer 8

查看其他可能的解决方案

仅包含 INSERT

sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'

易于实现，但如果任何列包含换行符，则会失败

SQLite 插入模式

for t in $(sqlite3 $DB .tables); do
    echo -e ".mode insert $t\nselect * from $t;"
done | sqlite3 $DB > backup.sql

这是一个很好的可自定义解决方案，但它如果您的列在spatialite中具有像“Geometry”类型这样的blob对象，

则不起作用 将转储与模式进行比较

sqlite3 some.db .schema > schema.sql
sqlite3 some.db .dump > dump.sql
grep -v -f schema.sql dump > data.sql

不知道为什么，但对我不起作用

另一个（新的）可能的解决方案

可能没有这个问题的最佳答案，但对我有用的一个是 grep 插入，考虑到列值中带有 像这样的表达式

grep -Pzo "(?s)^INSERT.*\);[ \t]*$"

要选择要转储的表.dump承认LIKE参数来匹配表名称，但如果这还不够，可能一个简单的脚本是更好的选择

TABLES='table1 table2 table3'

echo '' > /tmp/backup.sql
for t in $TABLES ; do
    echo -e ".dump ${t}" | sqlite3 database.db3 | grep -Pzo "(?s)^INSERT.*?\);$" >> /tmp/backup.sql
done

，或者，其他更详细地考虑外键并将所有转储封装在一个事务中

TABLES='table1 table2 table3'

echo 'BEGIN TRANSACTION;' > /tmp/backup.sql
echo '' >> /tmp/backup.sql
for t in $TABLES ; do
    echo -e ".dump ${t}" | sqlite3 $1 | grep -Pzo "(?s)^INSERT.*?\);$" | grep -v -e 'PRAGMA foreign_keys=OFF;' -e 'BEGIN TRANSACTION;' -e 'COMMIT;' >> /tmp/backup.sql
done

echo '' >> /tmp/backup.sql
echo 'COMMIT;' >> /tmp/backup.sql

考虑到如果 ); 是任何列中存在的字符串，则 grep 表达式将失败

要恢复它（在数据库中）与已经创建的表）

sqlite3 -bail database.db3 < /tmp/backup.sql

Answer 9

最好的方法是采用 sqlite3 数据库转储将执行的代码，不包括架构部分。

伪代码示例：

SELECT 'INSERT INTO ' || tableName || ' VALUES( ' || 
  {for each value} ' quote(' || value || ')'     (+ commas until final)
|| ')' FROM 'tableName' ORDER BY rowid DESC

请参阅：src/shell.c:838（对于 sqlite-3.5.9）了解实际代码

您甚至可以直接使用该 shell 并注释掉模式部分并使用它。

Answer 10

此版本适用于插入中的换行符：

sqlite3 database.sqlite3 .dump | grep -v '^CREATE'

实际上排除所有以 CREATE 开头的行，这些行不太可能包含换行符

Answer 11

retracile 的答案应该是最接近的答案，但它不适用于我的情况。 一个插入查询在中间中断，导出就停止了。 不知道是什么原因。 但是，它在 .dump 期间工作正常。

最后，我编写了一个工具来拆分从 .dump 生成的 SQL：

https:// /github.com/motherapp/sqlite_sql_parser/

Answer 12

您可以对表进行选择，在每个字段后插入逗号以生成 csv，或者使用 GUI 工具返回所有数据并将其保存到 csv。

Answer 13

根据 Command Line Shell For SQLite 的 SQLite 文档，您可以导出 SQLite 表（或部分表格）作为 CSV，只需将“模式”设置为“csv”，然后运行查询来提取表格中所需的行：

sqlite> .header on
sqlite> .mode csv
sqlite> .once c:/work/dataout.csv
sqlite> SELECT * FROM tab1;
sqlite> .exit

然后使用“.import”命令将 CSV（逗号分隔值）数据导入到SQLite 表：

sqlite> .mode csv
sqlite> .import C:/work/dataout.csv tab1
sqlite> .exit

请阅读有关要考虑的两种情况的进一步文档：(1) 表“tab1”以前不存在，(2) 表“tab1”已经存在。

Answer 14

例如，您可以使用如下所示的 .dump 将 apple.db 的架构和数据导出到 backup.sql。 *如果不存在，则创建backup.sql并且我的回答解释了如何将backup.sql导入orange.db：

sqlite3 apple.db .dump > backup.sql

并且，您只能导出<的架构code>apple.db 到 backup.sql with .schema 如下所示：

sqlite3 apple.db .schema > backup.sql

此外，您还可以导出 apple.db 的 schema 和数据使用以下命令将 复制到 backup.sql。 *.output 根据文件存在情况创建并选择或仅选择一个文件，并且您必须退出才能关闭该文件（例如，.exit 或 .quit< /code>) 否则 SQLite 命令的结果将输出到文件中：

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit

并且，您可以将 apple.db 中特定表的架构和数据导出到 backup.sql 使用以下命令：

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .dump person animal
sqlite> .exit

并且，您可以使用以下命令仅将 apple.db 的架构导出到 backup.sql：

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .schema
sqlite> .exit

并且，您可以仅导出使用以下命令将 apple.db 中的特定表复制到 backup.sql。 *.schema 仅导出一个特定表的架构，因此如果您想导出特定多个表的架构，则应多次运行 .schema：

sqlite3 apple.db
sqlite> .output backup.sql
sqlite> .schema person
sqlite> .schema animal
sqlite> .exit

最后，您可以使用以下命令仅将 apple.db 中特定表的数据导出到 backup.sql：

sqlite3 apple.db
sqlite> .mode insert
sqlite> .output backup.sql
sqlite> SELECT * FROM person, animal;
sqlite> .exit