Python SQLAlchemy not returning column names in results

Question

Very basic table, basic query.

My table has these 5 columns:

project_id, name, job, status, state

I wish to query all names related to a specific project_id.

I am receiving a tuple as the result set, with only the field values and not the field names.

RESULT: ('5', 'bob', 'worker', 'union', '123-456-7890', 'CA')

How would I amend this query to receive the field names (i.e. column titles) in the output?

DESIRED: {project_id: '5', name:'bob', job:'worker', status:'union', state:'CA'}

Code:

from sqlalchemy import create_engine

def my_engine(db):
    USERNAME = 'admin'
    PASSWORD = 'password'
    HOST = 'localhost'
    DB_PORT = 1234
    DATABASE = str(db)
    return create_engine('mysql+pymysql://{0}:{1}@{2}:{3}/{4}'
                         .format(USERNAME, PASSWORD, HOST, DB_PORT, DATABASE), pool_pre_ping=True)

def query_mysql(self, table_name, col_title, srx_val):
    myconn = my_engine('dbname')
    return_list = myconn.execute("SELECT * FROM `{0}` WHERE `{1}` = '{2}'"
                                .format(table_name, col_title, srx_val)).fetchall()
    return return_list

outdata = self.query_mysql('users', 'project_id', self.PROJ_NUM)

#returns: ('5', 'bob', 'worker', 'union', '123-456-7890', 'CA')

You should be getting a `ResultProxy` object back, it is printing like a tuple but contains `RowProxy` objects that can be accessed like a dictionary. Maybe this answer will help: https://stackoverflow.com/a/50141868/5982697 and the docs for ResultProxy (https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.ResultProxy) show how to access column keys in each row. You should be able to loop over rows in `outdata` and treat it like a dict, and if needed (see the above answer) make them a dict. — Heath, Aug 20 '19 at 19:47

camposer · Answer 1 · 2023-03-14T15:21:33.183

0

The only way it worked for me:

result = []
items = myconn.execute(query)
keys = items._metadata.keys._keys
for nh in items:
    result.append({keys[i]: nh[i]
        for i in range(len(keys))})
return result

edited Mar 14 '23 at 15:21

answered Mar 14 '23 at 15:12

camposer

5,152
2
17
15

score -1 · Answer 2 · edited Jan 04 '20 at 12:06

-1

Lucas Lima provides the solution here.

You would modify your code as follows:

def query_mysql(self, table_name, col_title, srx_val):
    myconn = my_engine('dbname')
    resproxy = myconn.execute("SELECT * FROM `{0}` WHERE `{1}` = '{2}'"
                                .format(table_name, col_title, srx_val)).fetchall()
    results = []
    if len(data)==0: return results

    for row_number, row in enumerate(resproxy):
        results.append({})
        for column_number, value in enumerate(row):
            results[row_number][row.keys()[column_number]] = value

    return results

edited Jan 04 '20 at 12:06

halfer

19,824
17
99
186

answered Aug 20 '19 at 19:50

cssyphus

37,875
18
96
111

This is *exactly* what I needed. thank you. I looked at Heath's comment at top but could not easily see out how to apply the information. Your code example worked perfectly. I don't know who downvoted you - I see no reason for it! - but will respect your request to upvote the other answer instead. – crashwap Aug 22 '19 at 17:51

Python SQLAlchemy not returning column names in results

Code:

2 Answers2