CODEWITHSUNDEEP
pythonmysql
Bowi
Bowi

Reputation: 1528

Is the DB work done in `execute` or in `fetch`?

I do not really understand, when a (MySQL) DB server will be "working" when I send a query to it:

Consider this Python3 code:

import mysql.connector as mc
connection = mc.connect (host = db_host, user = db_user, passwd = db_password, db = db_name)
cursor = connection.cursor()
cursor.execute("SELECT * FROM veryLongTable;")
result = cursor.fetchall()
cursor.close()
connection.close()

The whole thing spends a few seconds in the execute line and a few minutes in the fetchall line.

What is happening there after all? Is the DB server executing the query and transmitting the "raw" result to me in execute and doing nothing at the fetchall (and the latter is just "read the result I got from the server"), or is it storing the result and only transmitting it when I call fetchall? Or is the query just "prepared" in execute and almost all work is done in fetchall?

Upvotes: 1

Views: 534

Answers (1)

Thomas Sablik
Thomas Sablik

Reputation: 16451

execute executes the query on the server and returns an iterator. fetchall returns the rows.

The server returns an iterator and stores it in the cursor with execute. Then the server returns multiple rows with fetchall. Both operations require a connection to the server.

Almost all work is done in execute. The statement is executed and the data is prepared on the server to be fetched by the client. With fetchall the cursor iterates over the list of prepared rows and transfers them multiple of them at a time to the client.

"or is it storing the result and only transmitting it when I call fetchall" Yes, this is how it works.

Upvotes: 2

Related Questions