55. Python DB연동, CRUD, table 연습문제

문제1) goods 테이블을 이용하여 다음과 같은 형식으로 출력하시오.  
 <조건1> HDTV 수량 수정 
 <조건2> 전자레인지 레코드 추가 
 
[ goods 테이블 현황 ]
1 냉장고 2 850000
2 세탁기 3 550000
3 HDTV 3 1500000  <- 수량 수정
4 전자레인지 5  400000 <- 레코드 추가
전체 레코드 수 : 4

[ 상품별 총금액 ]
냉장고 상품의 총금액은 1,700,000
세탁기 상품의 총금액은 1,650,000
HDTV 상품의 총금액은 4,500,000
전자레인지 상품의 총금액은 2,000,000

 

import pymysql

config = {
    'host' : '127.0.0.1',
    'user' : 'scott',
    'password' : 'tiger',
    'database' : 'work',
    'port' : 3306,
    'charset':'utf8',
    'use_unicode' : True}

try :
    conn = pymysql.connect(**config)
    cursor = conn.cursor()
    '''
    #1. update
    query ="update goods set su = 3 where name = 'HDTV'"
    cursor.execute(query) # 레코드 수정 
    conn.commit() 
    
    #2. insert 
    query ="insert into goods values(4,'전자레인지',5,400000)"
    cursor.execute(query) # 레코드 추가 
    conn.commit() 
    '''
    #3. 총금액 계산 
    cursor.execute("select * from goods")
    dataset = cursor.fetchall()
    print("\t[ goods 테이블 현황 ]")
    for row in dataset :
        print(row[0], row[1], row[2], row[3])
    print('전체 레코드 수 : ', len(dataset))
    
    print("\t[ 상품별 총금액 ]")
    for row in dataset :
        tot = row[2] * row[3]
        print("{0} 상품의 총금액은 {1:3,d}".format(row[1],tot))    
    
except Exception as e :
    print('db error :', e)
finally:
    cursor.close(); conn.close()

 

 

 

 

문제2) 다음과 같은 메뉴를 이용하여 goods 테이블을 관리하시오.

[레코드 처리 메뉴]
1. 레코드 조회
2. 레코드 추가
3. 레코드 수정
4. 레코드 삭제
5. 프로그램 종료
    메뉴번호 입력 : 

 

import pymysql

config = {
    'host' : '127.0.0.1',
    'user' : 'scott',
    'password' : 'tiger',
    'database' : 'work',
    'port' : 3306,
    'charset':'utf8',
    'use_unicode' : True}

try:
    #db 연결 객체 생성 
    conn = pymysql.connect(**config)
    #SQL 실행 객체 생성 
    cursor = conn.cursor()    
    
    while True :  # 무한루프 
        print('\t[레코드 처리 메뉴 ]')
        print('1. 레코드 조회')
        print('2. 레코드 추가')
        print('3. 레코드 수정')
        print('4. 레코드 삭제')
        print('5. 프로그램 종료')    
        menu = int(input('\t메뉴번호 입력 : '))
        
        if menu == 1 : # 1. 레코드 조회
            sel = int(input('1.전체, 2.상품명 : '))
            if sel == 1 : 
                query = "select * from goods"
                cursor.execute(query) # sql문 실행 
                dataset = cursor.fetchall() # 전체 레코드 가져오기 
                print('-'*30)
                for row in dataset :
                    print(row[0], row[1], row[2], row[3])
                print('-'*30)
                print('전체 레코드 수 : ', len(dataset))
            elif sel == 2: 
                name = input('상품명 입력 : ')
                query =f"select * from goods where name like '%{name}%'"
                cursor.execute(query)
                dataset = cursor.fetchall()
                
                if dataset :
                    for row in dataset :
                        print(row)
                else :
                    print('검색된 상품은 없습니다.')                
        elif menu == 2: # 2. 레코드 추가
            code = int(input('code input : ')) 
            name = input('name input : ') 
            su = int(input('su input : ')) 
            dan = int(input('dan input : ')) 
            
            query = f"insert into goods values({code},'{name}',{su},{dan})"
            cursor.execute(query) #레코드 추가 
            conn.commit()  # db 반영
        elif menu == 3: #3. 레코드 수정
            code = int(input('update code input : ')) 
            su = int(input('update su input : ')) 
            dan = int(input('update dan input : ')) 
            query = f"""update goods 
                        set su = {su}, dan = {dan} 
                        where code={code}"""
            cursor.execute(query) # 레코드 수정 
            conn.commit() #db 반영
        elif menu == 4: #4. 레코드 삭제
            code = int(input('delete code input : '))
            query = f"select * from goods where code = {code}"
            cursor.execute(query) 
            row = cursor.fetchone() #1개 레코드 가져오기 
            
            if row : #code 있는 경우 
                cursor.execute(f"delete from goods where code = {code}") #삭제 
                conn.commit()  # db 반영
            else :
                print('해당 code는 없습니다.')
        elif menu == 5 :
            print('프로그램 종료')
            break #반복 exit
        else :
            print('해당 메뉴는 없습니다.')
        
