Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: Multiple rows from MySQL query saved in an array structure
Date: Wed, 20 Mar 2013 19:38:19 -0400
Organization: > Bestiaria Support Staff <
References: <abcd1234abc123ab12a0000000141000020000001052@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.3577.1363822717.2939.python-list@python.org>
Lines: 65
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:41625

On Wed, 20 Mar 2013 12:52:40 +0000, Norah Jones <nh.jones01@gmail.com>
declaimed the following in gmane.comp.python.general:

> 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.perm3", 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?

	What database adapter module are you using... The above sure doesn't
look like MySQLdb... 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()
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/