Be aware however that the resulting sqlar table is incomplete. It does not
have a "create unique index sqlar_autoindex_1 on sqlar(name)", and you may not
be able to create one because there may be duplicate name entries
notwithstanding that the rows are unique.
To do this properly and include the last "name" duplicate as the unique "name"
entry you would do for an arbitrary number of sqlar files:
import sqlite3
cn=sqlite3.connect(sys.argv[1], isolation_level=None) # open database and turn
off the broken magic
cn.execute('''CREATE TABLE IF NOT EXISTS sqlar(
name TEXT PRIMARY KEY, -- name of the file
mode INT, -- access permissions
mtime INT, -- last modification time
sz INT, -- original file size
data BLOB -- compressed content
);''')
for infile in sys.argv[2:]:
cn.execute('ATTACH DATABASE ? AS d1', [infile])
cn.execute('''insert into main.sqlar select * from d1.sqlar where true
on conflict (name) do
update set (mode, mtime, sz, data) = (excluded.mode,
excluded.mtime, excluded.sz, excluded.data);''')
cn.execute('DETACH d1')
cn.close()
*you need to be using a version of sqlite that understands the insert on
conflict (upsert) and true/false constants.
where the first param is the name of the database (which may already be an
sqlar database containing data) to merge into and the rest of the params are
the sqlar databases to merge from.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On Behalf
>Of Peng Yu
>Sent: Friday, 6 September, 2019 18:09
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] Merge two sqlar files
>
>OK. It works. But it seems that `.ar` is not necessary.
>
>import sqlite3
>infile1, infile2, outfile = sys.argv[1:]
>conn=sqlite3.connect(outfile)
>c=conn.cursor()
>c.execute('ATTACH DATABASE ? AS d1', [infile1])
>c.execute('ATTACH DATABASE ? AS d2', [infile2])
>c.execute('CREATE TABLE sqlar AS SELECT * FROM d1.sqlar UNION SELECT *
>FROM d2.sqlar')
>conn.commit()
>
>> UNION is for "unioning" the output of two select statements (with
>duplicate rows removed). It does not matter if the tables are in the same
>database or even on the same planet, as long as they can be accessed on the
>same connection. Sqlar creates nothing more than a standard sqlite
>database file with a table called sqlar that contains the data.
>>
>> >sqlite
>> SQLite version 3.30.0 2019-09-04 07:55:38
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .ar -cf test.db appi.py
>> sqlite> .ar -cf test1.db bs.py
>> sqlite> attach 'test.db' as test;
>> sqlite> attach 'test1.db' as test1;
>> sqlite> create table sqlar as select * from test.sqlar union select *
>from test1.sqlar;
>> sqlite> .ar -t
>> appi.py
>> bs.py
>
>--
>Regards,
>Peng
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
