4일차 sqlite

import sqlite3

def createTable(): # 없으면 만들고 connect, 있으면 connect

    try:

        sql = "create table if not exists student(name varchar(20), age int)"

        db = sqlite3.connect('my.db')

        db.execute(sql)

        db.commit() # sql 명령 확정

        # db.rollback() # sql 명령 취소

        db.close()

        print('create success')

    except Exception as err:

        print('에러: ', err)

def insertTable():

    try:

        db = sqlite3.connect('my.db')

        sql = "insert into student(name,age) values('홍길동', 20)" # sql 에서 문자열은 "" 로 묶기

        db.execute(sql)

        db.commit()

        # 한꺼번에 여러 데이터 동시에 입력 필요 시,

        sql2 = "insert into student(name,age) values(?,?)"

        data=[('김철수1', 40), ('김철수2', 30)]

        db.executemany(sql2, data)

        db.commit()

        db.close()

        print('insert success')

    except Exception as err:

        print('에러: ', err)

def deleteTable():

    try:

        sql = "delete from student where age=20" # 테이블 자체를 없애려면 "drop table student"

        db = sqlite3.connect('my.db')

        db.execute(sql)

        db.commit()

        db.close()

        print('delete success')

    except Exception as err:

        print('에러: ', err)

def updateTable():

    try:

        sql = "update student set name='김철수1',age='31' where name='김철수3'"

        db = sqlite3.connect('my.db')

        cur = db.cursor()

        cur.execute(sql)

        print('반영된 갯수:', cur.rowcount)

        db.commit()

        db.close()

        print('update success')

    except Exception as err:

        print('에러: ', err)

def selectTable():

    try:

        sql = "select * from student where age>30"

        db = sqlite3.connect('my.db')

        cur = db.cursor()

        cur.execute(sql)

        for c in cur: # 하나씩 가져오기. 한번에 다 (튜플 리스트로) 가져오려면 print(cur.fetchall())

            print(c) # 튜플로 출력됨

        db.close()

    except Exception as err:

        print('에러: ', err)

createTable()

insertTable()

updateTable()

selectTable()

deleteTable()