python postgresql migration script example one

python postgresql migration script example one

# -*- coding: utf-8 -*-
import sys
import psycopg2
#from migrate_users import *


pgsql_source_connection = None
pgsql_source_cursor = None
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():

# add column #
alt_todo = "alter table siren_todo add column ticket_id integer;"
alt_ticket = "alter table siren_ticket add column ticket_number integer;"
alt_event = "alter table siren_event add column item_id integer;"
alt_project = "alter table siren_project drop column is_dispaly;"
alt_project_repo = "alter table siren_project drop column code_repository_id;"

#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;"
#alt_todolist_number = "alter table siren_todolist add column todolist_number integer;"
#alt_note_number = "alter table siren_note add column note_number integer NOT NULL;"
#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;"

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_todo)
c.execute(alt_ticket)
c.execute(alt_event)
c.execute(alt_project)
c.execute(alt_project_repo)

#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('select * from siren_ticket order by created_on;')
tickets = {}
for ticket in list(c.fetchall()):
if ticket[8] not in tickets:
tickets[ticket[8]] = 1
#print tickets[ticket[8]]
c.execute('update siren_ticket set ticket_number = %d where id = %d' %(tickets[ticket[8]], ticket[0]))
tickets[ticket[8]] = tickets[ticket[8]] + 1
print tickets[ticket[8]]
print tickets


c.execute("update siren_event set type='TK' where type='TKC'")


c.execute("select * from siren_event where type='TK'")
for event in list(c.fetchall()):
e = event[7].split('/')
#print e[5]
c.execute('select ticket_number, title, project_id from siren_ticket where id=%s' %(e[5]))
for t in list(c.fetchall()):
print t[1]
c.execute('select name from siren_project where id=%s' %(t[2]))
for p in list(c.fetchall()):
print p[0]

c.execute("update siren_event set description = '#%s %s (%s)', action='U' where type='TK' and description='Comment:' and url='/siren/projects/1/tickets/%s/'" %(e[5], t[1], p[0], e[5]))
c.execute("update siren_event set description = '#%s %s (%s)' where type='TK' and action='A' and url='/siren/projects/1/tickets/%s/'" %(e[5], t[1], p[0], e[5]))

c.execute("update siren_event set description = '#%s %s (%s)', action='U' where type='TK' and description='Comment:' and url='/siren/projects/4/tickets/%s/'" %(e[5], t[1], p[0], e[5]))
c.execute("update siren_event set description = '#%s %s (%s)' where type='TK' and action='A' and url='/siren/projects/4/tickets/%s/'" %(e[5], t[1], p[0] ,e[5]))

#--------------#

c.execute('CREATE TABLE "siren_projectrepository" (\
"id" serial NOT NULL PRIMARY KEY,\
"code_repository_id" integer NOT NULL REFERENCES "django_vcs_coderepository" ("id") DEFERRABLE INITIALLY DEFERRED,\
"project_id" integer NOT NULL REFERENCES "siren_project" ("id") DEFERRABLE INITIALLY DEFERRED\
)')




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



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


if __name__ == "__main__":
main()
-------------------------------------------------