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 csvoutfile = 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 rowsoutcsv.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.

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 asheader.

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 csvfrom sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_baseBase = 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 dataBase.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 engineresult = 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