[Fixed]-How to get field names when running plain sql query in django


According to PEP 249, you can try using cursor.description, but this is not entirely reliable.


On the Django docs, there’s a pretty simple method provided (which does indeed use cursor.description, as Ignacio answered).

def dictfetchall(cursor):
    "Return all rows from a cursor as a dict"
    columns = [col[0] for col in cursor.description]
    return [
        dict(zip(columns, row))
        for row in cursor.fetchall()


I have found a nice solution in Doug Hellmann’s blog:


from itertools import *
from django.db import connection

def query_to_dicts(query_string, *query_args):
    """Run a simple query and produce a generator
    that returns the results as a bunch of dictionaries
    with keys for the column values selected.
    cursor = connection.cursor()
    cursor.execute(query_string, query_args)
    col_names = [desc[0] for desc in cursor.description]
    while True:
        row = cursor.fetchone()
        if row is None:
        row_dict = dict(izip(col_names, row))
        yield row_dict

Example usage:

  row_dicts = query_to_dicts("""select * from table""") 


try the following code :

def read_data(db_name,tbl_name): 
    details = sfconfig_1.dbdetails
    connect_string = 'DRIVER=ODBC Driver 17 for SQL Server;SERVER={server}; DATABASE={database};UID={username}\

    connection = pyodbc.connect(connect_string)#connecting to the server
    print("connencted to db")
    # query syntax 

    query = 'select top 100 * from '+'[{}].[dbo].[{}]'.format(db_name,tbl_name) + ' t where t.chargeid ='+ "'622102*3'"+';'
    df    = pd.read_sql_query(query,con=connection)

    return "connected to db...................."

Leave a comment