Multiple rows from MySQL query saved in an array structure

Question

I have this Python code:

self.lock_tables("read", ['nets_permissions as n', 'devices_permissions as d'])
usrs = self.db.get("SELECT n.user_id FROM nets_permissions as n \
                    left join devices_permissions as d \
                    on n.user_id = d.user_id \
                    where d.user_id is null \
                    and n.network_id=%s and n.perm<>3", netid)
self.unlock_tables()

for usr in usrs:
    self.lock_tables("write", ['devices_permissions'])
    self.db.execute("INSERT devices_permissions SET \
                     user_id=%s, network_id=%s, device_id=%s, perm=%s",\
                     usr, netid, sensid, perm)
    self.unlock_tables();

I first do a query to retrieve some user_id from two tables. I want save this user_id in one variable and after do a for loop to insert this records in another table...

This code doesn't work. I obtain this error:    
Exception: Multiple rows returned for Database.get() query

How can I retrieve this multiple rows and then process everyone of them at one time?

Comments

you can simplify irrelevant code (e.g. don't need to see a very long select statement)

Answer 1

What database adapter module are you using... The above sure doesn'tlook like MyS QLdb... Using MySQLdb's db-api functions the above would probably look something like (ignoring that your code sample appears to part of some method in a class):

import MySQLdb as db

con = db.connection(specify DB, user, password, etc.)

cur = con.cursor()

rslt = cur.execute("""select n.user_id from nets_permissions as n left join devices_permissions as d on n.user_id = d.user_id where d.user_id is null and n.network_id = %s and n.perm3""", netid)

***** UHM! "where d.user_id is null" implies (to me) that the join won't find anything -- n.user_id = d.user_id suggests you want NULL ids on both sides, but by definition NULL is never equal to NULL!

dta = cur.fetchall()
con.commit() #end implicit transaction

for usr in dta:
cur.execute("""insert devices_permissions (user_id, network_id, device_id, perm) values (%s, %s, %s, %s)""", (usr, netid, sensid, perm) )
con.commit()