0

I have a folder called Reports with several UTF-8 text files. Each file follows the next rule name:

userCode + '.txt'
I want to have a table with the next columns:

user_code        |   report
---------------------------------------------------
some user code   |   content of  userCode.txt

For example:

user_code        |   report
---------------------------------------------------
D3677234         |   'Born in blah blah ...... '
D4535535         |   'Traveled to blah blah ... '
 ....

where the content of D3677234.txt is Born in blah blah ...... and the content of D4535535.txt is Traveled to blah blah ...

So, I need a way to upload the content of those files into my table in Postgres directly from the Reports folder.

2
  • This is potentially more suitable for the DBA or Stackoverflow StackExchange sister sites but not on-topic for ServerFault
    – HBruijn
    Commented Jun 21 at 11:27
  • 1
    ...but even on those sites, people will expect you to show some effort. What have you tried so far? You may be interested in the psql \copy command (see the docs).
    – larsks
    Commented Jun 21 at 12:07

1 Answer 1

0
import os
import psycopg2

# Database connection parameters
db_params = {
    'dbname': 'your_database',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'port': 'your_port'
}

# Path to the Reports folder
reports_folder = 'path/to/Reports'

def read_file_content(file_path):
    with open(file_path, 'r', encoding='utf-8') as file:
        return file.read()

def insert_into_db(user_code, report_content):
    try:
        conn = psycopg2.connect(**db_params)
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO reports (user_code, report)
            VALUES (%s, %s)
            ON CONFLICT (user_code) DO UPDATE SET report = EXCLUDED.report;
        """, (user_code, report_content))
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as error:
        print(f"Error inserting data: {error}")

def main():
    for filename in os.listdir(reports_folder):
        if filename.endswith('.txt'):
            user_code = filename[:-4]
            file_path = os.path.join(reports_folder, filename)
            report_content = read_file_content(file_path)
            insert_into_db(user_code, report_content)
            print(f"Inserted/Updated {user_code}")

if __name__ == '__main__':
    main()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.