第17章 データベース

基本的なSQLiteの使用方法

Python標準ライブラリに組み込まれているsqlite3モジュールを用いて、SQLiteを使用した基本的なデータベース操作を以下に示します。socketモジュールを使用することで、簡単にソケット通信を実現することができます。以下に、基本的なTCPのクライアントおよびサーバーのソケット通信の例を示します。

データベースの作成と接続

import sqlite3

# データベースに接続 (存在しない場合は新規作成)
conn = sqlite3.connect('sample.db')

テーブルの作成

cursor = conn.cursor()

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
''')

conn.commit()

データの挿入

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 25))

conn.commit()

データの取得

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

データの更新

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "Alice"))
conn.commit()

データの削除

cursor.execute("DELETE FROM users WHERE name = ?", ("Bob",))
conn.commit()

データベースの接続を閉じる

conn.close()

トランザクション管理

try:
    conn.execute('BEGIN TRANSACTION')
    
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Charlie", 40))
    cursor.execute("UPDATE users SET age = ? WHERE name = ?", (35, "Alice"))

    conn.commit()
except sqlite3.Error as e:
    print("Database error occurred:", e)
    conn.rollback()

RDBMS接続への基本的な例

PostgreSQL

PostgreSQLへの接続には、psycopg2というライブラリが一般的に使用されます。

pip install psycopg2

import psycopg2

connection = psycopg2.connect(
    dbname="your_database_name",
    user="your_username",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()
cursor.execute("SELECT version();")
version = cursor.fetchone()
print(f"PostgreSQL version: {version}")

cursor.close()
connection.close()

MySQL

MySQLへの接続には、mysql-connector-pythonというライブラリを使用できます。

pip install mysql-connector-python

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL version: {version[0]}")

cursor.close()
connection.close()

Oracle

Oracleへの接続には、cx_Oracleというライブラリを使用します。

pip install cx_Oracle

import cx_Oracle

connection = cx_Oracle.connect(
    user="your_username",
    password="your_password",
    dsn="your_dsn"  # 例: "localhost:1521/orclpdb"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM v$version WHERE banner LIKE 'Oracle%'")
version = cursor.fetchone()
print(f"Oracle version: {version[0]}")

cursor.close()
connection.close()

Microsoft SQL Server

Microsoft SQL Serverへの接続には、pyodbcというライブラリがよく使用されます。

pip install pyodbc

import pyodbc

connection = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=your_database_name;"
    "UID=your_username;"
    "PWD=your_password;"
)

cursor = connection.cursor()
cursor.execute("SELECT @@version;")
version = cursor.fetchone()
print(f"SQL Server version: {version[0]}")

cursor.close()
connection.close()

MariaDB

MariaDBはMySQLと互換性があり、前述のmysql-connector-pythonで接続できます。ただし、mariadbというライブラリも存在しており、それを使用することもできます。

pip install mariadb

import mariadb

connection = mariadb.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

cursor = connection.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MariaDB version: {version[0]}")

cursor.close()
connection.close()

DB2

IBMのDB2に接続するためには、ibm_dbというライブラリが使用されます。

pip install ibm_db

import ibm_db

connection = ibm_db.connect(
    "DATABASE=your_database_name;"
    "HOSTNAME=localhost;"
    "PORT=50000;"
    "PROTOCOL=TCPIP;"
    "UID=your_username;"
    "PWD=your_password;", "", ""
)

stmt = ibm_db.exec_immediate(connection, "SELECT service_level, fixpack_num FROM sysibmadm.env_inst_info")
result = ibm_db.fetch_tuple(stmt)
print(f"DB2 version: {result}")

ibm_db.close(connection)

RDBMS以外の基本的な使用例

MongoDB

MongoDBは人気の高いドキュメント指向のNoSQLデータベースです。PythonでMongoDBを操作するには、pymongoというライブラリを使用します。

pip install pymongo

from pymongo import MongoClient

client = MongoClient('localhost', 27017)
db = client['your_database_name']
collection = db['your_collection_name']
collection.insert_one({"name": "Alice", "age": 25})
print(collection.find_one({"name": "Alice"}))

Redis

Redisは高速なインメモリデータベースで、キー-バリューのデータ構造を持っています。Pythonから操作するためにはredisというライブラリがあります。

pip install pymongo

import redis

client = redis.StrictRedis(host='localhost', port=6379, db=0)
client.set('key', 'value')
print(client.get('key').decode('utf-8'))

Cassandra

Cassandraはスケーラビリティと高可用性を持つカラム指向のNoSQLデータベースです。Pythonからの接続にはcassandra-driverを使用します。

pip install cassandra-driver

from cassandra.cluster import Cluster

cluster = Cluster(['localhost'])
session = cluster.connect('your_keyspace_name')
session.execute("INSERT INTO your_table_name (id, name) VALUES (1, 'Alice')")
result = session.execute("SELECT * FROM your_table_name WHERE id=1")
print(result[0])

Elasticsearch

Elasticsearchは検索とアナリティクスが得意なNoSQLデータベースで、JSONドキュメントを格納します。Pythonからの操作にはelasticsearchというライブラリが用いられます。

pip install elasticsearch

from elasticsearch import Elasticsearch

es = Elasticsearch(['localhost:9200'])
es.index(index='your_index_name', doc_type='your_type', id=1, body={'name': 'Alice', 'age': 25})
print(es.get(index='your_index_name', doc_type='your_type', id=1))

SQLインジェクションと対策

SQLインジェクションは、不正なSQLコードを含む入力を受け付けることによってデータベースが攻撃されるセキュリティ脆弱性の一つです。これにより、攻撃者はデータベースの内容を読み取ったり、変更したり、削除したりすることができる場合があります。

インジェクションの例

以下は、SQLインジェクションが可能なコードの一例です。

import sqlite3

def get_user_data(username):
    connection = sqlite3.connect('example.db')
    cursor = connection.cursor()
    
    # 脆弱なクエリの実行
    cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
    
    result = cursor.fetchone()
    cursor.close()
    connection.close()
    return result

上記の関数は、ユーザからの入力（username）をそのままSQLクエリに組み込んでいます。攻撃者が

' OR '1'='1'; --のような文字列をusernameとして提供すると、全てのユーザのデータが取得されてしまいます。

対策

SQLインジェクションを防ぐための一般的な方法は、パラメータ化されたクエリを使用することです。

def secure_get_user_data(username):
    connection = sqlite3.connect('example.db')
    cursor = connection.cursor()
    
    # パラメータ化されたクエリの使用
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    
    result = cursor.fetchone()
    cursor.close()
    connection.close()
    return result

上記のsecure_get_user_data関数では、? をプレースホルダとして使用して、後からパラメータとしてusernameを提供しています。この方法により、入力されたデータはSQLコードとして解釈されず、文字列として安全にデータベースに渡されます。

ORMライブラリ

ORM（Object-Relational Mapping）とは、オブジェクト指向プログラミング言語とリレーショナルデータベース間のデータのマッピングを行う手法やツールのことを指します。ORMを使用すると、データベースのテーブルをクラスとして、そのレコードをオブジェクトとして扱うことができ、SQLクエリの直接的な使用を避けることができます。

Pythonには多くのORMライブラリが存在しますが、代表的なものに「SQLAlchemy」や「Django ORM」などがあります。ここでは、フレームワークを使用せずに「SQLAlchemy」を例に、基本的なORMの使い方を紹介します。

SQLAlchemyの基本的な使用方法

インストール

pip install sqlalchemy

モデルの定義

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

データベースとの接続

DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(DATABASE_URL)

テーブルの作成

Base.metadata.create_all(engine)

セッションの作成とオブジェクトの追加

Session = sessionmaker(bind=engine)
session = Session()

new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()

クエリの実行

users = session.query(User).filter_by(name='Alice').all()
for user in users:
    print(user.id, user.name, user.age)

上記の例では、SQLAlchemyを使用してSQLiteデータベースにusersテーブルを作成し、データを追加およびクエリしています。SQLAlchemyは非常に柔軟で強力なORMツールであり、上記はその基本的な使い方に過ぎません。より詳細な情報や高度な機能については公式ドキュメントを参照することをおすすめします。

練習問題1.

SQLiteデータベースにbooksというテーブルを作成してください。このテーブルには、id, title, author, published_dateというカラムが必要です。

上記のテーブルに、以下のデータを3つ追加してください。

1. title: "Python Basics", author: "John Doe", published_date: "2020-01-15"
2. title: "Advanced Python", author: "Jane Smith", published_date: "2021-05-20"
3. title: "Python Web Development", author: "Alice Johnson", published_date: "2019-09-05"

authorが"Jane Smith"のレコードをクエリして、その情報を表示してください。

練習問題2.

ユーザーからの入力を模倣するための関数を作成してください。この関数は、書籍のタイトルを引数として受け取ります。

上記の関数を使用して、booksテーブルから特定のタイトルの書籍をクエリします。このとき、パラメータ付きクエリを使用してSQLインジェクション攻撃を防ぐようにしてください。

上記の関数をテストして、正しく動作することを確認してください。

練習問題3.

PostgreSQLデータベースをセットアップし、そこにstudentsという新しいテーブルを作成してください。このテーブルには、id, name, grade, majorというカラムが必要です。

Pythonから上記のPostgreSQLデータベースに接続するためのスクリプトを作成してください。

上記のスクリプトを使用して、3人の学生のデータをstudentsテーブルに追加してください。

majorが"Computer Science"のすべての学生をクエリし、そのリストを表示してください。