Python を使用して SQL データベースを作成および操作する方法

Python と SQL は、データアナリストにとって最も重要な言語の 2 つです。

この記事では、Python と SQL を接続するために知っておくべきことをすべて説明します。

リレーショナルデータベースからデータを直接機械学習パイプラインに取り込む方法、Python アプリケーションからのデータを独自のデータベースに保存する方法、その他思いつく限りのユースケースを学習します。

一緒に以下について説明します。

Python と SQL を一緒に使用する方法を学ぶ理由は何ですか?
Python 環境と MySQL サーバーをセットアップする方法
Python で MySQL サーバーに接続する
新しいデータベースの作成
テーブルとテーブルの関係の作成
テーブルにデータを追加する
データの読み取り
記録の更新
レコードの削除
Python リストからのレコードの作成
将来的にこれらすべてを行うための再利用可能な関数を作成する

とても便利で素晴らしいものがたくさんあります。さあ、始めましょう！

始める前に簡単なメモ: このチュートリアルで使用されるすべてのコードを含む Jupyter Notebook が、この GitHub リポジトリで入手できます。一緒にコーディングすることを強くお勧めします。

ここで使用されているデータベースと SQL コードはすべて、Towards Data Science に投稿された以前の SQL 入門シリーズのものです (記事の表示に問題がある場合は、私にご連絡ください。無料で参照できるリンクをお送りします)。

SQL やリレーショナルデータベースの背後にある概念に詳しくない場合は、そのシリーズを参照してください (もちろん、freeCodeCamp には膨大な量の優れたコンテンツが用意されています!)。

SQL で Python を使用する理由

データアナリストとデータサイエンティストにとって、Python には多くの利点があります。膨大な範囲のオープンソースライブラリにより、データアナリストにとって非常に便利なツールになります。

データ分析には pandas、NumPy、Vaex、視覚化には Matplotlib、seaborn、Bokeh、機械学習アプリケーションには TensorFlow、scikit-learn、PyTorch (さらに多数) があります。

(比較的) 習得が容易で多用途性を備えているため、Python が最も急速に成長しているプログラミング言語の 1 つであることは不思議ではありません。

データ分析に Python を使用している場合、このデータはどこから来たのかを尋ねる価値があります。

データセットのソースは多種多様ですが、多くの場合、特にエンタープライズビジネスでは、データはリレーショナルデータベースに保存されます。リレーショナルデータベースは、あらゆる種類のデータを作成、読み取り、更新、削除するための非常に効率的かつ強力で広く使用されている方法です。

最も広く使用されているリレーショナルデータベース管理システム (RDBMS) である Oracle、MySQL、Microsoft SQL Server、PostgreSQL、IBM DB2 はすべて、構造化照会言語 (SQL) を使用してデータにアクセスし、データを変更します。

各 RDBMS はわずかに異なる種類の SQL を使用するため、ある RDBMS 用に作成された SQL コードは、通常 (通常はかなり軽微な) 変更を加えなければ、別の RDBMS では機能しないことに注意してください。ただし、概念、構造、操作はほぼ同じです。

これは、働くデータアナリストにとって、SQL を深く理解することが非常に重要であることを意味します。 Python と SQL を組み合わせて使用する方法を理解すると、データを操作する際にさらに有利になります。

この記事の残りの部分では、それを実現する方法を正確に説明することに専念します。

はじめる

要件とインストール

このチュートリアルに沿ってコーディングするには、独自の Python 環境をセットアップする必要があります。

私は Anaconda を使用していますが、これを行う方法はたくさんあります。さらにヘルプが必要な場合は、Google で「Python のインストール方法」を検索してください。 Binder を使用して、関連する Jupyter Notebook とともにコーディングすることもできます。

MySQL Community Server は無料で業界で広く使用されているため、ここでは MySQL Community Server を使用します。 Windows を使用している場合は、このガイドがセットアップに役立ちます。 Mac および Linux ユーザー向けのガイドもここにあります (ただし、Linux ディストリビューションによって異なる場合があります)。

これらを設定したら、相互に通信できるようにする必要があります。

そのためには、MySQL Connector Python ライブラリをインストールする必要があります。これを行うには、指示に従うか、単に pip を使用します。

pip install mysql-connector-python

