How to join two tables in a SQLite DB using Python?

Question

I have two tables:

1) Table A with columns: id,c2,c3

2) Table B with columns: pid,c2

I want to join them such that, for A.id=B.pid, join columns c2 and c3 from A with the matching row in table B.

pid can occur multiple times in table B ,but id occurs only once in table A.

I saw other solutions and tried this:

    cursor.execute("""SELECT A.c2, A.c3, B.c2 
           FROM A
           INNER JOIN B 
           ON A.id=B.pid""")       

    conn.commit()

The code executed successfully but no changes were reflected in the tables.

I can provide actual data of tables and columns if required.

UPDATE

I understood that SELECT will only select and not update the data in the table B, but I still don't understand how to code the problem.

If you want to modify the existing tables, as is implied by your statement that "no changes were reflected in the tables," then you need to use either an UPDATE or an INSERT query. — rd_nielsen
– rd_nielsen, Commented Jun 10, 2018 at 15:03
@rd_nielsen alright but I tried looking up stuff and changing code but to no avail. I'd appreciate if you could direct me on how to code the above task — Jai Kotia
– Jai Kotia, Commented Jun 10, 2018 at 17:26
What are you trying to do? Update Table B with the value of c2 from Table A, update Table A with the value of c2 from Table B, add rows to Table B with the value of id and c2 from Table A, or something else? — rd_nielsen
– rd_nielsen, Commented Jun 10, 2018 at 17:28
Sorry if I didn't frame the problem properly. I want to match the id and pid as described above and then add the matching row values of c2 & c3 from table A to table B. — Jai Kotia
– Jai Kotia, Commented Jun 10, 2018 at 17:32
In your description, Table B only has two columns (pid and c2). Does Table B actually have a third column to contain the value of c3 from Table A, or do you want to put the value of c3 in the c2 column of Table B? — rd_nielsen
– rd_nielsen, Commented Jun 10, 2018 at 17:34

Mattias · Accepted Answer · 2018-06-10 15:07:08Z

3

The problem is that you are doing an SELECT, which collects the data you desire from the tables you have. So in your case you get data from the tables, join them and can se the results from the join, but no change will happen. You need to either use UPDATE or INSERT to affect the tables you have.

answered Jun 10, 2018 at 15:07

Mattias

4364 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jai Kotia Over a year ago

Ahh I see, all this while I thought JOIN would add the data from the tables. But my code aside, I still don't understand how to perform the said task

rd_nielsen · Accepted Answer · 2018-06-10 19:22:48Z

2

There's ambiguity in your comment "I want to match the id and pid as described above and then add the matching row values of c2 & c3 from table A to table B." There are (at least) two possibilities: 1) Table B contains an additional column c3, or 2) the value of c3 from Table A should be put in column c2 of Table B. Following is example code for each of these two alternatives.

Table B has a c3 column

insert into B
    (pid, c2, c3)
select
    A.id,
    A.c2,
    A.c3
from
    A
    inner join B on A.id = B.pid 
       and (A.c2 != B.c2 or A.c3 != B.c3)
    ;

The second part of the join clause ensures that data from Table A doesn't duplicate any data already in Table B.

Values of A.c3 are to be added as B.c2

a) Because two different columns of Table A are to be inserted to the same column of Table B, an easy way to do this is with two separate queries.

insert into B
    (pid, c2)
select
    A.id,
    A.c2
from
    A
    inner join B on A.id = B.pid and A.c2 != B.c2
    ;

insert into B
    (pid, c2)
select
    A.id,
    A.c3
from
    A
    inner join B on A.id = B.pid and A.c3 != B.c2
    ;

b) If it's important to do this in a single query, the c2 and c3 columns of Table A can be combined before inserting into Table B.

insert into B
    (pid, c2)
select
    A2.id,
    A2.c2
from
    (select id, c2 from A union select id, c3 as c2 from A) as A2
    inner join B on A2.id = B.pid and A2.c2 != B.c2
    ;

edited Jun 10, 2018 at 19:22

answered Jun 10, 2018 at 18:31

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

1 Comment

Jai Kotia Over a year ago

Thanks a lot your syntax helped me code the solution, albeit I had to change all of it to python

Jack Robison · Accepted Answer · 2018-06-10 18:09:45Z

You could run the following in an sqlite3 console (or from python) to replicate what you're doing:

create table a_table (
  id int primary key not null,
  c2 int not null,
  c3 int not null
);

create table b_table (
  pid int not null references a_table,
  c2 int not null
);

insert into a_table values (1, 2, 3);
insert into b_table values (1, 4);
insert into b_table values (1, 5);

select a.c2, a.c3, b.c2 from a_table a inner join b_table b on a.id=b.pid;

drop table a_table;
drop table b_table;

id only is unique within table_a, pid in table_b foreign keys it. sqlite has a built-in unique rowid column for each table, so we don't actually need the id field in table_a, but lets keep it to stick with your example.

Are you sure pid is not unique and may occur multiple times? This confuses the goal of updating table_a with values from table_b - in the above example would you want to update the row with id 1 in table_a with a new c2 value of 4 or 5? Or are you trying to do something else?

If pid is unique in table_b then you might as well only have table_a and insert or replace into it instead of updating from the second table.

Collectives™ on Stack Overflow

How to join two tables in a SQLite DB using Python?

3 Answers 3

1 Comment

Table B has a c3 column

Values of A.c3 are to be added as B.c2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Table B has a c3 column

Values of A.c3 are to be added as B.c2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related