CODEWITHSUNDEEP
pythonsqldjango
Kukula Mula
Kukula Mula

Reputation: 1869

Django raw sql, getting the fields value from a row

Disclaimer: the raw queries i've written bellow are dummies, my real queries are complicated and cannot be executed without raw query,

executing this sql: 

   from django.db import connection, transaction
   cursor = connection.cursor()
   cursor.execute("SELECT id, name, phone FROM table1 WHERE rule=1")
   result_list= cursor.fetchall()

next step require going over the result_list and execute a second query, in which i use the name parameter value in the where clause.

for row in result_list:
        sql_string = 'SELECT id FROM table2 WHERE name='+ row[1]
        cursor = connection.cursor()
        cursor.execute(sql_string)
        ids = cursor.fetchall()

My Question: How can I concatenate all the ids from the different iterations of the loop into a single ids array/list

the following is not what i'm looking for as I wish to have a single array/list of values and not an array of arrays/ list of lists

id_list = []
id_list.append(ids)

Upvotes: 1

Views: 1999

Answers (2)

knbk
knbk

Reputation: 53669

The iteration over result_list will produce a query for each iteration. A better way to do this is to use the SQL IN operator and select all the ids for you second table in one query. 

sql_string = 'SELECT id FROM table2 WHERE name IN (%s)'
cursor = connection.cursor()
# You should let cursor.execute handle string interpolation
cursor.execute(sql_string, [', '.join(x.name for x in result_list)]) 
id_list = [x.id for x in cursor.fetchall()]

The last line uses list comprehension to change the list of lists into a flat list containing all the ids.

Upvotes: 1

Daniel Roseman
Daniel Roseman

Reputation: 599600

I'm not completely sure I understand the question, but perhaps you should be using extend:

id_list.extend(ids)

Upvotes: 0

Related Questions