SQL 다루기

mssql database에서 data 불러오기

import pymssql

conn = pymssql.conncet(server='', user='아이디', password='패스워드', database='데이터베이스')
cursor = conn.cursor()
query = "SELECT * FROM DATABASE"
data = pd.read_sql(sql=query, con=conn)
conn.close()
df = pd.DataFrame(data)

database UPDATE or INSERT

conn = pymssql.conncet(server='', user='아이디', password='패스워드', database='데이터베이스')
cursor = conn.cursor()

try:
	for record in result_tuples:
    	cursor.execute("""SELECT * FROM tabel1 
        WHERE CODE=%s AND TIME_STAMPE=%s""", (record[0], record[1])
        existing_record = cursor.fetchone()
        if existing_record:
        	cursor.execute("""UPDATE table1 
            SET PRED=%s, TXN_TIME=CURRENT_TIMESTAMP
            WHERE CODE=%s AND TIME_STAMP=%s
            """, (record[2], record[0], record[1]))
            conn.commit()
        else:
        	cursor.execute("""INSERT INTO table1 
            (CODE, TIME_STAMP, PRED, TXN_TIME)
            VALUES (%s, %s, %s, CURRENT_TIMESTAMP)
            """, record)
            conn.commti()
    print("Inserting Result: Success")
    output_param=0
except Exception as e:
	print(f"Fail : {e}")
    output_param =1

conn.close()

• update or insert column이 많을 경우

set_clause = ', '.join([f"{col}=%s" for col in df.columns]) + ' , TXN_TIME = CURRENT_TIMESTAMP'
query_update =f"UPDATE {table} SET {set_clause} WHERE ~ "

col_str = str(df.columns.tolist()).replace("[",'').replace("]", '').replace("'", '') + ', TXN_TIME'
query_insert = f"INSERT INTO {table} ({col_str}) VALUES (%s, %s ~~~, CURRENT_TIMESTAMP)"