extract from db using python

secrets.json

{
    "db_password": "",
    "db_host": ""
}

sql_extract.py

###
#
# run this file with 'python sql_extract.py'
#
# you will need a secrets.json file in the same directory
# the secrets.json file needs 2 fields:
#   db_host: database hostname (e.g. "cpdb.abcdefg12345.us-east-2.rds.amazonaws.com" or "localhost")
#   db_password: the database password. if no password use empty string
#
###


# pip install json
import json

# pip install psycopg2
import psycopg2

def rowToDict(row):

    # update these fields as needed
    return {
        'officer_id': row[0],
        'salary': row[1],
        'pay_grade': row[2],
        'rank_changed': row[3],
        'rank': row[4],
        'year': row[5]
    }


if __name__ == '__main__':
    with open('secrets.json') as f:
      secrets = json.load(f)

    connection = psycopg2.connect(
        database="cpdb",
        user="cpdb-student",
        password=secrets['db_password'],
        host=secrets['db_host'],
        port='5432'
    )

    query = """select
                officer_id, salary, pay_grade, rank_changed, rank, year
                from data_salary
                order by year
                limit 50"""

    cur = connection.cursor()
    cur.execute(query)

    with open('python_results.csv', 'w') as outfile:
        writer = csv.writer(outfile)
        for r in results:
            writer.writerow(list(r))