개발노트: PANDAS 와 SQL 쿼리 편집

=== pandas 이용하여 sql 쿼리 조회 === <source lang=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() </source> [[category:python]] [[category:pandas]]