CODEWITHSUNDEEP
pythonsqlite
Scalextrix
Scalextrix

Reputation: 531

python sqlite: find matching rows from two tables and output columns

I have one sqlite database with two tables 'NNDATA' and 'TEAM'. TEAM has one column 'cpids'. NNDATA has 8 columns, column[0] is 'CPID' some but not all of which will match cpids from TEAM table.

where cpids from TEAM match CPID from NNDATA, I want to return the matched row entries from column[2] 'NeuralMagnitude' and column[5] 'Address' from NNDATA

I tried:

conn = sqlite3.connect("Rain.db")
c = conn.cursor()
address = c.execute('select Address from NNDATA where CPID=(select cpids from TEAM)').fetchall()
nn_mag = c.execute('select NeuralMagnitude from NNDATA where CPID=(select cpids from TEAM)').fetchall()
conn.close()

but it just gives me one result, I did a list compare and got 100s of matches, so Im 99% sure its not finding all the matches.

I also tried making cpids a variable:

conn = sqlite3.connect("Rain.db")
c = conn.cursor()
cpids = c.execute('select cpids from TEAM').fetchall()
address = c.execute('select Address from NNDATA where CPID={cpids}'.format(cpids=cpids)).fetchall()
nn_mag = c.execute('select NeuralMagnitude from NNDATA where CPID={cpids}'.format(cpids=cpids)).fetchall()
conn.close()

but that seemed to say Address and NeuralMagnitude were not recognised.

I saw some sqlite solutions but they seemed to run to several lines of code, and I think for Python it has to be called in a single line? Thanks.

Upvotes: 0

Views: 1295

Answers (1)

Scalextrix
Scalextrix

Reputation: 531

@snow was correct, thanks:

conn = sqlite3.connect("Rain.db")
c = conn.cursor()
address = c.execute('select Address from NNDATA where CPID in (select cpids from TEAM)').fetchall()
nn_mag = c.execute('select NeuralMagnitude from NNDATA where CPID in (select cpids from TEAM)').fetchall()
conn.close()

Upvotes: 1

Related Questions