formatting string and returning as geojson

[garynobles]

The data straight from the db
read in to data
type(data) tuple

Connecting to the PostgreSQL database...
Rows returned:  17
('9fde26933b7196e3373ffe80e45e7f36e13bf3aba6290d65ce1a2f5360cb246b', 'geo12.08145519945.50652789900000.000000test')
('e46117fb58a13de5d59a6a4a897d3cfd9ae9d4b616278b5d87e254a1c1ea5728', 'geo011.9158859025.40941360000000.000000This is a test')
('eb0cd00281521f241de36a59487b8923c6b2e62894fd411ca3c511a25a958c6d', 'geo45.38584480011.89942250000000.000000Città di Padova')
('bcbc94f00db0cfb5e01b88703ab858ed58082845a4d00309c5d7920418a6aab9', 'geo11.91584370045.409410600000000000000I love geo')
('1008ab688b0fdab30a00e5805d8c774739383288a98787e4740fbee109ea0e8b', 'geo45.40944419911.915854900000000000000')
('5e114a3fa9910b306f880ddedb3a703da9de96464d6c87cd52b5138b7cc78084', 'geo45.40320450011.922245600000000000000')
('aa4bb5d192f58a7f72151d3f47c37917651667ab573b9339f0ab35188ae2b6a3', 'geo000000000000000000000000000000000000')
('813617825c70f30c73c30c8cd20ecde37609b2c137d2b0d1a8ef06d1e33315ee', 'geo44.40320450010.922245600000000000000Hi all!!!')
('b30e845d00993f2258900c4052471a6947c6b3f1a375438b6f4cd206397186a5', 'geo52.7642852014.9228121084000000000000De Vrijheid')
('dd7903d0cea1b90fddc60ff15e3312261018b5edfcfc60dcf31e189a784dc76b', 'geo52.7346136774.9371617604000000000000Mienakker')
('d5070e592bc468140090b881e61993ba291cf3f133c3559a067e28b0c98223dc', 'geo45.70526570012.259708200000000000000')
('17cb54f68c451d19b3b7490316e9a099d290cd8ef80cb65509a0a8aa9d97e956', 'geo45.40936250011.916058399000000000000This is a test')
('71e4c6bb1858fcc65fe35a0fdd38c2a310e65edc24a3dc4c78d5ff6315eaf9ec', 'geo45.40947760011.915883800000000000000')
('569f2c49f12f26676e2995ae58631c10500f4c596d18a142f1927f74f123b6df', 'geo000000000000000000000000000000000000')
('3c05130fa815f0f8a4c0560189839327c784381ba5f104b257ce708463259ee8', 'geo45.40943009911.915891000000000000000He man')
('346de63a2a44ef0ac5793942c0c5a05764f16923ca46a4c20bf4566fd5bae3bd', 'geo45.40946890011.915854000000000000000ITEC Padova')
('4d0db19237393cbe98d972def8a47aff99942a498540594ff4abdead2105dac5', 'geo45.49301060012.330827699000000000000This is a test')
Database connection closed.

The full code

#!/usr/bin/python
import psycopg2
from config import config
import re
import geojson
import tempfile

def get_vendorfield():
    """ Connect to the PostgreSQL database server """

    global cur, data
    conn = None
    try:
        # read connection parameters
        params = config()

        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)

        # create a cursor
        cur = conn.cursor()

        cur.execute(
           "SELECT t.id, t.\"vendorField\" FROM transactions t WHERE t.\"vendorField\" LIKE 'geo%'")
        print("Rows returned: ", cur.rowcount)
        dashmap=(66, 3, 12, 12, 12, ) #this is used for the chunks - but the last chuck is missed because it is of unknown length
        row = cur.fetchone()

        while row is not None:
            #formatting each row - this feels over complicated

            data = row
            data = str(data).encode('latin1').decode('unicode_escape').replace("', '","")
            data = re.findall(''.join('(\S{{{}}})'.format(l) for l in dashmap), data)
            data = str(data)
            data = data.replace(r'"', '|').replace(r'|(', '').replace(r'|', '').replace(r'(', '').replace(r')', '').replace(r'[', '').replace(r']', '').replace(r"'", "").replace(" ","").split(",")

            with open('points.json', 'a') as outfile:
                geojson.dump(geodata, outfile, indent=2)
            outfile.close()

        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

        # execute a statement
        print('PostgreSQL database version:')
        cur.execute('SELECT version()')

        # display the PostgreSQL database server version
        db_version = cur.fetchone()
        print(db_version)

        # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')