ツールについて
SQL Serverは、
Microsoftによって開発されたリレーショナルデータベース管理システムです。他のアプリケーションが必要とするデータの格納と取得に使用されます。
PyMySQLは
PythonからMySQLに接続するためのライブラリの一つ
公式ドキュメント
Pythonは
高水準で解釈されるプログラミング言語であり、そのシンプルさと可読性で知られています。データ分析、Web開発、機械学習などに広く使用されています。
Pyodbcは、
SQL Serverに接続するためのオープンソースのPythonライブラリです。データベースとのやり取りにOpen Database Connectivity（ODBC）の標準を使用し、
Pythonとさまざまなデータベースシステムを接続することができる万能なツールです。
SQLAlchemyは、
Pythonアプリケーション向けのSQLツールキットとオブジェクトリレーショナルマッピング（ORM）の機能を提供する別のPythonライブラリです。
SQLコマンドとPythonコードを使用して、PythonアプリケーションがSQLデータベースと接続してやり取りすることができます。
SQLAlchemyを使用してSQL Serverに接続する方法は？
SQLAlchemyとは
SQLAlchemyは、PythonのORMの1つで、Pythonの中でよく利用されているORMの1つ
ORMとは
Object-Relational Mappingの略
テーブルとオブジェクト志向言語のクラスを1対1に対応させて，そのクラスのメソッド経由でデータを取得したり，変更したりできる
ORMの利点とは
①異なるDBの違いを吸収する
データベースの種類によらず、同じソースコードで操作できるので，複数のDBを併用する場合や，DBを変更する場合にも，コードの書き換えの必要がない．
②SQLを書かなくてもよい
SQLAlchemyを使うと，SQLを直接記述することなしに，DBを”Pythonic”に操作できる
インストール
pip install sqlalchemy
# MySQL用
pip install PyMySQL
# SQL Server用
# pip install pyodbc設定ファイルの作成/DB接続
setting.py
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
dialect = "mysql"
driver = "pymysql"
username = "root"
password = "root"
host = "127.0.0.1"
port = "3306"
database = "sample_db"
charset_type = "utf8"
db_url = f"{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type}"
# DB接続するためのEngineインスタンス
ENGINE = create_engine(db_url,
# echo=Trueだと実行のたびにSQLが出力される
echo=True)
# DBに対してORM操作するときに利用
# Sessionを通じて操作を行う
session = scoped_session(
sessionmaker(autocommit=False,
autoflush=False,
bind=ENGINE)
)
# 各modelで利用
# classとDBをMapping
Base = declarative_base()
Base.query = session.query_property()
| 要素 | 説明 |
|---|---|
| dialect | DBの種類(sqlite, mysql, postgresql, oracle, mssql) |
| driver | DBに接続するためのドライバー(“default” DBAPIになる） |
| username | DBに接続することができるユーザ名 |
| password | DBに接続するためのパスワード |
| host | ホスト名。(localhost, IPアドレス) |
| port | ポート番号。(指定しなければ、defaultのポート番号) |
| database | 接続するデータベース名 |
| charset_type | 文字コード(utf8とか) |
テーブル作成
user.py
from sqlalchemy import Column, Integer, String, DateTime, Sequence
from datetime import datetime
from os.path import dirname, abspath
import sys
import pprint
# pprint.pprint(sys.path)
# user.pyから3つ上のディレクトリの絶対パスを取得し、sys.pathに登録する
parent_dir = dirname(dirname(dirname(abspath(__file__)))) # 追加
if parent_dir not in sys.path: # 追加
sys.path.append(parent_dir) # 追加
from sql.setting.setting import ENGINE, Base
print(ENGINE)
print(Base)
class User(Base):
"""
ユーザモデル
"""
__tablename__ = 'users'
__table_args__ = {
'comment': 'ユーザー情報のマスターテーブル'
}
id = Column(Integer, Sequence('user_id_seq'), primary_key=True, autoincrement=True)
name = Column('name', String(200))
age = Column('age', Integer)
email = Column(String(255))
created_at = Column('created', DateTime, default=datetime.now, nullable=False)
updated_at = Column('modified', DateTime, default=datetime.now, nullable=False)
def main(args):
Base.metadata.create_all(bind=ENGINE)
if __name__ == "__main__":
main(sys.argv)
primary_key
primary_key=Trueとすることで、カラムを主キーとして扱います。
auto_increment
auto_increment=Trueとすることで、カラムはレコードが追加される毎に連番がわりふられていきます。
ORM操作
main.py
from models.user import User
from setting.setting import session
from sqlalchemy.sql import text
# INSERT
user = User()
user.name = '山田太郎'
user.email = 'yamata@xxx.com'
user.age = 39
session.add(user)
session.commit()
session.close()
# Select
users = session.query(User).filter(User.name == "山田太郎").all()
for user in users:
print(user.name, user.age)
session.close()
# Update
user = session.query(User).filter(User.id =="2").first()
user.name = '次郎'
session.commit()
session.close()
# Delete
session.query(User).filter(User.name == "山田太郎").delete()
session.commit()
session.close()
# 直接SQLを実行
#(1)生のSELECTを定義
t = text("select * from users")
#(2)SQL実行
result = session.execute(t)
#(3)取得結果のレコードから値が取得
for row in result:
print(row)
SQLAlchemy公式ドキュメント
Pyodbcを使用してPythonをSQL Serverに接続する
- Pyodbcライブラリをインストールする
pip install pyodbc2.Pyodbcモジュールをインポート
import pyodbc3.接続を確立する
サーバー名、データベース名、ユーザー名、パスワードが必要になります。
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user_name;PWD=password')
4.データベースとのやり取りをする
cursor.execute()メソッドを使用します。
cursor = conn.cursor()
cursor.execute('SELECT * FROM employees')
for row in cursor:
print(row)5.接続を閉じる
conn.close()
コメント