【Python】SQLAlchemyでSQLServerのテーブルを作成

スポンサーリンク

最近、pythonでFastAPIというWEBフレームワークを勉強しています。FastAPIでデータベースを使うとなったときにマッピングに使われているのが、SQLAlchemyです。

前から名前を聞いたことはあって、pythonとデータベースをマッピングするORMというのは知っていました。ただ、普段システム保守でSQLをよく書くことがあったのでpyodbcというodbc経由でデータベースに接続して直接SQLを書けばよいと思っていました。

今回FastAPIを勉強するにあたってそうもいかなくなったのでSQLAlchemyを使ってみることにしました。当記事はSQLAlchemyとpyodbcを使用してSQLServerに接続してデータベースとテーブルを実際に作成したときの対応方法について書こうと思います。

SQLAlchemyとは?

SQLAlchemyは、Pythonで一番利用されているORM(ORマッパー)のライブラリです。

ORMは、(Object Reration Mapper)の略でpythonのプログラムで記述したclassとテーブルを1対1でマッピングして、class経由でCRUD操作を行えます。

有名どころで言うと以下のデータベースに対応しています。

・Microsoft SQL Server
・MySQL
・Oracle
・PostgreSQL
・SQLite3

SQLAlchemyを使うメリット

SQLAlchemyを使用するメリットをざっくり上げると

データベースの違いを吸収してくれる

一つのデータベースだけしか使ったことがない場合は、経験がないかもしれませんがデータベースによってSQL文の書き方が違うことがあります。例えば、SQLで上位10件を抽出する場合は、

SQL SERVERの場合は、以下のようになります。

SELECT TOP 10 * FROM テーブル名

次にMySQLやOracleの場合は

SELECT * FROM テーブル名 LIMIT 10;

となります。仮にプログラムにSQLを直接書いていた場合、DBを変更するとPGの修正も必要ですが、SQLAlchemyの場合は、PGの修正は、接続部分の設定のみでSQL文を修正する必要はありません。

SQLを書く必要がない

SQLを書くのが苦手な人でもSQLを記述せずにデータベースのCRUD操作ができます。

pythonのWEBフレームワークでは、ORMの利用がデフォルト

最近のpythonのWEBフレームワークでは、データベースに接続する際は、ORMを使用した接続が主流です。Djangoは専用のORMですがFlaskやFastAPIは、SQLAlchemyを使用するのが主流となっています。

SQLAlchemyをテーブルを作成する(SQL Severの場合)

事前にSQL Serverに空のデータベースを作成してください。当記事ではtestというデータベースを事前に作成しています。

sqlalchemyのインストール

PythonでSQLAlchemyを使うにはPythonの環境にSQLAlchemyのライブラリをインストールする必要があります。python環境に以下のコマンドでインストールしてください。

#pip環境の場合:
pip install sqlalchemy
#anaconda環境の場合:
conda install sqlalchemy

今回作成するテーブル

今回は、sqlalchemyでtestというDBにusersというテーブルを作成しようと思います。

DB名:test

作成するテーブル:users

列名主キータイプ
userid文字列
firstname文字列

全ソース

任意のファイル名.pyファイルを新規作成し、以下のように記述します。
※ここではdb.pyというファイルを作成しています。

import urllib
from sqlalchemy import Column,String, create_engine
from sqlalchemy.ext.declarative import declarative_base
#pyodbcの接続文字列
#ドライバー名
driver = r'ODBC Driver 17 for SQL Server'
#インスタンス名(サーバ名\インスタンス名)
server = r'localhost\xxxx'
#SQL Serverに接続するユーザー名
username = 'ユーザー名'
#パスワード
password = 'パスワード'
#データベース名
db = 'test'
#ODBCの文字列を作成
odbc_connect = urllib.parse.quote_plus(

    'DRIVER={%s};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s' % (driver, server, username, password,db))
