PythonでOracle DatabaseからSQL Serverにデータをコピーする(ORM編)

この記事は公開から2年以上経過しています。

「PythonでOracle DatabaseからSQL Serverにデータをコピーする(SQL編)」では、Oracle Database上のデータを加工してからSQL Serverに移行させる要件で、Pythonのcx_Oracle、pyodbc、sqlalchemyと生SQLを使う方法を紹介しましたが、今回は生SQLではなくORMを使ってみたので、その備忘録。

テーブル構成

前回と同様です。検証用のため、数値キー列と文字列値の列だけのシンプルな構成です。

Oracle Database側(コピー元)：

CREATE TABLE TEST_TBL(ID INT PRIMARY KEY, VAL NVARCHAR2(50))

SQL Server側(コピー先)

CREATE TABLE TEST_TBL(ID INT PRIMARY KEY, VAL NVARCHAR(50))

サンプルソースコード

プログラムを実行すると、Oracle DatabaseのTEST_TBLレコードを一括で読み込み、トランザクションをかけてSQL ServerのTEST_TBLに1行ずつ書き込みを行います。

import sqlalchemy as sqlalc
import sqlalchemy.ext.declarative as alcexdecl
import sqlalchemy.orm as alcorm

# Oracle Database設定(コピー元)
HOST_ORA = 'ora ip address'
SN_ORA = 'ora service name'
USER_ORA = 'ora db user'
PASSWORD_ORA = 'ora db password'

# SQL Server(コピー先)
HOST_MSSQL = 'mssql ip address'
DB_MSSQL = 'mssql db name'
USER_MSSQL = 'mssql db user'
PASSWORD_MSSQL = 'mssql db password'

Base = alcexdecl.declarative_base()

# テーブルのエンティティクラス
class TestTbl(Base):
    __tablename__ = 'TEST_TBL'
    id = sqlalc.Column(sqlalc.INTEGER(), primary_key=True, autoincrement=False)
    val = sqlalc.Column(sqlalc.NVARCHAR(50))

    def __repr__(self) -> str:
        return f'{self.id} {self.val}'

# Oracle Databaseから指定テーブルの全レコードを取得する
tns_ora = f'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = {HOST_ORA})(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = {SN_ORA})))'
eng_ora = sqlalc.create_engine(f'oracle+cx_oracle://{USER_ORA}:{PASSWORD_ORA}@{tns_ora}')
with alcorm.sessionmaker(eng_ora)() as ssn_ora:
    rows_ora = ssn_ora.query(TestTbl).all()

# SQL Serverに全レコードを書き込む
conn_str_mssql = f'DRIVER={{SQL Server}};SERVER={HOST_MSSQL};DATABASE={DB_MSSQL};UID={USER_MSSQL};PWD={PASSWORD_MSSQL}'
eng_mssql = sqlalc.create_engine(f'mssql+pyodbc:///?odbc_connect={conn_str_mssql}')
with alcorm.sessionmaker(eng_mssql)() as ssn_mssql:
    for row_ora in rows_ora:
        row_mssql = TestTbl(id=row_ora.id, val=row_ora.val)
        ssn_mssql.add(row_mssql)
    ssn_mssql.commit()

ちなみに今回の例のようにSQL Server側で自動インクリメントではない整数型の主キー列を利用する場合は、エンティティクラスのID列の定義時にautoincrement=Falseを明示的に指定しないと、コミット時にSQLAlchemyが自動生成したSET IDENTITY_INSERT ONにより

Exception has occurred: ProgrammingError
(pyodbc.ProgrammingError) (‘42000’, "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]テーブル ‘TABLE_NAME’ には ID プロパティがありません。SET 操作は実行できません。 (8106) (SQLExecDirectW)")
[SQL: SET IDENTITY_INSERT [TABLE_NAME] ON]
(Background on this error at: https://sqlalche.me/e/14/f405)

The above exception was the direct cause of the following exception:

のようなエラーが発生するため、注意が必要です。

参考ウェブサイトなど

• SQL Alchemy
  SQLAlchemy 1.4 Documentation

以上です。