Python 기초 8장. DB 연동 (2)

< 공부한 내용 >

5. csv파일을 DB로

6. json파일을 DB로

7. group by 적용

 

5. csv파일을 DB로

'''
csv -> db table
1차 실행 : table 생성 -> 레코드 100개 삽입
2차 실행 : 레코드 조회
'''

import pandas as pd # csv file read
import pymysql # db 연동
config = {
     'host' : '127.0.0.1',
     'user' : 'root',
     'password' : '1234',
     'database' : 'work',
     'port' : 3306,
     'charset':'utf8',
     'use_unicode' : True}

bmi.csv

0.27MB

bmi = pd.read_csv("chap08_Database/data/bmi.csv")
print(bmi.info())
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 20000 entries, 0 to 19999
Data columns (total 3 columns):
height 20000 non-null int64 -> int
weight 20000 non-null int64 -> int
label 20000 non-null object -> varchar(20)
'''
height = bmi['height']
weight = bmi['weight']
label = bmi['label']

print(len(height)) # 20000
print(height[:5]) # 앞에서 5개
print(height[-5:]) # 뒤에서 5개

# db 연결
try :
     conn = pymysql.connect(**config)
     cursor = conn.cursor()

     '''

     # 테이블 생성 (생성 후 주석처리)
     sql = """create table bmi_tab(
     height int not null,
     weight int not null,
     label varchar(20) not null
     )"""
     cursor.execute(sql) # table 생성 : auto commit
     '''

     # select 실행
     cursor.execute("select * from bmi_tab")
     rows = cursor.fetchall()

     if rows : # 레코드 있는 경우
          for row in rows :
               print(row[0], row[1], row[2])

         print('전체 레코드 수 :', len(rows))
     else : # 레코드 없는 경우
          # 레코드 추가
          print("레코드 100개 추가")
          for i in range(100) :
               h = height[i]
               w = weight[i]
               l = label[i]
               sql = f"insert into bmi_tab values({h}, {w}, '{l}')"
               cursor.execute(sql)
               conn.commit()

except :
     print('db 연동 error')
finally :
     cursor.close()
     conn.close()

 

6. json파일을 DB로

'''
json -> db table
 1. json file read
 2. table 생성
 3. table에 레코드 추가(json data)
 4. table에 레코드 조회

json file decoding
  - json file(json 문자열) -> python dict
  1. {key:value, key:value} -> row : usagov_bitly.txt
  json.loads()
  2. [{row1}, {row2}, {rown}] -> list[{}] : labels.json
  json.load()
'''
import json

 

labels.json

0.01MB

# 1. json file decoding

file = open("chap08_Database/data/labels.json", encoding="utf-8")
#lines = file.read()
#print(lines)
# decoding : json 문자열 -> dict
lines = json.load(file)
print(lines) # [{'id': 76811, 'url':
print(type(lines)) # <class 'list'>
print(len(lines)) # 30
print(type(lines[0])) # <class 'dict'>

# row 단위 출력
cnt = 0
for line in lines :
     cnt += 1
     print(cnt, '->', line)

# 2. DataFrame 생성

 

import pandas as pd

df = pd.DataFrame(lines)
print(df.info())
'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 30 entries, 0 to 29
Data columns (total 5 columns):
id 30 non-null int64 -> int
url 30 non-null object -> varchar()
name 30 non-null object -> varchar()
color 30 non-null object -> varchar()
default 30 non-null bool -> str -> varchar()
'''
print(df.head())

# 3. db table 생성 -> 레코드 추가

 

import pymysql
config = {
     'host' : '127.0.0.1',
     'user' : 'root',
     'password' : '1234',
     'database' : 'work',
     'port' : 3306,
     'charset':'utf8',
     'use_unicode' : True}

'''
{
     "id": 76811,
     "url": "https://api.github.com/repos/pandas-dev/pandas/labels/Bug",
     "name": "Bug",
     "color": "e10c02",
     "default": false
}
'''

try :
     conn = pymysql.connect(**config)
     cursor = conn.cursor()

     # table 생성
     '''
     sql = """create table labels(
     id int not null,
     url varchar(150) not null,
     name varchar(50) not null,
     color varchar(50) not null,
     def char(5)
     )"""
     cursor.execute(sql) # table 생성
     '''

     # 레코드 조회
     cursor.execute("select * from labels")
     rows = cursor.fetchall()
     if rows :
          print("labels 레코드 조회")
          for row in rows :
               print(row)

     print("전체 레코드 수 :", len(rows))
     else :
          print("labels 레코드 삽입")
          for i in range(30) :
               uid = df.id[i] # df['column'] or df.column
               url = df.url[i]
               name = df.name[i]
               color = df.color[i]
               de = str(df.default[i]) # bool -> str
               sql = f"insert into labels values({uid},'{url}','{name}','{color}','{de}')"
               cursor.execute(sql)
               conn.commit() # db 반영
except Exception as e:
     print("db error : ", e)
finally:
     cursor.close()
     conn.close()

 

7. group by 적용

'''
bmi_tab 테이블 대상으로 groupby 적용
  - select 컬럼명, 집계함수(sum, avg) from 테이블명
    group by 집단변수 order by 칼럼명
'''

import pymysql
config = {
     'host' : '127.0.0.1',
     'user' : 'root',
     'password' : '1234',
     'database' : 'work',
     'port' : 3306,
     'charset':'utf8',
     'use_unicode' : True}

try :
     conn = pymysql.connect(**config)
     cursor = conn.cursor()

     sql = """select label, avg(height), avg(weight)
     from bmi_tab
     group by label
     order by label"""
     cursor.execute(sql)
     rows = cursor.fetchall()
     print('label\theight\tweight')
     if rows :
          for row in rows :
               print(f"{row[0]} {row[1]} {row[2]}")
except :
     print('db error')
finally:
     cursor.close()
     conn.close()

 

'''
label height weight
fat 152.3784 70.0811
normal 162.3125 57.4688
thin 175.6129 48.2581
'''

 

--------------------------------------------------- example ---------------------------------------------------

exam04.py

0.00MB