#データベースに接続するエンジンを作成
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % odbc_connect,echo=True)

#テーブル定義の基底クラス
Base = declarative_base()

#基底クラスBaseを継承してクラスを作成
class User(Base):

    __tablename__ = 'users'

    userid = Column('userid',String(32),primary_key = True)

    username = Column('firstname',String(32))


Base.metadata.create_all(bind=engine)

処理解説

odbcの設定

#odbcの接続文字列
#ドライバー名
driver = r'ODBC Driver 17 for SQL Server'
#インスタンス名(サーバ名\インスタンス名) 
server = r'hostname\instance' 
#SQL Serverに接続するユーザー名 
username = 'ユーザー名' 
#パスワード
password = 'パスワード' 
#データベース名
db = 'test'

sqlalchemyでSQLServerに接続する際は,pyodbc経由またはPymssqlで接続する必要があります。調べたところPymssqlは動作が不安定とのことで今回はpyodbc経由にしています。

ODBドライバ名は、ODBCデータソースアドミニストレーターの名前と同じにします。
私の場合はODBC Driver 17 for SQL Server

インスタンス名は、サーバー名\インスタンス名を入力します。バックスラッシュをエスケープ文字ではなく単なる文字列として扱うため先頭にrを付与しています。

ユーザー名とパスワードはSQL Serverに接続する際のユーザー名とパスワードを入力します。
(混合モード)

データベース名は、事前にSQL Serverで作成したデータベース名を記載します。

接続文字列の作成

#ODBCの文字列を作成
odbc_connect = urllib.parse.quote_plus(
    'DRIVER={%s};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s' % (driver, server, username, password,db))

上で変数にセットしたodbcの設定から接続文字列を作成します。urllib.parse.quote_plus()でエンコードしています。

エンジンの作成

#データベースに接続するエンジンを作成
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % odbc_connect,echo=True)

create_engine の引数にodbcの接続文字列を入れて接続するためのエンジンを作成します。
ちなみにecho=True は、SQL発行時に実行内容を標準出力する設定です。指定なしの場合は実行しても何も出力されないので最初は出力したほうが良いと思います。

基底クラスBaseを宣言

#テーブル定義の基底クラス
Base = declarative_base()

モデルとなる基底クラスを作成しています。これでBaseにモデルのベースとなるクラスが作成されます。後ほど作成するusersクラスは、この基底クラスのBaseを継承して作成します。

テーブルにマッピングするクラスを作成

#基底クラスBaseを継承してクラスを作成
class User(Base):
    __tablename__ = 'users'
    userid = Column('userid',String(32),primary_key = True) 
    username = Column('firstname',String(32))

__tablename__ = 'users'は、作成したテーブル名を指定しています。その下は、カラムを作成しています。

このクラスを作成するときの注意点として
・必ず主キーとなるColumnがないと作成時にエラーとなる。
・主キーがint型の場合は、id型と判別されて自動採番方式となる。
 ※自動採番にしたくない場合はautoincrement = Falseを記述
テーブルの作成
Base.metadata.create_all(bind=engine)

Baseクラス内のデータベースのテーブル情報を管理しているmetadataを新規で作成することでテーブルが作成されます。(bind=engine)は、使用するエンジンを指定しています。

プログラムの実行

上記で作成したdb.pyファイルをファイルが存在しているディレクトリに移動して実行します。

cd <db.pyが存在するフォルダ>
python db.py

これでSQL Serverを確認すると以下のようにテーブルが作成されていると思います。

まとめ

・SQLAlchemyは、pythonで一番使用されているORM
・SQLAlchemyを使うと、SQL文を書く必要がなくなるので便利
・SQLAlchemyでSQL Serverを使用するときはodbc経由で接続する
declarative_baseを使用してテーブルを作成するときは主キーが必ず必要
・主キーがint型の場合は、id型と判別されて自動採番方式となる

コメント

タイトルとURLをコピーしました