😒 🤖 🚢 Python：データベースの操作、パート1/2：DB-APIの使用 👩🏽‍🚀 🎨 👨🏼‍🚀

Python DB-APIは特定のライブラリではなく、個々のモジュールが特定のデータベースを操作する際に従う一連のルールです。異なるデータベースの個々の実装のニュアンスは異なる場合がありますが、一般的な原則により、異なるデータベースを操作するときに同じアプローチを使用できます。

この記事では、データベースを完全に操作できるDB-APIの基本的な方法について説明します。完全なリストは、記事の最後にあるリンクにあります。

必須レベル ：SQLおよびPython構文の基本的な理解。

さらに快適な作業のために在庫を準備します

PythonにはSQLiteデータベースのサポートが組み込まれています。このため、追加のものをインストールする必要はなく、スクリプトで標準ライブラリのインポートを指定するだけです。
```
import sqlite3 
```
作業するテストデータベースをダウンロードします。この記事では、「Chinook」オープンテストデータベース（MITライセンス）を使用します。次のリンクからダウンロードできます。

chinookdatabase.codeplex.com
github.com/lerocha/chinook-database

動作するのはバイナリ「Chinook_Sqlite.sqlite」のみです。
データベースの操作（表示、編集）の利便性のために、SQLiteをサポートするデータベースブラウザプログラムが必要です。この記事ではブラウザーの操作については説明しませんが、実験中にデータベースで何が起こっているかを視覚的に確認するのに役立ちます。

注：データベースに変更を加えるときは、変更を適用することを忘れないでください。変更が適用されていないデータベースはロックされたままです。

次のオプションを使用できます（最後の2つのオプションはクロスプラットフォームで無料です）：
- IDEの一部としてデータベースを操作するための使い慣れたユーティリティ。
- SQLiteデータベースブラウザー
- SQLiteStudio

データベースに応じたPython DB-APIモジュール

データベース	DB-APIモジュール
Sqlite	sqlite3
PostgreSQL	psycopg2
MySQL	mysql.connector
ODBC	pyodbc

ベースへの接続、カーソルの取得

最初に、最も基本的なDB-APIテンプレートを検討します。これは、以降のすべての例で使用します。

 #  ,      import sqlite3 #       #          conn = sqlite3.connect('Chinook_Sqlite.sqlite') #   -           cursor = conn.cursor() #         #        #        conn.close()

他のデータベースを使用する場合、たとえばPostrgeSQLの場合、追加の接続パラメーターが使用されます。

 conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database)

ベースからの読み取り

 #  SELECT    ,   SQL- cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") #     results = cursor.fetchall() results2 = cursor.fetchall() print(results) # [('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',), ('Aaron Goldberg',)] print(results2) # []

注：カーソルから結果を受け取った後、2回目はクエリ自体を繰り返さなければ結果を取得できません-空の結果が返されます！

データベースに書き込む

 #  INSERT    ,   SQL- cursor.execute("insert into Artist values (Null, 'A Aagrh!') ") #     ,        -    conn.commit() #   cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") results = cursor.fetchall() print(results) # [('A Aagrh!',), ('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',)]

注：データベースに対して複数の接続が確立され、そのうちの1つがデータベースを変更する場合、トランザクションが完了する（接続方法。 コミット （））またはキャンセルされる（接続方法。 ロールバック （）メソッド）まで、SQLiteデータベースはロックされます。

クエリを三重引用符で複数行に分割します

長いクエリは、単一引用符（ '' '...' ''）または二重引用符（ "" "..." ""）で囲まれている場合、任意の順序で複数の行に分割できます。

 cursor.execute(""" SELECT name FROM Artist ORDER BY Name LIMIT 3 """)

もちろん、このような単純な例では、ブレークダウンは意味をなしませんが、複雑な長いクエリでは、コードの可読性を劇的に向上させることができます。

1つのメソッド呼び出しでデータベースクエリを組み合わせる

.execute（）カーソルメソッドを使用すると、一度に1つの要求のみを行うことができます;セミコロンを介して複数の要求を行おうとするとエラーが発生します。

