0. Todo
- oracledb module 사용해 Oracle DB 접속하기
- 테이블 생성 및 삭제
- 데이터 CRUD
1. Oracle DB 접속하기
Python에서 Oracle DB에 접속하기 위해서는 oracledb 모듈을 사용해야 한다.
import oracledb
connection = oracledb.connect(
user="ADMIN",
password="ADMIN password", # DB 생성 시 입력한 비밀번호
dsn="TNS NAME", # Database connection -> Connection Strings -> TNS NAME
config_dir="wallet path", # wallet 디렉토리 경로
wallet_location="wallet path", # wallet 디렉토리 경로
wallet_password="wallet password") # wallet 다운로드 시 설정한 비밀번호
print("Successfully connected to Oracle Database")
2. 테이블 생성 및 삭제
테이블 생성
def create_table(table_name):
with connection.cursor() as cursor:
cursor.execute(
"create table " + table_name + " (" +
"""id number generated always as identity,
title varchar2(100),
category varchar2(100),
generated number(1,0),
published number(1,0),
summary varchar2(4000),
content varchar2(4000),
hashtag varchar2(300),
primary key (id, title))""")
테이블 삭제
def drop_table(table_name):
with connection.cursor() as cursor:
cursor.execute("""
begin
execute immediate 'drop table """ + table_name + "';" +
"""exception when others then if sqlcode <> -942 then raise; end if;
end;""")
3. 데이터 CRUD
Create (insert)
def insert_data(table_name, title, category):
with connection.cursor() as cursor:
cursor.execute(
"insert into " + table_name + "(TITLE, CATEGORY, GENERATED, PUBLISHED) values (:1, :2, 0, 0, 0)",
(title, category))
connection.commit()
Read (select)
def get_ungenerated_titles(table_name):
with connection.cursor() as cursor:
cursor.execute("SELECT id, title, category FROM " + table_name + " WHERE GENERATED = 0")
titles = cursor.fetchall()
return titles
Update
def update_article(table_name, title_id, summary, content, hashtag):
with connection.cursor() as cursor:
cursor.execute("UPDATE " + table_name + """
SET summary = :1, content = :2, hashtag = :3, generated = 1
WHERE id = :4
""", (summary, content, hashtag, title_id))
connection.commit()
Delete
def delete_data(table_name, title_id):
with connection.cursor() as cursor:
cursor.execute("DELETE " + table_name + """
WHERE id = :1
""", (title_id,))
connection.commit()
OpenAI(ChatGPT) API로 블로그 발행하기
반응형
