Python Pandas : pandas.io.sql.get_schema (DataFrame 내용을 sql create table syntax로 만들기)

 

 

 

 

 

 

 

 

Pandas로 DataFrame을 다루다보면 DataFrame data를 database에 load해야 할 경우가 있습니다.

 

따라서 아래처럼 create table 구문을 실행시켜 database table을 생성하고 해당 tabe에 insert하는 방법을 사용한다고 가정해봅시다.

 

create table test_table (
    col1    varchar,
    col2    bigint,
    col3    double
)

 

위 예시는 column이 3개밖에 없어서 다행이지만

만약 column이 30개인 DataFrame을 insert하려고 한다면 위 create table syntax에 column 30개에 대한 datatype을 일일이 적어줘야 할것입니다.

 

이는 너무 번거로운 작업이 아닐 수 없죠.

 

pandas에는 위처럼 특정 dataframe을 database에 upload하기 위해 필요한 create 구문을 생성해주는 method가 있습니다.

 

 

 

 

 

이를 위해선 pandas의 io.sql.get_schema method를 이용할 수 있으며

get_schema의 syntax는 다음과 같습니다.

 

pandas.io.sql.get_schema(DataFrame, name='table_name')

 

DataFrame : create table syntax의 원본이 될 DataFrame을 적어줍니다.

name='table_name' : create table syntax에서 생성할 table의 이름을 명시합니다.

 

 

 

import pandas as pd

dict_test = {
    'col1': [1, 2, 3, 4, 5],
    'col2': ['a', 'b', 'c', 'd', 'e'],
    'col3': [6, 7, 8, 9, 10],
    'col4': [1, 2, 3, 'b', 'c']
}

df_test = pd.DataFrame(dict_test)
print(df_test)

val_schema = pd.io.sql.get_schema(df_test, name='test_table')
print(val_schema)




-- Result
   col1 col2  col3 col4
0     1    a     6    1
1     2    b     7    2
2     3    c     8    3
3     4    d     9    b
4     5    e    10    c

CREATE TABLE "test_table" (
"col1" INTEGER,
  "col2" TEXT,
  "col3" INTEGER,
  "col4" TEXT
)

 

test할 dataframe(df_test)을 생성합니다.

그리고 해당 dataframe을 get_schema에 적용시킵니다.

 

 

 

 

 

 

위 예시를 보면 df_test에서 각 column별 data type은 다음과 같습니다.

 

col1 -->> Integer

col2 -->> String

col3 -->> Integer

col4 -->> String (col4에는 숫자와 문자가 모두 존재하기 때문에 이 경우 column의 data type은 String이 됩니다.)

 

 

 

CREATE TABLE "test_table" (
"col1" INTEGER,
  "col2" TEXT,
  "col3" INTEGER,
  "col4" TEXT
)

 

get_schema의 output을 보면 위와 같습니다.

create table syntax 구문이 만들어졌으며 생성될 table 이름은 명시한대로 test_table로 나와있습니다.

 

또한 총 4개의 컬럼이 적혀있고, 각 컬럼별로 datatype 까지 명시되어있습니다.

 

이렇게 자동으로 dataframe을 database에 올리기 위한 create table 구문을 생성해주는 것이 get_schema method입니다.

 

 

 

 

 

 

 

import pandas as pd

dict_test = {
    'col1': [1, 2, 3, 4, 5],
    'col2': ['a', 'b', 'c', 'd', 'e'],
    'col3': [6, 7, 8, 9, 10],
    'col4': [1, 2, 3, 'b', 'c']
}

df_test = pd.DataFrame(dict_test)
print(df_test)

val_schema = pd.io.sql.get_schema(df_test, name='test_table')

val_schema = val_schema.replace('\"', '')
val_schema = val_schema.replace('col1', 'col1')
val_schema = val_schema.replace('col2', 'col2')
val_schema = val_schema.replace('col3', 'col3')
val_schema = val_schema.replace('col4', 'col4')
val_schema = val_schema.replace('TEXT', 'varchar')
val_schema = val_schema.replace('INTEGER', 'int')
print(val_schema)



-- Result
   col1 col2  col3 col4
0     1    a     6    1
1     2    b     7    2
2     3    c     8    3
3     4    d     9    b
4     5    e    10    c

CREATE TABLE test_table (
col1 int,
  col2 varchar,
  col3 int,
  col4 varchar
)

 

근데 get_schema로 생성된 create table 구문이 모든 database에서 정상적으로 실행되지 않을 수 있습니다.

 

그런 경우 위처럼 replace를 사용하여 쌍따옴표를 없애거나, datatype을 변경하여 create table syntax를 자유롭게 조절하여 사용하면 됩니다.