合并具有不同表的所有SQLite数据库 [英] Merge all SQLite databases with different tables

问题描述

是否存在一种便宜/快速的方法来将SQLite数据库文件与其他表合并?

Is there a cheap/fast way to merge SQLite db files with different tables?

例如:

• a.db仅具有表a
• b.db仅具有表b
• ...

• a.db has only table a
• b.db has only table b
• ...

我想将它们合并到包含所有表a，b，c和d的abcd.db中.

I want to merge these into a abcd.db that contains all tables a, b, c, and d.

我认为我想要的是这样一个神奇的剧本:

I think what I want is such a magic script:

merge_script a.db b.db c.db d.db -o abcd.db

我注意到了这个提示和另一个

I have noticed this tip and another tip and the ATTACH trick, which insert all records into "main" database, but can I do this while tables are absent in the "main" database?

更新

我将SQLIte数据库文件用作简单的存储容器.

I use SQLIte database files as simple storage container.

在大多数情况下，我将每种类型的数据(与表名不同)存储在一个文件中，然后将它们合并到目标"数据库中.

In most cases, I store each type of data (differed by table name) in a single file, and then merge them into "target" database.

但是某些类型的数据应使用相同的表名称.如果使用sqlite3 .dump，则表名会有冲突.

But there are some types of data that should be in the same table name. if use sqlite3 .dump there would be a conflict in tablename.

但是.dump方法非常简单，我将做一些变通方法并使用它.

Yet the .dump approach is very simple, I'll just do some workaround and use it.

推荐答案

只需转储并还原:

for db in a, b, c, d; { sqlite3 ${db}.db .dump | sqlite3 abcd.db }

[更新]

另一个问题，如果表不是唯一的，并且我想合并内容，是否可以快速解决? tdihp

这是一个太宽泛的问题.例如，您将如何处理主键冲突?我会使用某些脚本语言(例如Python)来执行此操作，以应对错误情况(sqlalchemy.SqlSoup在这种情况下非常出色).

This is a little too broad a question. For example, what would you do with primary key violations? I would do this with some script language like Python in order to cope with error conditions (sqlalchemy.SqlSoup is excelent for this kind of thing).

也就是说，如果您知道表已经存在并且没有唯一的约束，则可能可以通过一些即席bash特技来解决问题-假设destinationtablename和sourcetablename具有相同的结构:

That said, if you know the table already exists and there is no unique constraints, you can probably get way with some adhoc bash stunt - assuming that destinationtablename and sourcetablename have the same structure:

$ ( echo '.mode insert destinationtablename'
    echo 'select * from sourcetablename;' 
  ) |  sqlite3 -batch -init - source.db \
    |  sqlite3 -batch destination.db

您可能不知道长注释线程在此站点中被认为是不礼貌的，因此建议您更新问题.一个更好的问题将是:

You may not be aware that long comment threads are considered rude in this site, instead you are encouraged to update your question. A better question will:

• 将来帮助其他有类似问题的用户
• 赚取可为您提供特权的声望点(这些点以后也可用于赏金)

这篇关于合并具有不同表的所有SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！