Pandas での SQL ファイルの読み取りと書き込み

プラトン再発行

フォロワー： 0

数年前にデータ分析を学び始めたとき、最初に学んだのは SQL と Pandas でした。データアナリストとして、SQL と Pandas を使用するための強力な基盤を持つことが重要です。どちらも、データアナリストがデータベースに保存されているデータを効率的に分析および操作するのに役立つ強力なツールです。

SQL と Pandas の概要

SQL (Structured Query Language) は、リレーショナルデータベースの管理と操作に使用されるプログラミング言語です。一方、Pandas はデータ操作と分析に使用される Python ライブラリです。

データ分析には大量のデータの操作が含まれ、このデータを保存するためにデータベースがよく使用されます。 SQL と Pandas は、データベースを操作するための強力なツールを提供し、データアナリストがデータを効率的に抽出、操作、分析できるようにします。これらのツールを活用することで、データアナリストは、他のツールでは入手が困難なデータから貴重な洞察を得ることができます。

この記事では、SQL と Pandas を使用してデータベースの読み取りと書き込みを行う方法を説明します。

DBに接続する

ライブラリのインストール

Pandas を使用して SQL データベースに接続するには、まず必要なライブラリをインストールする必要があります。必要な XNUMX つの主なライブラリは、Pandas と SQLAlchemy です。 Pandas は、冒頭で述べたように、大規模なデータ構造の保存を可能にする人気のあるデータ操作ライブラリです。対照的に、SQLAlchemy は、SQL データベースに接続して対話するための API を提供します。

コマンドプロンプトで次のコマンドを実行すると、Python パッケージマネージャー pip を使用して両方のライブラリをインストールできます。

$ pip install pandas
$ pip install sqlalchemy

接続する

ライブラリがインストールされたら、Pandas を使用して SQL データベースに接続できるようになります。

まず、SQLAlchemy エンジンオブジェクトを作成します。 create_engine()を選択します。 create_engine() 関数は Python コードをデータベースに接続します。データベースの種類と接続の詳細を指定する接続文字列を引数として受け取ります。この例では、SQLite データベースの種類とデータベースファイルのパスを使用します。

以下の例を使用して、SQLite データベースのエンジンオブジェクトを作成します。

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

SQLite データベースファイル (この場合は Student.db) が Python スクリプトと同じディレクトリにある場合は、以下に示すようにファイル名を直接使用できます。

engine = create_engine('sqlite:///student.db')

Pandas を使用した SQL ファイルの読み取り

接続が確立できたので、データを読み取ってみましょう。このセクションでは、 read_sql, read_sql_table, read_sql_query 関数と、それらを使用してデータベースを操作する方法。

Panda を使用した SQL クエリの実行 read_sql() 演算

　 read_sql() は、SQL クエリを実行し、結果を Pandas データフレームに取得できるようにする Pandas ライブラリ関数です。の read_sql() function は SQL と Python を接続し、両方の言語の力を活用できるようにします。関数はラップします read_sql_table() および read_sql_query()を選択します。 read_sql() 関数は、提供された入力に基づいて内部的にルーティングされます。つまり、入力が SQL クエリを実行する場合、その入力は次のようにルーティングされます。 read_sql_query()、データベーステーブルの場合は、にルーティングされます。 read_sql_table().

　 read_sql() 構文は次のとおりです。

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

SQL および con パラメータは必須です。残りはオプションです。ただし、これらのオプションのパラメーターを使用して結果を操作できます。各パラメータを詳しく見てみましょう。

