PANDAS 와 SQL 쿼리

DEVCAFE NOTE

Python

KEY POINTS · 핵심 정리

작성일2024년 10월 14일

pandas 이용하여 sql 쿼리 조회

import cx_Oracle
import pandas as pd
import json

# Function to load the configuration file
def load_db_config(file_path):
    with open(file_path, 'r') as f:
        config = json.load(f)
    return config

# Load the database configuration from the JSON file
db_config = load_db_config('db_config.json')
dsn_tns = cx_Oracle.makedsn(db_config['host'], db_config['port'], service_name=db_config['service_name'])
connection = cx_Oracle.connect(user=db_config['user'], password=db_config['password'], dsn=dsn_tns)


# Function to execute a SQL query and return the results as a DataFrame
def query_to_dataframe(query, connection):
    return pd.read_sql_query(query, connection)

# 1. Fetch initial data and save it as a DataFrame
initial_query = "SELECT OWNER || '.' || TABLE_NAME AS full_name FROM ALL_TABLES WHERE OWNER = 'DBCAFE'"
initial_df = query_to_dataframe(initial_query, connection)

# Display the initial DataFrame
print("Initial DataFrame:")
print(initial_df)

# 2. Extract information from the DataFrame for the new SQL query
# Assuming we want to use the full_name column from the initial DataFrame in the new query
full_names = initial_df['FULL_NAME'].tolist()
# Format each name with single quotes and join with commas
formatted_names = ", ".join(f"'{name}'" for name in full_names)

# 3. Construct and execute a new query using the extracted information
new_query = f"""
    SELECT * 
    FROM ALL_TABLES 
    WHERE (OWNER || '.' || TABLE_NAME) IN ({formatted_names})
"""

# Fetch new data based on the extracted information
new_df = query_to_dataframe(new_query, connection)

# Display the new DataFrame
print("\nNew DataFrame:")
print(new_df)

# Close the connection
connection.close()