[Python] sqlite3 모듈 사용 예제

import sqlite3

def create_lotto645_db():
    conn = sqlite3.connect('lotto645.db')

    c = conn.cursor()

    c.execute('''CREATE TABLE T_LOTTO645NUM (NUM INT,
                CONSTRAINT T_LOTTO645NUM_NUM_PK PRIMARY KEY(NUM))''')

    c.execute('''CREATE TABLE T_LOTTO645 (ROUND INT, NO1 INT, NO2 INT, NO3 INT, NO4 INT, NO5 INT, NO6 INT, NO7 INT,
                CONSTRAINT T_LOTTO645_ROUND_PK PRIMARY KEY(ROUND))''')
                
    c.execute('''CREATE VIEW V_LOTTO645NUMCNT (NUM, NO1CNT, NO2CNT, NO3CNT, NO4CNT, NO5CNT, NO6CNT, NO7CNT, CNT6, CNT7)
                AS SELECT NUM,
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO1=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO2=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO3=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO4=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO5=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO6=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO7=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO1=NUM OR NO2=NUM OR NO3=NUM OR NO4=NUM OR NO5=NUM OR NO6=NUM),
                (SELECT COUNT(*) FROM T_LOTTO645 WHERE NO1=NUM OR NO2=NUM OR NO3=NUM OR NO4=NUM OR NO5=NUM OR NO6=NUM OR NO7=NUM)
                FROM T_LOTTO645NUM''')
    
    conn.close()
    
create_lotto645_db()

 

# -*- coding: utf-8 -*-

filename = './Lotto645.csv'

f = open(filename, 'r')
fw = open('./Lotto645_insert.sql', 'wt')

for i in range(45):
    fw.write('insert into T_LOTTO645NUM values (' + str(i+1) + ');\n')

fw.write('\n')
    
while True:
    line = f.readline()
    if not line: break
    fw.write('insert into T_LOTTO645 (ROUND, NO1, NO2, NO3, NO4, NO5, NO6, NO7) values (' + line.strip() + ');\n')

fw.close()
f.close()

 

import sqlite3

def insert_lotto645_db():
    conn = sqlite3.connect('lotto645.db')
    
    c = conn.cursor()

    with open('./Lotto645_insert.sql', 'rt') as f:
        for line in f:
            c.execute(line)

    conn.commit()
    conn.close()

insert_lotto645_db()