パンダも使用するので、パンダもインストールしていることを確認してください。

pip install pandas

ライブラリのインポート

Python のすべてのプロジェクトと同様に、最初に行うことはライブラリをインポートすることです。

プロジェクトの開始時に使用するすべてのライブラリをインポートすることがベストプラクティスです。これにより、コードを読んだりレビューしたりする人は、何が起こるかを大まかに把握できるので、驚くことがなくなります。

このチュートリアルでは、MySQL Connector と pandas の 2 つのライブラリのみを使用します。

import mysql.connector
from mysql.connector import Error
import pandas as pd

関数で簡単にアクセスできるように、Error 関数を個別にインポートします。

MySQLサーバーへの接続

この時点で、システム上に MySQL Community Server がセットアップされているはずです。次に、そのサーバーへの接続を確立できるコードを Python で記述する必要があります。

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

このようなコードに再利用可能な関数を作成することは、最小限の労力で何度も使用できるようにするためのベストプラクティスです。これを一度書いてしまえば、今後もすべてのプロジェクトで再利用できるので、将来的には感謝するでしょう。

ここで何が起こっているのかを理解するために、この行を 1 行ずつ見てみましょう。

最初の行では、関数に名前を付け (create_server_connection)、その関数が受け取る引数に名前を付けています (host_name、user_name、および user_password)。

次の行は、サーバーが複数の開いた接続と混同されないように、既存の接続をすべて閉じます。

次に、Python の try-excel ブロックを使用して、潜在的なエラーを処理します。最初の部分では、ユーザーが引数に指定した詳細を使用して、mysql.connector.connect() メソッドを使用してサーバーへの接続を作成しようとします。これが機能すると、関数は嬉しい小さな成功メッセージを出力します。

ブロックの例外部分は、エラーが発生したという残念な状況で MySQL Server が返すエラーを出力します。

最後に、接続が成功すると、関数は接続オブジェクトを返します。

これを実際に使用するには、関数の出力を変数に代入し、それが接続オブジェクトになります。次に、他のメソッド (カーソルなど) をそれに適用し、他の便利なオブジェクトを作成できます。

connection = create_server_connection("localhost", "root", pw)

これにより、成功メッセージが生成されるはずです。

新しいデータベースの作成

接続を確立したので、次のステップはサーバー上に新しいデータベースを作成することです。

このチュートリアルではこれを 1 回だけ実行しますが、これを再利用可能な関数として再度作成します。これにより、将来のプロジェクトで再利用できる便利な関数が得られます。

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

この関数は、connection (接続オブジェクト) と query (次のステップで作成する SQL クエリ) という 2 つの引数を取ります。接続を介してサーバーでクエリを実行します。

接続オブジェクトでカーソルメソッドを使用してカーソルオブジェクトを作成します (MySQL コネクタはオブジェクト指向プログラミングパラダイムを使用しているため、親オブジェクトからプロパティを継承するオブジェクトが多数あります)。

このカーソルオブジェクトには、execute、executemany (このチュートリアルで使用します) などのメソッドと、他のいくつかの便利なメソッドがあります。

それが役立つ場合は、カーソルオブジェクトを、MySQL Server ターミナルウィンドウで点滅するカーソルへのアクセスを提供すると考えることができます。

次に、データベースを作成し、関数を呼び出すクエリを定義します。

このチュートリアルで使用されるすべての SQL クエリは、SQL チュートリアルシリーズで説明されています。完全なコードは、この GitHub リポジトリにある関連する Jupyter Notebook にあります。そのため、このチュートリアルでは SQL コードの動作については説明しません。チュートリアル。

ただし、これはおそらく最も単純な SQL クエリです。英語が読めるなら、それが何をするのか理解できるでしょう。

上記のように引数を指定して create_database 関数を実行すると、「school」というデータベースがサーバーに作成されます。

私たちのデータベースが「学校」と呼ばれているのはなぜですか?おそらく今が、このチュートリアルで実装しようとしているものを正確に詳しく見てみる良い機会かもしれません。

私たちのデータベース

前回のシリーズの例に続いて、インターナショナルランゲージスクール (法人顧客に専門的な語学レッスンを提供する架空の語学研修学校) のデータベースを実装します。

