SQLAlchemy - How to access column names from ResultProxy and write to CSV headers

Question

I am trying to use SQLAlchemy to establish a connection to a PostgreSQL Database, execute a SQL query and print the output of the file to a file in linux.

from sqlalchemy import create_engine
import yaml
import csv

outfile = open('/path/filename.csv', 'wb')
outcsv = csv.writer(outfile, delimiter='\t')


with open('config.yml') as f:
    cfg = yaml.safe_load(f)
    username = cfg['credentials']['username']
    password = cfg['credentials']['password']
    host = cfg['credentials']['host'] 
    port = cfg['credentials']['port']
    dbname = cfg['credentials']['dbname']
    engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(username, password, host, port, dbname))
    result = engine.execute("""select * from db.tablename """)

    # dump column titles (optional)
    outcsv.writerow(x[0] for x in result.description)

    # dump rows
    outcsv.writerows(result.fetchall())
    outfile.close()

However, I am getting the following error message - Traceback (most recent call last): File "", line 12, in AttributeError: 'ResultProxy' object has no attribute 'description'

If I comment the below command, I am successfully able to get the query result but without the headers. outcsv.writerow(x[0] for x in result.description)

Upon researching, I found the method - result._metadata.keys can generate the headers. However, it generates in some form of a list that I can't attach as header.

Please advise if there is any way to get the file header as well as the data into a csv file. Please consider the fact that I am beginner in Python while answering the above.

Answer 1

In this row in your example:

result = engine.execute("""select * from db.tablename """)

the variable result is pointed to an instance of the class sqlalchemy.engine.ResultProxy.

You want to get the column names to write to the first row of your csv file, and through inspecting your result object found result._metadata.keys which returns a regular python list of column names.

A convention in Python is that whenever an attribute, variable or method on an object is prefaced with an underscore (e.g. _metadata) that means it's not intended to be a public API, and the maintainers of the package may change their implementation in such a manner that breaks your code if you rely on these things (however unlikely for a stable library such as SQLAlchemy). Fortunately, in this case there is a documented public API for you to get what you need: ResultProxy.keys().

Remember that your result variable points to a ResultProxy instance, so you can access the keys() method on that to get the column names, e.g:

result.keys()

What happens when we try to write to your csv file:

outcsv.writerow(result.keys())

We get this exception:

TypeError: a bytes-like object is required, not 'str'

I'm going to assume that this is what you refer to when you say:

However, it generates in some form of a list that I can't attach as header.

You can have a look at the open() API but the issue is that you've opened the file in such a manner that it expects binary data, not the strings that are returned from result.keys(). So if we change the line where you open your file to:

outfile = open('/path/filename.csv', 'w')

outfile.writerow will accept the result of result.keys().

Everything else should "just work".

Here is the code I used to test:

import csv

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)

class Model(Base):
    __tablename__ = 'model'
    id = Column(Integer, primary_key=True)
    col1 = Column(String(10))
    col2 = Column(String(10))

if __name__ == '__main__':
    # create some test data
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)
    s = Session()
    data = dict(col1='a', col2='b')
    s.add_all(Model(**data) for _ in range(5))
    s.commit()
    s.close()
    # put the session away and work with the engine

    result = engine.execute("select * from model")

    outfile = open('filename.csv', 'w', newline='')
    outcsv = csv.writer(outfile, delimiter='\t')
    outcsv.writerow(result.keys())
    outcsv.writerows(result.fetchall())

here's the contents of the csv:

id  col1    col2
1   a   b
2   a   b
3   a   b
4   a   b
5   a   b