Python3-python-database-access
Python 3-MySQLデータベースアクセス
データベースインターフェイスのPython標準は、Python DB-APIです。 ほとんどのPythonデータベースインターフェイスは、この標準に準拠しています。
アプリケーションに適したデータベースを選択できます。 Python Database APIは、次のような幅広いデータベースサーバーをサポートしています-
- ガドフライ
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000
- Informix
- Interbase
- オラクル
- Sybase
- SQLite
利用可能なPythonデータベースインターフェースのリストを以下に示します-https://wiki.python.org/moin/DatabaseInterfaces[Python Database Interfaces and APIs]。 アクセスする必要のあるデータベースごとに個別のDB APIモジュールをダウンロードする必要があります。 たとえば、MySQLデータベースだけでなくOracleデータベースにもアクセスする必要がある場合、OracleとMySQLデータベースモジュールの両方をダウンロードする必要があります。
DB APIは、可能な限りPythonの構造と構文を使用してデータベースを操作するための最小限の標準を提供します。 このAPIには以下が含まれます-
- APIモジュールのインポート。
- データベースとの接続を取得します。
- SQLステートメントとストアドプロシージャの発行。
- 接続を閉じる
PythonにはSQLiteの組み込みサポートがあります。 このセクションでは、MySQLを使用してすべての概念を学習します。 MySQLとの一般的なインターフェイスであるMySQLdbモジュールは、Python 3と互換性がありません。 代わりに、http://www.pymysql.org/[PyMySQL]モジュールを使用します。
PyMySQLとは何ですか?
PyMySQLは、PythonからMySQLデータベースサーバーに接続するためのインターフェイスです。 Python Database API v2.0を実装し、純粋なPython MySQLクライアントライブラリが含まれています。 PyMySQLの目標は、MySQLdbのドロップイン代替物になることです。
PyMySQLをインストールする方法は?
さらに先に進む前に、マシンにPyMySQLがインストールされていることを確認してください。 Pythonスクリプトに次を入力して実行するだけです-
#!/usr/bin/python3
import pymysql
それは次の結果を生成する場合、それはMySQLdbモジュールがインストールされていないことを意味します-
Traceback (most recent call last):
File "test.py", line 3, in <module>
Import pymysql
ImportError: No module named pymysql
最後の安定版リリースはPyPIで利用でき、pipでインストールできます-
pip install pymysql
別の方法(例: pipが利用できない場合)、tarballはhttps://github.com/PyMySQL/PyMySQL[GitHub]からダウンロードでき、次のようにSetuptoolsでインストールできます-
$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.
注意-上記のモジュールをインストールするためのルート権限があることを確認してください。
データベース接続
MySQLデータベースに接続する前に、次の点を確認してください-
- データベースTESTDBを作成しました。
- TESTDBにテーブルEMPLOYEEを作成しました。
- このテーブルには、FIRST_NAME、LAST_NAME、AGE、SEX、およびINCOMEフィールドがあります。
- ユーザーID「testuser」とパスワード「test123」は、TESTDBにアクセスするように設定されています。
- PythonモジュールPyMySQLがマシンに適切にインストールされています。
- リンクを理解するためにMySQLチュートリアルを完了しました:/mysql/index [MySQL Basics。]
例
以下は、MySQLデータベース「TESTDB」との接続例です-
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)
# disconnect from server
db.close()
このスクリプトを実行すると、次の結果が生成されます。
Database version : 5.5.20-log
データソースとの接続が確立されると、接続オブジェクトが返され、さらに使用するために db に保存されます。それ以外の場合、 db はNoneに設定されます。 次に、 db オブジェクトを使用して cursor オブジェクトを作成し、これを使用してSQLクエリを実行します。 最後に、出てくる前に、データベース接続が閉じられ、リソースが解放されるようにします。
データベーステーブルの作成
データベース接続が確立されると、作成されたカーソルの execute メソッドを使用して、データベーステーブルにテーブルまたはレコードを作成する準備ができました。
例
データベーステーブルEMPLOYEEを作成しましょう-
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# disconnect from server
db.close()
INSERT操作
データベーステーブルにレコードを作成する場合は、INSERT操作が必要です。
例
次の例では、SQL _INSERT_ステートメントを実行して、EMPLOYEEテーブルにレコードを作成します-
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
上記の例は、SQLクエリを動的に作成するために次のように記述できます-
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
('Mac', 'Mohan', 20, 'M', 2000)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
例
次のコードセグメントは、パラメータを直接渡すことができる実行の別の形式です-
..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
(user_id, password))
..................................
読み取り操作
任意のデータベースに対するREAD操作は、データベースからいくつかの有用な情報を取得することを意味します。
データベース接続が確立されると、このデータベースにクエリを実行する準備が整います。 * fetchone()メソッドを使用して単一のレコードをフェッチするか、 fetchall()*メソッドを使用してデータベーステーブルから複数の値をフェッチできます。
- * fetchone()*-クエリ結果セットの次の行をフェッチします。 結果セットは、カーソルオブジェクトを使用してテーブルを照会するときに返されるオブジェクトです。
- * fetchall()*-結果セットのすべての行をフェッチします。 結果セットからすでにいくつかの行が抽出されている場合、取得します 結果セットの残りの行。
- rowcount -これは読み取り専用の属性であり、execute()メソッドの影響を受けた行の数を返します。
例
次の手順では、給与が1000を超えるEMPLOYEEテーブルのすべてのレコードを照会します-
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > '%d'" % (1000)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print fetched result
print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
# disconnect from server
db.close()
出力
これは、次の結果を生成します-
fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000
更新操作
データベースでのUPDATE操作とは、データベースですでに使用可能な1つ以上のレコードを更新することです。
次の手順は、SEXが 'M' であるすべてのレコードを更新します。 ここでは、すべての男性の年齢を1年増やします。
例
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
WHERE SEX = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
削除操作
データベースから一部のレコードを削除する場合は、DELETE操作が必要です。 以下は、年齢が20を超える従業員からすべてのレコードを削除する手順です。
例
#!/usr/bin/python3
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
トランザクションの実行
トランザクションは、データの一貫性を保証するメカニズムです。 トランザクションには、次の4つのプロパティがあります-
- Atomicity -トランザクションが完了するか、まったく何も起こりません。
- 一貫性-トランザクションは一貫した状態で開始し、システムを一貫した状態のままにする必要があります。
- 分離-トランザクションの中間結果は、現在のトランザクションの外部では見えません。
- 耐久性-トランザクションがコミットされると、システム障害が発生した後でも効果は持続します。
Python DB API 2.0は、トランザクションを_commit_または_rollback_する2つのメソッドを提供します。
例
トランザクションを実装する方法はすでに知っています。 ここに同様の例があります-
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
コミット操作
コミットは、変更をファイナライズするためにデータベースに緑のシグナルを与える操作です。この操作の後、変更を元に戻すことはできません。
*commit* メソッドを呼び出す簡単な例を次に示します。
db.commit()
ロールバック操作
1つ以上の変更に満足せず、それらの変更を完全に元に戻したい場合は、* rollback()*メソッドを使用します。
- rollback()*メソッドを呼び出す簡単な例を次に示します。
db.rollback()
データベースの切断
データベース接続を切断するには、close()メソッドを使用します。
db.close()
データベースへの接続がclose()メソッドを使用してユーザーによって閉じられた場合、未処理のトランザクションはすべてDBによってロールバックされます。 ただし、DBの下位レベルの実装の詳細に依存する代わりに、アプリケーションで明示的にcommitまたはrollbackを呼び出す方が適切です。
エラー処理
エラーには多くの原因があります。 いくつかの例は、実行されたSQLステートメントの構文エラー、接続障害、または既にキャンセルまたは終了したステートメントハンドルのフェッチメソッドの呼び出しです。
DB APIは、各データベースモジュールに存在する必要があるエラーの数を定義します。 次の表に、これらの例外をリストします。
Sr.No. | Exception & Description |
---|---|
1 |
Warning 致命的でない問題に使用されます。 StandardErrorをサブクラス化する必要があります。 |
2 |
Error エラーの基本クラス。 StandardErrorをサブクラス化する必要があります。 |
3 |
InterfaceError データベース自体ではなく、データベースモジュールのエラーに使用されます。 エラーをサブクラス化する必要があります。 |
4 |
DatabaseError データベースのエラーに使用されます。 エラーをサブクラス化する必要があります。 |
5 |
DataError データのエラーを参照するDatabaseErrorのサブクラス。 |
6 |
OperationalError データベースへの接続が失われるなどのエラーを参照するDatabaseErrorのサブクラス。 これらのエラーは通常、Pythonスクリプターの制御外です。 |
7 |
IntegrityError 一意性制約や外部キーなど、リレーショナルの整合性を損なう状況のDatabaseErrorのサブクラス。 |
8 |
InternalError カーソルがアクティブでなくなったなど、データベースモジュール内部のエラーを参照するDatabaseErrorのサブクラス。 |
9 |
ProgrammingError DatabaseErrorのサブクラス。不正なテーブル名などのエラーや、安全に非難できるものを参照します。 |
10 |
NotSupportedError サポートされていない機能を呼び出そうとすることを指すDatabaseErrorのサブクラス。 |
Pythonスクリプトはこれらのエラーを処理する必要がありますが、上記の例外を使用する前に、MySQLdbがその例外をサポートしていることを確認してください。 DB API 2.0仕様を読むと、それらに関する詳細情報を取得できます。