๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

Programming/SQL

SQLite

SQLite?

  • ์„œ๋ฒ„ ํ•„์š”์—†์ด DB์˜ ํŒŒ์ผ์— ๊ธฐ์ดˆํ•˜์—ฌ DB ์ฒ˜๋ฆฌ๋ฅผ ๊ตฌํ˜„ํ•œ ์ž„๋ฒ ๋””๋“œ SQL DB ์—”์ง„
  • ๋ณ„๋„์˜ ์„ค์น˜์—†์ด ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Œ
  • Mac OS ๋‚˜ Linux์— ๊ธฐ๋ณธ์ ์œผ๋กœ ์„ค์น˜๋˜์–ด ์žˆ์Œ
  • ํŒŒ์ด์ฌ์€ ๋ฒ„์ „ 2.5์ด์ƒ์ผ ๊ฒฝ์šฐ ๋ชจ๋“ˆ์ด ๊ธฐ๋ณธ ๋‚ด์ • ๋˜์–ด ์žˆ์Œ

1. ํŒŒ์ด์ฌ๊ณผ DB๋ฅผ ์—ฐ๊ฒฐ

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path) #mydb.db์— ์—ฐ๊ฒฐ
  • DB ์ด๋ฆ„์„ ์ •ํ•˜์—ฌ ์ž…๋ ฅ
  • ์œ„ ์ฝ”๋“œ์˜ ๊ฒฝ์šฐ mydb.db ํ˜•ํƒœ๋กœ ์ €์žฅ

2. Cursor ๊ฐ์ฒด

c = conn.cursor()
  • conn ๊ฐ์ฒด๋Š” SQL ์—ฐ๊ฒฐ๊ณผ ๊ด€๋ จ๋œ ์…‹ํŒ…์ด ํฌํ•จ๋˜์–ด ์žˆ์Œ
  • Cursor ๊ฐ์ฒด๋Š” Connect()ํ•จ์ˆ˜์˜ ์—ฐ๊ฒฐ์„ ์‚ฌ์šฉ
  • Cursor ๊ฐ์ฒด
    • SQL ์งˆ์˜(Query)๋ฅผ ์ˆ˜ํ–‰ํ•˜๊ณ  ๊ฒฐ๊ด„๋ฅด ์–ป๋Š”๋ฐ ์‚ฌ์šฉํ•˜๋Š” ๊ฐ์ฒด
    • Insert์ฒ˜๋Ÿผ DB์—๋งŒ ์ ์šฉ๋˜๋Š” ๋ช…๋ น์–ด๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ Select ๊ฐ™์ด ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์˜ฌ ๋•Œ๋Š” SQL ์งˆ์˜ ์ˆ˜ํ–‰ ๊ฒฐ๊ณผ์— ์ ‘๊ทผํ•˜๊ธฐ ์œ„ํ•œ Cursor๊ฐ€ ๋ฐ˜๋“œ์‹œ ํ•„์š”
    • conn.coursor() ์‚ฌ์šฉ์„ ์Šต๊ด€ํ™” ํ•˜์ž

3. SQL๋ฌธ ์‹คํ–‰

# stocks์ด๋ผ๋Š” ์ด๋ฆ„์˜ ํ…Œ์ด๋ธ”์„ ํ•˜๋‚˜ ์ƒ์„ฑ
c.execute("CREATE TABLE  IF NOT EXISTS  stocks (date text, trans text, symbol text, qty real, price real)")

# stocks ํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ๋ฅผ ํ•˜๋‚˜ insert
c.execute("INSERT INTO stocks VALUES ('20200701', 'TEST', 'AIFFEL', 1, 10000)")

# ๋ฐฉ๊ธˆ insertํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•ด ๋ด…๋‹ˆ๋‹ค.
c.execute("SELECT * FROM stocks")

