Skip to content

Instantly share code, notes, and snippets.

@k-zehnder

k-zehnder/export_postgres_table_to_csv.py

Created February 20, 2022 01:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save k-zehnder/5ffd09f64e5545fe3615f8ca7852d8e0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save k-zehnder/5ffd09f64e5545fe3615f8ca7852d8e0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. k-zehnder created this gist Feb 20, 2022.
    74 changes: 74 additions & 0 deletions export_postgres_table_to_csv.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    import csv
    import os
    import psycopg2

    # File path and name.
    filePath = '/home/batman/Desktop/'
    fileName = 'detailedflight.csv'

    # Database connection variable.
    connect = None

    # Check if the file path exists.
    if os.path.exists(filePath):

    try:

    # Connect to database.
    connect = psycopg2.connect(host='localhost', database='foo',
    user='postgres', password='password')

    except psycopg2.DatabaseError as e:

    # Confirm unsuccessful connection and stop program execution.
    print("Database connection unsuccessful.")
    quit()

    # Cursor to execute query.
    cursor = connect.cursor()

    # SQL to select data from the person table.
    # sqlSelect = \
    # "SELECT id, firstname, lastname, title, dob \
    # FROM person \
    # ORDER BY id"

    sqlSelect = "select * from detailedflight"
    try:

    # Execute query.
    cursor.execute(sqlSelect)

    # Fetch the data returned.
    results = cursor.fetchall()

    # Extract the table headers.
    headers = [i[0] for i in cursor.description]

    # Open CSV file for writing.
    csvFile = csv.writer(open(filePath + fileName, 'w', newline=''),
    delimiter=',', lineterminator='\r\n',
    quoting=csv.QUOTE_ALL, escapechar='\\')

    # Add the headers and data to the CSV file.
    csvFile.writerow(headers)
    csvFile.writerows(results)

    # Message stating export successful.
    print("Data export successful.")

    except psycopg2.DatabaseError as e:

    # Message stating export unsuccessful.
    print("Data export unsuccessful.")
    quit()

    finally:

    # Close database connection.
    connect.close()

    else:

    # Message stating file path does not exist.
    print("File path does not exist.")