Rutracker CSV Postgres import script

import os
import csv
import pg8000
connection = pg8000.connect(user='rutracker', password='dbpassword', database='rutracker')

# Configuration
categories = [2, 8, 9, 18, 22, 23, 35]

# Creating table if not exists
cursor = connection.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS shares(
    id serial PRIMARY KEY,
    category smallint NOT NULL DEFAULT 0,
    subcategory smallint NOT NULL DEFAULT 0,
    share_id integer NOT NULL,
    share_hash char(40) NOT NULL,
    share_name varchar(250) NOT NULL,
    share_size bigint,
    CONSTRAINT id_unique unique (share_id),
    CONSTRAINT hash_unique unique (share_hash)
);""")
connection.commit()
print('[OK] Database connection established')

dirs = list(filter(os.path.isdir, os.listdir(os.getcwd())))
for dirt in dirs:
    for category in categories:
        if(os.path.isfile(dirt+'/category_'+str(category)+'.csv')):
            file = open(dirt+'/category_'+str(category)+'.csv', 'r', encoding='UTF-8')
            shares = list(csv.DictReader(file, fieldnames=('cat_id', 'cat_name', 'id', 'hash', 'name', 'size'), delimiter=';', quotechar='"'))
            file.close()

            inserts = 0
            updates = 0

            for share in shares:
                cursor.execute("SELECT EXISTS(SELECT id FROM shares WHERE share_hash=%s LIMIT 1);", [share['hash']])
                if cursor.fetchone()[0]:
                    cursor.execute("UPDATE shares SET share_id=%s, share_name=%s, share_size=%s WHERE share_hash=%s;", [
                        share['id'],
                        share['name'],
                        share['size'],
                        share['hash']
                    ])
                    updates += 1
                else:
                    cursor.execute("SELECT EXISTS(SELECT id FROM shares WHERE share_id=%s LIMIT 1);", [share['id']])
                    if cursor.fetchone()[0]:
                        cursor.execute("UPDATE shares SET share_hash=%s, share_name=%s, share_size=%s WHERE share_id=%s;", [
                            share['hash'],
                            share['name'],
                            share['size'],
                            share['id']
                        ])
                        updates += 1
                    else:
                        cursor.execute("INSERT INTO shares (category, subcategory, share_id, share_hash, share_name, share_size) VALUES (%s, %s, %s, %s, %s, %s)", [
                            category,
                            share['cat_id'],
                            share['id'],
                            share['hash'],
                            share['name'],
                            share['size']
                        ])
                        inserts += 1

            connection.commit()
            if(updates==0):
                print("[OK] Pushing %s inserts for category #%s from dump at date %s" % (inserts, category, dirt))
            else:
                print("[OK] Pushing %s inserts and %s updates for category #%s from dump at date %s" % (inserts, updates, category, dirt))

cursor.close()