Re: [sqlite] Merge two sqlar files
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 On Behalf
>Of Peng Yu
>Sent: Friday, 6 September, 2019 18:09
>To: SQLite mailing list
>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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Merge two sqlar files
OK. I made a mistake. UNION of multiple tables should work. On Fri, Sep 6, 2019 at 7:28 PM Peng Yu wrote: > > Hi, I think that there is no way to UNION 3 tables in one command is > it. What is the best way to merge 3 and more sqlar files? Thanks. > > On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf wrote: > > > > > > 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 > > > > -- > > 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 On Behalf > > >Of Peng Yu > > >Sent: Friday, 6 September, 2019 17:04 > > >To: SQLite mailing list > > >Subject: [sqlite] Merge two sqlar files > > > > > >Hi, > > > > > >UNION is for tables in the same db file. Is there an easy command to > > >combine two sqlar files? Thanks. > > > > > >https://www.sqlitetutorial.net/sqlite-union/ > > > > > >-- > > >Regards, > > >Peng > > >___ > > >sqlite-users mailing list > > >sqlite-users@mailinglists.sqlite.org > > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > Regards, > Peng -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Merge two sqlar files
Hi, I think that there is no way to UNION 3 tables in one command is it. What is the best way to merge 3 and more sqlar files? Thanks. On Fri, Sep 6, 2019 at 6:30 PM Keith Medcalf wrote: > > > 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 > > -- > 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 On Behalf > >Of Peng Yu > >Sent: Friday, 6 September, 2019 17:04 > >To: SQLite mailing list > >Subject: [sqlite] Merge two sqlar files > > > >Hi, > > > >UNION is for tables in the same db file. Is there an easy command to > >combine two sqlar files? Thanks. > > > >https://www.sqlitetutorial.net/sqlite-union/ > > > >-- > >Regards, > >Peng > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Merge two sqlar files
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 -- 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 On Behalf >Of Peng Yu >Sent: Friday, 6 September, 2019 17:04 >To: SQLite mailing list >Subject: [sqlite] Merge two sqlar files > >Hi, > >UNION is for tables in the same db file. Is there an easy command to >combine two sqlar files? Thanks. > >https://www.sqlitetutorial.net/sqlite-union/ > >-- >Regards, >Peng >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
