[Python] 한글 유니코드 9 - 질의처리(select, from, where), 쿼리문 파이썬 파일만들기

select * from “Kpop_release>May”;    5월 테이블에서 모든 음악

select Album, “Artist(s)” from “Kpop_release>May” ;      5월 테이블에서 Album, Artist(s) 속성만

 

select count(*) from “Kpop_release>May” ;      5월 테이블 모든 음악 개수

select * from “Kpop_release>May” group by "Genre(s)";      5월 테이블 모든 음악 장르 그룹별로 보여줘

select Album from “Kpop_release>May” where “Genre(s)” = “Dance” ;     5월 테이블에서 댄스 엘범만

select Album from “Kpop_release>May” where “Artist(s)” = “BTS” ;    5월 테이블에서 BTS 엘범만

 

select Album from “Kpop_release>December” where Album like “%Love%” ;    12월 테이블에서 Love 들어간 엘범만

select Album from “Kpop_release>December” where Album like “Love%” ;      12월 테이블에서 Love 앞에 들어간 엘범만

select Album from “Kpop_release>December” where Album like “%Love” ;      12월 테이블에서 Love 뒤에 들어간 엘범만

 

select * from “Kpop_release>May” order by Date;     5월 테이블에서 날짜 순으로 보여줘

 

select * from “Kpop_release>may” where Album like “%Love%” ;       5월 테이블에서 Love 들어간 엘범 선택하고

union select * from “Kpop_release>August” where Album like “%Love%” ;        8월 테이블에서 Love 들어간 엘범 추가

union select * from “Kpop_release>December” where Album like “%Love%” ;        11월 테이블에서 Love 들어간 엘범 추가

 

---

find_musicdb.py

 #coding: utf-8
 import sqlite3
 import sys
 def connect_db(dbname):
     conn = sqlite3.connect(dbname)
     return conn
 def find (conn, table, cols = '*', condition=''):
     cur = conn.cursor()
     try:
         sql = f'select {cols} from {table}'
         if condition != '':
             sql=sql + f' where {condition}'
         print(sql)
         cur.execute(sql)
         results = cur.fetchall()
         return results
     except Exception as e:
         print(e)
 if __name__=='__main__':
     try:
         con = connect_db('music/musicDB.sqlite3')
         rst1 = find (con, '"Kpop_release>May"')
         print(rst1)
         rst2 = find(con, '"Kpop_release>August"', '"Artist(s)", Album', "Album like '%Love%'")
         print(rst2)
         con.close()
     except Exception as e:
         print(e, file=sys.stderr)

1. db 연결하기

2. find 함수 정의하기

    - 입력받을 db (conn), 테이블(table),  select 에 들어갈 것 (cols), where 에 들어갈 것 (condition) 으로 호출한다.

3. 사용하기

---

조금 더 개선해서 

find() 함수

find_group() 함수

find_orderly() 함수

find_union() 함수를 정의한다.

 #coding: utf-8
 import sqlite3
 import sys
 def connect_db(dbname):
     conn = sqlite3.connect(dbname)
     return conn
 def pretty_print(result):
     for row in result:
         cleaned_row = [str(item).replace('(', '').replace(')', '').replace('"', '') for item in row]
         print('\t'.join(cleaned_row))
 def find(conn, table, cols = '*', condition=''):
     cur = conn.cursor()
     try:
         sql = f'select {cols} from {table}'
         if condition != '':
             sql=sql + f' where {condition}'
         print(sql)
         cur.execute(sql)
         results = cur.fetchall()
         return results
     except Exception as e:
         print(e)
 def find_groups(conn, table, group='',cols = '*', condition='' ):
     cur = conn.cursor()
     try:
         sql = f'select {cols} from {table}'
         if condition != '':
             sql = sql + f' where {condition}'
         if group != '':
             sql = sql + f' group by {group}'
         print(sql)
         cur.execute(sql)
         results = cur.fetchall()
         return results
     except Exception as e:
         print(e)
def find_orderly(conn, table, order, cols='*', condition=''):
     cur = conn.cursor()
     try:
         sql = f'select {cols} from {table}'
         if condition != '':
             sql = sql + f' where {condition}'
         if order != '':
             sql = sql + f' order by {order}'
         print(sql)
         cur.execute(sql)
         results = cur.fetchall()
         return results
     except Exception as e:
         print(e)
 def find_union(conn, tables, cols='*',condition=''):
     cur = conn.cursor()
     try:
         a = 0
         for table in tables:
             if a == 0:
                 a=1
                 sql = f'select {cols} from {table}'
                 if condition != '':
                     sql = sql + f' where {condition}'
             else:
                 sql = sql + f'union select {cols} from {table}'
                 if condition != '':
                     sql = sql + f'where {condition}'
         print(sql)
         cur.execute(sql)
         results = cur.fetchall()
         return results
     except Exception as e:
         print(e)
 if __name__=='__main__':
     try:
         con = connect_db('music/musicDB.sqlite3')
          rst5 = find_union(con, ['"Kpop_release>May"','"Kpop_release>August"', '"Kpop_release>December"'], '*',"Album like '%Love%'")
         pretty_print(rst5)
         con.close()
     except Exception as e:
         print(e, file=sys.stderr)