Intersection in sqlite3 in Python
Question: I am trying to extract data that corresponds to a stock that is present in
both of my data sets (given in a code below).

This is my data:

    
    
    #(stock,price,recommendation)
    my_data_1 = [('a',1,'BUY'),('b',2,'SELL'),('c',3,'HOLD'),('d',6,'BUY')]
    
    #(stock,price,volume)
    my_data_2 = [('a',1,5),('d',6,6),('e',2,7)]
    

Here are my questions:

Question 1:

I am trying to extract price, recommendation, and volume that correspond to
asset 'a'. Ideally I would like to get a tuple like this:

    
    
    (u'a',1,u'BUY',5)
    

Question 2:

What if I wanted to get intersection for all the stocks (not just 'a' as in
Question 1), in this case it is stock 'a', and stock 'd', then my desired
output becomes:

    
    
    (u'a',1,u'BUY',5)
    (u'd',6,u'BUY',6)
    

How should I do this?

Here is my try (Question 1):

    
    
    import sqlite3
    
    my_data_1 = [('a',1,'BUY'),('b',2,'SELL'),('c',3,'HOLD'),('d',6,'BUY')]
    
    my_data_2 = [('a',1,5),('d',6,6),('e',2,7)]
    
     #I am using :memory: because I want to experiment
     #with the database a lot
    
    conn = sqlite3.connect(':memory:') 
    
    c = conn.cursor()
    
    c.execute('''CREATE TABLE MY_TABLE_1
              (stock TEXT, price REAL, recommendation TEXT )''' )
    
    c.execute('''CREATE TABLE MY_TABLE_2
              (stock TEXT, price REAL, volume REAL )''' )
    
    
    
    for ele in my_data_1:
        c.execute('''INSERT INTO MY_TABLE_1 VALUES(?,?,?)''',ele)
    
    for ele in my_data_2:
        c.execute('''INSERT INTO MY_TABLE_2 VALUES(?,?,?)''',ele)    
    
    conn.commit()
    
    # The problem is with the following line:
    
    c.execute( 'select* from my_table_1 where stock = ? INTERSECT select* from my_table_2 where stock = ?',('a','a')  )
    
    for entry in c:
        print entry
    

I get no error, but also no output, so something is clearly off.

I also tried this line:

    
    
    c.execute( 'select* from my_table_1 where stock = ? INTERSECT select volume from my_table_2 where stock = ?',('a','a') 
    

but it does not work, I get this error:

    
    
        c.execute( 'select* from my_table_1 where stock = ? INTERSECT select volume from my_table_2 where stock = ?',('a','a')  )
    sqlite3.OperationalError: SELECTs to the left and right of INTERSECT do not have the same number of result columns
    

I understand why I would have different number of resulting columns, but don't
quite get why that triggers an error.

How should I do this?

Thank You in advance


Answer: It looks like those two questions are really the same question.

**Why your query doesn't work:** Let's reformat the query.

    
    
    SELECT * FROM my_table_1 WHERE stock=?
    INTERSECT
    SELECT volume FROM my_table_2 WHERE stock=? 
    

There are two queries in the intersection,

  1. `SELECT * FROM my_table_1 WHERE stock=?`
  2. `SELECT volume FROM my_table_2 WHERE stock=?`

The meaning of "intersect" is "give me the rows that are in both queries".
That doesn't make any sense if the queries have a different number of columns,
since it's impossible for any row to appear in both queries.

Note that `SELECT volume FROM my_table_2` isn't a very useful query, since it
doesn't tell you which stock the volume belongs to. The query will give you
something like `{100, 15, 93, 42}`.

**What you're actually trying to do:** You want a join.

    
    
    SELECT my_table_1.stock, my_table_2.price, recommendation, volume
        FROM my_table_1
        INNER JOIN my_table_2 ON  my_table_1.stock=my_table_2.stock
        WHERE stock=?
    

Think of join as "glue the rows from one table onto the rows from another
table, giving data from both tables in a single row."

It's bizarre that the price appears in both tables; when you write the query
with the join you have to decide whether you want `my_table_1.price` or
`my_table_2.price`, or whether you want to join on
`my_table_1.price=my_table_2.price`. You may want to consider redesigning your
schema so this doesn't happen, it may make your life easier.

