python sqlalchemy: MySQL, PostgreSQL 호환 작업

python ORM library중 하나인 sqlalchemy를

MySQL, PostgreSQL 모두 동작이 가능하도록 호환성을 고려하여 사용하기

 

 

1. Boolean: MySQL이든 PostgreSQL이든 Boolean Column 속성 사용하기

MySQL tinyint vs PostgreSQL boolean

MySQL은 Boolean을 표현할 때 tinyint 타입을 권장

  0을 false, 1을 true로

  sqlalchemy로 Boolean type을 import하고 Column을 Boolean으로 설정하면

  생성된 table의 column이 tinyint(1)로 설정되어 있는 것을 확인 가능

 

PostgreSQL은 boolean 타입 지원

  boolean 타입 속성 컬럼에는 false, true 값이 들어간다

 

문제는 MySQL의 논리 비교 연산을 할 목적으로 integer 속성으로 컬럼을 생성하고

해당 로우를 sqlalchemy orm의 filter로 = 0, = 1, = True, = False 와 같이 사용하는 것은 가능하나,

 

PostgreSQL에서는 integer field를 boolean과 비교 연산하면 오류가 발생한다.

 

상호 호환성을 위해서는 애초에 sqlalchemy orm으로 column 속성을 정할 때 integer가 아니라 Boolean을 쓰자

from sqlalchemy import Boolean, Integer

  

 

2. PostgreSQL은 특히 비교하려는 컬럼 속성의 타입을 잘 처리해야 한다. (# in [''])

참 설명하기 특이하다고 할만한 경우이다.

DB에 값을 넣는 경우 null값 대신에 ''의 empty string을 넣는 경우가 꽤 있다.

코드 상 해당 필드를 가져와서 split을 하는 경우를 마주했는데,

''.split(",")의 결과값이 ['']으로 나온다.

이렇게 만들어진 리스트 값으로 sqlalchemy를 활용해서 filter 조건에 넣고 사용시

MySQL은 오류가 발생하지 않았다.

이때 비교하려는 컬럼의 속성은 integer이었는데, some integer in ['']와 같은 조건으로 orm을 작성했을 시

MySQL은 오류가 발생하지 않았다.

PostgreSQL은 아래와 같이 오류 발생

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: ""
LINE 3: WHERE sometable.sample_id IN ('')

 

MySQL이 관대하게 평가를 하는건지는 확인을 해봐야겠지만, MySQL에서 오류가 나지 않더라도,

다른 database와의 호환을 위해서는 컬럼 타입을 명확하게 관리할 필요가 있다.