PostgreSQL tables to csv (Backup database tables as CSV with Pandas)

postgres_pandas_backup.py

import os

import pandas as pd
import psycopg2
from dotenv import find_dotenv
from dotenv import load_dotenv
from psycopg2.extras import DictCursor


def main():
    conn = psycopg2.connect(os.environ.get("POSTGRES_DSN"))
    conn.autocommit = True

    tables = ['users', 'whatever']

    for table in tables:
        # fetch
        cursor = conn.cursor(cursor_factory=DictCursor)
        cursor.execute('SELECT * FROM %s' % table)
        rows = cursor.fetchall()

        # convert to dict manually
        dicts = [dict(row) for row in rows]

        # save to csv
        df = pd.DataFrame(dicts)
        df.to_csv('data/raw/%s.csv' % table)

if __name__ == '__main__':
    load_dotenv(find_dotenv())

    main()