#DB 연결 예외 처리          
except Exception as e :
    print('db 연동 오류 : ', e)
    conn.rollback()
finally:
    cursor.close()
    conn.close()

 

 

 

 

 

문제3) HidiSQL을 이용하여 다음과 같은 student 테이블을 만들고, 
      DB 연동 프로그램으로 레코드를 추가하고, 조회하시오.
  
조건1> HidiSQL 이용 : student 테이블 만들기       
       테이블 칼럼 구성 : 학번,이름,전화번호,학과,지도교수 
create or replace table student( 
  studno int primary key,              
  sname varchar(10) not null,                        
  tel varchar(15),     
  deptno int,                
  profno  int                
);  


조건2> DB 연동 프로그램 : 테이블에 레코드가 없으면 레코드 추가(insert)  
insert into student values (9411,'서진수','055)381-2158',201,1001);
insert into student values (9413,'이미경','02)266-8947',103,3002);
insert into student values (9415,'박동호','031)740-6388',202,4003);

조건3> DB 연동 프로그램 : 테이블에 레코드가 있으면 레코드 조회(select)   

 

import pandas as pd 
import pymysql # driver = python + DB(Mysql)
import os 
os.chdir('D:/ITWILL/3_Python-I/workspace/chap10_MariaDB/data')

st = pd.read_csv('student.csv')

config = {
    'host' : '127.0.0.1',
    'user' : 'scott',
    'password' : 'tiger',
    'database' : 'work',
    'port' : 3306,
    'charset':'utf8',
    'use_unicode' : True}

try :
    #db 연동 객체 생성 
    conn = pymysql.connect(**config)
    #sql문 실행 객체 
    cursor = conn.cursor()    
    
    #1. table 유무 판단 : student
    cursor.execute("show tables") #table 목록 조회 
    tables = cursor.fetchall() # table 목록 가져오기 
    
    sw = False # off
    for table in tables : 
        if 'student' in table :
            sw = True # on
    
    #2. 레코드 추가 or 레코드 조회 
    if sw : # table 있는 경우 
        #레코드 조회 
        sql = "select * from student"
        cursor.execute(sql)
        dataset = cursor.fetchall()
        
        if not(dataset) : #1) 레코드 없는 경우 
            #레코드 추가      
            sql = """insert into student values (9411,'서진수','055)381-2158',201,1001)"""                    
            cursor.execute(sql)        
            sql = """insert into student values (9413,'이미경','02)266-8947',103,3002)"""                    
            cursor.execute(sql)
            sql = """insert into student values (9415,'박동호','031)740-6388',202,4003)"""                    
            cursor.execute(sql)
            conn.commit() # db 반영 
            
            print('student 테이블 레코드 추가\n')
        
            #레코드 조회 
            sql = "select * from student"
            cursor.execute(sql)
            dataset = cursor.fetchall()
            
            for row in dataset : 
                print(row[0], row[1], row[2], row[3], row[4])
                
        else : #2) 레코드 있는 경우    
            for row in dataset : 
                print(row[0], row[1], row[2], row[3], row[4], sep=',')

        
    else : #table 없는 경우         
        print('student 테이블이 없습니다.')        
            
except Exception as e :
    print('db error : ', e)
finally :
    cursor.close(); conn.close()