파이썬 데이터베이스 - sqlite3 설치와 SQL문

 

 

 

 

데이터베이스

- 업무상 발생하는 데이터를 공용으로 사용하기 위해 필요한 자료를 중복을 최소화하여 컴퓨터 기억장치 내에 모아놓은 집합체

- CRUD(Create, Read, Update, Delete) 문법으로 테이블 조작. Read는 Select문으로 구현. 

- 관계형 데이터베이스 : 자료간의 관계를 행과 열로 구성된 2차원 테이블 형태로 제공.

- 행(Row) : tuple, record라고도 하며 하나의 객체로 표현

- 열(Attribute) : column, field라고도 하며 객체의 속성으로 표현

- 카디널리티(cardinality) : 행 개수의 총 합

- 차수(degree) : 열 개수의 총 합

- DBMS(DataBase Management System) : 데이터베이스의 효율적인 관리 환경을 제공하는 시스템 소프트웨어

 

 

 

SQL문

- 테이블 생성 : create table if not exists 테이블명(컬럼명 데이터타입[(길이)]);

- 테이블 삭제 : drop table 테이블명;

- 테이블 전체 조회 : select * from 테이블명;

- 테이블 조건부 조회 : select * from 테이블명 where 조건;

- 레코드 삽입 : insert into 테이블명 values(모든 컬럼에 들어갈 값) 

                        또는 insert into 테이블명 (컬럼1, 컬럼 2..) values (값1, 값2...)

- 레코드 수정 : update 테이블명 set 컬럼명 = 새로운 값 where 조건

- DBMS마다 키워드가 조금씩 다를 수 있음

- 끝에 세미콜론(;) 잊지 말것

 

 

 

SQLite3

- 공유를 허용하지 않으며 기기 내부에서만 사용할 수 있는 DBMS

- 파이썬에서 기본적으로 제공하기 때문에 파이썬에서는 import를 통해 바로 사용 가능

 

 

 

Q. 아래와 같은 item 테이블을 생성하고 상품 등록, 전체 조회, 입력한 코드에 따른 조회가 가능하도록 코딩

create table if not exists item(

code integer primary key, name text(30) unique not null, qty integer default 0, unit_price real default 0.0);

 

 

A.

import sqlite3
import os
 
try : 
    conn = sqlite3.connect("data/sqlite_db.db") # db 생성 - > 연결 object. 예를 들면 싸이월드에 접속한거.
    cursor = conn.cursor() # 데이터 내부의 객체를 가져오는 것. 객체 퍼가요~ 
    # 프로그램과 DB 간 데이터 교환할 준비가 됨
 
    # item 테이블 생성 쿼리문
    sql= """create table if not exists item(
        code integer primary key,
        name text(30) unique not null,
        qty integer default 0,
        unit_price real default 0.0)
        """
    cursor.execute(sql) # sql 적용. 
    conn.commit() # db 반영
 
    while True : # 메뉴 선택 방식
        print()
        num = int(input("1. 상품 등록 / 2. 상품 전체 조회 / 3. 상품 선택 조회 / 0. 종료 : "))
        if num == 1 :
            print("===상품등록===")
            code = int(input("상품 코드 입력\t: "))
            sql = f"select * from item where code = {code}"
            cursor.execute(sql) # 커서로 해당 레코드를 가리킴
            rows = cursor.fetchall() # db 프로그램에서 실행시키듯 sql 커밋해서 결과 불러옴
 
            if len(rows) > 0 :
                print('존재하는 코드입니다.')
                os.system("pause") # 계속하려면 아무 키나 누르십시오
                os.system('cls')
                continue
            else : 
                name = input("상품명 입력\t: ")
                su = int(input("수량 입력\t: "))
                dan = float(input("단가 입력\t: "))
                sql = f"insert into item values({code}, '{name}', {su}, {dan})"
                cursor.execute(sql)
                conn.commit()
                print("상품등록을 성공했습니다.")
 
        elif num == 2 :
            print("===상품 전체 조회===")
            sql = "select * from item"
            cursor.execute(sql)
            dataset = cursor.fetchall()
            print(*dataset, sep='\n')
 
        elif num == 3 :
            print("===상품 부분 조회===")
            in_code = int(input("조회할 코드를 입력하세요 : "))
            sql = f"select * from item where code = {in_code}"
            cursor.execute(sql)
            rows = cursor.fetchall()
            if len(rows) > 0 :
                for row in rows :
                    print("조회결과는 코드:{}, 제품명:{}, 수량:{}, 단가:{} 입니다.".format(row[0],row[1],row[2],int(row[3])))
            else : print("조회결과 입력한 코드에 맞는 상품이 었습니다.")
        else :
            break
 
except Exception as e : # 에러 내용을 e라는 이름으로 받음
    print('db 연동 실패 : ', e) # e의 내용 출력
    conn.rollback() # 모든 실행 취소
    
finally:
    cursor.close() # 객체 닫아줌
    conn.close()

 

# ver.2 클래스 활용하여 재구성
 
import sqlite3
 
class db_item :
    conn = cursor = None
    def __init__ (self, sql) :
        self.conn = sqlite3.connect("data/sqlite_db")
        self.cursor = self.conn.cursor()
        self.cursor.execute(sql)
        self.conn.commit()
    
    def register (self, code, name, su, dan) :
        r_code = code
        r_name = name
        r_su = su
        r_dan = dan
        sql = f"insert into item values({r_code}, '{r_name}', {r_su}, {r_dan})"
        self.cursor.execute(sql)
        self.conn.commit()
        return print("상품등록을 성공했습니다.")  
 
    def table_reader(self) :
        sql = "select * from item"
        self.cursor.execute(sql)
        dataset = self.cursor.fetchall()
        return print(*dataset, sep='\n')
    
    def record_reader(self, code) :
        r_code = code
        sql = f"select * from item where code = {r_code}"
        self.cursor.execute(sql)
        rows = self.cursor.fetchall()
        if len(rows) > 0 :
            for row in rows :
                return print("조회결과는 코드:{}, 제품명:{}, 수량:{}, 단가:{} 입니다.".format(row[0],row[1],row[2],int(row[3])))
        else : 
            return print("조회결과 입력한 코드에 맞는 상품이 없습니다.")
 
try : 
    sql= """create table if not exists item(
        code integer primary key,
        name text(30) unique not null,
        qty integer default 0,
        unit_price real default 0.0)
        """
    db = db_item(sql)
    print(type(db))
 
    while True :
        print()
        num = int(input("1. 상품 등록 / 2. 상품 전체 조회 / 3. 상품 선택 조회 / 0. 종료 : "))
        if num == 1 :
            print("===상품등록===")
            code = int(input("상품 코드 입력\t: "))
            name = input("상품명 입력\t: ")
            su = int(input("수량 입력\t: "))
            dan = float(input("단가 입력\t: "))
            db.register(code, name, su, dan)
 
        elif num == 2 :
            print("===상품조회1===")
            db.table_reader()
 
        elif num == 3 :
            print("===상품조회2===")
            in_code = int(input("조회할 코드를 입력하세요 : "))
            db.record_reader(in_code)
 
        else :
            break
 
except Exception as e :
    print('db 연동 실패 : ', e)
    db.conn.rollback()
    
finally:
    db.cursor.close()
    db.conn.close()

 

실행결과 : 

(상품조회1은 상품 전체 조회로, 상품조회2는 상품 선택 조회로 수정하였음.)