SHARE
TWEET

Untitled

a guest Jan 25th, 2019 109 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import psycopg2
  2. from collections import defaultdict
  3.  
  4. client = psycopg2.connect(database="clearcare", user="bzoidberg", password="a4bg5ka9pE3bGi2&", host="10.40.1.27")
  5. cursor = client.cursor()
  6.  
  7. # get all tables
  8. cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
  9. tables = [x[0] for x in cursor.fetchall()]
  10. fields = defaultdict(list)
  11.  
  12. # get all PKs
  13. where_clause = table_where_clause = ",".join(("%s::regclass" for x in tables))
  14. cursor.execute("SELECT c.relname, a.attname, format_type(a.atttypid, a.atttypmod) FROM pg_index i JOIN   pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) JOIN pg_class c ON c.oid = i.indrelid WHERE  i.indrelid in (%s) AND    i.indisprimary;" % where_clause, tables)
  15. for table_name, column_name, column_type in cursor.fetchall():
  16.     if column_type not in ('bigint', 'integer', 'smallint'): continue
  17.     fields[table_name].append(column_name)
  18.  
  19. # get all FKs
  20. cursor.execute("""SELECT conrelid::regclass AS "FK_Table"
  21.       ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column"
  22.       ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position(' REFERENCES ' in pg_get_constraintdef(c.oid))+12, position('(' in substring(pg_get_constraintdef(c.oid), 14))-position(' REFERENCES ' in pg_get_constraintdef(c.oid))+1) END AS "PK_Table"
  23.       ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14, position(')' in substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14))-1) END AS "PK_Column"
  24. FROM   pg_constraint c
  25. JOIN   pg_namespace n ON n.oid = c.connamespace
  26. WHERE  contype IN ('f', 'p ')
  27. AND pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %'
  28. ORDER  BY pg_get_constraintdef(c.oid), conrelid::regclass::text, contype DESC;""")
  29. for fk_table, fk_column, pk_table, pk_column in cursor.fetchall():
  30.     fields[fk_table].append(fk_column)
  31.  
  32. # do the conversion
  33. cursor.execute("SET session_replication_role = replica;")
  34. cursor.execute("START TRANSACTION")
  35.  
  36. # empty the database
  37. for table in fields:
  38.     if table in ('django_content_type', 'auth_permission', 'agency_agencycountry'):
  39.         print 'preserving %s' % table
  40.         continue
  41.     elif table == 'auth_user':
  42.         print 'preserving admin'
  43.         cursor.execute("delete from %s where id != 8657371" % table)
  44.     elif table == 'auth_user_user_permissions':
  45.         print 'preserving admin m2m'
  46.         cursor.execute("delete from %s where user_id != 8657371" % table)
  47.     else:
  48.         cursor.execute("delete from %s" % table)
  49.  
  50.  
  51. for table, fields in fields.items():
  52.     for field in fields:
  53.         print table, field
  54.         cursor.execute("ALTER TABLE %s ALTER COLUMN %s TYPE bigint" % (table, field))
  55.         if field == 'id':
  56.             cursor.execute("ALTER SEQUENCE %s_%s_seq START WITH 1000000000000000 RESTART WITH 1000000000000000 MINVALUE 1000000000000000 MAXVALUE 1999999999999999999" % (table, field))
  57. cursor.execute("COMMIT")
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