[SQL 기초] Oracle DB 파이썬에 연결하기

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

---

2025-04-20 추가) 파이썬에서 Oracle을 연결하기 위해서는 추가 작업 필요합니다. Oracle Instant Client 라는 것이 필요한데

https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html

Instant Client for macOS (Intel x86)

위의 오라클 페이지에 들어가서 (구글 검색 Instant Client 라고 해도 됩니다) 아래와 같은 사용하려는 툴과 관련된 파일을 다운로드 받습니다. 적절한 경로(예: instantclient_23_7폴더)에 파일들을 넣고

주의) 윈도우에 따라서 32bit 64bit를 선택하는것이 아니라 사용하려는 툴에 맞게 설치해야합니다. (DPI-1047 에러 발생가능)

instantclient-basic-macos.x64-19.8.0.0.0dbru.zip

instantclient-sdk-macos.x64-19.8.0.0.0dbru.zip

instantclient-sqlplus-macos.x64-19.8.0.0.0dbru.zip

 

환경변수 설정을 해주어야 합니다. 아래 블로그에서 잘 설명해주셨네요.

1. ORACLE_HOME 변수의 '변수 값'을 방금전에 말씀드린 instantclient 압축해제 한 폴더로 설정해줍니다. 

 

2.이렇게 추가된 ORACLE_HOME 변수를 Path에 설정해줍니다. 

 

3. instantclient 압축 해제한 폴더 아래 network 폴더를 생성해주고 다시 그 아래 admin 폴더를 생성합니다. 그리고 admin 안에 tnsnames.ora 파일을 생성(텍스트 편집)해줍니다. 아래는 로컬 호스트를 사용하는 예시입니다. 

ORCLDB =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
		(CONNECT_DATA =
			(SERVER = DEDICATED)
			(SERVICE_NAME = xe)
		)
	)

 

그리고 instantclient 압축 해제한 폴더 아래 network/admin 폴더를 TNS_ADMIN이라는 환경변수로 설정해줍니다. 

 

4. Visual Studio Redistributable 가 설치되지 않았다면 설치해줍니다. 

 

https://leewoogi.tistory.com/305

[Window] 윈도우 11에서 Oracle Instant Client 다운로드 및 설정

 

 

이렇게 기본적인 작업을 했는데도 저는 에러가 계속 발생했습니다. 해결과정을 하나씩 따라가 보겠습니다. 

oracledb.exceptions.DatabaseError: ORA-12541: TNS:no listener 에러 

: Oracle 클라이언트가 데이터베이스에 연결을 시도했는데, 해당 주소/포트에서 리스너(listener)가 응답하지 않아서 발생하는 에러

Checklist) 명령프롬프트(관리자 권한 실행)에서 lsnrctl status 로 리스너가 실행중인지 확인하고 만약 실행중이 아니라면 lsnrctl start로 리스너를 시작해줍니다. 정상적으로 실행중이라면 아래와 같이 나옵니다. 

 

---

이번 포스팅에서는 Oracle DB의 데이터를 파이썬으로 불러오거나 파이썬의 데이터를 Oracle DB로 저장하는 방법을 정리해보겠습니다. 

python에서 Oracle DB에 연결하는 2가지 방식을 소개하려고 합니다. 

1. oracledb 패키지의 기본 커넥션 개체와 cursor를 통해서 작업 수행

• Oracle DB 연결

import oracledb

# Oracle Client 경로 설정 (필요한 경우)
oracledb.init_oracle_client(lib_dir=r"D:\app\trill\product\18.0.0\instantclient_21_13")

# DB 연결
connection = oracledb.connect(
    user="system",
    password="1234",          # DB 생성 시 설정한 비밀번호
    dsn="127.0.0.1:1521/XE"   # TNS 또는 makedsn
)
print("Successfully connected to Oracle Database")

• 쿼리 실행 : oracledb.connect() 방식으로 쿼리를 실행하려면 cursor 객체를 생성한 후, execute 메서드를 사용해 쿼리를 실행할 수 있습니다.

cursor = connection.cursor()

