본문 바로가기
Python

Python - SQLAlchemy, pymysql 사용

by 올엠 2022. 5. 12.
반응형

SQLAlchemy는 Python에서 mysql등 데이터베이스를 질의할 때 유용하게 활용할 수 있는 옵션이다.

 

https://docs.sqlalchemy.org/en/14/core/engines.html#mysql

 

Engine Configuration — SQLAlchemy 1.4 Documentation

Engine Configuration The Engine is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect, which describes how to talk

docs.sqlalchemy.org

 

 

Database 연결

기본적으로 연결은 db의 create_engine을 활용한다. pymysql를 Python에서 MySQL에 많이 사용되기 때문에 함께 적어주면 보다 편히 활용이 가능하다. pymysql을 사용하기 위해서는 pip를 이용해서 추가로 설치를 해주어야 한다.

그리고 패스워드의 경우 특수기호가 스트링 처리시 문제가 발생하는 경우 정상 입력이 되지 않는다. 따라서 urllib.parse의 quote_plus를 이용해서 패스워드 부분에 대해 전처리를 해주는 것이 좋다.

import urllib.parse
import sqlalchemy as db

db_password = "pax%ssw/0rd"
str_db_password = urllib.parse.quote_plus(db_password)
database_url = f"mysql+pymysql://username:{str_db_password}@db.database.com/db_name"
engine = db.create_engine(database_url)

정상적으로 연결이 되었는지 확인하기 위해서는 연결후 close 시점에 에러가 발생하므로 아래와 같이 연결 테스트를 진행할 수 있다.

conn = engine.connect()
conn.close()

사용방법은 대표적으로 T-SQL 구문을 사용하는 방법과, Metadata를 활용한 구조체를 방식으로 활용할 수 있다.

T-SQL 사용

T-SQL은 복잡하지 않거나, 일방향성으로 사용할 때 유용한 방법으라고 할 수 있다.

주로 Insert 나 Delete 와 같이 추가적인 데이터를 따로 Python에서 조정하지 않아도 되는 경우에 필자는 사용하였다.

사용법은 execute 를 통해 실행하고자 하는 T-SQL을 넣어 결과를 확인하는 구조이다.

아래와 같이 사용할 수 있다. pymysql만 사용할 때에는 별도의 commit을 해주어야 하지만, SQLAlchemy를 사용할 경우 별도의 commit이 필요하지 않다.

test_query = "INSERT INTO tablename (`id`, `column`) VALUE('{0}', '{1}')".format('value1', 'value2')
my_conn.execute(test_query)

사용후 commit을 통해 적용을 해주면 된다.

 

Metadata 사용

위 T-SQL 방식을 사용하면 가장 불편한 부분이 데이터를 조정하거나 비교하는 부분들이다. T-SQL을 정교하게 조작하여 할 수 있다고 하지만, SQLAlchemy를 이용하면 프로그램에서 다루기 편한 구조체 방식으로 사용할 수 있기 때문에 이 방식을 보다 선호하게 된다.

테이블의 Metadata는 아래 코드를 통해서 가져올 수 있다.

metadata = db.MetaData()
table = db.Table('tablename', metadata, autoload=True, autoload_with=engine)

이외에도 사전에 모델을 선언해서 사용하는 방식으로도 활용이 가능하다.

위와 같이 table에 Metadata를 가져온 경우 select * from table 을 아래와 같이 조작할 수 있다.

select의 반환값이 List

query = db.select([table])

print(query)

특정 조건을 사용하고자 한다면, where 절을 다음과 같이 사용이 가능하다.

query = db.select([table]).where(table.columns.columnname == 'test')

print(query)

SQLAlchemy에서는 query 라는 구분을 제공하는데 query를 통해서 자신이 원하는 질의 내용을 이용이 가능하다.

 

데이터 처리

실제 데이터를 요청하기 위해서는, .fetchall() 또는 갯수를 조절이 가능한 .fechmany()를 통해 이 가능하다.

result = my_conn.execute(query).fetchall()
print(result)

데이터 처리에 pandas의 DataFrame을 이용할 수 있다.

 

필터링

그럼 SQLAlchemy를 이용해서 진행할 수 있는 몇가지 예제에 대해 살펴보자.

 

where

SQL :
SELECT * FROM table 
WHERE test = A

SQLAlchemy :
db.select([table]).where(census.columns.test == 'A')

in

SQL :
SELECT address, test
FROM table
WHERE address IN (Seoul, Home)

SQLAlchemy :
db.select([table.columns.address, census.columns.test]).where(census.columns.address.in_(['Seoul', 'Home']))

and, or, not

SQL :
SELECT * FROM table
WHERE address = 'Seoul' AND NOT test = 'N'

SQLAlchemy :
db.select([table]).where(db.and_(table.columns.address == 'Seoul', table.columns.test != 'N'))

이외에도 query를 이용한 방법도 존재하므로 아래와 같이 사용이 가능하다.

query = db.query([table]).filter(db.or_(table.columns.columnname == 'test', table.columns.columnname == 'test2')).filter(table.columns.columnname != None).all()
print(query)

query 관련된 부분은 아래에서 보다 자세히 확인이 가능하다.

https://docs.sqlalchemy.org/en/14/orm/query.html

 

Query API — SQLAlchemy 1.4 Documentation

Query API This section presents the API reference for the ORM Query object. For a walkthrough of how to use this object, see Object Relational Tutorial (1.x API). The Query Object Query is produced in terms of a given Session, using the Session.query() met

docs.sqlalchemy.org

 

반응형

댓글0