Merging two SQLite tables from the same database with python and sqlite3

Question

I am trying to create a new table that combines columns from two different tables using an id column that is common to both as a keyword. Let's imagine then that I have a database named db.db that includes two tables named table1 and table2.

table1 looks like this:

id | item | price
-------------
 1 | book | 20  
 2 | copy | 30   
 3 | pen  | 10

and table2 like this:

id | shop | color
-------------
 1 |  11  | blue  
 2 |  34  | red   
 3 |  25  | red

What should I write in sqlite3 to have something like this??

id | item | price | shop | color
-------------------------------
 1 | book | 20    |  11  | blue 
 2 | copy | 30    |  34  | red   
 3 | pen  | 10    |  25  | red

Thanks!

Possible duplicate of Create new SQLite table combining column from other tables with sqlite3 and python — Shawn
– Shawn, Commented Oct 12, 2018 at 0:44

flmlopes · Accepted Answer · 2018-10-13 18:54:01Z

2

Edited: try to change the LEFT JOIN for INNER JOIN to see if it works.

import sqlite3
conn = sqlite3.connect('db.db')   
c = conn.cursor()

def merged():
    c.execute('CREATE TABLE table3 AS SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id')
    for line in c.fetchall():
        print(line)
merged()

edited Oct 13, 2018 at 18:54

answered Oct 11, 2018 at 22:46

flmlopes

621 silver badge10 bronze badges

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

4 Comments

Totor Over a year ago

I have tried your approach that but when I query, table 2 still has the same name of columns. I would like to have a table3 that has the 4 columns

flmlopes Over a year ago

i editted. try to change the left join for inner join

Totor Over a year ago

I tried again with INNER JOINT but still, I have the number of columns in table2 than at the beginning. But ive tried the following and works, maybe I didnt explain well what i wanted: 'CREATE TABLE table3 AS SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id'

flmlopes Over a year ago

great, i am glad that you got it right. i will put that in the final answer, thanks

Ajax1234 · Accepted Answer · 2018-10-11 22:38:00Z

1

You can use itertools.groupby:

import itertools, sqlite3
d1 = list(sqlite3.connect('db.db').cursor().execute("SELECT id, item, price FROM table1"))
d2 = list(sqlite3.connect('db.db').cursor().execute("SELECT id, item, price FROM table2"))
full_data = sorted(d1+d2, key=lambda x:x[0])
grouped = [[a, list(b)] for a, b in itertools.groupby(full_data, key=lambda x:x[0])]
final_grouped = [[a, *[i for c in b for i in c[1:]]] for a, b in grouped]
conn = sqlite3.connect('db.db')
conn.execute('CREATE TABLE file3 (id real, item text, price real, shop real, color text)')
conn.executemany('INSERT INTO file3 VALUES (?, ?, ?, ?, ?)', final_grouped)
conn.commit()
conn.close()

answered Oct 11, 2018 at 22:38

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Collectives™ on Stack Overflow

Merging two SQLite tables from the same database with python and sqlite3

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related