Working with a PostgreSQL Database

work_with_postgresql.py

import psycopg2
import csv

dbname = 'xxxxx'
user = 'xxxxx'
password = 'xxxxxx'
host = '111.111.11.1'


def connector_db(dbname, user, password, host):
    conn = psycopg2.connect(dbname=f'{dbname}', user=f'{user}', password=f'{password}', host=f'{host}' )
    cursor = conn.cursor()
    cursor.execute("""

                    SELECT 
                        f.order_id, i.title, f.filename, f.creation, f.status, i.category_id, i.type_id, f.filesize/8/1024/1024::FLOAT as filesize_Mb 
                    FROM 
                        files f
                    JOIN 
                        info i 
                    ON 
                        f.order_id=i.order_id
                    WHERE 
                        f."type"='pdf' 
                    AND 
                        (i.type_id=9 or i.type_id=11) AND f.creation > '2018-07-01' AND f.creation < '2019-07-01'
                    LIMIT
                        10000
                    """)

    return cursor


def create_new_file(file_name):
    csv_file = open(f'{file_name}', 'w')
    data = [['order_id', 'title', 'filename', 'creation', 'status', 'category_id', 'type_id', 'filesize_Mb']]
    with csv_file:
                writer = csv.writer(csv_file)
                writer.writerows(data)
    csv_file.close()


def write_file(file_name, cursor):
    for row in cursor:
        csv_file = open(f'{file_name}', 'a')
        data = [[row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]]]
        with csv_file:
            writer = csv.writer(csv_file)
            writer.writerows(data)


if __name__ == '__main__':

    file_name = 'export_db.csv'

    create_new_file(file_name)
    cursor = connector_db(dbname, user, password, host)
    write_file(file_name, cursor)

    cursor.close()
    print('end')