SQL 如何转储某些 SQLite3 表的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/75675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I dump the data of some SQLite3 tables?
提问by pupeno
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)? The dump should be in SQL format, as it should be easily re-entered into the database later and should be done from the command line. Something like
如何转储数据库的某些 SQLite3 表（不是所有表）的数据，并且仅转储数据，而不转储模式？转储应该是 SQL 格式，因为它应该很容易在以后重新输入到数据库中，并且应该从命令行完成。就像是
sqlite3 db .dump
but without dumping the schema and selecting which tables to dump.
但不转储模式并选择要转储的表。
回答by CyberFonic
You don't say what you wish to do with the dumped file.
你没有说你想对转储的文件做什么。
I would use the following to get a CSV file, which I can import into almost everything
我将使用以下内容来获取 CSV 文件，我可以将其导入到几乎所有内容中
.mode csv
-- use '.separator SOME_STRING' for something other than a comma.
.headers on
.out file.csv
select * from MyTable;
If you want to reinsert into a different SQLite database then:
如果要重新插入不同的 SQLite 数据库，则：
.mode insert <target_table_name>
.out file.sql
select * from MyTable;
回答by jellyfish
You can do this getting difference of .schema and .dump commands. for example with grep:
你可以这样做得到 .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.sqlfile will contain only data without schema, something like this:
data.sql文件将只包含没有架构的数据，如下所示：
BEGIN TRANSACTION;
INSERT INTO "table1" VALUES ...;
...
INSERT INTO "table2" VALUES ...;
...
COMMIT;
I hope this helps you.
我希望这可以帮助你。
回答by polyglot
Not the best way, but at lease does not need external tools (except grep, which is standard on *nix boxes anyway)
不是最好的方法，但至少不需要外部工具（除了 grep，这是 *nix 盒子上的标准）
sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'
but you do need to do this command for each table you are looking for though.
但是您确实需要为您要查找的每个表执行此命令。
Note that this does not include schema.
请注意，这不包括架构。
回答by Paul Egan
You can specify one or more table arguments to the .dump special command, e.g.sqlite3 db ".dump 'table1' 'table2'".
您可以为 .dump 特殊命令指定一个或多个表参数，例如sqlite3 db ".dump 'table1' 'table2'"。
回答by retracile
Any answer which suggests using grep to exclude the CREATElines or just grab the INSERTlines from the sqlite3 $DB .dumpoutput will fail badly. The CREATE TABLEcommands list one column per line (so excluding CREATEwon't get all of it), and values on the INSERTlines can have embedded newlines (so you can't grab just the INSERTlines).
任何建议使用 grep 排除CREATE行或仅从输出中获取INSERT行的答案都sqlite3 $DB .dump将失败。这些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
Tested on sqlite3 version 3.6.20.
在 sqlite3 版本 3.6.20 上测试。
If you want to exclude certain tables you can filter them with $(sqlite $DB .tables | grep -v -e one -e two -e three), or if you want to get a specific subset replace that with one two three.
如果您想排除某些表，您可以使用 过滤它们$(sqlite $DB .tables | grep -v -e one -e two -e three)，或者如果您想获得特定的子集，将其替换为one two three.
回答by Drew
As an improvement to Paul Egan's answer, this can be accomplished as follows:
作为对 Paul Egan 的回答的改进，这可以按如下方式完成：
sqlite3 database.db3 '.dump "table1" "table2"' | grep '^INSERT'
--or--
- 或者 -
sqlite3 database.db3 '.dump "table1" "table2"' | grep -v '^CREATE'
The caveat, of course, is that you have to have grep installed.
当然，需要注意的是，您必须安装 grep。
回答by Davoud Taghawi-Nejad
In Python or Java or any high level language the .dump does not work. We need to code the conversion to CSV by hand. I give an Python example. Others, examples would be appreciated:
在 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
If you have 'panel data, in other words many individual entries with id's add this to the with look and it also dumps summary statistics:
如果您有“面板数据”，换句话说，许多带有 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
回答by PeterCo
According to the SQLite documentation for the Command Line Shell For SQLiteyou can export an SQLite table (or part of a table) as CSV, simply by setting the "mode" to "csv" and then run a query to extract the desired rows of the table:
根据命令行 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
Then use the ".import" command to import CSV (comma separated value) data into an SQLite table:
然后使用“.import”命令将CSV（逗号分隔值）数据导入到SQLite表中：
sqlite> .mode csv
sqlite> .import C:/work/dataout.csv tab1
sqlite> .exit
Please read the further documentation about the two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.
请阅读有关要考虑的两种情况的进一步文档：(1) 表“tab1”以前不存在，(2) 表“tab1”已经存在。
回答by harningt
The best method would be to take the code the sqlite3 db dump would do, excluding schema parts.
最好的方法是采用 sqlite3 db dump 会执行的代码，不包括架构部分。
Example pseudo code:
示例伪代码：
SELECT 'INSERT INTO ' || tableName || ' VALUES( ' ||
{for each value} ' quote(' || value || ')' (+ commas until final)
|| ')' FROM 'tableName' ORDER BY rowid DESC
See: src/shell.c:838(for sqlite-3.5.9) for actual code
请参阅：（src/shell.c:838对于 sqlite-3.5.9）实际代码
You might even just take that shell and comment out the schema parts and use that.
您甚至可以使用该 shell 并注释掉模式部分并使用它。
回答by Francisco Puga
Review of other possible solutions
其他可能的解决方案
Include only INSERTs
仅包含 INSERT
sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"'
Easy to implement but it will fail if any of your columns include new lines
易于实施，但如果您的任何列包含新行，它将失败
SQLite insert mode
SQLite 插入模式
for t in $(sqlite3 $DB .tables); do
echo -e ".mode insert $t\nselect * from $t;"
done | sqlite3 $DB > backup.sql
This is a nice and customizable solution, but it doesn't work if your columns have blob objects like 'Geometry' type in spatialite
这是一个很好且可定制的解决方案，但如果您的列在空间中具有像“几何”类型的 blob 对象，则它不起作用
Diff the dump with the schema
用模式区分转储
sqlite3 some.db .schema > schema.sql
sqlite3 some.db .dump > dump.sql
grep -v -f schema.sql dump > data.sql
Not sure why, but is not working for me
不知道为什么，但对我不起作用
Another (new) possible solution
另一个（新的）可能的解决方案
Probably there is not a best answer to this question, but one that is working for me is grep the inserts taking into account that be new lines in the column values with an expression like this
这个问题可能没有最好的答案，但对我有用的是 grep 插入，考虑到列值中的新行，带有这样的表达式
grep -Pzo "(?s)^INSERT.*\);[ \t]*$"
To select the tables do be dumped .dumpadmits a LIKE argument to match the table names, but if this is not enough probably a simple script is better option
选择表确实被转储.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
or, something more elaborated to respect foreign keys and encapsulate all the dump in only one transaction
或者，更详细地尊重外键并将所有转储封装在一个事务中
TABLES='table1 table2 table3'
echo 'BEGIN TRANSACTION;' > /tmp/backup.sql
echo '' >> /tmp/backup.sql
for t in $TABLES ; do
echo -e ".dump ${t}" | sqlite3 | 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
Take into account that the grep expression will fail if );is a string present in any of the columns
考虑到如果);任何列中存在字符串，grep 表达式将失败
To restore it (in a database with the tables already created)
恢复它（在已经创建表的数据库中）
sqlite3 -bail database.db3 < /tmp/backup.sql
