[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 가 있다. 

* 실제로 일하는 건 cursor 가 다한다.

 

dbpath 변수는 데이터베이스 파일의 경로를 지정하는 곳이고 파일이 없으면 새로 생성한다.

---

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[, parameters])

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개 만큼의 데이타를 한꺼번에 가져올 때 사용된다.

* print(row) : 각 row는 Tuple로 리턴되며, 컬럼 순서대로 데이타가 표시된다.

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()