[Python]SQLAlchemyでSQLを使う

テスト用テーブル

テスト用として使ったテーブルは次の通りです。

CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

通常のSQLを実行する

from sqlalchemy import create_engine

engine = create_engine('mysql://my_username:my_password@127.0.0.1/my_database')

engine.execute("INSERT INTO posts(id, content) VALUES (1, 'Hello')")

rs = engine.execute("SELECT * FROM posts")
for row in rs:
    print((row['id'], row['content']))

実行結果:

(1, 'Hello')

パラメータ化されたSQLを実行する

DBAPIがサポートしている形式を利用する

利用しているDBAPIがサポートしている形式でパラメータ化されたSQLを実行できます。

パラメータのプレイスホルダの形式にはqmark, numeric, named, format, pyformatがあります。

参考: https://www.python.org/dev/peps/pep-0249/

例えばDBAPIとしてmysqlclientを使っている場合はパラメータの形式として次のように '%s' をプレイスホルダに使います。

engine.execute(
    "INSERT INTO posts(id, content) VALUES (%s, %s)",
    1, 'Hello')

sqlalchemy.text を使ってパラメータ名をクエリに埋め込む

sqlalchemy.text を使うとプレイスホルダにNamed styleを使えます。パラメータはキーワード引数として渡します。

from sqlalchemy import create_engine, text

engine = create_engine('mysql+pymysql://my_username:my_password@127.0.0.1/my_database')

engine.execute(text('INSERT INTO posts(id, content) VALUES (:id, :content)')
               id=1, content='Hello')

rs = engine.execute('SELECT * FROM posts')

for row in rs:
    print((row['id'], row['content']))

パラメータは辞書として渡すこともできます。

engine.execute(text("INSERT INTO posts(id, content) VALUES (:id, :content)"),
               {'id': 1, 'content': 'Hello'})

辞書のリストとしてパラメータを渡すと、繰り返してSQLが実行されます。

engine.execute(text("INSERT INTO posts(id, content) VALUES (:id, :content)"),
               [{'id': 1, 'content': 'Hello'},
                {'id': 2, 'content': 'Goodbye'}])