Clever Geek Handbook

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

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







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



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



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





データベースに応じた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"])




追加資料（英語）







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



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





More articles:


All Articles