daily pastebin goal
15%
SHARE
TWEET

PostgreSQL / psycopg2 ETL Testing

mapbaker Jan 25th, 2016 (edited) 108 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. __author__ = 'matthew_baker'
  2. import psycopg2
  3.  
  4. connDev = psycopg2.connect("host=host1 dbname=dpspgisdev user=postgres password=***")
  5. connQa = psycopg2.connect("host=host2 dbname=dpspgisdev user=postgres password=***")
  6. curDev = connDev.cursor()
  7. curQa = connQa.cursor()
  8.  
  9. print "Truncating Source"
  10. curQa.execute('delete from "tempHoods"')
  11. connQa.commit()
  12. #Get Data
  13. curDev.execute('select  nbhd_id, ltrim(rtrim(nbhd_name)), typology, ltrim(rtrim(notes)), cast(geom as varchar) from "CCD_Neighborhoods";')
  14. rows = curDev.fetchall()
  15.  
  16.  
  17. sql_insert = 'INSERT INTO "tempHoods" (nbhd_id, nbhd_name, typology, notes, geom) values '
  18. sql_values = ['(%s, %s, %s, %s, %s)']
  19.  
  20.  
  21. data_values = []
  22. batch_size = 1
  23.  
  24. for i, row in enumerate(rows, 1):
  25.             data_values += row[:5]
  26.             if i % batch_size == 0:
  27.                 sql_stmt = sql_insert + ','.join(sql_values*batch_size) + ';'
  28.                 #print (sql_stmt, data_values)
  29.                 curQa.execute (sql_stmt , data_values )
  30.                 connQa.commit()
  31.                # print data_values
  32.                 data_values = []
  33.  
  34.             if (i % batch_size != 0):
  35.                 sql_stmt = sql_insert + ','.join(sql_values*(i % batch_size)) + ';'
  36.                 curQa.execute (sql_stmt, data_values)
  37.                 #print (sql_stmt, data_values)
  38.                 connQa.commit()
  39.  
  40. # close connections
  41. curDev.close()
  42. curQa.close()
  43. connDev.close()
  44. connQa.close()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top