Python Database Snippets

Multiple county and buffer process

# Multiprocces/concurrent processing of individual county streets buffered by separate ACC type
# keywords: python, psycopg2, gis, multiprocess, concurrent, sql, postgis

import concurrent.futures
import time
import psycopg2, psycopg2.extras
import getpass

counties = ['ALBA', 'ALLE', 'BRON', 'BROO', 'CATT', 'CAYU', 'CHAU', 'CHEM', 'CHEN', 'CLIN', 'COLU', 'CORT', 'DELA', 'DUTC', 'ERIE', 'ESSE', 'FRAN', 'FULT', 'GENE', 'GREE', 'HAMI', 'HERK', 'KING', 'LEWI', 'LIVI', 'MONR', 'MONT', 'NASS', 'NEWY', 'NIAG', 'ONON', 'ONTA', 'ORAN', 'ORLE', 'OSWE', 'OTSE', 'PUTN', 'QUEE', 'RENS', 'RICH', 'ROCK', 'SARA', 'SCHE', 'SCHO', 'SCHU', 'SENE', 'STLA', 'STEU', 'SUFF', 'SULL', 'TIOG', 'TOMP', 'ULST', 'WARR', 'WASH', 'WAYN', 'WEST', 'WYOM', 'YATE', 'MADI', 'ONEI', 'JEFF']
# ACC 1, 2, 3, 4, 5 buffers in feet.
buffers = [50, 40, 30, 20, 10, 5]

password = getpass.getpass(prompt='Password')

start = time.perf_counter()

def do_something(county):

    for acc in enumerate(buffers, 1):
        sql = f'SELECT abbreviation as county, acc, ST_UNION(ST_BUFFER(s.wkb_geometry, {acc[1]}))                 FROM streetsegment s, counties_shoreline c                 WHERE ST_INTERSECTS(s.wkb_geometry, c.wkb_geometry)                     AND s.acc = \'{acc[0]}\'                     AND c.abbreviation = \'{county}\'                GROUP BY abbreviation, acc'

        conn = psycopg2.connect(user='postgres', host='localhost', dbname='nys_streets', port='', password=password)
        cursor = conn.cursor()
        cursor2 = conn.cursor()

        try:
            cursor.execute(sql)
            result = cursor.fetchall()
        #     print(result)
        except psycopg2.Error as e:
            print("Error1")
            print(e)

        try:
            psycopg2.extras.execute_values(cursor2, "INSERT INTO county_streets (county, acc, wkb_geometry) VALUES %s", result)
            conn.commit()
        except psycopg2.Error as e:
            print("Error2")
            print(e)

        cursor.close()
        cursor2.close()
        conn.close()
    return f'Finished "{county}:{acc[0]}" @ {time.strftime("%H:%M:%S", time.localtime())}'

print(f'Start time: {time.strftime("%H:%M:%S", time.localtime())}')
with concurrent.futures.ProcessPoolExecutor() as executor:
    results = [executor.submit(do_something, county) for county in counties] 
    
    for f in concurrent.futures.as_completed(results):
        print(f.result())

finish = time.perf_counter()

print(f'Finished in {round(finish-start)/2} seconds(s)')

Multiple Queries

import concurrent.futures
import time
import psycopg2
import getpass

password = getpass.getpass(prompt='Password')

start = time.perf_counter()

def do_something(letter):
    conn = psycopg2.connect(user='postgres', host='localhost', dbname='', port='', password=password)
    cursor = conn.cursor()
    sql = f'SELECT COUNT(*) FROM streetsegment WHERE completestreetname ILIKE \'{letter}%\''
    cursor.execute(sql)
    result = cursor.fetchone()
    conn.close()
    return f'Ran "{letter}" found: {result}(s)'

letters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXZ'

with concurrent.futures.ProcessPoolExecutor() as executor:
    results = [executor.submit(do_something, l) for l in letters] 
    
    for f in concurrent.futures.as_completed(results):
        print(f.result())

finish = time.perf_counter()

print(f'Finished in {round(finish-start)/2} seconds(s)')

Multiple update

import concurrent.futures
import time
import psycopg2
import getpass

# letters = 'A'
letters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXZ'
# password = getpass.getpass(prompt='Password')

start = time.perf_counter()
def do_something(letter):
    sql = f'SELECT acc, ST_UNION(ST_BUFFER(s.wkb_geometry, 10)) \
            FROM streetsegment s\
            WHERE s.acc = \'2\' \
                AND s.completestreetname ILIKE \'{letter}%\'\
            GROUP BY acc'
    print(sql);
    conn = psycopg2.connect(user='postgres', host='localhost', dbname='nys_streets', port='', password=password)
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.close()
    return f'Ran "{letter}"'

with concurrent.futures.ProcessPoolExecutor() as executor:
    results = [executor.submit(do_something, l) for l in letters] 
    
    for f in concurrent.futures.as_completed(results):
        print(f.result())
    

Multiprocessing

import multiprocessing
import time

start = time.perf_counter()

def do_something(letter):
    print(f'Task: {letter}')
    time.sleep(1)
    print(f'Completed {letter}')
    
processes = []

letters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXZ'

for letter in letters:
    p = multiprocessing.Process(target=do_something, args=[letter])
    p.start()
    processes.append(p)
    
for process in processes:
    process.join()

finish = time.perf_counter()

print(f'Finished in {round(finish-start)/2} seconds(s)')