studyhard-/Migrating_Oracle_data_to_MySQL.sql at master · sungjh0726/studyhard- · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
-- importing modules and declaring functions

import pymysql
import cx_Oracle

def get_oracle_conn():
    return cx_Oracle.connect("hr", "hrpw", "localhost:1521/xe")

def get_mysql_conn(_db):
    return pymysql.connect(
    host='localhost',
    user='dooo',
    password='1',
    port=3307,
    db=_db,
    charset='utf8')


-- 1. JOBS to Job table

connection = mu.get_oracle_conn()
myconn = mu.get_mysql_conn('doodb')

with connection:

    cursor = connection.cursor()
    sql = '''select * from JOBS'''
    cursor.execute(sql)
    rows = cursor.fetchall()

for row in rows:
    print(row)

with myconn:
    cur =  myconn.cursor()
    cur.execute("call sp_drop_fk_refs('Job')")

    cur.execute("drop table if exists Job")

    sql_create = '''
        create table Job (
            id varchar(45) not null,
            title varchar(45) not null,
            min_salary int default 0,
            max_salary int default 0,
            primary key(id)
        )
    '''

    cur.execute(sql_create)

    sql_insert = "insert into Job(id, title, min_salary, max_salary) values(%s, %s, %s, %s)"

    cur.executemany(sql_insert, rows)
    print("Affected Row Count is", cur.rowcount)


-- 2. DEPARTMENTS to Department

connection = mu.get_oracle_conn()
myconn = mu.get_mysql_conn('doodb')

with connection:

    cursor = connection.cursor()
    sql = '''select DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID from DEPARTMENTS'''
    cursor.execute(sql)
    rows = cursor.fetchall()

for row in rows:
    print(row)

with myconn:
    cur =  myconn.cursor()
    cur.execute("call sp_drop_fk_refs('Department')")

    cur.execute("drop table if exists Department")

    sql_create = '''
        create table Department (
            id int default 0 not null,
            name varchar(45) not null,
            manager_id int default 0,
            primary key(id)
        )
    '''

    cur.execute(sql_create)

    sql_insert = "insert into Department(id, name, manager_id) values(%s, %s, %s)"

    cur.executemany(sql_insert, rows)
    print("Affected Row Count is", cur.rowcount)


-- 3. EMPLOYEES to Employee

connection = mu.get_oracle_conn()
myconn = mu.get_mysql_conn('doodb')

with connection:

    cursor = connection.cursor()
    sql = '''select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,
                    JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID from EMPLOYEES'''
    cursor.execute(sql)
    rows = cursor.fetchall()

for row in rows:
    print(row)

with myconn:
    cur =  myconn.cursor()
    cur.execute("call sp_drop_fk_refs('Employee')")

    cur.execute("drop table if exists Employee")

    sql_create = '''
        create table Employee (
            id int default 0 not null,
            first_name varchar(45),
            last_name varchar(45) not null,
            email varchar(45) not null,
            tel varchar(45),
            hire_date datetime not null default current_timestamp,
            job varchar(45) not null default ' ',
            salary decimal(8,2) default 0,
            commission_pct decimal(4,2) default 0,
            manager_id int default 0,
            department int default 0,
            primary key(id)
        )
    '''

    cur.execute(sql_create)

    sql_insert = '''insert into Employee(id, first_name, last_name, email, tel, hire_date,
                                         job, salary, commission_pct, manager_id, department)
                    values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''


    cur.executemany(sql_insert, rows)
    print("Affected Row Count is", cur.rowcount)