言葉を信じない人のために：

 cursor.execute(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """) # sqlite3.Warning: You can only execute one statement at a time.

この問題を解決するには、カーソルメソッドを数回呼び出すことができます。実行（）

 cursor.execute("""insert into Artist values (Null, 'A Aagrh!');""") cursor.execute("""insert into Artist values (Null, 'A Aagrh-2!');""")

または、カーソルメソッドを使用します。 executescript （）

 cursor.executescript(""" insert into Artist values (Null, 'A Aagrh!'); insert into Artist values (Null, 'A Aagrh-2!'); """)

このメソッドは、クエリが別の変数またはファイルに保存されていて、そのようなクエリをデータベースに適用する必要がある場合にも便利です。

リクエストで値の置換を行います

重要！どのような状況でも、文字列連結（+）または文字列内のパラメーター補間（％）を使用して変数をSQLクエリに転送しないでください。このようなクエリの形成は、ユーザーデータがそれに該当する可能性がある場合、SQLインジェクションへのゲートウェイです！

正しい方法は、.execute（）メソッドの2番目の引数を使用することです

次の2つのオプションが可能です。

 # C       : cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT ?", ('2')) #     : cursor.execute("SELECT Name from Artist ORDER BY Name LIMIT :limit", {"limit": 3})

注1 ： '？'の代わりにPostgreSQL（UPD：およびMySQL）で置換に使用：％s

注2 ：この方法では、テーブル名を置き換えることはできません。この場合に考えられる解決策の1つをここで検討します： stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553

UPD：注3 ： paramstyleパラメーターについて言及してくれたIgelkoに感謝します-このモジュールで変数を置換するために使用されるスタイルを決定します。
さまざまな検索スタイルを操作するための便利なトリックのリンクを次に示します。

カーソルメソッドを使用して、コレクションを通過する行を複数挿入します。 executemany （）

 #  ,     -    ! #        ! new_artists = [ ('A Aagrh!',), ('A Aagrh!-2',), ('A Aagrh!-3',), ] cursor.executemany("insert into Artist values (Null, ?);", new_artists)

カーソルメソッドを使用して、結果を1つずつ取得します。 fetchone （）

常にタプルまたはNoneを返します。リクエストが空の場合。

 cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3") print(cursor.fetchone()) # ('A Cor Do Som',) print(cursor.fetchone()) # ('Aaron Copland & London Symphony Orchestra',) print(cursor.fetchone()) # ('Aaron Goldberg',) print(cursor.fetchone()) # None

重要！ .fetchall（）または.fetchone（）のどちらを使用するかに関係なく、標準カーソルはサーバーからすべてのデータを一度に取得します

イテレーターとしてのカーソル

 #     for row in cursor.execute('SELECT Name from Artist ORDER BY Name LIMIT 3'): print(row) # ('A Cor Do Som',) # ('Aaron Copland & London Symphony Orchestra',) # ('Aaron Goldberg',)

UPD：コードの安定性の向上

貴重な追加をしてくれたパラタガスに感謝します：
プログラムをより安定させるために（特に書き込み操作中に）、データベースアクセス命令をtry-except-elseブロックでラップし、次のようにsqlite3のネイティブエラーオブジェクトを使用できます。

 try: cursor.execute(sql_statement) result = cursor.fetchall() except sqlite3.DatabaseError as err: print("Error: ", err) else: conn.commit()

UPD：psycopg2でwithを使用する

貴重な追加をしてくれたKurtRotzkeに感謝します。
psycopg2の最近のバージョンでは、これを行うことができます。

 with psycopg2.connect("dbname='habr'") as conn: with conn.cursor() as cur:

Pythonの一部のオブジェクトには__enter__および__exit__メソッドがあり、上記の例のように、これらのオブジェクトと「きれいに」対話することができます。

UPD：row_factoryの使用

貴重な追加をしてくれたremzalpに感謝します：
row_factoryを使用すると、クエリからメタデータを取得し、最終的に、たとえば列の名前で結果にアクセスできます。
基本的に-文字列を返すときにデータを処理するコールバック。はい、必要なものがすべて揃っている最も便利なcursor.descriptionです。

ドキュメントの例：

 import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con = sqlite3.connect(":memory:") con.row_factory = dict_factory cur = con.cursor() cur.execute("select 1 as a") print(cur.fetchone()["a"])

追加資料（英語）

短い無料オンラインコース-Udacity-リレーショナルデータベース入門-SQLの構文と原理、Python DB-API-理論と実践を1つのパッケージで説明します。初心者に強くお勧めします！
Pythonでの高度なSQLiteの使用
tutorialspoint.comのSQLite Pythonチュートリアル
PythonでのSQLiteデータベース操作の徹底ガイド
UPD： Python 3 DB-APIの初心者向けガイド
SQLiteオンラインガイド：

記事の2番目の部分は開発中です。SQLAlchemyを使用してPythonでデータベースを操作する方法について説明します。

ディスカッションに招待します。

私がどこかで間違えたか、重要なことを考慮しなかった場合-コメントを書いてください。重要なコメントは、著者名を示す記事の後半に追加されます。
明確でない点があり、説明が必要な場合は、コメントに質問を書いてください。または、私または他の読者が答えを出すと、答えのある実用的な質問が後で記事に追加されます。

Python：データベースの操作、パート1/2：DB-APIの使用