why can't I fetch sql statements in python?

Question

I have a very large table (374870 rows) and when I run the following code timestamps just ends up being a long int with the value 374870.... I want to be able to grab all the timestamps in the table... but all I get is a long int :S

import MySQLdb

db = MySQLdb.connect(
                host    = "Some Host",
                user    = "SOME USER",
                passwd  = "SOME PASS",
                db      = "SOME DB",
                port    = 3306

        )

sql = "SELECT `timestamp` from `table`"

timestamps = db.cursor().execute(sql)

eumiro · Accepted Answer · 2010-09-23 13:42:31Z

2

Try this:

cur = db.cursor()
cur.execute(sql)
timestamps = []
for rec in cur:
    timestamps.append(rec[0])

answered Sep 23, 2010 at 13:42

eumiro

214k36 gold badges307 silver badges264 bronze badges

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

3 Comments

Richard Over a year ago

This solutions works, Thanks eumiro. still a bit annoyed that fetchall() doesn't work.

eumiro Over a year ago

you can change my fourth line into for rec in cur.fetchall(): and it will work too

Richard Over a year ago

arg, Brain gone to mush. that is what I was screwing up. fetch functions belong to cursor object :S

unwind · Accepted Answer · 2010-09-23 13:42:44Z

1

You need to call fetchmany() on the cursor to fetch more than one row, or call fetchone() in a loop until it returns None.

answered Sep 23, 2010 at 13:42

unwind

402k64 gold badges492 silver badges620 bronze badges

3 Comments

Richard Over a year ago

the problem is that timestamps ends up being a long int... Maybe due to the large size of my table. so fetchone() fetchmany() or fetchall() will not work for timestamps because a long int object does not have those functions...

eumiro Over a year ago

@Richard, 374870 is not really a large table. Try my answer and you will see that the list timestamps can accomodate your data. If you tell me what it now looks like, we can try to convert it to any format you need.

John Machin Over a year ago

@Richard: fetchone and friends are cursor methods. See my answer.

John Machin · Accepted Answer · 2010-09-23 14:14:02Z

0

Consider the possibility that the not-very-long integer that you are getting is the number of rows in your query result.

Consider reading the docs (PEP 249) ... (1) return value from cursor.execute() is not defined; what you are seeing is particular to your database and for portability sake should not be relied on. (2) you need to do results = cursor.fetch{one|many|all}() or iterate over the cursor ... for row in cursor: do_something(row)

answered Sep 23, 2010 at 14:14

John Machin

83.2k12 gold badges147 silver badges193 bronze badges

Collectives™ on Stack Overflow

why can't I fetch sql statements in python?

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related