PythonとSQLの融合：練習問題10問で学ぶデータ処理の基礎
Pythonは汎用性の高いプログラミング言語であり、データ分析、機械学習、Web開発など幅広い分野で利用されています。一方、SQL（Structured Query Language）はデータベースを操作するための標準的な言語です。この2つを組み合わせることで、データの抽出、加工、分析といった一連の処理を効率的に行うことができます。
本記事では、PythonとSQLの連携に焦点を当て、初心者向けの練習問題10問を用意しました。各問題には解説とサンプルコードを記載し、読者が実践を通して理解を深められるように構成しています。SQLの基礎知識がない読者の方にも分かりやすく説明することを心がけますので、安心して読み進めてください。
1. PythonとSQL連携の基本：sqlite3モジュール
PythonでSQLデータベースを扱う上で最も基本的なモジュールはsqlite3です。SQLiteは軽量なディスクベースのデータベースであり、外部サーバーを必要としません。そのため、開発環境構築が容易で、小規模なデータ処理やプロトタイプ作成に適しています。
import sqlite3 # データベースに接続（ファイルが存在しない場合は新規作成） conn = sqlite3.connect('mydatabase.db') # カーソルオブジェクトを作成 cursor = conn.cursor() # テーブルの作成 cursor.execute(''' CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, salary REAL ) ''') # 変更をコミット（保存） conn.commit() # データベース接続を閉じる conn.close()
このコードは、mydatabase.dbという名前のSQLiteデータベースに接続し、employeesテーブルを作成しています。cursorオブジェクトを使ってSQLクエリを実行し、conn.commit()で変更を保存します。最後に、conn.close()でデータベース接続を閉じます。
Explanation: This code snippet demonstrates the fundamental steps of connecting to an SQLite database using Python's sqlite3 module, creating a table named 'employees' with columns for ID, name, and salary. The IF NOT EXISTS clause ensures that the table is only created if it doesn't already exist. The connection is then committed (saved) and closed.
2. データ挿入：INSERT文
次に、INSERT文を使ってテーブルにデータを挿入する方法を学びましょう。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # データの挿入 employee_data = [ ('Alice', 50000), ('Bob', 60000), ('Charlie', 70000) ] cursor.executemany("INSERT INTO employees (name, salary) VALUES (?, ?)", employee_data) # 変更をコミット conn.commit() # データベース接続を閉じる conn.close()
executemany()メソッドを使うことで、複数のデータを効率的に挿入できます。?はプレースホルダーであり、employee_dataの各タプルが対応する値を代入します。これにより、SQLインジェクションのリスクを軽減できます。
Explanation: This code demonstrates how to insert multiple rows of data into the 'employees' table using the INSERT statement and the executemany() method. The placeholders (?) are used to prevent SQL injection vulnerabilities by safely substituting values from the employee_data list. The commit() function saves the changes to the database.
3. データ抽出：SELECT文
SELECT文を使ってテーブルからデータを抽出する方法です。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # 全てのデータを抽出 cursor.execute("SELECT * FROM employees") rows = cursor.fetchall() for row in rows: print(row) # データベース接続を閉じる conn.close()
fetchall()メソッドは、クエリ結果の全ての行をリストとして返します。各行はタプルで表現され、テーブルのカラムに対応する値が含まれています。
Explanation: This code snippet retrieves all rows from the 'employees' table using a SELECT * query. The fetchall() method returns a list of tuples, where each tuple represents a row in the result set. The code then iterates through this list and prints each row to the console.
4. 条件付き抽出：WHERE句
WHERE句を使うことで、特定の条件を満たすデータのみを抽出できます。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # 給料が60000以上の従業員を抽出 cursor.execute("SELECT * FROM employees WHERE salary > ?", (60000,)) rows = cursor.fetchall() for row in rows: print(row) # データベース接続を閉じる conn.close()
?プレースホルダーを使うことで、SQLインジェクションのリスクを軽減できます。
Explanation: This code demonstrates the use of a WHERE clause to filter data based on a specific condition. In this case, it retrieves only those employees whose salary is greater than 60000. The placeholder (?) and tuple (60000,) are used for safe parameter substitution, preventing SQL injection attacks.
5. データ更新：UPDATE文
UPDATE文を使ってテーブルのデータを更新する方法です。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # Bobの給料を65000に更新 cursor.execute("UPDATE employees SET salary = ? WHERE name = ?", (65000, 'Bob')) # 変更をコミット conn.commit() # データベース接続を閉じる conn.close()
WHERE句を使って、更新対象の行を特定します。
Explanation: This code snippet demonstrates how to update data in the 'employees' table using an UPDATE statement. It changes Bob's salary to 65000. The WHERE name = ? clause ensures that only Bob's record is updated, and the placeholder prevents SQL injection.
6. データ削除：DELETE文
DELETE文を使ってテーブルからデータを削除する方法です。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # Charlieを削除 cursor.execute("DELETE FROM employees WHERE name = ?", ('Charlie',)) # 変更をコミット conn.commit() # データベース接続を閉じる conn.close()
WHERE句を使って、削除対象の行を特定します。
Explanation: This code demonstrates how to delete a record from the 'employees' table using a DELETE statement. It removes Charlie's entry. The WHERE name = ? clause specifies which row to delete, and the placeholder prevents SQL injection vulnerabilities.
7. 集計関数：COUNT, SUM, AVG, MIN, MAX
SQLには、データの集計を行うための様々な関数が用意されています。Pythonと組み合わせることで、より高度なデータ分析が可能になります。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # 全従業員の数をカウント cursor.execute("SELECT COUNT(*) FROM employees") count = cursor.fetchone()[0] print(f"全従業員数: {count}") # 給料の合計を計算 cursor.execute("SELECT SUM(salary) FROM employees") total_salary = cursor.fetchone()[0] print(f"給料の合計: {total_salary}") # データベース接続を閉じる conn.close()
fetchone()メソッドは、クエリ結果の最初の行をタプルとして返します。
Explanation: This code snippet demonstrates the use of aggregate functions in SQL to perform data analysis. It calculates the total number of employees using COUNT(*) and the sum of all salaries using SUM(salary). The fetchone()[0] retrieves the first (and only) value from the result set, which is then printed to the console.
8. 並び替え：ORDER BY句
ORDER BY句を使うことで、抽出したデータを特定のカラムで並び替えることができます。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # 給料の高い順に並び替え cursor.execute("SELECT * FROM employees ORDER BY salary DESC") rows = cursor.fetchall() for row in rows: print(row) # データベース接続を閉じる conn.close()
DESCは降順（descending order）を表し、ASCは昇順（ascending order）を表します。デフォルトでは昇順に並び替えられます。
Explanation: This code snippet demonstrates how to sort the results of a query using the ORDER BY clause. It retrieves all employees and sorts them in descending order based on their salary (ORDER BY salary DESC). The DESC keyword specifies descending order; without it, the results would be sorted in ascending order by default.
9. グループ化：GROUP BY句とHAVING句
GROUP BY句を使うことで、特定のカラムの値が同じ行をグループ化できます。HAVING句は、WHERE句と同様の条件を指定しますが、グループ化した結果に対して適用されます。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # 給料が60000以上の従業員の数をカウント cursor.execute("SELECT name, COUNT(*) FROM employees WHERE salary > ? GROUP BY name HAVING COUNT(*) > 0", (60000,)) rows = cursor.fetchall() for row in rows: print(row) # データベース接続を閉じる conn.close()
この例では、給料が60000以上の従業員を名前でグループ化し、各名前の出現回数をカウントしています。HAVING COUNT(*) > 0は、少なくとも1人の従業員がいるグループのみを表示します。
Explanation: This code demonstrates the use of GROUP BY and HAVING clauses to group data and filter results based on aggregate functions. It groups employees by their name, filters for those with salaries greater than 60000, and then counts how many employees are in each group. The HAVING COUNT(*) > 0 clause ensures that only groups with at least one employee meeting the salary criteria are included in the results.
10. より複雑なクエリ：結合 (JOIN)
複数のテーブルからデータを抽出するには、JOIN句を使用します。例えば、employeesテーブルとdepartmentsテーブルを結合して、各従業員の所属部署を取得することができます。
import sqlite3 # データベースに接続 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() # departmentsテーブルを作成 cursor.execute(''' CREATE TABLE IF NOT EXISTS departments ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ) ''') conn.commit() # 従業員と部署の紐付けを追加 cursor.execute("INSERT INTO departments (name) VALUES ('Sales'), ('Engineering')") conn.commit() # 従業員に部署を割り当てる cursor.execute("UPDATE employees SET department_id = (SELECT id FROM departments WHERE name = 'Sales') WHERE name = 'Alice'") cursor.execute("UPDATE employees SET department_id = (SELECT id FROM departments WHERE name = 'Engineering') WHERE name IN ('Bob', 'Charlie')") conn.commit() # 従業員と部署を結合して表示 cursor.execute(''' SELECT e.name, d.name AS department_name FROM employees e JOIN departments d ON e.department_id = d.id ''') rows = cursor.fetchall() for row in rows: print(row) # データベース接続を閉じる conn.close()
この例では、employeesテーブルとdepartmentsテーブルをdepartment_idカラムで結合し、各従業員の名前と所属部署の名前を表示しています。
Explanation: This code demonstrates how to use a JOIN clause to combine data from multiple tables. It creates a 'departments' table, populates it with some departments, and then links employees to their respective departments by updating the 'department_id' column in the 'employees' table. Finally, it performs a JOIN between the two tables based on the 'department_id' column to retrieve each employee's name and department name. The alias e and d are used for brevity and clarity.
まとめ
本記事では、PythonとSQLの連携に関する基礎的な練習問題10問を紹介しました。これらの問題を実践することで、SQLクエリの作成、データベースへの接続、データの抽出・加工・更新といった一連の流れを理解することができます。
PythonとSQLの組み合わせは、データ処理において非常に強力なツールとなります。本記事で学んだ知識を基に、より複雑なデータ分析やアプリケーション開発に挑戦してみてください。
参照先:
これらのリソースを活用することで、さらに深くPythonとSQLの連携について学ぶことができます。
