import pandas as pd
from pandas import Series,DataFrame
emp = pd.read_csv("C:\\data\\emp.csv")
emp.info()
emp.HIRE_DATE = pd.to_datetime(emp.HIRE_DATE) # 문자형 >> 날짜형
import matplotlib.pyplot as plt
from matplotlib import font_manager, rc
font_name = font_manager.FontProperties(fname='C:\\Windows\\Fonts\\malgun.ttf').get_name()
rc('font',family = font_name)
import numpy as np
import sqlite3
import cx_Oracle
from sqlalchemy.engine import create_engine
import re
| [문제] 근무일수가 가장 많은 10위까지 직원들의 employee_id, last_name, department_name, 근무일수를 출력해주세요. 단, sqlite3 이용하세요. |
| import sqlite3 conn = sqlite3.connect('C:/data/insa.db') # DB 접속 c = conn.cursor() # cursor 생성 c.execute('select * from sqlite_master') # 테이블 목록 확인 c.fetchall() c.execute(""" select e.*, d.department_name from(select * from (select *, dense_rank() over(order by working_day desc) rank from(select employee_id, last_name, department_id, (strftime("%s","now") - strftime("%s",hire_date))/86400 working_day from emp)) where rank <= 10) e, dept d where e.department_id = d.department_id order by e.rank """) c.fetchall() |
| c.execute(""" with emp_working as ( select employee_id, last_name, department_id, (strftime("%s","now")-strftime("%s",hire_date))/86400 working_day from emp), emp_rank as (select employee_id, last_name, department_id, working_day, dense_rank() over(order by working_day desc) rank from emp_working), top_10 as (select * from emp_rank where rank <= 10) select t.employee_id, t.last_name, d.department_name, t.working_day, t.rank from top_10 t, dept d where t.department_id = d.department_id order by 5 """) c.fetchall() |
| [문제] 근무일수가 가장 많은 10위까지 직원들의 employee_id, last_name, department_name, 근무일수를 출력해주세요. 단, pandas 이용하세요. |
| import pandas as pd from pandas import Series,DataFrame emp = pd.read_csv("C:\\data\\emp.csv") emp.info() dept = pd.read_csv("C:\data\dept.csv") dept.info() x = DataFrame({'사번':emp.EMPLOYEE_ID, '이름':emp.LAST_NAME, '부서코드':emp.DEPARTMENT_ID, '근무일수':(pd.Timestamp.now()-pd.to_datetime(emp.HIRE_DATE)).dt.days}) x x['순위'] = x['근무일수'].rank(ascending=False, method='dense').astype('int') x top_10 = x[x['순위'] <= 10] top_10 result = pd.merge(top_10,dept,left_on='부서코드',right_on='DEPARTMENT_ID') result = result[['사번','이름','DEPARTMENT_NAME','근무일수','순위']].sort_values(by='순위') result |
c.execute("create table insa(id integer, name char, sal integer)")
c.execute("insert into insa(id, name, sal) values(1, 'james', 1000)")
c.execute("select * from insa")
c.fetchall()
# 변수값을 통한 insert
| v_id = 2 v_name = '홍길동' v_sal = 2000 c.execute("insert into insa(id, name, sal) values(?, ?, ?)", (v_id, v_name, v_sal)) c.execute("select * from insa") c.fetchall() conn.commit() |
| c.execute("select * from insa where id = ?", (v_id,)) # 튜플형식에 맞춰 1개 값일 때 끝에 , 넣기 c.fetchall() |
# 변수값을 통한 update
| v_id = 2 v_name = '박찬호' c.execute("update insa set name = ? where id = ?", (v_name, v_id)) c.execute("select * from insa") c.fetchall() |
# 변수값을 통한 delete
| c.execute("delete from insa where id = ?", (v_id,)) c.execute("select * from insa") c.fetchall() conn.rollback() c.close() conn.close() |
■ 파이썬에서 오라클 접속
| # 설치 Anaconda Prompt > conda install -c anaconda cx_oracle ... Proceed ([y]/n)? y |
import cx_Oracle # 대소문자 주의
| # Oracle 리스너 상태를 확인 CMD > lsnrctl status ... Default Service XE (HOST=DESKTOP-60J2T7A)(PORT=1521) |
# 오라클 접속
| conn = cx_Oracle.connect('hr', 'hr', 'DESKTOP-60J2T7A:1521/xe', encoding='UTF-8') # 접속계정, 비번, PC명 : 포트 / 서비스명 cursor = conn.cursor() |
# 칼럼명 업데이트
| cursor.execute('select * from employees') data = cursor.fetchall() df = DataFrame(data) df # 컬럼정보가 없다. cursor.execute(""" SELECT column_name FROM user_tab_columns WHERE table_name = 'EMPLOYEES' """) col = cursor.fetchall() col # list[ tuple() ] df.columns = [j for i in col for j in i] df |
| for i in col: print(i[0]) |
| [i[0] for i in col] |
| [j for i in col for j in i] |
# 테이블 생성
| cursor.execute("""create table insa(id number, name varchar2(30), day date)""") < SQL > 이름 널? 유형 ---- -- ------------ ID NUMBER NAME VARCHAR2(30) DAY DATE |
# INSERT
| cursor.execute("""insert into insa(id, name, day) values(1, '홍길동', sysdate)""") cursor.execute('commit') cursor.execute('select * from insa') cursor.fetchall() |
| v_id = 2 v_name = '박찬호' v_day = pd.Timestamp.now().date() cursor.execute("""insert into insa(id,name,day) values(:b_id, :b_name, :b_day)""", (v_id, v_name, v_day)) # 바인드변수로 표시 cursor.execute('commit') cursor.execute('select * from insa') cursor.fetchall() |
| sql = """insert into insa(id, name, day) values(:b_id, :b_name, :b_day)""" cursor.execute(sql, [3, '손흥민', pd.Timestamp.now()]) cursor.execute('commit') cursor.execute('select * from insa') cursor.fetchall() cursor.execute(sql, b_id=4, b_name='나얼', b_day=pd.Timestamp.now()) cursor.execute('commit') cursor.execute('select * from insa') cursor.fetchall() |
| sql = """insert into insa(id, name, day) values(:b_id, :b_name, :b_day)""" data = dict(b_id=5, b_name='윤건', b_day=pd.Timestamp.now()) cursor.execute(sql, data) cursor.execute('commit') cursor.execute('select * from insa') cursor.fetchall() |
SQL문에 변수 처리하는 이유 ?
실행계획을 공유하기 위한 목적(CPU, 메모리 사용을 줄일 수 있다.)
| cursor.execute('select * from insa where id=1') cursor.fetchall() cursor.execute('select * from insa where id=2') cursor.fetchall() |
| ... ↓ |
| cursor.execute('select * from insa where id = :b_id', (1,)) cursor.fetchall() cursor.execute('select * from insa where id = :b_id', [1]) cursor.fetchall() |
| ... ↓ |
| cursor.execute('select * from insa where id = :b_id', (v_id,)) cursor.fetchall() cursor.execute('select * from insa where id = :b_id', [v_id]) cursor.fetchall() cursor.execute('select * from insa where id = :b_id', b_id = v_id) cursor.fetchall() |
# UPDATE
| cursor.execute("""update insa set name = '제임스' where id = 2""") cursor.execute('select * from insa') cursor.fetchall() cursor.execute('rollback') cursor.execute('select * from insa') cursor.fetchall() |
| v_id = 2 v_name = '제임스' cursor.execute("""update insa set name = :b_name where id = :b_id """, b_name = v_name, b_id = v_id) cursor.execute('select * from insa') cursor.fetchall() cursor.execute('rollback') cursor.execute('select * from insa') cursor.fetchall() |
# DELETE
| v_id = 2 cursor.execute("""delete from insa where id = :b_id""", b_id = v_id) cursor.execute('select * from insa') cursor.fetchall() cursor.execute('rollback') cursor.execute('select * from insa') cursor.fetchall() |
<< Pandas DataFrame -> Oracle Table 이관 작업 >>
| from sqlalchemy.engine import create_engine db_sw = 'oracle' sql_driver = 'cx_oracle' username = 'hr' password = 'hr' host = 'DESKTOP-60J2T7A' port = 1521 service = 'xe' path = db_sw+'+'+sql_driver+'://'+username+':'+password+'@'+host+':'+str(port)+'/?service_name='+service path # 'oracle+cx_oracle://hr:hr@DESKTOP-60J2T7A:1521/?service_name=xe' engine = create_engine(path) emp.to_sql('emp_new', engine, schema='hr', index=False) |
< SQL >
| desc emp_new | 이름 널? 유형 -------------- -- ---------- EMPLOYEE_ID NUMBER(19) FIRST_NAME CLOB LAST_NAME CLOB EMAIL CLOB PHONE_NUMBER CLOB HIRE_DATE CLOB JOB_ID CLOB SALARY NUMBER(19) COMMISSION_PCT FLOAT(126) MANAGER_ID FLOAT(126) DEPARTMENT_ID FLOAT(126) |
| alter table emp_new modify commission_pct number; alter table emp_new modify manager_id number; alter table emp_new modify department_id number; desc emp_new |
이름 널? 유형 -------------- -- ---------- EMPLOYEE_ID NUMBER(19) FIRST_NAME CLOB LAST_NAME CLOB EMAIL CLOB PHONE_NUMBER CLOB HIRE_DATE CLOB JOB_ID CLOB SALARY NUMBER(19) COMMISSION_PCT NUMBER MANAGER_ID NUMBER DEPARTMENT_ID NUMBER |
| alter table emp_new modify first_name varchar2(30); > 오류 : CLOB타입은 문자형(varchar2(4000),char(2000))으로 변환 불가 -> 칼럼추가해서 이관해라. - CLOB : 문자형. 1개 필드 안에 4GB 저장 가능 |
|
| 1. 새로운 컬럼을 생성 alter table emp_new add new_column varchar2(30); desc emp_new 3. 새로운 칼럼(varchar2)에 clob의 컬럼의 정보를 대입 update emp_new set new_column = dbms_lob.substr(first_name, 4000, 1); commit; select * from emp_new; 3. 기존 clob 컬럼 삭제 alter table emp_new drop column first_name; desc emp_new 4. 새로운 컬럼 이름을 기존 컬럼 이름으로 수정 alter table emp_new rename column new_column to first_name; |
|
| desc emp_new | 이름 널? 유형 -------------- -- ------------ EMPLOYEE_ID NUMBER(19) LAST_NAME CLOB EMAIL CLOB PHONE_NUMBER CLOB HIRE_DATE CLOB JOB_ID CLOB SALARY NUMBER(19) COMMISSION_PCT NUMBER MANAGER_ID NUMBER DEPARTMENT_ID NUMBER FIRST_NAME VARCHAR2(30) |
■ 정규표현식(Regular Expression)
특정 패턴과 일치하는 문자열 검색, 치환, 제거하는 기능을 제공한다.
■ 메타문자(meta charachers)
원래 문자가 가진 뜻이 아닌 특별한 용도로 사용하는 문자
| [A-Za-z] | [ ] 사이의 문자들과 매치 |
| [^] | not을 의미 |
| [sql] | s 또는 q 또는 l |
| [가-힣ㄱ-ㅎㅏ-ㅣ] | 한글 자모음 |
| \w 또는 [a-zA-Z가-힣0-9] | 문자, 숫자 패턴 |
| \W 또는 [^a-zA-Z가-힣0-9] | 문자, 숫자가 아닌 패턴 |
| \s 또는 [ \t\n\r\f\v] | 공백문자 |
| \S 또는 [^ \t\n\r\f\v] | 공백문자가 아닌 패턴 |
| \d 또는 [0-9] | 숫자 패턴 |
| \D 또는 [^0-9] | 숫자가 아닌 패턴 |
| a.b | .(점) 위치에 모든 문자를 의미 (줄바꿈 \n 제외) |
| a\.b | .(점)을 문자로 인식 |
| a[.]b | .(점)을 문자로 인식 |
| a|b | a 또는 b |
| ^ | 시작 |
| $ | 종료 |
| a*b | * 바로 앞의 문자가 0번 이상을 찾는다. a가 있을 수도 있고 없을 수도 있다. b, ab, aaab |
| a+b | + 바로 앞의 문자가 1번 이상을 찾는다. a가 무조건 있어야 한다. ab, aab |
| ab?c | ? 바로 앞의 문자가 0번, 1번을 찾는다. b가 없거나 1번 있다. ac, abc |
| a{2}b | {n} 바로 앞의 문자가 n번 반복을 찾는다. aab |
| a{2,3}b | {n,m} 바로 앞의 문자가 n번 또는 m번 반복을 찾는다. aab, aaab |
| a{2,}b | {n} 바로ㅇ 앞의 문자가 n번 이상 반복을 찾는다. aab, aaab |
import re
re.match : 문자열의 '처음'부터 정규식과 매칭되는지를 찾는 함수.
| re.match('\w', '100') bool(re.match('\w', '100')) |
<re.Match object; span=(0, 1), match='1'> True |
| re.match('\w', '_') bool(re.match('\w', '_')) |
<re.Match object; span=(0, 1), match='_'> True |
| re.match('\w', '-python') bool(re.match('\w', '-python')) |
False |
| re.match('\W', '-python') bool(re.match('\W', '-python')) |
<re.Match object; span=(0, 1), match='-'> True |
| bool(re.match('[-]', '-python')) bool(re.match('[-]', 'py-thon')) |
True False |
| bool(re.match('c*a','ccat')) | True |
| bool(re.match('c+a','ccat')) bool(re.match('c+a','at')) |
True False |
| bool(re.match('c?a','cat')) bool(re.match('c?a','at')) |
True True |
| bool(re.match('c{2,3}a','cccat') bool(re.match('c{2,}a','cccccat')) |
True True |
| bool(re.match('c|a','ct')) bool(re.match('c|a','at')) bool(re.match('c|a','bat')) |
True True False |
| bool(re.match('[0-9]th','21th')) bool(re.match('[0-9][0-9]th','21th')) bool(re.match('[0-9]{2}th','21th')) bool(re.match('[0-9]*th','21th')) bool(re.match('[0-9]+th','21th')) bool(re.match('[0-9]?th','21th')) bool(re.match('\d\dth','21th')) bool(re.match('\d{2}th','21th')) |
False True # 첫번째 자리, 두번째 자리 숫자패턴 True True True False True True |
| re.match('Or','Oracle engineer') re.match('or','Oracle engineer') re.match('or','Oracle engineer', re.I) |
True # Or 로 시작? False True # re.I 대소문자 무시 |
| m = re.match('Oracle','Oracle engineer') | <re.Match object; span=(0, 6), match='Oracle'> |
| m.group() m.span() m.start() m.end() 'Oracle enginner'[m.start():m.end()] |
'Oracle' # 매치된 문자열 리턴 (0, 6) # 매치된 문자열의 시작, 끝-1 0 # 매치된 문자열 시작위치 6 # 매치된 문자열 끝 위치 'Oracle' |
re.search : 해당하는 문자열에서 어디서든지 찾는다. 먼저나오는 1개
| re.match('engineer', 'Oracle engineer') | False |
| re.search('engineer', 'Oracle engineer') | <re.Match object; span=(7, 15), match='engineer'> |
| m = re.search('engineer','Oracle engineer') m.group() m.span() m.start() m.end() |
<re.Match object; span=(7, 15), match='engineer'> 'engineer' (7, 15) 7 15 |
re.findall : 일치하는 문자열 전부를 리스트로 반환한다.
| re.search('engineer', 'Oracle engineer Oracle engineer') | <re.Match object; span=(7, 15), match='engineer'> |
| re.findall('engineer', 'Oracle engineer Oracle engineer') | ['engineer', 'engineer'] |
| [문제] data 변수에서 숫자 패턴을 찾아주세요. data = '오늘은 2023년 11월 28일 입니다.' |
|
| re.findall('[0-9]+', data) re.findall('\d+', data) |
['2023', '11', '28'] |
| [문제] data 변수에서 문자 패턴을 찾아주세요. data = '오늘은 2023년 11월 28일 입니다.' |
|
| re.findall('\D+', data) re.findall('[^0-9]+', data) # 숫자가 아닌 re.findall('[가-힣]+', data) |
['오늘은 ', '년 ', '월 ', '일 입니다.'] ['오늘은 ', '년 ', '월 ', '일 입니다.'] ['오늘은', '년', '월', '일', '입니다'] |
re.sub : 패턴을 치환하는 함수
| source = 'oracle engin' | |
| source.replace('engin','engineer') | 'oracle engineer' |
| re.sub('engin', 'engineer', source) re.sub(기존문자열, 변경문자열, 데이터) |
|
re.split : 패턴을 이용해서 문자열 분리
| 'python.programming'.split('.') | ['python', 'programming'] |
| re.split('\.', 'python.programming') | |
| re.split('[.]', 'python.programming') | |
| re.split('[.:]', 'python.programming:PLSQL') | ['python', 'programming', 'PLSQL'] |
| re.split('[.: ]', 'python.programming:PLSQL SQL') re.split('[.:\s]', 'python.programming:PLSQL SQL') |
['python', 'programming', 'PLSQL', 'SQL'] |
'PYTHON 3.11' 카테고리의 다른 글
| 35 PYTHON 예외사항, SQLite (1) | 2023.11.27 |
|---|---|
| 34 PYTHON CLASS, 상속 (1) | 2023.11.23 |
| 33 PYTHON 그래프, CLASS (1) | 2023.11.22 |
| 32 PYTHON RANK, PANDAS, UNSTACK, PIVOT, MATPLOTLIB (3) | 2023.11.21 |
| 31 PYTHON isin, null, apply, str, 그룹함수, MERGE (1) | 2023.11.20 |