# ์กฐํšŒ๋œ ๋‚ด์—ญ์„ ์ปค์„œ๋ฅผ ํ†ตํ•ด ๊ฐ€์ ธ์™€ ์ถœ๋ ฅํ•ด ๋ด…๋‹ˆ๋‹ค.
print(c.fetchone())

4. conn.commit()

  • SQL์งˆ์˜๊ฐ€ ๋๋‚ฌ๋‹ค๋ฉด conn.commit() ๋ฅผ ํ˜ธ์ถœํ•ด์„œ DB๊ฐ€ ์‹ค์ œ๋กœ update
conn.commit()   # commit()์€ cursor์˜ ๋ฉ”์†Œ๋“œ๊ฐ€ ์•„๋‹ˆ๋ผ connection์˜ ๋ฉ”์†Œ๋“œ
  • commit()์„ ํ†ตํ•ด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ๋ฐ์ดํ„ฐ ๋ณ€๊ฒฝ์ด ๋ฐ˜์˜๋จ
    • ์ด๋Ÿฐ ๊ฒƒ์„ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ๋Š” ํŠธ๋žœ์žญ์…˜(transation) ๊ด€๋ฆฌ ๋ผ๊ณ  ํ•จ
    • ํŠธ๋žœ์žฌ์…˜์ด๋ž€?
      • ์ชผ๊ฐœ์ง€๋ฉด ์•ˆ๋˜๋Š” ์—…๋ฌด ๋‹จ์œ„
      • ex) ์˜จ๋ผ์ธ ์‡ผํ•‘์—์„œ์˜ ๊ฒฐ์ œ์™„๋ฃŒ์™€ ์ƒํ’ˆ๋ฐฐ์†ก์ฒ˜๋ฆฌ๊ฐ€ ์ชผ๊ฐœ์ ธ์„œ๋Š” ์•ˆ๋จ

5. DB์™€ ์—ฐ๊ฒฐ ๋Š๊ธฐ

c.close()      # ๋จผ์ € ์ปค์„œ๋ฅผ ๋‹ซ์€ ํ›„
conn.close()    # DB ์—ฐ๊ฒฐ์„ ๋‹ซ์•„ ์ค๋‹ˆ๋‹ค.
  • commit()์„ ์™„๋ฃŒํ–ˆ๋‹ค๋ฉด DB์™€ ์—ฐ๊ฒฐ ๋Š๊ธฐ

6. DDL ๋ฌธ์œผ๋กœ ํ…Œ์ด๋ธ” ์ƒ์„ฑ

import sqlite3
import os
db_path = os.getenv('HOME')+'/mydb.db'

conn = sqlite3.connect(db_path)
c = conn.cursor()

#- ! ์žฌ์‹คํ–‰ ์‹œ ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•  ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ ์•„๋ž˜์ฒ˜๋Ÿผ ํ•ด๋‹น ํ…Œ์ด๋ธ”๋“ค์„ ๋ชจ๋‘ ์ง€์›Œ์ค๋‹ˆ๋‹ค.
c.execute("DROP TABLE IF EXISTS ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ")
c.execute("DROP TABLE IF EXISTS ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ2")
c.execute("DROP TABLE IF EXISTS ๋Œ€์ถœ๋‚ด์—ญ")
c.execute("DROP TABLE IF EXISTS ๋„์„œ๋ช…")

#----- 1st table : ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ -----#
c.execute("CREATE TABLE IF NOT EXISTS ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ (ID varchar, ์ด๋ฆ„ varchar, ๋„์„œID varchar, ๋Œ€์ถœ์ผ varchar, ๋ฐ˜๋‚ฉ์ผ varchar)")
#- ์ƒ์„ฑ(create)๋ฌธ : ํ…Œ์ด๋ธ”๋ช…, ๋ณ€์ˆ˜๋ช…, ๋ณ€์ˆ˜ํƒ€์ž…์„ ์ง€์ •

