CODEWITHSUNDEEP
pythonmysql
user1229351
user1229351

Reputation: 2035

python mysql select return only first row of table, not all

im dealing with strage problem and this is like this:

this query should return all of my table:

db = MySQLdb.connect(host="localhost", port=3306, user="A", passwd="B", db="X")
cursor = db.cursor()
cursor.execute("select * from mytable")
cursor.fetchall()
for row in cursor:
print row

for loop should print all rows in cursor but it will only print the first one. it seems cursor is filled with first row only.

is there anything that i missed here? thanks

Upvotes: 1

Views: 17708

Answers (7)

nir
nir

Reputation: 117

i know its been very long time since, but i didnt find this answer anywhere else and thought it might help.

cursor.execute("SELECT top 1 * FROM my_table")

Upvotes: 0

Joe Ho
Joe Ho

Reputation: 928

You can try limit:

cursor.execute("select * from mytable limit 1")

Upvotes: 2

puma
puma

Reputation: 328

I also had this problem. My mistake was that after inserting new row in the table I didn't commit the result. So you should add db.commit() after INSERT command.

Upvotes: 0

Blairg23
Blairg23

Reputation: 12065

This is not the correct way to use the .fetchall() method. Use cursor.stored_results() and then do a fetchall() on the results to perform this task, like this:

db = MySQLdb.connect(host="localhost", port=3306, user="A", passwd="B", db="X")
cursor = db.cursor()
cursor.execute("select * from mytable")
results = cursor.stored_results()
for result in results:
    print result.fetchall()

Upvotes: 0

Carlos Rodriguez
Carlos Rodriguez

Reputation: 883

you need a dic and save the result here

dic={}
cursor.execute("select * from table")
dic['table']=cursor.fetchall()
for row in range(len(dic['table'])):
    print dic['table'][row]

and if you need print any colum

print dic['table'][row]['colum']

Upvotes: 0

Racso
Racso

Reputation: 2449

Try

db = MySQLdb.connect(host="localhost", port=3306, user="A", passwd="B", db="X")
cursor = db.cursor()
for row in cursor.execute("select * from mytable"):
    print row

Upvotes: 0

Marcel Pfeiffer
Marcel Pfeiffer

Reputation: 1068

You need to put the output of cursor.fetchall() into a variable. Like

db = MySQLdb.connect(host="localhost", port=3306, user="A", passwd="B", db="X")
cursor = db.cursor()
cursor.execute("select * from mytable")
rows = cursor.fetchall()
for row in rows:
    print row

Upvotes: 3

Related Questions