sql: SQLクエリまたはデータベーステーブル名
con: 接続オブジェクトまたは接続 URL
index_col: このパラメータを使用すると、SQL クエリ結果の XNUMX つ以上の列をデータフレームインデックスとして使用できます。単一の列または列のリストを指定できます。
coerce_float: このパラメータは、数値以外の値を浮動小数点数に変換するか文字列のままにするかを指定します。デフォルトでは true に設定されています。可能であれば、数値以外の値を float 型に変換します。
params: パラメータは、動的値を SQL クエリに渡すための安全な方法を提供します。 params パラメータを使用して、辞書、タプル、またはリストを渡すことができます。データベースに応じて、params の構文は異なります。
parse_dates: これにより、結果として得られるデータフレーム内のどの列を日付として解釈するかを指定できます。単一の列、列のリスト、またはキーを列名として、値を列形式として持つディクショナリを受け入れます。
columns: これにより、リストから選択した列のみをフェッチできるようになります。
chunksize: 大規模なデータセットを扱う場合、チャンクサイズが重要です。クエリ結果をより小さなチャンクで取得し、パフォーマンスを向上させます。

これが使用方法の例です read_sql():

コード：

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

出力：

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson  2000-02-23
2 Peter Griffen  2001-04-15
3 Meg Aniston  2001-09-20
Date type of dateOfBirth: datetime64[ns]

データベースに接続した後、データベースからすべてのレコードを返すクエリを実行します。 Student テーブルを作成し、DataFrame に保存します。 df。「ロール番号」列は、 index_col パラメータ、および「dateOfBirth」データ型は「datetime64[ns]」です。 parse_dates。使用できます read_sql() データを取得するだけでなく、挿入、削除、更新などの他の操作も実行できます。 read_sql() ジェネリック関数です。

DB からの特定のテーブルまたはビューのロード

Pandas を使用した特定のテーブルまたはビューのロード read_sql_table() これは、データベースから Pandas データフレームにデータを読み取る別の手法です。

何ですか read_sql_table?

Pandas ライブラリは以下を提供します。 read_sql_table この関数は、クエリを実行せずに SQL テーブル全体を読み取り、結果を Pandas データフレームとして返すように特別に設計されています。

の構文 read_sql_table() 以下の通りです：

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

を除いて table_name およびスキーマ、パラメータは次と同じ方法で説明されます。 read_sql().

table_name：パラメータ table_name データベース内の SQL テーブルの名前です。
schema: このオプションのパラメータは、テーブル名を含むスキーマの名前です。

データベースへの接続を作成した後、 read_sql_table をロードする関数 Student テーブルを Pandas DataFrame に追加します。

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

出力：

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
1 2 Peter Griffen  2001-04-15
2 3 Meg Aniston  2001-09-20

メモリを大量に消費する可能性のある大きなテーブルであると仮定します。をどのように使用できるかを調べてみましょう chunksize この問題に対処するためのパラメータ。

ベストプラクティス、業界で認められた標準、および含まれているチートシートを含む、Gitを学習するための実践的で実用的なガイドを確認してください。グーグルGitコマンドを停止し、実際に学ぶそれ！

コード：

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

出力：

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson  2000-02-23
0 2 Peter Griffen  2001-04-15
0 3 Meg Aniston  2001-09-20

覚えておいてください chunksize テーブルにレコードが 1 つしかないため、here is 3 を使用しています。

Pandas の SQL 構文を使用して DB に直接クエリを実行する

データベースから洞察を抽出することは、データアナリストや科学者にとって重要な部分です。そのために、 read_sql_query() 機能。

read_sql_query() とは何ですか?

パンダの使用 read_sql_query() 関数を使用すると、SQL クエリを実行して結果を直接 DataFrame に取得できます。の read_sql_query() 関数は特別に作成されています SELECT 発言。他の操作には使用できません。 DELETE or UPDATE.

構文：

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

すべてのパラメータの説明は、 read_sql() 関数。以下にその例を示します read_sql_query():

コード：

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

出力：

 firstName lastName
0 Mark Simson

Pandas を使用した SQL ファイルの作成

データの分析中に、いくつかのエントリを変更する必要があるか、データを含む新しいテーブルまたはビューが必要であることが判明したとします。新しいレコードを更新または挿入するには、次の方法があります。 read_sql() そしてクエリを書きます。ただし、その方法は時間がかかる可能性があります。 Panda は、と呼ばれる優れたメソッドを提供します。 to_sql() このような状況のために。

