코드 보기
import warnings
from pathlib import Path
warnings.filterwarnings("ignore")데이터베이스 (Databases)
서론
엄청난 양의 데이터가 데이터베이스에 살고 있으므로, 데이터베이스에 접근하는 방법을 아는 것은 필수적입니다. 때로는 누군가에게 스냅샷을 .csv 파일로 다운로드해 달라고 부탁할 수도 있지만, 이는 곧 고통스러워집니다. 변경 사항이 생길 때마다 다른 사람과 소통해야 하기 때문입니다. 여러분은 필요할 때 필요한 데이터를 얻기 위해 데이터베이스에 직접 손을 뻗을 수 있기를 원할 것입니다.
이 장에서는 먼저 SQL 데이터베이스와 상호 작용할 수 있는 파이썬 패키지의 기초를 배웁니다. 즉, 데이터베이스에 연결하고 SQL1 쿼리로 데이터를 가져오는 방법을 알아봅니다. SQL은 structured query language의 약자로 데이터베이스의 공용어이며, 모든 데이터 과학자가 배워야 할 중요한 언어입니다. 이 장이 끝날 때쯤 SQL 마스터가 되지는 않겠지만, 가장 중요한 구성 요소를 식별하고 그것들이 무엇을 하는지 이해할 수 있게 될 것입니다.
사전 준비
이 장에서는 pandas, SQLModel, ibis 패키지가 필요합니다. 아마 이미 pandas는 설치되어 있을 것입니다. SQLModel과 ibis를 설치하려면 컴퓨터의 명령줄에서 각각 pixi add sqlmodel과 pixi add ibis-framework를 실행하세요. 먼저 일반적인 패키지들을 불러오고 자세한 경고 메시지들을 끄겠습니다.
데이터베이스 기초
가장 단순한 수준에서 데이터베이스는 데이터베이스 용어로 테이블(tables)이라고 불리는 데이터 프레임들의 집합이라고 생각할 수 있습니다. pandas 데이터 프레임과 마찬가지로, 데이터베이스 테이블은 이름이 지정된 열들의 집합이며, 열의 모든 값은 동일한 타입입니다. 데이터 프레임과 데이터베이스 테이블 사이에는 세 가지 높은 수준의 차이점이 있습니다:
데이터베이스 테이블은 디스크(즉, 파일)에 저장되며 임의로 커질 수 있습니다. 데이터 프레임은 메모리에 저장되며 근본적으로 제한이 있습니다(비록 그 제한이 많은 문제에 충분히 클지라도 말이죠). 디스크와 메모리의 차이를 장기 기억과 단기 기억의 차이와 같다고 생각할 수 있습니다(후자는 용량이 훨씬 제한적입니다).
데이터베이스 테이블에는 거의 항상 인덱스가 있습니다. 책의 인덱스와 마찬가지로, 데이터베이스 인덱스를 사용하면 모든 행을 일일이 살펴보지 않고도 관심 있는 행을 빠르게 찾을 수 있습니다.
대부분의 고전적인 데이터베이스는 기존 데이터 분석이 아니라 빠른 데이터 수집에 최적화되어 있습니다. 이러한 데이터베이스는 데이터가 열 단위가 아니라 행 단위로 저장되기 때문에 행 지향(row-oriented)이라고 불립니다. 최근에는 기존 데이터 분석을 훨씬 빠르게 만들어주는 열 지향(column-oriented) 데이터베이스가 많이 개발되었습니다.
데이터베이스는 데이터베이스 관리 시스템(줄여서 DBMS)에 의해 운영되며, 크게 세 가지 형태로 제공됩니다:
- 클라이언트-서버 DBMS는 강력한 중앙 서버에서 실행되며, 여러분의 컴퓨터(클라이언트)에서 연결합니다. 조직 내 여러 사람과 데이터를 공유하는 데 적합합니다. 인기 있는 클라이언트-서버 DBMS로는 PostgreSQL, MariaDB, SQL Server, Oracle 등이 있습니다.
- 클라우드 DBMS는 Snowflake, Amazon RedShift, Google BigQuery와 같이 클라이언트-서버 DBMS와 유사하지만 클라우드에서 실행됩니다. 즉, 매우 큰 데이터셋을 쉽게 처리할 수 있고 필요에 따라 자동으로 더 많은 계산 리소스를 제공할 수 있습니다.
- 인-프로세스(In-process) DBMS는 SQLite나 duckdb처럼 여러분의 컴퓨터에서 전적으로 실행됩니다. 여러분이 주 사용자인 대규모 데이터셋 작업을 하는 데 적합합니다.
데이터베이스 연결하기
파이썬에서 데이터베이스에 연결하는 데는 여러 옵션이 있지만, 어떤 종류의 데이터베이스에 연결하느냐에 따라 달라집니다. 단계(예: 데이터베이스 연결, 데이터 업로드, SQL 실행)는 비슷하므로 하나의 예시인 SQLite 데이터베이스를 선택해도 큰 무리는 없을 것입니다. SQLite는 작고 빠르며 독립적인 SQL 데이터베이스 엔진으로, 세계에서 가장 많이 사용되는 데이터베이스 엔진입니다. 여러분의 컴퓨터와 휴대폰에 있는 많은 데이터가 실제로는 SQLite 데이터베이스에 들어 있을 것입니다. (Simon Willison은 여러분이 만든 데이터 중 일부를 내보내는 데 도움이 되는 훌륭한 도구들을 가지고 있습니다!)
어떤 종류의 SQL 데이터베이스에 연결하든 항상 두 단계가 필요합니다:
데이터베이스와의 연결을 제공하는 데이터베이스 인터페이스를 항상 사용하게 됩니다. 예: 파이썬에 내장된 sqlite 패키지
또한 데이터를 데이터베이스로 밀어 넣거나 가져오는 패키지를 사용하게 됩니다. 예: pandas
연결의 구체적인 세부 사항은 DBMS마다 매우 다르기 때문에 안타깝게도 여기에서 모든 세부 사항을 다룰 수는 없습니다. 초기 설정에는 약간의 시행착오(그리고 아마도 약간의 검색)가 필요하겠지만, 일반적으로 한 번만 설정하면 됩니다. 여기서는 몇 가지 기초를 다루기 위해 최선을 다하겠습니다.
이 책을 위해 클라이언트-서버나 클라우드 DBMS를 설정하는 것은 번거로울 것이므로, 대신 컴퓨터 내부 DBMS를 사용하겠습니다.
데이터베이스 직접 다루기
음악 상점의 아티스트, 노래, 앨범 정보와 상점 직원, 고객 및 고객의 구매 정보를 포함하고 있는 Chinook 데이터베이스라는 작은 SQLite 데이터베이스에 연결해 보겠습니다. 이 정보는 11개의 테이블에 담겨 있습니다. 아래 그림은 데이터 스키마를 보여줍니다:
이 책의 깃허브 저장소 여기에서 Chinook.sqlite 파일을 다운로드할 수 있습니다. 아래 연습 문제를 따라 하려면 여러분의 컴퓨터에서 코드가 실행되는 위치를 기준으로 ’data’라는 하위 디렉토리에 저장해야 합니다.
우선, 파이썬에 내장된 sqlite3 엔진을 사용하여 데이터베이스에 연결하고 ‘Artist’ 테이블에서 처음 10개 항목을 선택하는 아주 간단한 SQL 쿼리를 실행해 보겠습니다:
코드 보기
import sqlite3
con = sqlite3.connect(Path("data/Chinook.sqlite"))
cursor = con.execute("SELECT * FROM Artist LIMIT 10;")
rows = cursor.fetchall()
rows[(1, 'AC/DC'),
(2, 'Accept'),
(3, 'Aerosmith'),
(4, 'Alanis Morissette'),
(5, 'Alice In Chains'),
(6, 'Antônio Carlos Jobim'),
(7, 'Apocalyptica'),
(8, 'Audioslave'),
(9, 'BackBeat'),
(10, 'Billy Cobham')]여기서 출력은 튜플(tuple)이라고 불리는 파이썬 객체 형태입니다. 이를 pandas 데이터 프레임에 넣고 싶다면 바로 전달하면 됩니다:
코드 보기
import pandas as pd
pd.DataFrame(rows)| 0 | 1 | |
|---|---|---|
| 0 | 1 | AC/DC |
| 1 | 2 | Accept |
| 2 | 3 | Aerosmith |
| 3 | 4 | Alanis Morissette |
| 4 | 5 | Alice In Chains |
| 5 | 6 | Antônio Carlos Jobim |
| 6 | 7 | Apocalyptica |
| 7 | 8 | Audioslave |
| 8 | 9 | BackBeat |
| 9 | 10 | Billy Cobham |
또 다른 유용한 힌트는 열 이름을 잘 모를 경우 다음을 통해 얻을 수 있다는 점입니다:
코드 보기
[i[0] for i in cursor.description]['ArtistId', 'Name']데이터베이스 생성하기
나중에 데이터 조각들에 (효율적으로) 접근하기 위해 SQL 데이터베이스를 만들고 싶을 때가 많습니다. sqlite 패키지를 직접 사용하여 테스트 데이터베이스를 만들어 보겠습니다. 이 과정은 CREATE TABLE 문으로 시작하여 테이블 이름을 쓰고, 그 뒤에 열 이름과 데이터 타입을 나열합니다.
코드 보기
create_query = """CREATE TABLE test (country VARCHAR(20), gdp REAL, health INTEGER);"""
con_new = sqlite3.connect("data/test_database.sqlite")
con_new.execute(create_query)
con_new.commit()이 코드가 실행되면 테스트 데이터베이스가 생성된 것입니다! (여러분의 컴퓨터에서 data 디렉토리를 확인하여 작동했는지 볼 수 있지만, data 디렉토리가 아직 존재하지 않는다면 에러가 발생할 것입니다.)
이제 데이터베이스를 몇 가지 값으로 채워보겠습니다:
코드 보기
test_data = [("US", 1, 3), ("UK", 0.6, 2), ("France", 0.8, 1)]
con_new.executemany("INSERT INTO test VALUES(?, ?, ?)", test_data)
con_new.commit()마지막으로, 제대로 작동했는지 확인해 보겠습니다:
코드 보기
con_new.execute("SELECT * FROM test").fetchall()[('US', 1.0, 3), ('UK', 0.6, 2), ('France', 0.8, 1)]와아, 성공했네요!
기초 SQL 쿼리
SQL 쿼리에 대한 전체 설명은 이 책의 범위를 벗어나지만, 여러분이 기초를 갖출 수 있도록 노력하겠습니다. SQL의 최상위 구성 요소를 문(statements)이라고 합니다. 일반적인 문으로는 새 테이블을 정의하기 위한 CREATE, 데이터를 추가하기 위한 INSERT, 데이터를 검색하기 위한 SELECT가 있습니다. 우리는 SELECT 문(또는 쿼리)에 집중할 것인데, 이는 데이터 과학자로서 거의 이것만 사용하게 되기 때문입니다.
쿼리는 절(clauses)로 구성됩니다. SELECT, FROM, WHERE, ORDER BY, GROUP BY, LIMIT라는 여섯 가지 중요한 절이 있습니다. 모든 쿼리에는 SELECT2 절과 FROM3 절이 있어야 하며 가장 간단한 쿼리는 SELECT * FROM table로, “table”이라는 이름의 지정된 테이블에서 모든 열을 선택합니다. WHERE와 ORDER BY는 어떤 행을 포함할지 그리고 어떻게 정렬할지를 제어합니다. GROUP BY는 쿼리를 요약으로 변환하여 집계(aggregation)가 일어나게 합니다. LIMIT은 반환되는 행의 수를 제한합니다.
중요: SQL에서는 순서가 중요합니다. 절은 항상 SELECT, FROM, WHERE, GROUP BY, ORDER BY 순서로 작성해야 합니다. 혼란스럽게도 이 순서는 실제 평가 순서인 FROM, WHERE, GROUP BY, SELECT, ORDER BY와는 일치하지 않습니다.
이들 중 몇 가지가 실제로 작동하는 것을 보겠습니다. 몇 개의 항목을 가져오려면 다음과 같이 합니다.
SELECT * FROM Artist LIMIT 10;특정 열을 지정하려면 다음과 같이 합니다.
SELECT name FROM Artist LIMIT 10;순서를 거꾸로 하려면 “ORDER BY” 다음에 “DESC”를 사용하세요. 예를 들어, 이 쿼리는 가장 긴 트랙 10개를 알려줍니다.
SELECT name, milliseconds FROM track ORDER BY milliseconds DESC LIMIT 10;또 다른 주요 기능은 필터링 기능입니다. 예를 들어, 길이가 최소 3분 이상인 첫 10개 트랙을 요청할 수 있습니다. 이것을 실제로 실행해 보겠습니다:
코드 보기
sql_query = "SELECT name, milliseconds FROM track WHERE milliseconds > 1e3*3*60 ORDER BY milliseconds ASC LIMIT 10;"
cursor = con.execute(sql_query)
rows = cursor.fetchall()
rows[('Bodies', 180035),
('Vivo Isolado Do Mundo', 180035),
('Elvis Ate America', 180166),
('Remote Control', 180297),
('Promises', 180401),
('Emergency', 180427),
('À Vontade (Live Mix)', 180636),
('Hyperconectividade', 180636),
('On Fire', 180636),
('Fascinação', 180793)]groupby를 시도해 보겠습니다. 이 쿼리는 앨범별로 그룹화하여 (첫 5개) 앨범의 평균 트랙 길이를 분 단위로 찾습니다:
코드 보기
sql_groupby = "SELECT albumid, AVG(milliseconds)/1e3/60 FROM track GROUP BY albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;"
cursor = con.execute(sql_groupby)
rows = cursor.fetchall()
rows[(340, 0.863),
(345, 1.11065),
(318, 1.6882166666666667),
(314, 1.69135),
(328, 1.8377666666666668)]조인 (Joins)
pandas의 조인에 익숙하다면 SQL 조인도 매우 유사하다는 것을 알 수 있습니다. ‘album’ 테이블과 ‘track’ 테이블을 조인하여 위 쿼리에서 앨범의 이름을 찾을 수 있는지 보겠습니다.
둘 이상의 테이블에 동일한 열 이름이 있는 즉시, 해당 열 이름을 사용할 때 어떤 테이블을 가리키는지 명시해야 한다는 점에 유의하세요. 조인에는 여러 옵션(INNER, LEFT 등)이 있으며 여기에서 더 자세히 알아볼 수 있습니다.
코드 보기
sql_join = "SELECT track.albumid, AVG(milliseconds)/1e3/60, album.title FROM track INNER JOIN album ON (track.albumid = album.albumid) GROUP BY album.albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;"
cursor = con.execute(sql_join)
rows = cursor.fetchall()
rows[(340, 0.863, "Liszt - 12 Études D'Execution Transcendante"),
(345, 1.11065, "Monteverdi: L'Orfeo"),
(318, 1.6882166666666667, 'SCRIABIN: Vers la flamme'),
(314, 1.69135, 'English Renaissance'),
(328, 1.8377666666666668, 'Charpentier: Divertissements, Airs & Concerts')]함수 (Functions)
우리는 이미 한 가지 함수인 AVG를 몰래 사용했습니다. 열에 적용할 수 있는 다른 함수들로는 SUM, MIN, MAX, FIRST, LAST 및 (데이터 과학자의 친구인) COUNT가 있습니다. SQL 함수에 대한 자세한 내용은 여기에서 확인할 수 있습니다.
Datasette으로 SQL 데이터베이스 탐색하기
SQL은 처음에는 꽤 위협적일 수 있지만, 이를 더 잘 이해하기 위한 훌륭한 도구로 Datasette가 있습니다. Datasette은 데이터베이스를 탐색(및 생성, 공유)하기 위한 도구입니다. 저자는 “Datasette은 데이터 저널리스트, 박물관 큐레이터, 사서, 지방 정부, 과학자, 연구자 및 데이터를 세상과 공유하고 싶어 하는 모든 사람을 대상으로 합니다. 이는 정형 데이터를 최대한 생산적으로 다룰 수 있도록 고안된 40개의 도구와 99개의 플러그인으로 구성된 더 넓은 생태계의 일부입니다.”라고 말합니다.
이런 맥락에서 Datasette은 두 가지 방식으로 유용합니다:
- 클릭만으로 데이터베이스를 대화형으로 탐색할 수 있습니다.
- 필터링 작업에서 SQL
SELECT문이 어떻게 구성되는지 또는 그 반대의 과정을 볼 수 있습니다.
기본적으로 Datasette이 실행되면 데이터셋의 모든 테이블을 보여주는 랜딩 웹페이지(여러분의 컴퓨터에서 실행 중이더라도!)가 나타납니다. 테이블을 클릭하여 탐색, 재정렬, 필터링 등을 할 수 있습니다. 또한 어떤 컷을 만들든 그에 해당하는 SQL 쿼리를 볼 수 있으며, 상자에 SQL 쿼리를 직접 입력하여 원하는 데이터를 가져올 수도 있습니다. 하지만 Datasette은 SELECT 문만 지원한다는 점에 유의하세요.
유용한 추가 기능은 원하는 대로 필터링한 후 SQL 쿼리 결과를 웹페이지에서 CSV 또는 JSON 파일로 다운로드할 수 있다는 점입니다.
Datasette을 사용하여 데이터를 탐색하는 몇 가지 방법이 있습니다:
- 여러분의 컴퓨터에서 직접 실행할 수 있습니다.
- (이미 클라우드에 호스팅된) 온라인 버전을 사용해 볼 수 있습니다. 예: 이 발전소 데이터베이스
- 온라인 코딩 서비스인 glitch를 사용하여 실행할 수 있습니다. 여기에서 예시를 확인하세요.
Datasette은 파이썬 패키지로 제공되므로 명령줄에서 pixi global install datasette를 실행하여 설치할 수 있습니다. 파이썬 환경에 설치한 후 다음을 실행하세요.
datasette path/to/database.db -o그러면 기본 브라우저가 즉시 열려 데이터베이스의 테이블을 보여주는 페이지가 나타납니다(페이지 주소는 http://localhost:8001/로 시작합니다).
마지막으로 Datasette을 사용하는 SQL 교육 자료가 여기에 있습니다.
**연습 문제 여러분의 컴퓨터에 있는 Chinook 데이터베이스를 사용하여, 앞서 보았던 조인이 포함된 SQL 쿼리를 실행해 보세요.
SELECT
albumid,
AVG(milliseconds) / 1e3 / 60
FROM
track
GROUP BY
albumid
ORDER BY
AVG(milliseconds) ASC
LIMIT
5
```**
>
>
## **pandas**로 SQL 다루기
**pandas**는 SQL 작업에 아주 잘 준비되어 있습니다. `read_sql()` 함수를 사용하여 방금 만든 쿼리를 바로 전달할 수 있습니다. 단, 데이터베이스에 대해 생성한 연결(connection)도 함께 전달해야 한다는 점을 기억하세요:
::: {#f5c83909 .cell execution_count=12}
``` {.python .cell-code}
pd.read_sql(sql_join, con)| AlbumId | AVG(milliseconds)/1e3/60 | Title | |
|---|---|---|---|
| 0 | 340 | 0.863000 | Liszt - 12 Études D'Execution Transcendante |
| 1 | 345 | 1.110650 | Monteverdi: L'Orfeo |
| 2 | 318 | 1.688217 | SCRIABIN: Vers la flamme |
| 3 | 314 | 1.691350 | English Renaissance |
| 4 | 328 | 1.837767 | Charpentier: Divertissements, Airs & Concerts |
:::
이 기능의 장점 중 하나는 SQL의 열 이름이 데이터 프레임의 열 이름으로 바로 전달된다는 것입니다.
이제 Visual Studio Code에서 파이썬을 작성할 때(최소한 파이썬 익스텐션이 설치되어 있다면), 고품질의 구문 강조 및 자동 완성 지원을 받을 수 있습니다. 또한 파이썬 언어의 확장 기능을 사용하면 다루는 변수의 타입에 대해 매우 세심한 관리가 가능합니다. 파이썬을 통해 SQL 데이터베이스에 접근할 때도 이 모든 혜택을 누릴 수 있다면 좋지 않을까요? 다음에 살펴볼 두 패키지가 바로 그것을 제공합니다. 두 패키지 모두 파이썬에서 SQL 데이터베이스 작업을 훨씬 쉽고 생산적으로 만들어 줍니다.
ibis로 SQL 다루기
SQL 쿼리를 텍스트로 일일이 작성해야 하는 것은 그리 만족스럽지 못합니다. 만약 pandas 명령어로 바로 명령을 내릴 수 있다면 어떨까요? 완전히 똑같이 할 수는 없지만, ibis라는 패키지를 사용하면 상당히 근접하게 할 수 있습니다. ibis는 데이터베이스에서 데이터를 읽어와서 마치 pandas 데이터 프레임처럼 쿼리하고 싶을 때 특히 유용합니다.
Ibis는 로컬 데이터베이스(예: SQLite), 서버 기반 데이터베이스(예: Postgres) 또는 클라우드 기반 데이터베이스(예: Google BigQuery)에 연결할 수 있습니다. 연결 구문은 예를 들어 ibis.bigquery.connect와 같습니다.
로컬에 호스팅된 Chinook 데이터베이스에서 이미 본 명령어들을 재현하며 ibis가 작동하는 것을 보겠습니다. 먼저 패키지를 불러오고 데이터베이스에 연결합니다.
코드 보기
import ibis
ibis.options.interactive = True
connection = ibis.sqlite.connect("data/Chinook.sqlite")
track = connection.table("track")
track.head()┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string(200) │ int64 │ !int64 │ int64 │ string(220) │ !int64 │ int64 │ !decimal(10, 2) │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────────────────────────────────────────────────────────┼──────────────┼──────────┼─────────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ │ 2 │ Balls to the Wall │ 2 │ 2 │ 1 │ NULL │ 342562 │ 5510424 │ 0.99 │ │ 3 │ Fast As a Shark │ 3 │ 2 │ 1 │ F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman │ 230619 │ 3990994 │ 0.99 │ │ 4 │ Restless and Wild │ 3 │ 2 │ 1 │ F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman │ 252051 │ 4331779 │ 0.99 │ │ 5 │ Princess of the Dawn │ 3 │ 2 │ 1 │ Deaffy & R.A. Smith-Diesel │ 375418 │ 6290521 │ 0.99 │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────┴──────────────┴──────────┴─────────────────┘
좋습니다. 이제 다음 쿼리를 재현해 보겠습니다: “SELECT albumid, AVG(milliseconds)/1e3/60 FROM track GROUP BY albumid ORDER BY AVG(milliseconds) ASC LIMIT 5;”. 우리는 groupby와 mutate(pandas의 assign 문과 같다고 생각하면 됩니다), sort를 사용한 다음, 상위 5개 항목만 보여주기 위해 limit()를 사용하겠습니다.
코드 보기
track.group_by("AlbumId").mutate(
mean_mins_track=track.Milliseconds.mean() / 1e3 / 60
).order_by("mean_mins_track").limit(5)┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ mean_mins_track ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string(200) │ int64 │ !int64 │ int64 │ string(220) │ !int64 │ int64 │ !decimal(10, 2) │ float64 │ ├─────────┼──────────────────────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────┼──────────────┼─────────┼─────────────────┼─────────────────┤ │ 3496 │ Étude 1, In C Major - Preludio (Presto) - Liszt │ 340 │ 4 │ 24 │ NULL │ 51780 │ 2229617 │ 0.99 │ 0.863000 │ │ 3501 │ L'orfeo, Act 3, Sinfonia (Orchestra) │ 345 │ 2 │ 24 │ Claudio Monteverdi │ 66639 │ 1189062 │ 0.99 │ 1.110650 │ │ 3452 │ SCRIABIN: Prelude in B Major, Op. 11, No. 11 │ 318 │ 4 │ 24 │ NULL │ 101293 │ 3819535 │ 0.99 │ 1.688217 │ │ 3448 │ Lamentations of Jeremiah, First Set \ Incipit Lamentatio │ 314 │ 2 │ 24 │ Thomas Tallis │ 69194 │ 1208080 │ 0.99 │ 1.691350 │ │ 3492 │ Sing Joyfully │ 314 │ 2 │ 24 │ William Byrd │ 133768 │ 2256484 │ 0.99 │ 1.691350 │ └─────────┴──────────────────────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────┴──────────────┴─────────┴─────────────────┴─────────────────┘
코드 보기
track.group_by("AlbumId").mutate(mean_mins_track=track.Milliseconds.mean() / 1e3 / 60)┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ mean_mins_track ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ !int64 │ !string(200) │ int64 │ !int64 │ int64 │ string(220) │ !int64 │ int64 │ !decimal(10, 2) │ float64 │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼───────────────────────────────────────────┼──────────────┼──────────┼─────────────────┼─────────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ 4.000692 │ │ 6 │ Put The Finger On You │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205662 │ 6713451 │ 0.99 │ 4.000692 │ │ 7 │ Let's Get It Up │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 233926 │ 7636561 │ 0.99 │ 4.000692 │ │ 8 │ Inject The Venom │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 210834 │ 6852860 │ 0.99 │ 4.000692 │ │ 9 │ Snowballed │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 203102 │ 6599424 │ 0.99 │ 4.000692 │ │ 10 │ Evil Walks │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263497 │ 8611245 │ 0.99 │ 4.000692 │ │ 11 │ C.O.D. │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 199836 │ 6566314 │ 0.99 │ 4.000692 │ │ 12 │ Breaking The Rules │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263288 │ 8596840 │ 0.99 │ 4.000692 │ │ 13 │ Night Of The Long Knives │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205688 │ 6706347 │ 0.99 │ 4.000692 │ │ 14 │ Spellbound │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 270863 │ 8817038 │ 0.99 │ 4.000692 │ │ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴───────────────────────────────────────────┴──────────────┴──────────┴─────────────────┴─────────────────┘
조인은 어떨까요? 물론 이 또한 가능합니다. 예시로, ‘genre’ 테이블과 ‘track’ 테이블을 공통 변수인 “GenreId”를 기준으로 조인해 보겠습니다.
코드 보기
genre = connection.table("genre")
genre_and_track = track.inner_join(
genre, predicates=track["GenreId"] == genre["GenreId"]
)
genre_and_track┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓ ┃ TrackId ┃ Name ┃ AlbumId ┃ MediaTypeId ┃ GenreId ┃ Composer ┃ Milliseconds ┃ Bytes ┃ UnitPrice ┃ Name_right ┃ ┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩ │ int64 │ string(200) │ int64 │ int64 │ int64 │ string(220) │ int64 │ int64 │ decimal(10, 2) │ string(120) │ ├─────────┼─────────────────────────────────────────┼─────────┼─────────────┼─────────┼────────────────────────────────────────────────────────────────────────┼──────────────┼──────────┼────────────────┼─────────────┤ │ 1 │ For Those About To Rock (We Salute You) │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 343719 │ 11170334 │ 0.99 │ Rock │ │ 2 │ Balls to the Wall │ 2 │ 2 │ 1 │ NULL │ 342562 │ 5510424 │ 0.99 │ Rock │ │ 3 │ Fast As a Shark │ 3 │ 2 │ 1 │ F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman │ 230619 │ 3990994 │ 0.99 │ Rock │ │ 4 │ Restless and Wild │ 3 │ 2 │ 1 │ F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman │ 252051 │ 4331779 │ 0.99 │ Rock │ │ 5 │ Princess of the Dawn │ 3 │ 2 │ 1 │ Deaffy & R.A. Smith-Diesel │ 375418 │ 6290521 │ 0.99 │ Rock │ │ 6 │ Put The Finger On You │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 205662 │ 6713451 │ 0.99 │ Rock │ │ 7 │ Let's Get It Up │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 233926 │ 7636561 │ 0.99 │ Rock │ │ 8 │ Inject The Venom │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 210834 │ 6852860 │ 0.99 │ Rock │ │ 9 │ Snowballed │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 203102 │ 6599424 │ 0.99 │ Rock │ │ 10 │ Evil Walks │ 1 │ 1 │ 1 │ Angus Young, Malcolm Young, Brian Johnson │ 263497 │ 8611245 │ 0.99 │ Rock │ │ … │ … │ … │ … │ … │ … │ … │ … │ … │ … │ └─────────┴─────────────────────────────────────────┴─────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────┴──────────────┴──────────┴────────────────┴─────────────┘
Ibis는 여기서 보여드린 것보다 훨씬 많은 일들을 할 수 있습니다. 지연 평가(lazy evaluation), 지리 공간 작업, 타이핑, 그리고 당연히 여러분이 기대하는 모든 일반적인 SQL 명령어들이 포함됩니다. 자세한 내용은 문서를 참고하세요.
SQLModel로 SQL 다루기
Ibis만이 파이썬에서 SQL 작업을 수행하기 위한 똑똑한 옵션은 아닙니다. SQLModel 또한 훌륭한 선택지입니다. 프로덕션 수준의 SQL 데이터베이스를 만들거나, 매우 풍부한 지원을 바탕으로 ibis와는 약간 다른 스타일의 구문을 사용하여 쿼리하고 싶을 때 sqlmodel을 사용하세요.
sqlmodel의 주요 강점 몇 가지는 환상적인 자동 완성 지원과 데이터 타입에 매우 엄격하다는 점입니다(이는 특히 데이터베이스를 생성할 때 장기적으로 시간을 아껴줄 것입니다).
먼저 터미널에서 pixi add sqlmodel을 실행하여 패키지가 설치되어 있는지 확인하세요.
SQLModel로 SQL 데이터베이스 생성하기
“hero”라는 이름의 SQL 테이블이 있고 다음과 같은 데이터를 담고 싶다고 가정해 보겠습니다:
| id | name | secret_name | age |
|---|---|---|---|
| 1 | Deadpond | Dive Wilson | null |
| 2 | Spider-Boy | Pedro Parqueador | null |
| 3 | Rusty-Man | Tommy Sharp | 48 |
| 4 | Ms Amazing | Barjabeen Bhabra | 17 |
이제 SQL 테이블을 만들기 위해 클래스(class)라는 구조를 작성합니다. 클래스는 함수와 비슷해 보이지만, 이 클래스의 본문에는 필드 이름을 나열하고 그 뒤에 해당 데이터의 타입을 나타내는 파이썬 코드를 작성합니다:
코드 보기
from typing import Optional
from sqlmodel import Field, Session, SQLModel, create_engine
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None그런 다음 데이터 행을 생성합니다(프로그래밍 방식으로 할 수도 있지만 여기서는 직접 작성하겠습니다). 아래에서 필드들에 대한 자동 완성이 지원된다는 점에 유의하세요!
코드 보기
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Ms Amazing", secret_name="Barjabeen Bhabra", age=17)이제 실제 데이터베이스를 생성하고 연결해야 합니다. 구문은 데이터베이스 타입 이름(여기서는 sqlite) 다음에 :///가 오고, 그 뒤에 데이터베이스를 저장하고 싶은 상대 경로를 적습니다.
코드 보기
engine = create_engine("sqlite:///data/hero.db")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.commit()이제 직접적인 방식(즉, sqlite3 패키지 사용)을 통해 우리가 만든 데이터베이스로부터 내용을 읽어올 수 있습니다:
코드 보기
con = sqlite3.connect(Path("data/hero.db"))
con.execute("SELECT * FROM hero;").fetchall()[(1, 'Deadpond', 'Dive Wilson', None),
(2, 'Spider-Boy', 'Pedro Parqueador', None),
(3, 'Rusty-Man', 'Tommy Sharp', 48),
(4, 'Ms Amazing', 'Barjabeen Bhabra', 17)]SQLModel을 사용하여 SQL에서 읽기
SQLModel은 모든 일반적인 SQL 문을 함수로 번역해 줍니다. “SELECT”도 다르지 않습니다. SQLModel에서 SQL 데이터베이스를 바로 읽어오는 예제를 살펴보겠습니다:
코드 보기
from sqlmodel import select
with Session(engine) as session:
statement = select(Hero) # SELECT * from hero와 동일
results = session.exec(statement) # 명령 실행
for hero in results:
print(hero)secret_name='Dive Wilson' name='Deadpond' id=1 age=None
secret_name='Pedro Parqueador' name='Spider-Boy' id=2 age=None
secret_name='Tommy Sharp' name='Rusty-Man' id=3 age=48
secret_name='Barjabeen Bhabra' name='Ms Amazing' id=4 age=17또한 results.all()을 통해 전체 내용을 한꺼번에 가져올 수도 있습니다. 결과 객체는 “hero” 객체들의 리스트이며, 그 속성은 heroes[0].name 등과 같이 접근할 수 있습니다.
코드 보기
with Session(engine) as session:
statement = select(Hero)
results = session.exec(statement)
heroes = results.all()
print(*heroes, sep="\n")secret_name='Dive Wilson' name='Deadpond' id=1 age=None
secret_name='Pedro Parqueador' name='Spider-Boy' id=2 age=None
secret_name='Tommy Sharp' name='Rusty-Man' id=3 age=48
secret_name='Barjabeen Bhabra' name='Ms Amazing' id=4 age=17SQLModel 문서에서 더 많은 정보를 찾을 수 있지만, 기대하시는 대부분의 SQL 쿼리 명령어를 사용할 수 있습니다. 앞서 본 예제 중 하나를 실행해 보겠습니다:
코드 보기
with Session(engine) as session:
statement = select(Hero).where(Hero.age < 100).limit(2).order_by(Hero.age)
results = session.exec(statement)
heroes = results.all()
print(*heroes, sep="\n")secret_name='Barjabeen Bhabra' name='Ms Amazing' id=4 age=17
secret_name='Tommy Sharp' name='Rusty-Man' id=3 age=48이는 SQLModel이 할 수 있는 일들(조인, 데이터 업데이트, 행 삭제 등)의 겉면만 살짝 살펴본 것입니다.
SQL에 대해 더 배울 수 있는 곳
이 장을 마치고 SQL에 대해 더 배우고 싶다면 다음 리소스들을 추천합니다:
- Renée M. P. Teate의 SQL for Data Scientists는 데이터 과학자의 요구에 특별히 맞춰 설계된 SQL 입문서로, 실제 조직에서 접할 수 있는 고도로 상호 연결된 데이터의 예시를 포함하고 있습니다.
- Anthony DeBarros의 Practical SQL은 데이터 저널리스트(설득력 있는 이야기를 하는 데 특화된 데이터 과학자)의 관점에서 작성되었으며, 데이터를 데이터베이스에 넣고 자신만의 DBMS를 운영하는 것에 대해 더 자세히 다룹니다.
- 기초적인 내용이 한곳에 정리된 수많은 sql cheatsheets 중 하나를 확인해 보세요.
- 브라우저에서 바로 SQLime을 통해 SQL을 연습해 보세요.
- 추리 소설 풀기를 통해 재미있게 SQL을 배워보세요.