0

I have stablished a function in MySQL that lets me query some data by injecting other queries results into a Temporary Table.

My question is, is it possible to query said Temporary table once I've run my function using MySQL connector?

The reason I'm asking is that, while running a simple query from within python:

query = ("SELECT data FROM mytemporarytable")
cursor.execute(query)
cursor_buff = cursor.fetchall()

I'm getting the following error:

mysql.connector.errors.ProgrammingError: 1146 (42S02): Table 'mydatabase.mytemporarytable' doesn't exist

Thank you very much in advance!

Alvaro Bravo
  • 71
  • 7

1 Answers1

1

A temporary table is only accessible in one session, as long as you didn't create it in one you can't access it after words.

So instead of a function make a stored procedure of it.

And end it by

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   CREATE TEMPORARY TABLE mytemporarytable SELECT * FROM orig_tbl LIMIT 0;
   SELECT data FROM mytemporarytable;
END

and then

use the resutset like explained here https://stackoverflow.com/a/15320433/5193536

nbk
  • 45,398
  • 8
  • 30
  • 47