# 예제 데이터 삽입
insert_sql = """
    INSERT INTO C##FINANCE.KOR_TICKER (ticker, price)
    VALUES (:1, :2)
"""
data = [("005930.KS", 54000), ("000660.KS", 85000)]  # 튜플 리스트 형식으로 데이터 준비

# 여러 데이터 삽입
cursor.executemany(insert_sql, data)
connection.commit()  # 트랜잭션 커밋
print("Data inserted successfully")

• 데이터 조회 : 조회된 데이터를 출력하거나 pandas DataFrame으로 변환하여 사용하려면 fetchall() 메서드를 활용합니다.

select_sql = "SELECT * FROM C##FINANCE.KOR_TICKER"
cursor.execute(select_sql)

# 조회 결과 출력
for row in cursor.fetchall():
    print(row)

 

조회 결과를 pandas DataFrame에 바로 로드할 수도 있습니다.

import pandas as pd

# 쿼리 실행 및 DataFrame 변환
cursor.execute(select_sql)
columns = [col[0] for col in cursor.description]  # 컬럼 이름 추출
data = cursor.fetchall()
df = pd.DataFrame(data, columns=columns)
print(df)

• 테이블 생성

create_table_sql = """
CREATE TABLE C##FINANCE.KOR_TICKER (
    ticker VARCHAR2(20),
    price NUMBER
)
"""
cursor.execute(create_table_sql)
print("Table created successfully")

• Oracle table에 pandas DataFrame 저장

kor_ticker = pd.DataFrame({
    'ticker': ['005930.KS', '000660.KS'],
    'price': [54000, 85000]
})

# DataFrame 데이터를 튜플 형식으로 변환
data = [tuple(x) for x in kor_ticker.to_numpy()]

# SQL 쿼리 준비
insert_sql = "INSERT INTO C##FINANCE.KOR_TICKER (ticker, price) VALUES (:1, :2)"
cursor.executemany(insert_sql, data)
connection.commit()
print("DataFrame data inserted successfully")

 

작업이 끝나면 반드시 커넥션과 커서를 종료합니다. 

cursor.close()
connection.close()
print("Connection closed")

 

이런 방식이 제가 처음에 사용했던 방식인데, 조금 번거롭더라구요.. 그래서 나중에는 두번째 방식을 더 자주 사용하고 있습니다. 

 

2. SQLAlchemy를 통한 Oracle DB 연결
(SQLAlchemy와 cx_Oracle 또는 oracledb를 함께 사용)

• Oracle DB 연결

from sqlalchemy import create_engine

user = 'system'
password = '1234'
dsn = "127.0.0.1:1521/XE"
engine = create_engine(f'oracle+oracledb://{user}:{password}@{dsn}')

• 데이터 조회

from sqlalchemy import text

# 쿼리 실행
with engine.connect() as con:
    result = con.execute(text("SELECT * FROM C##FINANCE.KOR_TICKER"))
    for row in result:
        print(row)

 

pandas DataFrame으로 바로 불러오려면 다음같이 해주면 됩니다. 

conn = engine.connect()
df = pd.read_sql_query(text("SELECT count(*) FROM \"C##FINANCE\".FMP_US_BALANCESHEET WHERE SYMBOL='AMAT' AND TDATE='2024-04-28'"), conn)
conn.close()

 

• Oracle table에 Pandas DataFrame 저장

import pandas as pd
import sqlalchemy as sqla

kor_ticker = pd.DataFrame({'ticker': ['005930.KS', '000660.KS'], 'price': [54000, 85000]})  # 예시 데이터프레임

dtyp = {}
if engine.connect().dialect.name == 'oracle':
    for column in kor_ticker.columns:
        if kor_ticker[column].dtype == 'object':
            dtyp[column] = sqla.types.VARCHAR(kor_ticker[column].astype(str).str.len().max())
        elif kor_ticker[column].dtype in ['float', 'float64']:
            dtyp[column] = sqla.types.FLOAT

kor_ticker.to_sql('KOR_TICKER', engine, dtype=dtyp, schema='C##FINANCE', if_exists='replace', index=False)

 

아무래도 좀 더 쉬워지지 않았나요?