How to get a CSV string from querying a relational DB?

My solution was using PyGreSQL library and defining this function:

import pg

def get_csv_from_db(query, cols):
    """
    Given the SQL @query and the expected @cols,
    a string formatted CSV (containing headers) is returned

    :param str query:
    :param list of str cols:
    :return str:
    """

    connection = pg.DB(
        dbname=my_db_name,
        host=my_host,
        port=my_port,
        user=my_username,
        passwd=my_password)

    header = ','.join(cols) + '\n'
    records_list = []
    for row in connection.query(query).dictresult():
        record = []
        for c in cols:
            record.append(str(row[c]))
        records_list.append(",".join(record))
    connection.close()

    return header + "\n".join(records_list)

Unfortunately this solution expects the column names in input (which is not too bad IMHO) and iterate over the dictionary result with Python code.

Other solutions (especially out of the box) using other packages are more than welcome.

This is another solution based on PsycoPG and Pandas:

import psycopg2
import pandas as pd

def get_csv_from_db(query):
    """
    Given the SQL @query a string formatted CSV (containing headers) is returned

    :param str query:
    :return str:
    """

    conn = psycopg2.connect(
        dbname=my_db_name,
        host=my_host,
        port=my_port,
        user=my_username,
        passwd=my_password)
    cur = conn.cursor()
    cursor.execute("query")
    df = pd.DataFrame(cur.fetchall(), columns=[desc[0] for desc in cur.description])
    cur.close()
    conn.commit()
    return df.to_csv()

I hadn't chance to test it yet though.

here is a different approach from other answers, Using Pandas.

i suppose you have a database connection already, for example I'm using Oracle database, same can be done by using respective library for your relational db.

only these 2 lines do the trick,

df = pd.read_sql(query, con)
df.to_csv("file_name.csv")

Here is a full example using Oracle database:

dsn = cx_Oracle.makedsn(ip, port,service_name)    
con = cx_Oracle.connect("user","password",dsn)
query = """"select * from YOUR_TABLE"""
df = pd.read_sql(query, con)
df.to_csv("file_name.csv")

PyGreSQL's Cursor has method copy_to. It accept as stream file-like object (which must have a write() method). io.StringIO does meet this condition and do not need access to disk, so it should be possible to do:

import io
csv_io = io.StringIO()
# here connect to your DB and get cursor
cursor.copy_to(csv_io, "SELECT * FROM table", format="csv", decode=True)
csv_io.seek(0)
csv_str = csv_io.read()

Explanation: many python modules accept file-like object, meaning you can use io.StringIO() or io.BytesIO() in place of true file-handles. These mimick file opened in text and bytes modes respectively. As with files there is position of reader, so I do seek to begin after usage. Last line does create csv_str which is just plain str. Remember to adjust SQL query to your needs.

Note: I do not tested above code, please try it yourself and write if it works as intended.