hHf1sREP

1from django.db import connection
2from io import StringIO
3import csv
4
5with connection.cursor() as cursor:
6    buffer = StringIO()
7    writer = csv.writer(buffer, quoting=csv.QUOTE_MINIMAL, quotechar='"', delimiter='\t',
8                        lineterminator="\n")
9    for item in items:
10        writer.writerow(
11            [item['place_id'], item['location'], item['name'], item['vicinity']]
12        )
13    buffer.seek(0)
14
15    cursor.execute("""
16        CREATE TEMPORARY TABLE IF NOT EXISTS tmp_google(
17          place_id character varying(255) NOT NULL,
18          location geometry(Point,4326) NOT NULL,
19          name character varying(255) NOT NULL,
20          vicinity character varying(255) NOT NULL
21          )
22          """)
23
24    cursor.copy_expert("""
25        COPY tmp_google(place_id, location, name, vicinity)
26        FROM stdin WITH DELIMITER as '\t' csv QUOTE '\"' ESCAPE '\"' NULL 'null'
27        """, buffer)
28
29    cursor.execute(
30        """
31        INSERT INTO storages (
32            place_id, location, name, vicinity, datetimeadded, datetimeupdated, is_active
33        )
34        SELECT place_id, location, name, vicinity, NOW(), NOW(), TRUE
35            FROM tmp_google t
36
37        WHERE NOT EXISTS (
38            SELECT * from storages s
39            WHERE s.place_id=g.place_id
40        )
41        """
42    )
43    cursor.execute(
44        """
45            UPDATE storages s
46                    SET
47                datetimeupdated = NOW(),
48                location = s.location,
49                name = s.t.name,
50                vicinity = t.vicinity
51
52            FROM tmp_google t
53            WHERE s.place_id = t.place_id
54        """
55    )
56    buffer.close()