top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Multiple rows from MySQL query saved in an array structure

+1 vote

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)

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)

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?

posted Mar 20, 2013 by Salil Agrawal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button
you can simplify irrelevant code (e.g. don't need to see a very long select statement)

1 Answer

+1 vote

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) )

answer Mar 20, 2013 by anonymous
Similar Questions
0 votes

I want to create a random float array of size 100, with the values in the array ranging from 0 to 5. I have tried random.sample(range(5),100) but that does not work. How can i get what i want to achieve?

+2 votes

I want to use the stdout of child process as an input of another thread, but some how I am not able to read the stdout. Using Popen I have created a child process and stdout of it I have redirected to PIPE (because I don't want that to be printed on with my main process). Now using this statement,"Line=Proc.stdout.readline()" I want to read stdout of child process and I have to do operation on that. but somehow i am not able to read from stdout of child process.

Following is the code snapshot -

Proc = Popen(["python.exe",""],stdout=PIPE)

while True:
            Line = Proc.stdout.readline()
            print Line

Here, process) is continuously printing "Hello World!!" in place of reading stdout of child process.

+2 votes

Develop a C# Windows Form Application that allows users to do all of the following.
Read a List of patient's information from a text file (*.txt), the input text file is selected from the Open File Dialog.
Your program should accept any text file with the following format:
a. The file contains columns with Basic information about patients.
b. Columns may be separated by spaces and/or tabs.
c. The first line in the file is the column header.
d. After the header, each line represents a Patient (name, address, phone#, Bdate, gander ,wheight, height and blood type).
e. Successfully imported patients are displayed on a data grid view in a second form and added to a patient list.

  1. The user will be able to display on The Grid view :
    a) Female patients.
    b) Patients with age<45 year. c) Save Over weighted patients on a text file .  Note: To find over weighted patients you have to calculate the BMI value. BMI=Weight/ (Height*Height). If BMI <18.5 >>>>> under weighted.
    18.5<=BMI<=25 >>>>>>>Normal.
    BMI>25 >>>>>>>>>>>> Over Weighted.
+2 votes

How we can send mail with attachment in Python? Is it any prerequisite for it?

+2 votes

The problem description:

There are set of processes running on my system say process_set_1. Build a process agent which runs an asynchronous socket listening to incoming requests from process_set_1. Each process sends an id. The agent stores these ids in a dictionary and sends response that ur id has been accepted. Now agent process receives some data from another process (which is some sort of sister process for the agent). This data contains id along with a command which is to be sent by the agent to the process_set_1 through HTTP client over AF_UNIX socket, since each process in process_set_1 has a HTTP listening CLI. The process agent sends an HTTP request by recognizing id stored in dictionary to the process_set_1. A service running in the process_set_1 routes this HTTP command to the respective process.

Now my problem is the HTTP request which to be sent must go through AF_UNIX socket. I got this solution.

class UnixStreamHTTPConnection(httplib.HTTPConnection):

    def __init__(self, path, host='localhost/rg_cli',port=None, strict=None,
        httplib.HTTPConnection.__init__(self, host, port=port, strict=strict,

    def connect(self):
        self.sock = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)

But this cannot work since normal socket will not work and thus i thought of asyncore module in python. To use asyncore module again i will have to subclass asyncore.dispatcher. This class also contains connect() method.

Another problem is I don't know how asyncore module works and thus not able to find a way to mix the work of 1) listening forever, accept the connection, store id and the sock_fd.
2) accept data from the process' agent sister process, retrieve the sock_fd by matching the id in the dictionary and send it through the AF_UNIX socket.

Please help since i have already spent 2 days digging it out. Sorry if could explain my problem very well.