Fetchall returning only one column in Python?

Question

I have a a code like this:

db = MySQLdb.connect(user='root', db='galaxy', passwd='devil', host='localhost')
cursor = db.cursor()
cursor.execute('SELECT username, password FROM galaxy_user')
names = [row[0] for row in cursor.fetchall()]
passw = [password[1] for password in cursor.fetchall()]
db.close()

The problem is I can only access either names or passw from the following code. With this I can only get the username. I get empty list for passw. Now if I switch this like:

passw = [row[1] for row in cursor.fetchall()]
names = [password[1] for password in cursor.fetchall()

I get the value of passw but names is empty list. What's happening?

Answer 1

After each cursor.execute you can use cursor.fetchall only once. It "exhausts" the cursor, gets all its data and then it cannot be "read" again.

With the following code you read all data at the same time:

db = MySQLdb.connect(user='root', db='galaxy', passwd='devil', host='localhost')
cursor = db.cursor()
cursor.execute('SELECT username, password FROM galaxy_user')
names, passw = zip(*cursor.fetchall())
db.close()

Another possibility would be to store all data in a list and then read it as if it were a cursor:

records = cursor.fetchall()
names = [record[0] for record in records]
passw = [record[1] for record in records]

Or how about a dictionary (name -> password)?

user_pass = dict(cursor.fetchall())

or simply (as @JonClemens suggested):

user_pass = dict(cursor)