このセクションでは、まずデータベースに新しいテーブルを構築し、次に既存のテーブルを編集します。

SQLデータベースに新しいテーブルを作成する

新しいテーブルを作成する前に、まず次のことについて説明します。 to_sql() 詳細に。

何ですか to_sql()?

　 to_sql() Pandas ライブラリの関数を使用すると、データベースの書き込みまたは更新が可能になります。の to_sql() 関数は DataFrame データを SQL データベースに保存できます。

の構文 to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

のみ name および con パラメータは実行に必須です to_sql(); ただし、他のパラメータを使用すると、さらなる柔軟性とカスタマイズオプションが提供されます。各パラメータについて詳しく説明します。

name: 作成または変更する SQL テーブルの名前。
con: データベースの接続オブジェクト。
schema: テーブルのスキーマ (オプション)。
if_exists: このパラメータのデフォルト値は「fail」です。このパラメータを使用すると、テーブルがすでに存在する場合に実行するアクションを決定できます。オプションには、「失敗」、「置換」、および「追加」が含まれます。
index: インデックスパラメータはブール値を受け入れます。デフォルトでは、これは True に設定されており、DataFrame のインデックスが SQL テーブルに書き込まれることを意味します。
index_label: このオプションのパラメータを使用すると、インデックス列の列ラベルを指定できます。デフォルトでは、インデックスはテーブルに書き込まれますが、このパラメータを使用して特定の名前を指定できます。
chunksize: SQL データベースに一度に書き込まれる行数。
dtype: このパラメータは、列名としてキー、データ型として値を含むディクショナリを受け入れます。
method: Method パラメーターを使用すると、SQL にデータを挿入するために使用されるメソッドを指定できます。デフォルトでは [なし] に設定されており、パンダはデータベースに基づいて最も効率的な方法を見つけます。メソッドパラメータには主に XNUMX つのオプションがあります。
- multi: 単一の SQL クエリに複数の行を挿入できます。ただし、すべてのデータベースが複数行の挿入をサポートしているわけではありません。
- 呼び出し可能な関数: ここでは、挿入用のカスタム関数を作成し、メソッドパラメーターを使用してそれを呼び出すことができます。

これはを使用した例です to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

Customer という名前の新しいテーブルがデータベースに作成され、「Name」と「Age」という XNUMX つのフィールドが含まれます。

データベースのスナップショット:

to_sql() の出力

Pandas データフレームを使用した既存のテーブルの更新

データベース内のデータの更新は、特に大規模なデータを扱う場合には複雑なタスクです。ただし、 to_sql() Pandas の関数を使用すると、このタスクがはるかに簡単になります。データベース内の既存のテーブルを更新するには、 to_sql() 関数は、 if_exists パラメータを「置換」に設定します。これにより、既存のテーブルが新しいデータで上書きされます。

以下はその例です to_sql() 以前に作成されたものを更新します Customer テーブル。仮に、 Customer このテーブルでは、Paul という名前の顧客の年齢を 9 歳から 10 歳に更新したいと考えています。これを行うには、まず DataFrame 内の対応する行を変更し、次に to_sql() データベースを更新する機能。

コード：

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

データベースでは、ポールの年齢が更新されます。

to_sql() の出力

まとめ

結論として、Pandas と SQL はどちらも、SQL データベースへのデータの読み取りや書き込みなどのデータ分析タスクのための強力なツールです。 Pandas は、SQL データベースに接続し、データベースから Pandas データフレームにデータを読み取り、データフレームデータをデータベースに書き戻す簡単な方法を提供します。

Pandas ライブラリを使用すると、データフレーム内のデータを簡単に操作できます。一方、SQL は、データベース内のデータをクエリするための強力な言語を提供します。 Pandas と SQL の両方を使用してデータの読み取りと書き込みを行うと、特にデータが非常に大きい場合に、データ分析タスクの時間と労力を節約できます。全体として、SQL と Pandas を併用すると、データアナリストや科学者がワークフローを合理化するのに役立ちます。