Add Column Names to retrieved data from pymysql

Question

I have a simple SQL command via pymsql to retrieve data

db = pymysql.connect(
    db=DATABASE,
    passwd=DB_PASSWORD,
    host=DB_HOST,
    user=DB_USER,
)
cursor = db.cursor()
cursor.execute("""
    select flight, aircraft_model
    from flights
    where flights.aircraft_type in ('boeing', 'airbus')
""")
res = cursor.fetchall()

This will return a array of tuples e.g.

[
('tk123', 'b737'),
('us123', 'a230')
]

How can I have the first tuple to be the column names e.g.

[
('flight', 'aircraft_model')
('tk123', 'b737'),
('us123', 'a230')
]

I thought it was quite obvious from above, but I am not looking for similar to MySQL: Get column name or alias from query as that has a different output format than I specified.

is there a reason you have to have the column names? and if you do can't you just [('column', 'names'), *cursor.fetchall()]? — Kenzo Staelens
– Kenzo Staelens, Commented May 15 at 14:05
Yes as it is needed further down the script. Ideally I'd get the column names from the cursor / return from the sql command as there are over 50 different database calls so doing it manually isn't the cleanest — user7692855
– user7692855, Commented May 15 at 14:12
Another solution is to use the DictCursor class in your connect statement, eg. pymysql.connect(cursorclass=pymysql.cursors.DictCursor, ...). This will return the query results as a collection of dictionaries rather than tuples. — Lord Elrond
– Lord Elrond, Commented May 15 at 14:43
if your SQL query has all columns in first line select flight, aircraft_model then you could extract names using string functions, and later put as first element on list [(...tuple with names ...), *cursor.fetchall()]. Or probably you could get columns from cursor.description. And you have 50 places then you should create one function which gets only SQL query and it executes query, and it also adds first row with columns, and use this function in 50 places. — furas
– furas, Commented May 16 at 13:19
I have the (well at least a) answer, but unfortunately can't answer the question as the question is closed. — user7692855
– user7692855, Commented May 17 at 15:58

user7692855 · Accepted Answer · 2025-05-23 11:38:24Z

0

I got it working with the following code:

db = pymysql.connect(
    db=DATABASE,
    passwd=DB_PASSWORD,
    host=DB_HOST,
    user=DB_USER,
)
cursor = db.cursor()
cursor.execute("""
    select flight, aircraft_model
    from flights
    where flights.aircraft_type in ('boeing', 'airbus')
""")
res = (tuple([i[0] for i in c.description]),) + c.fetchall()

answered May 23 at 11:38

user7692855

1,4726 gold badges21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add Column Names to retrieved data from pymysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related