python postgresql migration script example three# -*- coding: utf-8 -*- import sys import psycopg2 pgsql_source_connection = None pgsql_source_curs

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

import sys
import psycopg2


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'] = 'library'
c['user'] = 'saju'
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():

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

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

c.execute('CREATE TABLE "lib_contributor" (\
"id" serial NOT NULL PRIMARY KEY,\
"library_id" integer NOT NULL REFERENCES "lib_library" ("id") DEFERRABLE INITIALLY DEFERRED,\
"name" varchar(1000) NOT NULL\
)')


c.execute('CREATE TABLE "lib_book_author" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_editor" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_reviser" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_compiler" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_translator" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_illustrator" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_forward" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_interpreter" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_cover_designer" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_commentry" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_preface" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_introduction" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_prologue" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_reteller" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_cover_photographer" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')


c.execute('CREATE TABLE "lib_publisher" (\
"id" serial NOT NULL PRIMARY KEY,\
"library_id" integer NOT NULL REFERENCES "lib_library" ("id") DEFERRABLE INITIALLY DEFERRED,\
"name" varchar(300) NOT NULL\
)')

c.execute('CREATE TABLE "lib_book_publisher" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"publisher_id" integer NOT NULL REFERENCES "lib_publisher" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "publisher_id")\
)')


c.execute('select * from lib_book;')
for book in list(c.fetchall()):
#book[4] author
#book[5] editor
#book[6] reviser
#book[7] compiler
#book[8] translator
#book[9] illustrator
#book[10] forward
#book[11] interpreter
#book[12] cover_designer
#book[44] commentry
#book[45] preface
#book[46] introduction
#book[47] prologue
#book[49] reteller
#book[50] cover_photographer
#book[19] publisher

if book[4]:
print "author", book[4]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[4],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_author (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[5]:
print "editor", book[5]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[5],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_editor (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[6]:
print "reviser", book[6]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[6],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_reviser (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[7]:
print "compiler", book[7]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[7],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_compiler (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[8]:
print "translator", book[8]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[8],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_translator (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[9]:
print "illustrator", book[9]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[9],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_illustrator (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[10]:
print "forward", book[10]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[10],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_forward (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[11]:
print "interpreter", book[11]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[11],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_interpreter (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[12]:
print "cover_designer", book[12]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[12],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_cover_designer (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[44]:
print "commentry", book[44]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[44],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_commentry (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[45]:
print "preface", book[45]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[45],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_preface (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[46]:
print "introduction", book[46]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[46],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_introduction (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[47]:
print "prologue", book[47]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[47],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_prologue (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[49]:
print "reteller", book[49]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[49],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_reteller (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[50]:
print "cover_photographer", book[50]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[50],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_cover_photographer (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[19]:
c.execute("insert into lib_publisher (library_id, name) values (%s,%s) returning id", (book[1], book[19],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_publisher (book_id, publisher_id) values(%s,%s)", (book[0], id))

c.execute('commit;')



c.execute('begin;')
c.execute('ALTER TABLE lib_book DROP COLUMN author')
c.execute('ALTER TABLE lib_book DROP COLUMN editor')
c.execute('ALTER TABLE lib_book DROP COLUMN reviser')
c.execute('ALTER TABLE lib_book DROP COLUMN compiler')
c.execute('ALTER TABLE lib_book DROP COLUMN translator')
c.execute('ALTER TABLE lib_book DROP COLUMN illustrator')
c.execute('ALTER TABLE lib_book DROP COLUMN forward')
c.execute('ALTER TABLE lib_book DROP COLUMN interpreter')
c.execute('ALTER TABLE lib_book DROP COLUMN cover_designer')
c.execute('ALTER TABLE lib_book DROP COLUMN commentry')
c.execute('ALTER TABLE lib_book DROP COLUMN preface')
c.execute('ALTER TABLE lib_book DROP COLUMN introduction')
c.execute('ALTER TABLE lib_book DROP COLUMN prologue')
c.execute('ALTER TABLE lib_book DROP COLUMN reteller')
c.execute('ALTER TABLE lib_book DROP COLUMN cover_photographer')
c.execute('commit;')


c.execute('begin;')
c.execute('ALTER TABLE lib_physicalbook RENAME COLUMN accessionNo TO accession_no')
c.execute('ALTER TABLE lib_member RENAME COLUMN memberID TO member_id')
c.execute('ALTER TABLE lib_member RENAME COLUMN joinDate TO join_date')
c.execute('ALTER TABLE lib_member RENAME COLUMN expiryDate TO expiry_date')
c.execute('ALTER TABLE lib_member RENAME COLUMN numTickets TO num_tickets')
c.execute('ALTER TABLE lib_member RENAME COLUMN numAvailTickets TO num_avail_tickets')

c.execute('ALTER TABLE lib_reservation RENAME COLUMN reservedDate TO reserved_date')
c.execute('ALTER TABLE lib_reservation RENAME COLUMN expiryDate TO expiry_date')
c.execute('ALTER TABLE lib_reservation RENAME COLUMN lentOut TO lent_out')

c.execute('ALTER TABLE lib_lending RENAME COLUMN physicalBook_id TO physical_book_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN previousState_id TO physical_book_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN nextState_id TO next_state_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN reservedDate TO reserved_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN issueDate TO issue_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN returnDate TO return_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN returnedDate TO returned_date')

c.execute('ALTER TABLE lib_memberprofile_bookProfiles RENAME TO lib_memberprofile_book_profile')
c.execute('commit;')


c.close()



def main():
copy_data()


if __name__ == "__main__":
main()