Python DB

관계형 DB

sqlite
maria db - 중형 DB 무료

자료
w3cschool SQL

sqlite3 권장은 매 execution 마다 connect / close 하는 것.

테이블 생성

import sqlite3

# connection은 DB가 존재하면, connect만 함.# DB가 없으면 생성하고 connect
# 표준 SQL에서 문자열은 무조건 single quote를 쓴다. '# 다만, execute의 파라미터로 튜플을 넘겨줄 때는 double도 상관 없음.
def createStudentTable():
    try:
        c = sqlite3.connect("my.db")
        sql = "create table if not exists student(name varchar(20), age int)"       

        c.execute(sql)
        c.commit() # 추가 수정 삭제은 commit을 불러줘야 함.        

        print("OK")
        c.close()
    except Exception as err:
        print(err)

def insertStudent(data):
    try:
        c = sqlite3.connect("my.db")
        sql = "insert into student (name, age) values (?,?)"      

        c.execute(sql, data)
        c.commit() # 추가 수정 삭제은 commit을 불러줘야 함.        

        print("OK")
        c.close()
    except Exception as err:
        print(err)

def insertStudentMany(data):
    try:
        c = sqlite3.connect("my.db")
        sql = "insert into student (name, age) values (?,?)"        

        c.executemany(sql, data)
        c.commit() # 추가 수정 삭제은 commit을 불러줘야 함.        

        print("OK")
        c.close()
    except Exception as err:
        print(err)

createStudentTable()
insertStudent(("이순신", 20))
insertStudentMany([("이순신", 20), ("홍길동", 30)])

데이터 Insert

import sqlite3

def createTable():
    try:
        c = sqlite3.connect("per.db")
        sql = "create table if not exists person (name varcahr(20), age int)"       
        c.execute(sql)
        c.commit()
        c.close()
    except Exception as err:
         print (err)

def insertRecord(data):
    try:
        c = sqlite3.connect("per.db")
        sql = "insert into person (name, age) values (?, ?)"        
        c.executemany(sql, data)
        c.commit()
        c.close()
    except Exception as err:
         print (err)

createTable()

data = []
while True:
    name = input("이름:")
    age = int(input("나이:"))
    data.append((name, age))
    yn = input("계속 입력 ? :")
    if (yn != 'y'):
        insertRecord(data)
        break

데이터 업데이트

import sqlite3

def createTable():
    try:
        c = sqlite3.connect("per.db")
        sql = "create table if not exists person (name varcahr(20), age int)"       
        c.execute(sql)
        c.commit()
        c.close()
    except Exception as err:
         print (err)

def updateData():
    try:
        c = sqlite3.connect("per.db")
        sql = "update person set name='김철수4', age=22 where name='김철수'"       
        c.execute(sql)
        c.commit()
        c.close()
        print("수정 성공")
    except Exception as err:
        print(err)

updateData()

동적 업데이트

import sqlite3

def createTable():
    try:
        c = sqlite3.connect("per.db")
        sql = "create table if not exists person (name varcahr(20), age int)"        
        c.execute(sql)
        c.commit()
        c.close()
    except Exception as err:
         print (err)

def updateData(name, new_name, new_age):
    try:
        c = sqlite3.connect("per.db")
        sql = "update person set name=?, age=? where name=?"        
        data = (new_name, new_age, name)
        c.execute(sql, data)
        c.commit()
        c.close()
        print("수정 성공")
    except Exception as err:
        print(err)

name = input("검색이름: ")
new_name = input("수정이름:")
new_age = int(input("수정 나이:"))
updateData(name, new_name, new_age)

삭제 하기
# deleteimport sqlite3

def createTable():
    try:
        c = sqlite3.connect("per.db")
        sql = "create table if not exists person (name varcahr(20), age int)"        c.execute(sql)
        c.commit()
        c.close()
    except Exception as err:
         print (err)

def deleteData(name):
    try:
        c = sqlite3.connect("per.db")
        sql = "delete from person where name=?"        
        data = (name,)
        c.execute(sql, data)
        c.commit()
        c.close()
        print("삭제 성공")
    except Exception as err:
        print(err)

new_name = input("삭제이름:")
deleteData(new_name)


검색하기
def selectPartData(self, name):
    try:
        c = sqlite3.connect("per.db")
        cursor = c.cursor()
        sql = "select * from person where name like ?"        arg =('%' +name+'%',)
        cursor.execute(sql, arg)
        data = cursor.fetchall()
        c.close()
        print("성공")
        return data
    except Exception as err:
        print(err)
        return None




def selectData(name, age):
    try:
        c = sqlite3.connect("per.db")
        cursor = c.cursor()
        #sql = "select * from person where name=? and age>?"        #sql = "select * from person where name=? and age in (30,40)"        #sql = "select * from person where name=? and age between 20 and 40" #  20 <= age <=40        #sql = "select * from person where name like %철%" # 이름에 철 포함된 모든 것을 가져옴.        #sql = "select * from person where name like 철%" # 이름이 철로 시작 하는 것        #sql = "select * from person where name like %철" # 이름이 철로 끝나는 것
        #sql = "select * from person order by name" # 이름으로 정렬 ㄱㄴㄷ        #sql = "select * from person order by name desc" # 이름으로 내림 차순 정렬 ㅎㅍㅌ        #sql = "select * from person limit 3" #3개만 가져옴.        #sql = "select * from person order by age desc limit 3" # 가장 나이 많은 3개만 가져옴.
        #sql = "select count(age) from person " # 나이 항목의 개수 (나이가 빈 경우가 없으면 row와 같음)        #sql = "select count(*) from person " # row의 개수. 결과는 cursor.fetchone() 하면 tuple로 결과가 나옴.        #sql = "select max(age) from person " # 가장 많은 나이        #sql = "select avg(age) from person "        #sql = "select sum(age) from person "        sql = "select max(age) sum(age) from person " # 결과가 2개이기 때문에 cursor.fetchall() 하면 2항 튜플이 온다.
        #data = (name, age)        #cursor.execute(sql, data) # 결과 값이 메모리에 loading 됨.        cursor.execute(sql)

        # data = cursor.fetchall() # 메모리에 올라가 있는 값을 한꺼번에 가져오게 됨. [(),] 형태임.        # for n, a in data:        #     print(n, a)
        for n, a in cursor: # 메모리에 올라가 있는 값을 하나씩 가져오기.            print(n,a)

        c.close()
        print("성공")
    except Exception as err:
        print(err)




def showData(sortup = False):
    print("="*60, "이름       국어     영어      수학       총점      평균", "="*60, sep='\n')
    try:
       c = sqlite3.connect("grade.db")
       if (sortup == False):
         sql = "select * from grade"       else:
         sql = "select * from grade order by name"       cursor = c.cursor()
       cursor.execute(sql)
       for i in cursor:
           s = sum(i[1:])
           print("{:5} {:10} {:10} {:10} {:10} {:10.2f}".format(i[0], i[1], i[2], i[3], s, s/3))

       sql = "select avg( kor ), avg( eng ), avg( math ) from grade "       cursor = c.cursor()
       cursor.execute(sql)
       a = cursor.fetchone()
       print("과목별 평균 {:10.2f} {:10.2f} {:10.2f}".format(float(a[0]), float(a[1]), float(a[2])))

       c.close()
    except Exception as err:
        print(err)