このエンティティ関係図 (ERD) は、エンティティ (教師、クライアント、コース、参加者) をレイアウトし、それらの間の関係を定義します。

ERD とは何か、ERD を作成してデータベースを設計する際に考慮すべき点に関するすべての情報は、この記事に記載されています。

生の SQL コード、データベース要件、データベースに入れるデータはすべてこの GitHub リポジトリに含まれていますが、このチュートリアルを進めるときにもすべてがわかります。

データベースへの接続

MySQL Server にデータベースを作成したので、このデータベースに直接接続するように create_server_connection 関数を変更できます。

1 つの MySQL サーバー上に複数のデータベースが存在する可能性があることに注意してください (実際、一般的です)。そのため、関心のあるデータベースには常に自動的に接続したいと考えています。

これは次のように行うことができます。

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

これはまったく同じ関数ですが、もう 1 つの引数 (データベース名) を取り、それを引数として connect() メソッドに渡します。

クエリ実行関数の作成

(今のところ) 最後に作成する関数は、非常に重要な関数、つまりクエリ実行関数です。これは、Python に文字列として保存された SQL クエリを取得し、cursor.execute() メソッドに渡してサーバー上で実行します。

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

この関数は、SQL クエリで詳述されているコマンドが実装されていることを確認するために connection.commit() メソッドを使用する点を除いて、前述の create_database 関数とまったく同じです。

これは主力関数となり、テーブルの作成、それらのテーブル間の関係の確立、テーブルへのデータの入力、データベース内のレコードの更新と削除に (create_db_connection と併せて) 使用します。

SQL の専門家であれば、この関数を使用すると、そこらに存在する複雑なコマンドやクエリをすべて Python スクリプトから直接実行できます。これはデータを管理するための非常に強力なツールになります。

テーブルの作成

これで、サーバーに対して SQL コマンドを実行し、データベースの構築を開始する準備がすべて整いました。最初に必要なテーブルを作成します。

Teacher テーブルから始めましょう。

create_teacher_table = """
CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  language_1 VARCHAR(3) NOT NULL,
  language_2 VARCHAR(3),
  dob DATE,
  tax_id INT UNIQUE,
  phone_no VARCHAR(20)
  );
 """

connection = create_db_connection("localhost", "root", pw, db) # Connect to the Database
execute_query(connection, create_teacher_table) # Execute our defined query

まず最初に、SQL コマンド (詳細はここで説明) を適切な名前の変数に割り当てます。

この場合、複数行の文字列に Python の三重引用符表記を使用して SQL クエリを保存し、それを実行するためにexecute_query 関数に入力します。

この複数行の書式設定は、単に人間がコードを読むためだけのものであることに注意してください。 SQL も Python も、SQL コマンドがこのように分散されていても「気に」しません。構文が正しい限り、どちらの言語もそれを受け入れます。

ただし、コードを読む人間の利益のために (たとえそれが将来のあなただけであっても)、コードをより読みやすく理解しやすくするためにこれを行うことは非常に役立ちます。

SQL の演算子の CAPITALIZATION にも同じことが当てはまります。これは広く使用されている規則であり、強く推奨されていますが、コードを実行する実際のソフトウェアでは大文字と小文字が区別されず、「CREATE TABLE Teacher」と「create table Teacher」は同一のコマンドとして扱われます。

このコードを実行すると、成功メッセージが表示されます。これは、MySQL Server コマンドラインクライアントでも確認できます。

素晴らしい！次に、残りのテーブルを作成しましょう。

create_client_table = """
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  industry VARCHAR(20)
);
 """

create_participant_table = """
CREATE TABLE participant (
  participant_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  phone_no VARCHAR(20),
  client INT
);
"""

create_course_table = """
CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(40) NOT NULL,
  language VARCHAR(3) NOT NULL,
  level VARCHAR(2),
  course_length_weeks INT,
  start_date DATE,
  in_school BOOLEAN,
  teacher INT,
  client INT
);
"""


connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_client_table)
execute_query(connection, create_participant_table)
execute_query(connection, create_course_table)

これにより、4 つのエンティティに必要な 4 つのテーブルが作成されます。

ここで、それらの間のリレーションシップを定義し、参加者テーブルとコーステーブルの間の多対多リレーションシップを処理するためのテーブルをもう 1 つ作成します (詳細については、ここを参照してください)。

