Pythonデータの永続性-Sqlite3モジュール

CSV、JSON、XMLなどのファイルの主な欠点の1つは、ファイルが本質的に構造化されていないため、ランダムアクセスやトランザクション処理にはあまり役に立たないことです。そのため、内容の変更が非常に難しくなります。

これらのフラットファイルは非同期処理機能がないため、クライアント/サーバー環境には適していません。非構造化データファイルを使用すると、データの冗長性と不整合が生じます。

これらの問題は、リレーショナルデータベースを使用することで解決できます。データベースは、冗長性と不整合をなくし、データの整合性を維持するために編成されたデータのコレクションです。リレーショナルデータベースモデルは非常に人気があります。

その基本的な概念は、エンティティテーブル（リレーションと呼ばれる）にデータを配置することです。エンティティテーブル構造は、値が各行で一意である1つの属性を提供します。このような属性は*「主キー」*と呼ばれます。

1つのテーブルの主キーが他のテーブルの構造に現れる場合、それは*「外部キー」*と呼ばれ、これが2つのテーブル間の関係の基礎を形成します。このモデルに基づいて、現在利用可能な多くの人気のあるRDBMS製品があります-

ガドフライ
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
オラクル
Sybase
SQLite

SQLiteは、さまざまなアプリケーションで使用される軽量のリレーショナルデータベースです。自己完結型、サーバーレス、構成不要のトランザクションSQLデータベースエンジンです。データベース全体は単一のファイルであり、ファイルシステムの任意の場所に配置できます。これは、フットプリントが非常に小さく、構成がゼロのオープンソースソフトウェアです。組み込みデバイス、IOT、モバイルアプリで広く使用されています。

すべてのリレーショナルデータベースはSQLを使用してテーブル内のデータを処理します。ただし、以前は、これらの各データベースは、データベースのタイプに固有のPythonモジュールを使用してPythonアプリケーションに接続されていました。

したがって、それらの間の互換性の欠如がありました。ユーザーが別のデータベース製品に変更したい場合、それは難しいことが判明します。この非互換性の問題は、 'Python Enhancement Proposal（PEP 248）'を引き上げ、DB-APIと呼ばれるリレーショナルデータベースへの一貫したインターフェイスを推奨することで解決されました。最新の推奨事項は DB-API バージョン2.0と呼ばれます。（PEP 249）

Pythonの標準ライブラリは、Pythonプログラムを通じてSQLiteデータベースを処理するためのDB-API準拠モジュールであるsqlite3モジュールで構成されています。この章では、PythonとSQLiteデータベースの接続について説明します。

前述したように、Pythonにはsqlite3モジュールの形式でSQLiteデータベースのサポートが組み込まれています。その他のデータベースの場合、pipユーティリティを使用して、それぞれのDB-API準拠のPythonモジュールをインストールする必要があります。たとえば、MySQLデータベースを使用するには、PyMySQLモジュールをインストールする必要があります。

pip install pymysql

以下の手順は、DB-APIで推奨されています-

* connect（）*関数を使用してデータベースとの接続を確立し、接続オブジェクトを取得します。
接続オブジェクトの* cursor（）*メソッドを呼び出して、カーソルオブジェクトを取得します。
実行するSQLステートメントで構成されるクエリ文字列を作成します。
* execute（）*メソッドを呼び出して、目的のクエリを実行します。 *接続を閉じます。

import sqlite3
db=sqlite3.connect('test.db')

ここで、dbはtest.dbを表す接続オブジェクトです。データベースがまだ存在しない場合は、そのデータベースが作成されることに注意してください。接続オブジェクトdbには以下のメソッドがあります-

Sr.No.	Methods & Description
1	cursor():* このConnectionを使用するCursorオブジェクトを返します。
2	commit(): 保留中のトランザクションをデータベースに明示的にコミットします。
3	rollback(): このオプションのメソッドにより、トランザクションは開始点にロールバックされます。
4	close(): データベースへの接続を完全に閉じます。

カーソルは特定のSQLクエリのハンドルとして機能し、結果の1つ以上の行を取得できます。カーソルオブジェクトは、次のステートメントを使用してSQLクエリを実行するために接続から取得されます-

cur=db.cursor()

カーソルオブジェクトには、次のメソッドが定義されています-

Sr.No	Methods & Description
1	execute() 文字列パラメータでSQLクエリを実行します。
2	executemany() タプルのリスト内のパラメーターのセットを使用してSQLクエリを実行します。
3	fetchone() クエリ結果セットから次の行をフェッチします。
4	fetchall() クエリ結果セットから残りのすべての行をフェッチします。
5	callproc() ストアドプロシージャを呼び出します。
6	close() カーソルオブジェクトを閉じます。

次のコードは、test.dbにテーブルを作成します。

