我该如何转储数据库的某些SQLite3表(而非所有表)的数据，而仅转储数据，而不是模式？
转储应采用SQL格式，因为以后应轻松将其重新输入数据库并应从命令行完成。 就像是
但不转储架构并选择要转储的表。
您没有说要对转储的文件做什么。
我将使用以下内容获取CSV文件，该文件可以导入几乎所有内容
1
2
3
4
5
| .mode csv
-- use '.separator SOME_STRING' for something other than a comma.
.headers ON
.out file.csv
SELECT * FROM MyTable; |
如果您想重新插入另一个SQLite数据库，则：
1
2
3
| .mode INSERT <target_table_name>
.out file.sql
SELECT * FROM MyTable; |
您可以这样做来获得.schema和.dump命令的区别。例如使用grep：
1
2
3
| sqlite3 SOME.db .schema > schema.sql
sqlite3 SOME.db .dump > dump.sql
grep -vx -f schema.sql dump.sql > DATA.sql |
data.sql文件将仅包含不带模式的数据，如下所示：
1
2
3
4
5
6
| BEGIN TRANSACTION;
INSERT INTO"table1" VALUES ...;
...
INSERT INTO"table2" VALUES ...;
...
COMMIT; |
我希望这可以帮助你。
这不是最好的方法，但至少不需要外部工具(grep除外，它始终是* nix盒上的标准配置)
1
| sqlite3 DATABASE.db3 .dump | grep '^INSERT INTO"tablename"' |
但是您确实需要为要查找的每个表执行此命令。
请注意，这不包括架构。
您可以为.dump特殊命令指定一个或多个表参数，例如sqlite3 db".dump 'table1' 'table2'"。
任何建议使用grep排除CREATE行或仅从sqlite3 $DB .dump输出中抓住INSERT行的答案都将严重失败。 CREATE TABLE命令每行列出一列(因此排除CREATE不会得到全部)，并且INSERT行上的值可以嵌入换行符(因此您不能仅抓取INSERT行)。
1
2
3
4
| FOR t IN $(sqlite3 $DB .tables); do
echo -e".mode insert $t
select * from $t;"
done | sqlite3 $DB > backup.sql |
在sqlite3 3.6.20版上测试。
如果要排除某些表，则可以使用$(sqlite $DB .tables | grep -v -e one -e two -e three)对其进行过滤，或者如果要获取特定的子集，请使用one two three替换它们。
作为对保罗·伊根(Paul Egan)答案的改进，可以按以下步骤完成：
1
| sqlite3 DATABASE.db3 '.dump"table1""table2"' | grep '^INSERT' |
- 要么 -
1
| sqlite3 DATABASE.db3 '.dump"table1""table2"' | grep -v '^CREATE' |
注意，当然，您必须安装grep。
在Python或Java或任何高级语言中，.dump不起作用。我们需要手动将转换编码为CSV。我举一个Python示例。其他的，例子将不胜感激：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| 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外观中，并且还会转储摘要统计信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
| 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 |
根据命令行外壳程序的SQLite文档对于SQLite，只需将"模式"设置为" csv"，然后运行查询以提取所需的行，即可将SQLite表(或表的一部分)导出为CSV。桌子：
1
2
3
4
5
| sqlite> .header ON
sqlite> .mode csv
sqlite> .once c:/WORK/dataout.csv
sqlite> SELECT * FROM tab1;
sqlite> .exit |
然后使用" .import"命令将CSV(逗号分隔值)数据导入到SQLite表中：
1
2
3
| sqlite> .mode csv
sqlite> .import C:/WORK/dataout.csv tab1
sqlite> .exit |
请阅读有关这两种情况的更多文档：(1)表" tab1"以前不存在，并且(2)表" tab1"已存在。
审查其他可能的解决方案
仅包括INSERT
1
| sqlite3 DATABASE.db3 .dump | grep '^INSERT INTO"tablename"' |
易于实现，但是如果您的任何列包含新行，它将失败
SQLite插入模式
1
2
3
4
| FOR t IN $(sqlite3 $DB .tables); do
echo -e".mode insert $t
select * from $t;"
done | sqlite3 $DB > backup.sql |
这是一个不错的且可自定义的解决方案，但如果您的列在spacespaceite中具有Blob对象(例如" Geometry"类型)，则该方法将无效
与模式差异转储
1
2
3
| sqlite3 SOME.db .schema > schema.sql
sqlite3 SOME.db .dump > dump.sql
grep -v -f schema.sql dump > DATA.sql |
不知道为什么，但对我不起作用
另一个(新的)可能的解决方案
这个问题可能没有最佳答案，但对我来说有用的是grep插入，考虑到列值中的换行符是这样的：
1
| grep -Pzo"(?s)^INSERT.*\);[ \t]*$" |
要选择要转储的表，.dump允许使用LIKE参数来匹配表名，但是如果这样做还不够，则可能使用简单的脚本是更好的选择
1
2
3
4
5
6
| 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 |
或者，更详细地说明如何尊重外键并将所有转储封装在一个事务中
1
2
3
4
5
6
7
8
9
10
| 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表达式将失败
还原它(在已创建表的数据库中)
1
| sqlite3 -bail DATABASE.db3 < /tmp/backup.sql |
最好的方法是采用sqlite3 db dump可以执行的代码，不包括模式部分。
伪代码示例：
1
2
3
| 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并注释掉架构部分并使用它。
此版本可与插入符内的换行符一起使用：
sqlite3 database.sqlite3 .dump | grep -v '^CREATE'
实际上，排除以CREATE开头的所有行(不太可能包含换行符)
retracile的答案应该是最接近的答案，但它不适用于我的情况。一个插入查询刚刚在中间中断，并且导出刚刚停止。不知道是什么原因。但是，它在.dump期间工作正常。
最后，我编写了一个工具，用于拆分从.dump生成的SQL：
https://github.com/motherapp/sqlite_sql_parser/
您可以在表格上进行选择，在每个字段后插入逗号以产生csv，或使用GUI工具返回所有数据并将其保存到csv。
