MySQL-VSCode-Python - Return EMPTY TABLE from query if no data is found INSTEAD of "No data"

Question

I have searched a lot for this answer on the internet but no luck so far... I am using VSCode with python to run queries in MySQL database. I have installed "MySQL" extension so i can run queries from an .sql file. Database is hosted with XAMPP. I algo use SQLAlchemy to run queries and fetch outputs to export them to excel files.

Here is the deal, if I run a query and for some reason there are no rows found (query output has zero rows) it returns "No data" instead of returning an empty table with column names from query....

here is an example:

DATABASE: "company" --->database with just one table "vendors"

TABLE: vendors --->EMPTY table with 2 columns: vendor_id and vendor_name

QUERY:

USE company;
SELECT * FROM vendors;

QUERY OUTPUT= "No data" (as shown in the image)

no data output image

DESIRED OUTPUT=

vendor_id	vendor_name

made this kind of output on excel

If I run the code on .sql files i get no data

if i run the code on vscode using SQLAlchemy connector (create engine) i get an empty list as a result, here is the code:

query = "select * from vendors;"
engine = get_engine(user=user, port=port, host=host, db=db, password=password)
with engine.connect() as con:
    res = con.execute(text(f"{query}")).fetchall()
print(res)

output = []

hope you can help me thank you all!

EDIT: could find the answer

than you @GeorgRichter; despise not being the answer, it gave me a nice approach about how to do it! so i thank you for that! here is what i ended up with:

with engine.connect() as con:
                output = con.execute(text(sql_query))
                if output.rowcount == 0:
                    metadata = output.cursor.description
                    column_names = [i[0] for i in metadata]
                    return column_names
                else:
                    rows = output.fetchall()

I checked for rows in the cursor; if no rows were found, it iterates over the metadata[0] which has the column names in order to get what i wanted.

Answer 1

Even if the result set is empty, MySQL server always sends metadata to the client, which contains table and column names, types etc.

This is also described in PEP-249.

So don't try to fetchall first, instead first obtain the CursorResult object:

with engine.connect() as con:
    result = con.execute(text(f"{query}"))

From CursorResult object you can get the metadata:

    metadata= result.cursor.description

Then iterate over the rows:

    for row in result:
        print(row)