[SQL] Python 에서 SQL 다루기

1. Python에서 데이터베이스에 연결하는 법

import sqlite3 # built-in library (Python 2.x & 3.x)

dbpath = "chinook.db" 

conn = sqlite3.connect(dbpath)
cur = conn.cursor() 

• Python에서 sql을 사용할 때에는 먼저 db와 연결해주는 connect함수와 실제로 그안에서
  일을 하는 cursor 가 있다.
  

2. python 에서 SQL에 데이터를 삽입하는 방법

script = """
DROP TABLE IF EXISTS employees;

CREATE TABLE employees(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL, 
salary REAL,
department TEXT,
position TEXT,
hireDate TEXT);

INSERT INTO employees(name, salary, department, position, hireDate) VALUES('Dave', 300, 'Marketing', 'LV1', '2020-01-01');
INSERT INTO employees(name, salary, department, position, hireDate) VALUES('Clara', 420, 'Sales', 'LV2', '2018-01-11');
INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(3, 'Jane', 620, 'Developer', 'LV4', '2015-11-01');
INSERT INTO employees VALUES(4, 'Peter', 530, 'Developer', 'LV2', '2020-11-01'); 
"""

cur.executescript(script)
conn.commit() # 실제로 DB에 위 Table & Data가 저장된다.

• 실제 쿼리문을 작성할 때는 문자열로 작성해서 cur객체에 executescript()로 쿼리문을 전달하고 데이터베이스의 commit 과 동일하게 변경을 적용한다.

execute(sql[, parameters])

• execute()는 단일 SQL 문만 실행합니다.
  하나 이상의 명령문을 실행하려고 하면 Warning이 발생합니다.
  하나의 호출로 여러 SQL 문을 실행하려면 executescript()를 사용하십시오.

executemany(sql[, parameters])

• 시퀀스 seq_of_parameters에 있는 모든 매개 변수 시퀀스나 매핑에 대해 SQL 명령을 실행합니다.
  sqlite3 모듈은 시퀀스 대신 매개 변수를 산출하는 이터레이터도 허용합니다.

executescript(sql_script)

• 하나의 호출로 여러 SQL 문을 실행하려면 executescript()를 사용하십시오.
  이것은 한 번에 여러 SQL 문을 실행하기 위한 비표준 편의 메서드입니다.
  먼저 COMMIT 문을 실행한 다음, 매개 변수로 가져온 SQL 스크립트를 실행합니다.

쿼리문 전달 방법은 위의 3가지가 있는 것이다.

예)

executemany(sql[,parameter])

data = [('Elena', 510, 'Recruiter', 'LV3', '2020-07-01'), 
        ('Sujan', 710, 'HR', 'LV5', '2014-06-01'),
        ('Jake', 210, 'CEO', 'LV8', '2012-01-01')]

cur.executemany("INSERT INTO employees(name, salary, department, position, hireDate) 
					VALUES(?, ?, ?, ?, ?)", data)
conn.commit()

• VALUES(?, ?, ?, ?, ?) 의 값의 data 로 들어간 튜플이 각각 대입하는 방법이다.

3. Database에서 Python 으로 가져오는 방법

• fetchall() 모든 데이타를 한꺼번에 클라이언트로 가져올 때 사용된다.
• fetchone() 한번 호출에 하나의 Row 만을 가져올 때 사용된다.
• fetchmany(n) n개 만큼의 데이타를 한꺼번에 가져올 때 사용된다.

예)

cur.execute("SELECT * FROM employees;")

employee_list = cur.fetchall()

for employee in employee_list:
    print(employee)

3. 1 Database에서 pandas Dataframe으로 가져오는 방법

import pandas as pd

df = pd.read_sql_query("SELECT * FROM employees", conn) 

4. 연결해제

conn.close()

• 연결을 Close 해주어야 DB 파일인 .sqlite 파일의 삭제 등이 가능합니다.

5. 데이터베이스 삽입과 데이터프레임변환 한번에 하기

script = """
INSERT INTO artists (name)
VALUES
    ("Buddy Rich"),
    ("Candido"),
    ("Charlie Byrd");
""" 
cur.execute(script) 

script = """
SELECT
    ArtistId,
    Name
FROM
    Artists
ORDER BY
    ArtistId DESC;
""" 
cur.execute(script) 

df = pd.read_sql_query(script, conn)
df.head()

실제 예제)

• 클롤링 도중 하나하나씩 db파일에 데이터를 삽입하는 것

for li in boxItems:
      title = li.find_element_by_class_name('proTit').text # li.find_element_by_css_selector('h5.proTit').text
      price = li.find_element_by_class_name('proPrice').text.replace(',','').replace('원~','') # li.find_element_by_css_selector('.proPrice')
      image = li.find_element_by_class_name('img').get_attribute('src')

      sql_query = "INSERT INTO tour_crawl(title, price, image) values('{}',{},'{}')".format(title, price, image) # TEXT인 제목은 ''로 감싸주는 것에 유의
      print('SQL Query :', sql_query[:90], "...")

      cur.execute(sql_query)
      conn.commit()

이상으로 Python 에서 SQL을 다루는 방법에 대한 설명을 마치도록 하겠습니다.

감사합니다.

더 나은 개발이 되길 바라며:)