pyodbc cursor.fetchall() is returning "strange" values

Question

I have a class with multiple functions to handle database operations, one of them is used to run queries (select, update, delete, insert....), but I'm having problems getting the result of my "select" queries. The output values are being formatted strangely as if they were a "list inside of a list":

Here's the portions of my code that's behaving strangely:

# database.connector.py

import logging
import pyodbc

class DBConnector:
   ...

    def _open_cnxn(self) -> pyodbc.Connection:
        """Opens a connection to the database.

        Returns:
            pyodbc.Connection: Connection object
        """

        self.logger.info(f'Openning connection to "{self.server}".')
        return pyodbc.connect(self.conn_string)

    def _run_query(self, query: str, type: str = "select") -> list:
        """Run the given query

        Args:
            query (str): query string
            type (str, optional): select, insert, update, delete, create. Defaults to "select".

        Returns:
            list: Query result if select | else commits and returns [None]
        """

        try:
            cursor = self._open_cnxn().cursor()
            self.logger.info(f'Executing query: "{query}".')
            cursor.execute(query)
        except Exception as error:
            self.logger.error(
                f'There was an error while trying to execute the query.: "{str(error)}"'
            )

        # DEBUG
        columns = [column[0] for column in cursor.description]
        results = [row for row in cursor.fetchall()]

        print(columns)
        print(results)
        # DEBUG

        match type:
            case "select":
                return results
            case "insert" | "update" | "delete" | "create":
                cursor.commit()
                self.logger.info(f"Commited changes to {columns}")
                return [None]

My connection string is inside of __init__() and looks like this:

        self.conn_string = f"DRIVER={{ODBC Driver 17 for SQL Server}}; \
                             SERVER={self.server}; \
                             DATABASE={self.database}; \
                             UID={self.username}; \
                             PWD={self.password}"

I'm using a "testing module" with this code: # testing.py

import configparser
import logging
import sys

from database.connector import DBConnector

secrets = configparser.ConfigParser()
secrets.read("secrets/config.ini")

SERVER = secrets.get("DB_HOMOLOG", "server")
DATABASE = secrets.get("DB_HOMOLOG", "database")
USERNAME = secrets.get("DB_HOMOLOG", "username")
PASSWORD = secrets.get("DB_HOMOLOG", "password")

connector = DBConnector(
    server=SERVER, database=DATABASE, username=USERNAME, password=PASSWORD
)

date_field = "STATUS"
print(connector._run_query("SELECT STATUS FROM TICKETS GROUP BY STATUS"))

the expected result for that code was a list like this:

["New", "Ongoing", "Closed", "Canceled", "Solved"]

instead I'm receiving this:

[("New",), ("Ongoing",), ("Closed",), ("Canceled",), ("Solved",)]

I don't know why my values are being formatted like that. Can someone give me a clue?

Answer 1

the expected result for that code was a list like this: ["New", "Ongoing", "Closed", "Canceled", "Solved"]

instead I'm receiving this: [("New",), ("Ongoing",), ("Closed",), ("Canceled",), ("Solved",)]

This is correct behavior. There is no bug here.

fetchall() doesn't know ahead of time if your query will be returning just one column or multiple columns, so for consistency it returns each row result as tuple.

("New",) is the Python representation for a one-element tuple.