import pro_mig_util as mu
conn_oracle_hr = mu.get_oracle_conn("hr", "hrpw", "localhost:1521/xe")
conn_mysql_dadb = mu.get_mysql_conn("dadb")
myconn = mu.get_mysql_conn("dadb")
with conn_oracle_hr:
# cursor를 만들어줍니다
cur = conn_oracle_hr.cursor()
sql = '''select region_id, region_name from Regions'''
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
print(row)
with myconn:
cur = myconn.cursor()
cur.execute("drop table if exists Region")
sql_create = '''create table Region(region_id smallint not null primary key,
region_name varchar(30))
'''
cur.execute(sql_create)
sql_insert = '''insert into Region(region_id, region_name) values(%s, %s)'''
cur.executemany(sql_insert, rows)
print("Affectedrowcount is", cur.rowcount)
import pro_mig_util as mu
conn_betterdb = mu.get_mysql_conn('betterdb')
conn_dadb = mu.get_mysql_conn('dadb')
# read from source db
with conn_betterdb:
cur = conn_betterdb.cursor()
sql = "select id, name, prof, classroom from Subject"
cur.execute(sql)
rows = cur.fetchall()
# write to target db
with conn_dadb:
cur = conn_dadb.cursor()
# cur.execute('truncate table Subject')
trc = mu.trunc_table(conn_dadb, 'Subject')
print("tuncated>>", trc)
sql = '''insert into Subject(id, name, prof, classroom)
values(%s, %s, %s, %s)'''
cur.executemany(sql, rows)
print("AffectedRowCount is", cur.rowcount)
conn_dadb.commit()
import pro_mig_util as mu
conn_oracle_hr = mu.get_oracle_conn("hr", "hrpw", "localhost:1521/xe")
conn_mysql_betterdb = mu.get_mysql_conn("betterdb")
conn_creat_tables = mu.get_mysql_conn("betterdb")
with conn_creat_tables:
cur = conn_creat_tables.cursor()
cur.execute("call sp_drop_fk_refs('Job')")
cur.execute("drop table if exists Job")
sql_create_job = '''create table Job(job_id varchar(10) not null primary key,
job_name varchar(31) not null,
min_salary decimal(10,2) not null,
max_salary decimal(10,2) not null
)'''
cur.execute(sql_create_job)
cur.execute("call sp_drop_fk_refs('Job_history')")
cur.execute("drop table if exists Job_history")
sql_create_job_history = '''create table Job_history(employee_id smallint unsigned not null,
start_date date not null,
end_date date not null,
job_id varchar(10),
department_id smallint unsigned
)'''
cur.execute(sql_create_job_history)
cur.execute("call sp_drop_fk_refs('Department')")
cur.execute("drop table if exists Department")
sql_create_department = '''create table Department(department_id smallint unsigned not null primary key,
import pro_mig_util as mu
conn_mysql_betterdb = mu.get_mysql_conn('betterdb')
conn_oracle_hr = mu.get_oracle_conn("hr","hrpw", "localhost:1521/xe")
table_department = 'Department'
table_departments = 'Departments'
table_employee = 'Employee'
table_employees = 'Employees'
table_job = 'Job'
table_jobs = 'Jobs'
table_job_history = 'Job_history'
cols_department = "department_id, department_name, manager_id, employee_cnt"
cols_departments = "department_id, department_name, manager_id, emp_cnt"
cols_employee = "employee_id, first_name, last_name, email, tel, hire_date, job_id, salary, commission_pct, manager_id, department_id"
cols_employees = "employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id"
cols_job = "job_id, job_name, min_salary, max_salary"
cols_jobs = "job_id, job_title, min_salary, max_salary"
cols_job_history = "employee_id, start_date, end_date, job_id, department_id"
cols_job_historys = "employee_id, start_date, end_date, job_id, department_id"
rand_row_count = 0
# read from source db
with conn_oracle_hr:
oracle_departments_cnt = mu.get_count(conn_oracle_hr, table_departments)
oracle_employees_cnt = mu.get_count(conn_oracle_hr, table_employees)
oracle_jobs_cnt = mu.get_count(conn_oracle_hr, table_jobs)
oracle_job_history_cnt = mu.get_count(conn_oracle_hr, table_job_history)
cur = conn_oracle_hr.cursor()
sql = "select * from (select " + cols_departments + " from " + table_departments + " order by dbms_random.random) where rownum <= :1"