sqlalchemy란 뭘까요?
sqlalchemy란 파이썬을 이용하여 데이터베이스와 연결해 특별한 SQL문 없이 고유의 파이썬 문법으로 데이터를 다루게 도와주는 편리한 모듈입니다.
데이터베이스를 다룰 수 있을 뿐만 아니라, 가져온 데이터를 내부 파이썬 로직에 의해 자기가 원하는 형태로 가공할 수도 있으니 정말 신기할 따름입니다. 저도 아직은 sqlalchemy에 대해 많은 것은 알지 못하나, 간단하게라도 몇몇 기능을 소개해 드리겠습니다.
데이터베이스 연결
먼저 sqlalchemy를 임포트 합니다.
import sqlalchemy as sa
그리고 사용할 데이터베이스에 연결을 합니다. 만약 기존에 있던 데이터베이스가 없다 해도 일단 이름을 정해 연결이 되면, 후에 자동적으로 생성됩니다.
engine = sa.create_engine("sqlite:///profile.db", echo=True)인자로 데이터베이스의 주소값을 넘겨주면 되는데요, 일단은 로컬에 저장하기 위해 이렇게 작성했습니다. echo를 True로 설정하면 쿼리 실행 마다 작동되는 실제 SQL문을 확인할 수 있습니다.
테이블 생성
meta_data = sa.MetaData()
user_table = sa.Table(
"users",
meta_data,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String(10)),
sa.Column("pass", sa.Boolean, default=False),
sa.Column("birthday", sa.Date),
)
meta_data.create_all(engine)먼저 sa.MetaData()를 통해 메타데이터를 생성해야 테이블을 만들 수 있습니다. 그리고 sa.Table을 통해 테이블을 만드는데요, 첫번째 인자로 테이블 이름, 두번째 인자로 메타데이터, 그다음 부터는 sa.Column()을 통해 컬럼명과 컬럼의 데이터타입을 정해 컬럼들을 생성할 수 있습니다.
각각의 컬럼들에 대해선 이렇게 접근할 수 있습니다.
user_table.c.name
user_table.c.bitrthday
# 모든 컬럼명들 확인
user_table.c.keys()데이터 삽입
ins = user_table.insert()
conn = engine.connect()
conn.execute(ins,
{"id": 1,
"name": "Lee",
"pass": False,
"birthday": "1996-08-27"}
)테이블에 데이터를 삽입할 수 있는 첫번째 방법입니다.
우선 앞서만든 테이블에 insert()메소드를 붙여 삽입 인스턴스를 만든 후, 데이터베이스와 연결된 engine을 통해 데이터 삽입 프로세스를 실행시킵니다. 새로 추가할 데이터들은 보시다시피 컬럼명들을 key값으로 한 사전형으로 넘겨줄 수 있습니다.
ins = user_table.insert().values(id=2, name="Sam", pass=True, birthday="1997-05-03")
conn = engine.connect()
conn.execute(ins)위와 비슷한 방식으로 이렇게 삽입 인스턴스를 만들 때 미리 넣은 데이터들을 다 정해주고 삽입 프로세스를 실행시키는 방법도 있습니다.
query = sa.insert(user_table).values(
[
{"id": 1, "name": "Lee", "pass": False, "birthday": "1996-08-27"},
{"id": 2, "name": "Sam", "pass": True, "birthday": "1997-05-03"},
]
)
conn = engine.connect()
conn.execute(query)테이블에 데이터를 삽입할 수 있는 두번째 방법입니다.
사실 방법이 다른 것도 아니고, 그냥 코드의 형태만 달라질 뿐입니다. sqlalchemy의 insert함수를 이용해 특정 테이블에 대한 쿼리를 직접 만든 다음 그 쿼리를 실행시켜줍니다. values()메소드에 들어가는 데이터는 위에서 작성한 것처럼 사전형으로 표시된 행 데이터들을 리스트 안에 담아주면 됩니다.
만약 동일한 컬럼 구조를 가진 판다스 데이터프레임을 바로 테이블에 집어넣으려면 어떻게 하면 좋을까요? 그럴때는 to_dict()메소드를 이용하여 바로 우리가 원하는 형태로 변형할 수 있습니다.
df = pd.DataFrame(....)
query = sa.insert(user_table).values(
df.to_dict(orient="record")
)
conn = engine.connect()
conn.execute(query)
그리고, 사실 가장 파이썬답고 깔끔하게 코드를 작성하려면 아래처럼 하는 것이 최곱니다.
with engine.connect() as conn:
conn.execute(
sa.insert(user_table).values(
...
)
)
데이터 업데이트
데이터 삽입과 방법이 동일합니다. 조건문을 추가한 예시코드를 보여드리겠습니다.
with engine.connect() as conn:
conn.execute(
sa.update(user_table).where(user_table.c.id == 1).values(
[
{"id": 1, "name": "Kim", "pass": False, "birthday": "1998-12-24"},
]
)
)
데이터 찾기
# 모든 데이터
s = select(user_table)
# 특정 컬럼에 대한 데이터
s = select(user_table.c.name)
# 특정 조건을 만족하는 데이터
s = select(user_table).where(user_table.users.c.name == "Sam")
# 실행
result = conn.execute(s)위 코드 처럼 테이블 안의 모든 데이터, 특정 컬럼에 대한 데이터, 특정 조건을 만족하는 데이터 같이 상황에 맞게 원하는 데이터를 찾을 수 있습니다.
이제 찾은 데이터 내부를 열람해 봅시다.
for row in result:
print(row.name)
for id, name, pass in result:
print(id, name, pass)이렇게 한 줄씩 열람하는 방법도 있고
이렇게 한꺼번에 모든 데이터들을 열람하는 방법도 있습니다.
result.cursor.fetchall()
"""이런 형태로 리턴됩니다.
[
(1, "Kim", False, "1998-04-01"),
(2, "Lee", False, "1996-08-27"),
(3, "Sam", True, "1994-03-11"),
]
"""
이제 이 데이터들을 보기 좋게 데이터프레임으로 리폼해 볼까요?
t = user_table
data = result.cursor.fetchall()
df = pd.DataFrame(data, columns=t.c.keys())
# 사실 이 방법이 더 간단합니다.
# df = pd.read_sql("SELECT * FROM user_table", engine)
참고자료
- sqlalhemy core 공식 문서 - https://docs.sqlalchemy.org/en/14/core/
'데이터' 카테고리의 다른 글
| [데이터] 빅데이터 기초 분석 총정리 (이론) (0) | 2021.09.29 |
|---|