adb-final-project/correct_supplier_csv.py at main · razetime/adb-final-project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import csv
import re
import psycopg2
import psycopg2.extras


hostname = 'localhost' #information from postgresql servers
database = 'adb_final'
username = 'postgres'
pwd = 'a'
port_id = 5432
conn = None

def findcd(addr):
    city = None
    if c := re.search('(..)市',addr):
        city = c.group(1)
    dist = None
    if d := re.search('(..)[區鎮]',addr):
        dist = d.group(1)
    return (city,dist)

with open("e-commerce_data\\電商數據\\1_供應商\\supplier.csv",newline='') as infile,psycopg2.connect(
                host = hostname,
                dbname = database,
                user = username,
                password = pwd,
                port = port_id) as conn:
    cur=conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    header = next(csv.reader([infile.readline()]))
    for idx, line in enumerate(infile):
        row = next(csv.reader([line]))
        if len(row) != len(header):
            continue
        new_row = [*row[0:3],*findcd(row[3]),row[4],*findcd(row[5])]
        final_row = []
        for i in new_row:
            if i == '':
                final_row.append(None)
            else:
                final_row.append(i)
        # print(final_row[1])
        cur.execute("insert into supplier values (%s,%s,%s,%s,%s,%s,%s,%s)", final_row)