data = [('101','๋ฌธ๊ฐ•ํƒœ','aaa','2020-06-01','2020-06-05'),
             ('101','๋ฌธ๊ฐ•ํƒœ','ccc','2020-06-20','2020-06-25'),
             ('102','๊ณ ๋ฌธ์˜','bbb','2020-06-01',None),
             ('102','๊ณ ๋ฌธ์˜','ddd','2020-06-08',None),
             ('103','๋ฌธ์ƒํƒœ','ccc','2020-06-01','2020-06-05'),
             ('104','๊ฐ•๊ธฐ๋‘ฅ',None,None,None)]
#- ์ž…๋ ฅํ•  ๋ฐ์ดํ„ฐ๋ฅผ ๊ทธ๋Œ€๋กœ ์ž…๋ ฅ (๋ณ€์ˆ˜๋ช… ์ˆœ์„œ ๊ธฐ์ค€๋Œ€๋กœ)

c.executemany('INSERT INTO ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ VALUES (?,?,?,?,?)', data)
#- ์ž…๋ ฅํ•  ๋ฐ์ดํ„ฐ๋ฅผ ์‹ค์ œ ํ…Œ์ด๋ธ”์— insertํ•˜๊ธฐ
#-----------------------------------------------#

#----- 2nd table : ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ2 -----#
c.execute("CREATE TABLE IF NOT EXISTS ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ2 (ID varchar, ์ด๋ฆ„ varchar, ๋Œ€์ถœ๋…„์›” varchar, ๋Œ€์ถœ์ผ์ˆ˜ varchar)")

data = [('101','๋ฌธ๊ฐ•ํƒœ','2020-06','20์ผ'),
             ('102','๊ณ ๋ฌธ์˜','2020-06','10์ผ'),
             ('103','๋ฌธ์ƒํƒœ','2020-06','8์ผ'),
             ('104','๊ฐ•๊ธฐ๋‘ฅ','2020-06','3์ผ')]
c.executemany('INSERT INTO ๋„์„œ๋Œ€์ถœ๋‚ด์—ญ2 VALUES (?,?,?,?)', data)
#--------------------------------------------------#

#----- 3rd table : ๋Œ€์ถœ๋‚ด์—ญ -----#
c.execute("CREATE TABLE IF NOT EXISTS ๋Œ€์ถœ๋‚ด์—ญ (ID varchar, ์ด๋ฆ„ varchar, ๋„์„œID varchar)")

data = [('101','๋ฌธ๊ฐ•ํƒœ','aaa'),
             ('102','๊ณ ๋ฌธ์˜','bbb'),
             ('102','๊ณ ๋ฌธ์˜','fff'),
             ('103','๋ฌธ์ƒํƒœ','ccc'),
             ('104','๊ฐ•๊ธฐ๋‘ฅ',None)]
c.executemany('INSERT INTO ๋Œ€์ถœ๋‚ด์—ญ VALUES (?,?,?)', data)
#-----------------------------------------#

#----- 4th table : ๋„์„œ๋ช… -----#
c.execute("CREATE TABLE IF NOT EXISTS ๋„์„œ๋ช… (๋„์„œID varchar, ๋„์„œ๋ช… varchar)")

data = [('aaa','์•…๋ชฝ์„ ๋จน๊ณ  ์ž๋ž€ ์†Œ๋…„'),
             ('bbb','์ข€๋น„์•„์ด'),
             ('ccc','๊ณต๋ฃก๋ฐฑ๊ณผ์‚ฌ์ „'),
             ('ddd','๋นจ๊ฐ„๊ตฌ๋‘'),
             ('eee','์ž ์ž๋Š” ์ˆฒ์†์˜ ๋ฏธ๋…€')]

c.executemany('INSERT INTO ๋„์„œ๋ช… VALUES (?,?)', data)
#--------------------------------------#

