如何转储某些SQLite3表的数据?

sql sqlite

195507 观看

13回复

105042 作者的声誉

如何转储数据,只转储数据库的某些SQLite3表(不是所有表)的数据,而不是模式?转储应该是SQL格式的,因为它应该稍后可以很容易地重新输入到数据库中,并且应该从命令行完成。就像是

sqlite3 db .dump

但是没有转储模式并选择要转储的表。

作者: pupeno 的来源 发布者: 2008 年 9 月 16 日

回应 (13)


-3

0 作者的声誉

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

作者: unicorn.ninja 发布者: 16.09.2008 06:55

2

538 作者的声誉

最好的方法是获取sqlite3 db dump将执行的代码,不包括模式部分。

伪代码示例:

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并注释掉架构部分并使用它。

作者: harningt 发布者: 17.09.2008 02:05

203

3400 作者的声誉

你没有说你想对转储文件做什么。

我会使用以下内容来获取CSV文件,我可以将其导入几乎所有内容

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

如果要重新插入另一个SQLite数据库,则:

.mode insert <target_table_name>
.out file.sql 
select * from MyTable;
作者: CyberFonic 发布者: 13.10.2008 10:18

36

5932 作者的声誉

不是最好的方法,但至少不需要外部工具(除了grep,无论如何都是* nix盒子上的标准)

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

但是你确实需要为你正在寻找的每个表执行此命令。

请注意,这不包括架构。

作者: polyglot 发布者: 08.01.2009 01:04

33

301 作者的声誉

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

作者: Paul Egan 发布者: 21.12.2009 06:12

139

427 作者的声誉

你可以通过.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;

我希望这可以帮助你。

作者: jellyfish 发布者: 23.09.2011 08:10

8

3311 作者的声誉

作为对Paul Egan的回答的改进,这可以通过以下方式实现:

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

- 要么 -

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

当然,需要注意的是你必须安装grep。

作者: Drew 发布者: 01.11.2011 11:19

2

928 作者的声誉

此版本适用于插入内的换行符:

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

在实践中,排除所有以CREATE不太可能包含换行符的行开头

作者: Elia Schito 发布者: 16.05.2012 01:40

9

9553 作者的声誉

任何建议使用grep排除CREATE行或只是INSERTsqlite3 $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

测试了sqlite3版本3.6.20。

如果要排除某些表,可以使用它们进行过滤$(sqlite $DB .tables | grep -v -e one -e two -e three),或者如果要获取特定的子集,请将其替换为one two three

作者: retracile 发布者: 16.11.2013 03:18

6

8998 作者的声誉

在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 with look并且它还会转储摘要统计信息:

        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 
作者: Davoud Taghawi-Nejad 发布者: 14.05.2014 03:14

0

1945 作者的声誉

通过伸缩的答案应该是最接近的答案,但它对我的情况不起作用。一个插入查询刚刚在中间断开,导出刚刚停止。不确定是什么原因。然而它在期间工作正常.dump

最后,我编写了一个用于拆分SQL生成的工具.dump

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

作者: Walty Yeung 发布者: 17.02.2015 03:42

2

15247 作者的声誉

审查其他可能的解决方案

仅包含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
作者: Francisco Puga 发布者: 18.05.2016 10:23

4

581 作者的声誉

根据SQLite 命令行Shell的SQLite文档,您可以将SQLite表(或表的一部分)导出为CSV,只需将“mode”设置为“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”已经存在。

作者: PeterCo 发布者: 19.01.2017 09:38
32x32