오지's blog

distinct와 order by를 함께 쓰려고 할때

SELECT REG_DATE FROM ( SELECT DISTINCT(REPLACE(SUBSTR(CNSL_REG_TME, 1, 10), '-', '')) AS REG_DATE FROM GCWB_WDB.ODS.O_CS_CNSL_HST ) ORDER BY REG_DATE DESC; 하나씩 풀어간다
개발노트/데이터베이스 2023. 6. 28. 16:22
쿼리 수행시 ValueError: unsupported format character 'I' (0x49) at index 69 에러 해결방안

result = engine.execute(f"SELECT TABLE_NAME FROM information_schema.TABLES WHERE ENGINE LIKE '%InnoDB%' AND TABLE_TYPE='BASE TABLE';").fetchone() ValueError: unsupported format character 'I' (0x49) at index 69 에러 발생 % 대신 %%으로 수정하여 해결 result = engine.execute(f"SELECT TABLE_NAME FROM information_schema.TABLES WHERE ENGINE LIKE '%%InnoDB%%' AND TABLE_TYPE='BASE TABLE';").fetchone()
개발노트/Python 2023. 4. 25. 15:08
AttributeError: 'OptionEngine' object has no attribute 'execute' 에러 해결 방안

df = pd.read_sql_query(sql = sql, con = mysql_conn) AttributeError: 'OptionEngine' object has no attribute 'execute' 다음과 같이 변경 필요 df = pd.read_sql_query(sql = text(sql), con = mysql_conn.connect()) reference. https://levelup.gitconnected.com/how-to-fix-attributeerror-optionengine-object-has-no-attribute-execute-in-pandas-eb635fbb89e4 How to Fix AttributeError: ‘OptionEngine’ object has no attrib..
개발노트/데이터베이스 2023. 4. 19. 16:55
airflow에서 select값을 가져오는 방법

sf_hook = SnowflakeHook(snowflake_conn_id="SNOWFLAKE_CONN_ID") sql = f"SELECT DISTINCT(REPLACE(SUBSTR({date_column_name}, 1, 10),'-', '')) FROM {table_name} WHERE SUBSTR({date_column_name}, 1, 10)='{yesterday.strftime(date_string)}';" conn = sf_hook.get_conn() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() 참고 https://stackoverflow.com/questions/53565834/fetch-results-from-..
개발노트/데이터베이스 2023. 4. 18. 10:52
s3에서 가장 최근 업로드한 파일 찾기 - sorted 연습 python

s3의 특정버킷내 stackoverflow를 보면서 아름다운 코드를 발견하여 이에 기록하게 되었다. https://stackoverflow.com/questions/45375999/how-to-download-the-latest-file-of-an-s3-bucket-using-boto3 How to download the latest file of an S3 bucket using Boto3? The other questions I could find were refering to an older version of Boto. I would like to download the latest file of an S3 bucket. In the documentation I found that there is..
개발노트/Python 2023. 4. 13. 15:31
Prev 1 ··· 3 4 5 6 7 8 9 ··· 29 Next