これもまったく同じ方法で行います。

alter_participant = """
ALTER TABLE participant
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

alter_course = """
ALTER TABLE course
ADD FOREIGN KEY(teacher)
REFERENCES teacher(teacher_id)
ON DELETE SET NULL;
"""

alter_course_again = """
ALTER TABLE course
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

create_takescourse_table = """
CREATE TABLE takes_course (
  participant_id INT,
  course_id INT,
  PRIMARY KEY(participant_id, course_id),
  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE
);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, alter_participant)
execute_query(connection, alter_course)
execute_query(connection, alter_course_again)
execute_query(connection, create_takescourse_table)

これで、適切な制約、主キー、外部キー関係とともにテーブルが作成されました。

テーブルにデータを追加する

次のステップでは、いくつかのレコードをテーブルに追加します。ここでも、execute_query を使用して既存の SQL コマンドをサーバーに送ります。もう一度 Teacher テーブルから始めましょう。

pop_teacher = """
INSERT INTO teacher VALUES
(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),
(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), 
(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),
(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),
(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_teacher)

これは機能しますか? MySQL コマンドラインクライアントで再度確認できます。

次に、残りのテーブルにデータを入力します。

pop_client = """
INSERT INTO client VALUES
(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),
(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),
(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),
(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),
(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');
"""

pop_participant = """
INSERT INTO participant VALUES
(101, 'Marina', 'Berg','491635558182', 101),
(102, 'Andrea', 'Duerr', '49159555740', 101),
(103, 'Philipp', 'Probst',  '49155555692', 102),
(104, 'René',  'Brandt',  '4916355546',  102),
(105, 'Susanne', 'Shuster', '49155555779', 102),
(106, 'Christian', 'Schreiner', '49162555375', 101),
(107, 'Harry', 'Kim', '49177555633', 101),
(108, 'Jan', 'Nowak', '49151555824', 101),
(109, 'Pablo', 'Garcia',  '49162555176', 101),
(110, 'Melanie', 'Dreschler', '49151555527', 103),
(111, 'Dieter', 'Durr',  '49178555311', 103),
(112, 'Max', 'Mustermann', '49152555195', 104),
(113, 'Maxine', 'Mustermann', '49177555355', 104),
(114, 'Heiko', 'Fleischer', '49155555581', 105);
"""

pop_course = """
INSERT INTO course VALUES
(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),
(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),
(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),
(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),
(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),
(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),
(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),
(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),
(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);
"""

pop_takescourse = """
INSERT INTO takes_course VALUES
(101, 15),
(101, 17),
(102, 17),
(103, 18),
(104, 18),
(105, 18),
(106, 13),
(107, 13),
(108, 13),
(109, 14),
(109, 15),
(110, 16),
(110, 20),
(111, 16),
(114, 12),
(112, 19),
(113, 19);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_client)
execute_query(connection, pop_participant)
execute_query(connection, pop_course)
execute_query(connection, pop_takescourse)

すばらしい！これで、Python コマンドのみを使用して、MySQL にリレーション、制約、およびレコードを備えたデータベースが作成されました。

理解しやすいように、段階的に説明してきました。しかし、この時点で、これはすべて 1 つの Python スクリプトに非常に簡単に記述でき、ターミナルの 1 つのコマンドで実行できることがわかります。強力なもの。

データの読み取り

これで、機能するデータベースが完成しました。データアナリストは、勤務している組織内の既存のデータベースに触れる可能性があります。これらのデータベースからデータを取り出して、Python データパイプラインに入力できるようにする方法を知っておくと非常に役立ちます。これが私たちが次に取り組むことです。

このためには、もう 1 つ関数が必要になります。今回は、cursor.commit() の代わりにcursor.fetchall() を使用します。この関数では、データベースからデータを読み取り、変更は行いません。

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

繰り返しますが、execute_query と非常に似た方法でこれを実装します。簡単なクエリで試して、どのように機能するかを確認してみましょう。

q1 = """
SELECT *
FROM teacher;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

まさに私たちが期待していることです。この関数は、コーステーブルとクライアントテーブルの JOIN を伴うクエリなど、より複雑なクエリでも機能します。

