python操作sqlite 3のいくつかの問題の分析

データベースによってはそれぞれの特徴があり、以前はmysqlに詳しいと思っていたが、すべてsqlite 3に移してはいけない.筆者は最近sqlite 3を使用する際に出会った問題について総括分析を行い、相応の解決方法を提供し、参考に供した.
 
1、どのようにしてレコードをinsertしますか?
例は、sql文の%d、'%s'がプレースホルダである場合、文字列プレースホルダには引用符が必要であり、実行時に%(...)のmid、mnameなどの変数に置き換えられることに注意してください.

import sqlite3
conn = sqlite3.connect("movie.db")
msql = "insert into movieinfo(mid,mname, myear, mgenre, mruntime, rank, mrating, link) values(%d, '%s', %d, '%s','%s', '%s', %f, '%s')"%(mid, mname, myear, mgenre, mruntime, rank,mrating, link)
conn.execute(msql)

2、どのようにデータテーブルを空にしますか?
例は以下のとおりです.sql標準にはTRUNCATE TABLE文があり、テーブルのすべての内容をクリアします.ほとんどのDBMSではDELETEよりTRUNCATEの方が速いが、sqlite 3ではこの文はサポートされていない.sqlite 3では「DELETE FROM Table Name」をそのまま使えばいいし、DELETEを最適化し、通常のDELETEよりもずっと速くなります.

dsql = "delete from movieinfo"
conn.execute(dsql)

 
3、insertする変数に単一引用符がある場合?
挿入する文字列変数mname値がmovie's nameのような変数に単一引用符がある場合、変数の単一引用符はプレースホルダ'%s'以外の引用符と閉じられ、挿入エラーが発生します.また、このような単一引用符がハッカーによって利用されると、プログラムがsqlに注入されるセキュリティの問題を引き起こす可能性がある.
最良の解決策は、%dや'%s'などのプレースホルダを使わずに、公式の提案に従うことです.統一的に使用しますか?プレースホルダとして、sqlite 3は状況に応じて文字列中の各種特殊文字を自動的に処理する.挿入エラーを回避できるだけでなく、プログラムの侵入を効果的に防止できます.例は次のとおりです.%を使用し、置換することに注意してください.

msql = "insert into movieinfo(mid,mname, myear, mgenre, mruntime, rank, mrating, link) values(?, ?, ?, ?, ?, ?, ?,?)", (mid, mname, myear, mgenre, mruntime, rank, mrating, link)
conn.execute(msql)

4、insertの変数にUnicode文字がある場合?
変数にunicode文字がある場合、例えば挿入する文字列変数mname値がu'u 4 e 2 du 6587'である.このとき,問題3のsql文を採用しても,うまく挿入できないという問題が依然として発生する.この場合、パラメータ付きsql文処理を使用できます.例は次のとおりです.

msql = "insert into movieinfo(mid,mname, myear, mgenre, mruntime, rank, mrating, link) values(?, ?, ?, ?, ?, ?, ?,?)"
parameter = [mid, mname, myear, mgenre,mruntime, rank, mrating, link]
conn.execute(msql, parameter)
 

5、insertする変数にプライマリ・キーの重複問題がある場合?
元のデータにプライマリ・キー重複(idなど)のレコードがある場合、insert or replace文を使用してすべてのレコードをデータベースに挿入します.このように、idが繰り返すレコードは、最後の項目(すなわち、先に挿入されたレコードが後に挿入されたレコードに置き換えられる)のみを保持し、例は以下の通りである.

msql = "insert or replace intomovieinfo(mid, mname, myear, mgenre, mruntime, rank, mrating, link) values(?, ?,?, ?, ?, ?, ?, ?)"
parameter = [mid, mname, myear, mgenre,mruntime, rank, mrating, link]
conn.execute(msql, parameter)

6、一度に複数のレコードをinsertしたいなら?
予め用意された複数のsql文を使用して一括insert操作を行うことはできませんが、sqlite 3は依然として複数のレコードを同時に挿入するツールexecutemany()を提供しています.例は、execute()文がexecutemany()に変更されていることに注意してください.

msql = "insert or replace intomovieinfo(mid, mname, myear, mgenre, mruntime, rank, mrating, link) values(?, ?,?, ?, ?, ?, ?, ?)"
parameters = [(mid1, mname1, myear1, mgenre1,mruntime1, rank1, mrating1, link1), (mid2, mname2, myear2, mgenre2, mruntime2,rank2, mrating2, link2)]
conn.executemany(msql, parameters)

 
7、「database islocked」の問題が発生した場合?
多くの人がOperationalError:database is lockedの問題に遭遇すると信じています.ネット上で最も一般的な解釈はsqlite 3がマルチスレッド操作をサポートしていないことです.複数のプロセスが同じデータベースを同時に操作すると、データベースがロックされます.この場合、問題が繰り返される場合は、データベースの交換を検討する必要がある場合があります.
しかし、別の可能性は、いくつかの操作が終了していない場合に、次の操作が行われていることです(ファイルの下に*.db-journalファイルがあるかどうかを確認することで、通常はこのファイルがないことを確認できます).たとえばconn.execute(「delete from movieinfo」)を実行すると、フォルダの下にmovieinfoが生成されます.db-journalファイルは、セッションが処理されていないことを示します.このとき、データベースに対して他の操作を行うと、「OperationalError:database is locked」例外が放出されます.
データテーブルのクリア操作が完了すると、conn.commit()が直ちにコミットされる.db-journalファイルが消え、すべてのセッションが完了したことを示します.この時点で他の操作を行えば、問題はありません.