while loop in python sql query

Question

Hi i have try this query in php which is running fine and i have to do this same in python

   $select=mysql_query("SELECT DISTINCT A.entity_id AS entity_id ,A.email AS email,A.catquizid AS style_quiz_score ,A.catquizquesans AS style_quiz_answer,A.created_at AS date_joined,A.is_active AS is_active ,B.attribute_id AS attribute_id,B.value AS info FROM `customer_entity` AS A inner join  `customer_entity_varchar` AS B on A.entity_id=B.entity_id WHERE B.`attribute_id` IN  (1,2) limit 10",$conn);

    $arr=array();

    while($result= mysql_fetch_assoc($select))
        { 
            if(!isset($arr[$result['entity_id']]['lastname'])){
                $arr[$result['entity_id']]['firstname'] = $result['info'];
            }
            $arr[$result['entity_id']]['lastname'] = $result['info'];
            $arr[$result['entity_id']]["email"]=$result['email'];
            $arr[$result['entity_id']]["style_quiz_score"]=$result['style_quiz_score'];
            $arr[$result['entity_id']]["style_quiz_answer"]=$result['style_quiz_answer'];
            $arr[$result['entity_id']]["date_joined"]=$result['date_joined'];
            $arr[$result['entity_id']]["is_active"]=$result['is_active'];

            $arr[$result['entity_id']]["username"]=normalize_str($result['email']);
        }

and in python i have tried this

def customer_migrate(request):
    cursor = connections['migration'].cursor()
    cursor.execute("SELECT DISTINCT A.entity_id AS entity_id ,A.email AS email,A.catquizid AS style_quiz_score ,A.catquizquesans AS style_quiz_answer,A.created_at AS date_joined,A.is_active AS is_active ,B.attribute_id AS attribute_id,B.value AS info FROM customer_entity AS A inner join  customer_entity_varchar AS B on A.entity_id=B.entity_id WHERE B.attribute_id limit 4 ")
    row = cursor.fetchall()

how can i use the while loop in the python query ,

why was this question tagged with 'django'? are you looking for a solution for django too? — wuher
– wuher, Commented Feb 6, 2013 at 11:42
in that case, i suggest using django's models: docs.djangoproject.com/en/dev/topics/db/models it allows you to execute raw sql queries too: docs.djangoproject.com/en/dev/topics/db/sql — wuher
– wuher, Commented Feb 6, 2013 at 13:23

Lysergia25 · Accepted Answer · 2013-02-06 11:34:53Z

4

Use fetchone() or just iterate through the cursor:

row = cursor.fetchone()
while row is not None:
    print row
    row = cursor.fetchone()

or

for row in cursor:
    print row

edited Feb 6, 2013 at 11:34

answered Feb 6, 2013 at 11:29

Lysergia25

1442 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rohit Goel Over a year ago

the main problem i am facing is with alias names , i cannot fetch tha data like row['entity_id'] , because it doesn't return the dict names , i have read the docs how i get the data with dict names but it doesn't work for me . NOw what i want is the way i used the while loop in PHP can i used it in python

Rohit Goel Over a year ago

i have use this desc = cursor.description and got the folowing ('entity_id', 3, 2, 10, 10, 0, 0)('email', 253, 23, 765, 765, 0, 0)('style_quiz_score', 253, 5, 765, 765, 0, 0)('style_quiz_answer', 252, 0, -1, -1, 0, 0)('date_joined', 12, 19, 19, 19, 0, 0)('is_active', 1, 1, 1, 1, 0, 0)('attribute_id', 2, 1, 5, 5, 0, 0)('info', 253, 7, 765, 765, 0, 0) , i cant understand its meaning

Lysergia25 Over a year ago

@Rohit Are you using pyodbc? Since you are selecting with "AS" you should be able to do: row.entitiy_id Also, the list order of the row is the same as the select statement, so you could use list indexes.

Rohit Goel Over a year ago

if i am using arr['a']=row[0][7] i got the 7th element of row array, how can i do this inside while loop

Rohit Goel Over a year ago

yes you are right but for one user there are 4 rows whose last element is different rest are the same so i have to access them like [0][7],[1][7]....so i want this in while loop , i know it might be confusing for you

|

Anrik · Accepted Answer · 2024-07-09 01:13:09Z

-1

Try:

goDBConnect = 'DRIVER={ODBC Driver 17 for SQL Server};Server=????;Database=????;UID=????;PWD=????'

goCursor = pyodbc.connect(goDBConnect).cursor()

goCursor.execute("""SELECT *
                     FROM table""")

goColumns = [foColumn[0] for foColumn in goCursor.description]

goRowData = goCursor.fetchone()

while goRowData is not None:
   goDictRowData = dict(zip(goColumns, goRowData))
   print(goDictRowData)
   goRowData = goCursor.fetchone()
goCursor.close()

edited Jul 9, 2024 at 1:13

answered Jul 9, 2024 at 1:02

Anrik

594 bronze badges

Collectives™ on Stack Overflow

while loop in python sql query

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related