q5 = """
SELECT course.course_id, course.course_name, course.language, client.client_name, client.address
FROM course
JOIN client
ON course.client = client.client_id
WHERE course.in_school = FALSE;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q5)

for result in results:
  print(result)

非常に素晴らしい。

Python のデータパイプラインとワークフローでは、これらの結果をさまざまな形式で取得して、より使いやすくしたり、操作しやすくしたりすることが必要になる場合があります。

いくつかの例を見て、それを行う方法を見てみましょう。

出力をリストにフォーマットする

#Initialise empty list
from_db = []

# Loop over the results and append them into our list

# Returns a list of tuples
for result in results:
  result = result
  from_db.append(result)

出力をリストのリストにフォーマットする

# Returns a list of lists
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)

出力を pandas DataFrame にフォーマットする

Python を使用するデータアナリストにとって、パンダは私たちの美しく信頼できる古い友人です。データベースからの出力を DataFrame に変換するのは非常に簡単で、そこからの可能性は無限です。

# Returns a list of lists and then creates a pandas DataFrame
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)


columns = ["course_id", "course_name", "language", "client_name", "address"]
df = pd.DataFrame(from_db, columns=columns)

ここであなたの目の前に広がる可能性を見ていただければ幸いです。わずか数行のコードで、処理可能なすべてのデータを、それが存在するリレーショナルデータベースから簡単に抽出し、最先端のデータ分析パイプラインに取り込むことができます。これは本当に役立つものです。

記録の更新

データベースを保守しているとき、既存のレコードを変更する必要がある場合があります。このセクションでは、その方法を見ていきます。

ILS が、既存顧客の 1 つである Big Business Federation がオフィスを 23 Fingiertweg, 14534 Berlin に移転することを通知されたとします。この場合、データベース管理者 (つまり私たちです!) はいくつかの変更を加える必要があります。

ありがたいことに、SQL UPDATE ステートメントと一緒にexecute_query関数を使用してこれを行うことができます。

update = """
UPDATE client 
SET address = '23 Fingiertweg, 14534 Berlin' 
WHERE client_id = 101;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

ここでは WHERE 句が非常に重要であることに注意してください。 WHERE 句を指定せずにこのクエリを実行すると、Client テーブル内のすべてのレコードのすべてのアドレスが 23 Fingertweg に更新されます。それは私たちがやりたいことではありません。

UPDATE クエリで「WHERE client_id=101」を使用していることにも注意してください。「WHERE client_name='Big Business Federation'」または「WHERE address='123 Falschungstraße, 10999 Berlin'」、さらには「WHERE address LIKE '%Falschung%'」を使用することも可能でした。

重要なことは、WHERE 句を使用すると、更新するレコードを一意に識別できるということです。

レコードの削除

DELETE を使用して、execute_query 関数を使用してレコードを削除することもできます。

リレーショナルデータベースで SQL を使用する場合、DELETE 演算子の使用には注意する必要があります。これは Windows ではありません。「これを削除してもよろしいですか?」というメッセージは表示されません。警告ポップアップが表示され、ごみ箱がありません。何かを削除すると、それは本当に消えてしまいます。

そうは言っても、実際には時々削除する必要があります。それでは、コーステーブルからコースを削除して、それを見てみましょう。

まずは、どんなコースがあるのか思い出してみましょう。

コース 20 の「Fortgeschrittenes Russisch」（あなたと私にとっては「上級ロシア語」です）が終わりに近づいているので、データベースから削除する必要があるとします。

この段階では、SQL コマンドを文字列として保存し、それを主力のexecute_query 関数に入力するというこの方法にまったく驚かないでしょう。