conn.commit()
conn.close()
print("๋ฟ…๐Ÿ’›")
c.execute("CREATE TABLE IF NOT EXISTS ๋„์„œ๋ช… (๋„์„œID varchar, ๋„์„œ๋ช… varchar)")
  • ๋„์„œ๋ช…์—์„œ CREATE TABLE IF NOT EXISTS ๋„์„œ๋ช… (๋„์„œID varchar, ๋„์„œ๋ช… varchar) ์€ ๋งŒ์•ฝ ํ…Œ์ด๋ธ”์ด ์กด์žฌํ•˜์ง€ ์•Š๋Š”๋‹ค๋ฉด ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑ, ๊ทธ ๋•Œ ๋ณ€์ˆ˜๋ช…๊ณผ ๊ฐ ๋ณ€์ˆ˜ ํƒ€์ž…์„ ์ง€์ •
data = [('aaa','์•…๋ชฝ์„ ๋จน๊ณ  ์ž๋ž€ ์†Œ๋…„'),
             ('bbb','์ข€๋น„์•„์ด'),
             ('ccc','๊ณต๋ฃก๋ฐฑ๊ณผ์‚ฌ์ „'),
             ('ddd','๋นจ๊ฐ„๊ตฌ๋‘'),
             ('eee','์ž ์ž๋Š” ์ˆฒ์†์˜ ๋ฏธ๋…€')]
  • ๊ฐ ๋ณ€์ˆ˜๋ช…์— ๋งž๊ฒŒ ๋ฐ์ดํ„ฐ๋ฅผ ์‹ค์ œ๋กœ ์ƒ์„ฑ
c.executemany('INSERT INTO ๋„์„œ๋ช… VALUES (?,?)', data)
  • INSERT INTO ๋„์„œ๋ช… VALUES (?,?) ๊ฐ ํ…Œ์ด๋ธ” ๋ณ€์ˆ˜์— ์ž…๋ ฅํ•œ ๋ฐ์ดํ„ฐ๋“ค์„ ๋„ฃ๊ฒ ๋‹ค๋Š” ๋œป
  • ? ๋ฅผ ์ด์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ฟผ๋ฆฌ์— ๋ฐ”์ธ๋”ฉ
  • c.executemany() ๋ฉ”์†Œ๋“œ : ํ•œ๊บผ๋ฒˆ์— ์—ฌ๋Ÿฌ๊ฐœ์˜ ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌ ๊ฐ€๋Šฅ
  • ๋ฐ์ดํ„ฐ ์กฐํšŒ
conn = sqlite3.connect(db_path)
c = conn.cursor()

for row in c.execute('SELECT * FROM ๋„์„œ๋ช…'):
  print(row)
#๊ฒฐ๊ณผ
('aaa','์•…๋ชฝ์„ ๋จน๊ณ  ์ž๋ž€ ์†Œ๋…„'),
('bbb','์ข€๋น„์•„์ด'),
('ccc','๊ณต๋ฃก๋ฐฑ๊ณผ์‚ฌ์ „'),
('ddd','๋นจ๊ฐ„๊ตฌ๋‘'),
('eee','์ž ์ž๋Š” ์ˆฒ์†์˜ ๋ฏธ๋…€')

7. ๋ฐ์ดํ„ฐ ํƒ€์ž… ์ •์˜ ํ™•์ธ : pragma table_info('ํ…Œ์ด๋ธ”๋ช…')

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)  # mydb.db์— ์—ฐ๊ฒฐํ•ฉ๋‹ˆ๋‹ค.
c = conn.cursor()

for row in c.execute('pragma table_info(๋„์„œ๋Œ€์ถœ๋‚ด์—ญ)'):
  print(row)

'Programming > SQL' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

[HackerRank] Basic Select : Japanese Cities' Attributes (MySQL)  (0) 2022.02.03
[HackerRank] Basic Select : Select By ID (MySQL)  (0) 2022.02.03
[HackerRank] Basic Select : Select All (MySQL)  (0) 2022.02.03
SQL ๊ธฐ๋ณธ  (0) 2021.11.16
MySQL ์ •๋ฆฌ  (0) 2021.01.25