python postgresql migration script example two

python postgresql migration script example two

# -*- coding: utf-8 -*-

import sys
import psycopg2
#from migrate_users import *


pgsql_source_connection = None
pgsql_source_cursor = None

Publish Post

pgsql_source_queries = 0


# Gets the PostgreSQL Source credentials
def get_pg_source_credentials():
c = {}
c['host'] = 'localhost'
c['database'] = 'olddb1'
c['user'] = 'kane'
c['password'] = 'xxx'
c['port'] = '5432'
return c





# Opens a connection to the Source PostgreSQL database
def get_pgsql_source_connection():
try:
credentials = get_pg_source_credentials()
conn = psycopg2.connect("dbname='" + credentials['database'] + "' " + \
"user='" + credentials['user'] + "' " + \
"host='" + credentials['host'] + "' " + \
"port='" + credentials['port'] + "' " + \
"password='" + credentials['password'] + "'")
print "connected to Source db"
except Exception, e:
print "I am unable to connect to the Source PostgreSQL database"
raise e
return conn


def copy_data():
#ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
#ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

# -------- #

alt_message_number = "alter table siren_message add column message_number integer NOT NULL;"
alt_milestone_number = "alter table siren_milestone add column milestone_number integer NOT NULL DEFAULT 0;"
alt_todolist_number = "alter table siren_todolist add column todolist_number integer NOT NULL DEFAULT 0;"
alt_note_number = "alter table siren_note add column note_number integer NOT NULL DEFAULT 0;"
alt_mockupset_number = "alter table siren_mockupset add column mockupset_number integer NOT NULL;"
alt_category_number = "alter table siren_category add column category_number integer NOT NULL DEFAULT 0;"


alt_ticket_number = "ALTER TABLE siren_ticket ALTER COLUMN ticket_number SET NOT NULL;"


pgsql_source_connection = get_pgsql_source_connection()
pgsql_source_cursor = pgsql_source_connection.cursor()

c = pgsql_source_connection.cursor()
c.execute('begin;')

c.execute(alt_message_number)
c.execute(alt_milestone_number)
c.execute(alt_todolist_number)
c.execute(alt_note_number)
c.execute(alt_mockupset_number)
c.execute(alt_category_number)

c.execute(alt_ticket_number)

c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_milestone where project_id='+str(project[0])+';')
m = {}
for milestone in list(c.fetchall()):
print milestone[9]
if milestone[9] not in m:
m[milestone[9]] = 1
#print milestone[milestone[9]]
c.execute('update siren_milestone set milestone_number = %d where id = %d' %(m[milestone[9]], milestone[0]))
m[milestone[9]] = m[milestone[9]] + 1
#print m[milestone[9]]
print m


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_todolist where project_id='+str(project[0])+';')
t = {}
for todolist in list(c.fetchall()):
print todolist[9]
if todolist[9] not in t:
t[todolist[9]] = 1
c.execute('update siren_todolist set todolist_number = %d where id = %d' %(t[todolist[9]], todolist[0]))
t[todolist[9]] = t[todolist[9]] + 1
print t


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_note where project_id='+str(project[0])+';')
n = {}
for note in list(c.fetchall()):
print note[8]
if note[8] not in n:
n[note[8]] = 1
c.execute('update siren_note set note_number = %d where id = %d' %(n[note[8]], note[0]))
n[note[8]] = n[note[8]] + 1
print n


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_category where project_id='+str(project[0])+';')
cat = {}
for category in list(c.fetchall()):
print category[5]
if category[5] not in cat:
cat[category[5]] = 1
c.execute('update siren_category set category_number = %d where id = %d' %(cat[category[5]], category[0]))
cat[category[5]] = cat[category[5]] + 1
print cat


alt_milestone_number_drop = "alter table siren_milestone ALTER column milestone_number DROP DEFAULT;"
alt_todolist_number_drop = "alter table siren_todolist ALTER column todolist_number DROP DEFAULT;"
alt_note_number_drop = "alter table siren_note ALTER column note_number DROP DEFAULT;"
alt_category_number_drop = "alter table siren_category ALTER column category_number DROP DEFAULT;"

c.execute(alt_milestone_number_drop)
c.execute(alt_todolist_number_drop)
c.execute(alt_note_number_drop)
c.execute(alt_category_number_drop)

c.execute('commit;')
c.close()



def main():
copy_data()
#print "PostgreSQL queries: {0}".format(pgsql_queries)


if __name__ == "__main__":
main()

-------------------------------------------------