Merge two sqlar files

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Merge two sqlar files

Peng Yu
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Merge two sqlar files

Keith Medcalf

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 <[hidden email]> On Behalf
>Of Peng Yu
>Sent: Friday, 6 September, 2019 17:04
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Merge two sqlar files

Peng Yu
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Merge two sqlar files

Peng Yu
In reply to this post by Keith Medcalf
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 <[hidden email]> 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 <[hidden email]> On Behalf
> >Of Peng Yu
> >Sent: Friday, 6 September, 2019 17:04
> >To: SQLite mailing list <[hidden email]>
> >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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Merge two sqlar files

Peng Yu
OK. I made a mistake. UNION of multiple tables should work.

On Fri, Sep 6, 2019 at 7:28 PM Peng Yu <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> On Behalf
> > >Of Peng Yu
> > >Sent: Friday, 6 September, 2019 17:04
> > >To: SQLite mailing list <[hidden email]>
> > >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
> > >[hidden email]
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Regards,
> Peng



--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Merge two sqlar files

Keith Medcalf
In reply to this post by Peng Yu

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 <[hidden email]> On Behalf
>Of Peng Yu
>Sent: Friday, 6 September, 2019 18:09
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users