import sqlite3
db=sqlite3.connect('test.db')
cur =db.cursor()
cur.execute('''CREATE TABLE student (
StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT (20) NOT NULL,
age INTEGER,
marks REAL);''')
print ('table created successfully')
db.close()

データベースで必要なデータの整合性は、接続オブジェクトの* commit（）および rollback（）*メソッドによって実現されます。 SQLクエリ文字列に、例外を発生させる可能性のある不正なSQLクエリが含まれている可能性があります。これは適切に処理する必要があります。そのため、execute（）ステートメントはtryブロック内に配置されます。成功した場合、結果はcommit（）メソッドを使用して永続的に保存されます。クエリが失敗すると、rollback（）メソッドを使用してトランザクションが取り消されます。

次のコードは、test.dbのStudentテーブルに対してINSERTクエリを実行します。

import sqlite3
db=sqlite3.connect('test.db')
qry="insert into student (name, age, marks) values('Abbas', 20, 80);"
try:
   cur=db.cursor()
   cur.execute(qry)
   db.commit()
print ("record added successfully")
except:
   print ("error in query")
   db.rollback()
db.close()

ユーザー入力によって動的に提供されるINSERTクエリの値句のデータが必要な場合は、Python DB-APIで推奨されているパラメーター置換を使用します。は？文字はクエリ文字列のプレースホルダーとして使用され、execute（）メソッドでタプルの形式で値を提供します。次の例では、パラメーター置換メソッドを使用してレコードを挿入します。名前、年齢、マークは入力として使用されます。

import sqlite3
db=sqlite3.connect('test.db')
nm=input('enter name')
a=int(input('enter age'))
m=int(input('enter marks'))
qry="insert into student (name, age, marks) values(?,?,?);"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,a,m))
   db.commit()
   print ("one record added successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

sqlite3モジュールは、複数のレコードを一度に追加できる* executemany（）*メソッドを定義しています。追加するデータは、各タプルに1つのレコードが含まれるタプルのリストで指定する必要があります。リストオブジェクトは、クエリ文字列とともに、executemany（）メソッドのパラメーターです。ただし、executemany（）メソッドは他の一部のモジュールではサポートされていません。

*UPDATE* クエリには通常、WHERE句で指定された論理式が含まれます。execute（）メソッドのクエリ文字列には、UPDATEクエリ構文を含める必要があります。 name = 'Anil'の 'age'の値を23に更新するには、次のように文字列を定義します。

qry="update student set age=23 where name='Anil';"

更新プロセスをより動的にするために、上記のようにパラメーター置換メソッドを使用します。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
a=int(input(‘enter age’))
qry="update student set age=? where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (a, nm))
   db.commit()
   print("record updated successfully")
except:
   print("error in query")
   db.rollback()
db.close()

同様に、DELETE操作は、SQLのDELETEクエリ構文を持つ文字列でexecute（）メソッドを呼び出すことによって実行されます。ちなみに、 DELETE クエリには通常、 WHERE 句も含まれます。

import sqlite3
db=sqlite3.connect('test.db')
nm=input(‘enter name’)
qry="DELETE from student where name=?;"
try:
   cur=db.cursor()
   cur.execute(qry, (nm,))
   db.commit()
   print("record deleted successfully")
except:
   print("error in operation")
   db.rollback()
db.close()

データベーステーブルの重要な操作の1つは、テーブルからのレコードの取得です。 SQLはこの目的のために SELECT クエリを提供します。 SELECTクエリ構文を含む文字列がexecute（）メソッドに渡されると、結果セットオブジェクトが返されます。カーソルオブジェクトには、結果セットから1つまたは複数のレコードを取得できる重要なメソッドが2つあります。

fetchone（）

結果セットから次に使用可能なレコードをフェッチします。取得したレコードの各列の値からなるタプルです。

fetchall（）

残りのすべてのレコードをタプルのリストの形式でフェッチします。各タプルは1つのレコードに対応し、テーブルの各列の値を含みます。

次の例は、studentテーブルのすべてのレコードをリストします。

import sqlite3
db=sqlite3.connect('test.db')
37
sql="SELECT * from student;"
cur=db.cursor()
cur.execute(sql)
while True:
   record=cur.fetchone()
   if record==None:
      break
   print (record)
db.close()

SQLiteデータベースの代わりにMySQLデータベースを使用する場合は、上記のように PyMySQL モジュールをインストールする必要があります。データベース接続プロセスのすべてのステップは同じです。MySQLデータベースがサーバーにインストールされているため、connect（）関数にはURLとログイン認証情報が必要です。

import pymysql
con=pymysql.connect('localhost', 'root', '***')

SQLiteと異なる可能性があるのは、MySQL固有のデータ型のみです。同様に、pyodbcモジュールをインストールすることにより、任意のODBC互換データベースをPythonで使用できます。

Python-data-persistence-sqlite3-module

Pythonデータの永続性-Sqlite3モジュール

fetchone（）

fetchall（）