delete_course = """
DELETE FROM course 
WHERE course_id = 20;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, delete_course)

意図した効果があったことを確認してみましょう。

予想通り、「上級ロシア語」はなくなりました。

これは、DROP COLUMN コマンドを使用して列全体を削除したり、DROP TABLE コマンドを使用してテーブル全体を削除したりする場合にも機能しますが、このチュートリアルでは説明しません。

ただし、実際に試してみてください。架空の学校のデータベースから列やテーブルを削除しても問題はありません。運用環境に移行する前に、これらのコマンドに慣れておくことをお勧めします。

ああ、くどい

この時点で、永続的なデータストレージのための 4 つの主要な操作を完了できるようになりました。

私たちは次の方法を学びました。

作成 - まったく新しいデータベース、テーブル、レコード
読み取り - データベースからデータを抽出し、そのデータを複数の形式で保存します
更新 - データベース内の既存のレコードを変更します。
削除 - 不要になったレコードを削除します

これらは非常に便利な機能です。

ここでの作業を終了する前に、もう 1 つ習得すべき非常に便利なスキルがあります。

リストからのレコードの作成

テーブルにデータを設定するときに、execute_query 関数で SQL INSERT コマンドを使用してデータベースにレコードを挿入できることがわかりました。

Python を使用して SQL データベースを操作していることを考えると、Python データ構造 (リストなど) を取得してデータベースに直接挿入できれば便利です。

これは、Python で作成したソーシャルメディアアプリでのユーザーアクティビティのログを保存したり、構築した Wiki にユーザーからの入力を保存したりする場合などに便利です。これには、考えられる限り多くの用途があります。

この方法は、データベースがいつでもユーザーに公開されている場合、データベース全体に損傷を与えたり、場合によっては破壊する可能性がある SQL インジェクション攻撃を防ぐのに役立つため、より安全です。

これを行うには、これまで使用してきた単純なexecute()メソッドの代わりに、executemany()メソッドを使用して関数を作成します。

def execute_list_query(connection, sql, val):
    cursor = connection.cursor()
    try:
        cursor.executemany(sql, val)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

これで関数が完成しました。SQL コマンド ('sql') と、データベースに入力する値を含むリスト ('val') を定義する必要があります。値はタプルのリストとして保存する必要があります。これは、Python でデータを保存する非常に一般的な方法です。

データベースに 2 人の新しい教師を追加するには、次のようなコードを記述します。

sql = '''
    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) 
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    '''
    
val = [
    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), 
    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')
]

ここで、「sql」コードでは値のプレースホルダーとして「%s」を使用していることに注意してください。 Python の文字列の '%s' プレースホルダーとの類似点は単なる偶然です (そして率直に言って、非常に混乱を招きます)。MySQL Python ではすべてのデータ型 (文字列、整数、日付など) に '%s' を使用したいと考えています。コネクタ。

Stackoverflow では、Python でこれを行うことに慣れているため、誰かが混乱して整数に '%d' プレースホルダーを使用しようとしたという多くの質問を見ることができます。これはここでは機能しません。値を追加したい列ごとに '%s' を使用する必要があります。

次に、executemany 関数は、「val」リスト内の各タプルを取得し、プレースホルダーの代わりにその列に関連する値を挿入し、リストに含まれる各タプルに対して SQL コマンドを実行します。

これは、データが正しくフォーマットされている限り、複数行のデータに対して実行できます。この例では、説明のために 2 人の新しい教師を追加するだけですが、原則的には必要なだけ追加できます。

このクエリを実行して、教師をデータベースに追加しましょう。

connection = create_db_connection("localhost", "root", pw, db)
execute_list_query(connection, sql, val)

ILS へようこそ、ハンクとスー！

これはさらにもう 1 つの非常に便利な機能で、Python スクリプトやアプリケーションで生成されたデータを取得し、データベースに直接入力できるようになります。

結論

このチュートリアルでは多くのことを取り上げてきました。

Python と MySQL Connector を使用して、MySQL Server にまったく新しいデータベースを作成し、そのデータベース内にテーブルを作成し、それらのテーブル間の関係を定義し、テーブルにデータを設定する方法を学びました。

データベース内のデータを作成、読み取り、更新、削除する方法について説明しました。

既存のデータベースからデータを抽出して pandas DataFrame にロードし、PyData スタックが提供するすべての可能性を活用して分析やさらなる作業の準備を整える方法を検討してきました。

逆に、Python スクリプトとアプリケーションによって生成されたデータを取得し、それらをデータベースに書き込み、後で取得したり操作したりできるように安全に保存する方法も学びました。

このチュートリアルが、Python と SQL を併用してデータをさらに効果的に操作する方法を理解するのに役立つことを願っています。

私のプロジェクトや仕事をもっと見たい場合は、私の Web サイト craigdoesdata.de にアクセスしてください。このチュートリアルに関するフィードバックがある場合は、私に直接ご連絡ください。すべてのフィードバックは温かく受け取られます。