[Python] MSSQL 연동 (feat. csv파일 mssql에 저장하기 - 2)

"기상자료개방포털"의 기온데이터 csv파일을 mssql에 저장하는 부분을 해보겠습니다.

 

MSSQL 테이블 만들기

 - 아래와 같은 형식의 테이블을 만들어 줍니다.

 - 테이블 생성 쿼리

USE [DZ]
GO

/****** Object:  Table [dbo].[tblTemp]    Script Date: 2023-05-29 오후 2:07:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTemp](
	[Date] [date] NOT NULL,
	[Location] [nchar](10) NOT NULL,
	[AverageTemp] [numeric](18, 2) NULL,
	[LowTemp] [numeric](18, 2) NULL,
	[HighTemp] [numeric](18, 2) NULL,
 CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[Location] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

csv 파일 MSSQL 테이블에 저장하기

tblTemp 저장 프로시저 만들기

 - 날짜(Date), 지역(Location) 을 키 값으로 없으면 INSERT, 있으면 UPDATE 프로시저

USE [DZ]
GO
/****** Object:  StoredProcedure [dbo].[PRC_TEST1]    Script Date: 2023-05-30 오후 6:57:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<dz>
-- Create date: <2023.05.30>
-- Description:	<Description,,>
-- =============================================
alter PROCEDURE [dbo].[tblTemp_IU] 
	@P_Date AS NVARCHAR(20),
	@P_Location AS NVARCHAR(20),
	@P_AverageTemp AS NVARCHAR(20),
	@P_LowTemp AS NVARCHAR(20),
	@P_HighTemp AS NVARCHAR(20)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @V_Date AS date,
			@V_AverageTemp AS NUMERIC(18, 2),
			@V_LowTemp AS NUMERIC(18, 2),
			@V_HighTemp AS NUMERIC(18, 2)

	set @V_Date = convert(Date, @P_Date)
	SET @V_AverageTemp = IIF(REPLACE(@P_AverageTemp, ' ', '') = '', null, convert(float, @P_AverageTemp))
	SET @V_LowTemp = IIF(REPLACE(@P_LowTemp, ' ', '') = '', null, convert(float, @P_LowTemp))
	SET @V_HighTemp = IIF(REPLACE(@P_HighTemp, ' ', '') = '', null, convert(float, @P_HighTemp))

	IF REPLACE(@P_LowTemp, ' ', '') = ''
	BEGIN
		SET @V_LowTemp = NULL
	END
	IF REPLACE(@P_HighTemp, ' ', '') = ''
	BEGIN
		SET @V_HighTemp = NULL
	END


    IF NOT EXISTS(SELECT TOP 1 * FROM tblTemp where Date = @V_Date and Location = @P_Location)
	BEGIN
		INSERT INTO tblTemp VALUES(@V_Date, @P_Location, @V_AverageTemp, @V_LowTemp, @V_HighTemp)
	END
	ELSE
	BEGIN
		UPDATE tblTemp SET
			AverageTemp = @V_AverageTemp,
			LowTemp = @V_LowTemp,
			HighTemp = @V_HighTemp
		where Date = @V_Date and Location = @P_Location
	END
END

PYTHON 저장 소스

import pymssql
import csv

f = open('ta_20230529134639.csv', 'r', encoding='cp949')
data = csv.reader(f, delimiter=',')
#print(data)
header = next(data)    # 헤더 - 제목부분 건너띄기

server = '192.168.0.2'
database = 'DZ'
username = 'sa'
password = 'pwd'

#############################################################################
# MSSQL 접속
conn = pymssql.connect(server, username, password, database) 
# auto commit 을 사용할 경우 : conn.autocommit(True)
cursor = conn.cursor()

#############################################################################
for row in data:
    cursor.callproc('tblTemp_IU', (str(row[0]), row[1], row[2], row[3], row[4]))   
    conn.commit()

f.close()
conn.close()

 

끝~