import mig_util as mu
oraconn = mu.get_oracle_conn()
myconn = mu.get_mysql_conn('doodb')
table = 'EMPLOYEES'
cols = "EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID"
rand_row_count = 0
with oraconn :
ora_cnt = mu.get_count(oraconn, table)
cur = oraconn.cursor()
sql = "SELECT * FROM (SELECT " + cols + " FROM " + table + " ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum <= 10"
cur.execute(sql)
ora_list = cur.fetchall()
rand_row_count = cur.rowcount
with myconn :
my_cnt = mu.get_count(myconn ,"Employees")
print("이관된 oracle 레코드수", ora_cnt, ", 이관된 mysql 레코드 수 =", my_cnt)
if ora_cnt != my_cnt:
print("레코드 수가 일치하지 않습니다! oracle=", ora_cnt, ", mysql =", my_cnt)
exit()
import mig_util as mu
connection = mu.get_oracle_conn()
myconn = mu.get_mysql_conn('dadb')
with connection:
# cursor를 만들어줍니다
cursor = connection.cursor()
sql = '''select region_id, region_name from Regions'''
# cursor.execute(sql, dept_id=30)
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
with myconn:
cur = myconn.cursor()
cur.execute("drop table if exists Regions")
sql_create = '''
create table Regions(
id smallint not null auto_increment primary key,
region_name varchar(36)
)
'''
cur.execute(sql_create)
import mig_util as mu
connection = mu.get_oracle_conn("hr", "hrpw", "localhost:1521/xe")
myconn = mu.get_mysql_conn('dadb')
with connection:
# cursor를 만들어줍니다
cursor = connection.cursor()
sql = '''select region_id, region_name from Regions'''
# cursor.execute(sql, dept_id=30)
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
print(row)
with myconn:
cur = myconn.cursor()
cur.execute("drop table if exists Regions")
sql_create = '''
create table Regions(
id smallint not null auto_increment primary key,
region_name varchar(36)
)
'''
cur.execute(sql_create)
import 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"
import mig_util as mu
conn_hr = mu.get_oracle_conn()
# onn_dadb = mu.get_mysql_conn('doodb')
rand_row_count = 0
# read from source db
with conn_hr:
cur = conn_hr.cursor()
oracle_inner_count = "select count(*) from Employees e inner join Departments d on e.department_id = d.id"
cur.execute(oracle_inner_count)
ora_inner_count = cur.fetchone()[0]
print(ora_inner_count)
# cur = conn_hr.cursor()
# sql = "select " + cols + " from " + table + " order by rand() limit %s"
# rand_row_count = round(dooo_cnt / 3)
# cur.execute(sql, (rand_row_count,))
# dooo_list = cur.fetchall()
# lst = []
# for i in dooo_list:
# l = list(i)
# l.append(i[3])
# lst.append(l)
# print("lst=", lst)
# with conn_dadb:
# da_cnt = mu.get_count(conn_dadb, table)