Python Language
データベースアクセス
サーチ…
備考
Pythonは多くの異なるタイプのデータベースを扱うことができます。これらのタイプごとに異なるAPIが存在します。これらの異なるAPI間の類似性を奨励するため、PEP 249が導入されました。
このAPIは、データベースへのアクセスに使用されるPythonモジュール間の類似性を促進するために定義されています。これを行うことで、モジュール間の一貫性を確保し、一般的にデータベース間での移植性が向上し、Pythonからのデータベース接続の幅が広がります。 PEP-249
MySQLdbを使用したMySQLデータベースへのアクセス
最初に行う必要があるのは、connectメソッドを使用してデータベースへの接続を作成することです。その後、その接続で動作するカーソルが必要になります。
カーソルのexecuteメソッドを使用してデータベースと対話し、接続オブジェクトのcommitメソッドを使用して変更をコミットします。
すべてが完了したら、カーソルと接続を閉じることを忘れないでください。
Dbconnectクラスには、必要なものがすべて含まれています。
import MySQLdb
class Dbconnect(object):
def __init__(self):
self.dbconection = MySQLdb.connect(host='host_example',
port=int('port_example'),
user='user_example',
passwd='pass_example',
db='schema_example')
self.dbcursor = self.dbconection.cursor()
def commit_db(self):
self.dbconection.commit()
def close_db(self):
self.dbcursor.close()
self.dbconection.close()
データベースと対話するのは簡単です。オブジェクトを作成したら、executeメソッドを使用します。
db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')
ストアドプロシージャを呼び出す場合は、次の構文を使用します。パラメータリストはオプションであることに注意してください。
db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )
クエリが完了したら、結果に複数の方法でアクセスできます。カーソルオブジェクトは、すべての結果をフェッチするか、ループするジェネレータです。
results = db.dbcursor.fetchall()
for individual_row in results:
first_field = individual_row[0]
ジェネレータを直接使用するループが必要な場合：
for individual_row in db.dbcursor:
first_field = individual_row[0]
データベースに変更をコミットする場合は、次のようにします。
db.commit_db()
カーソルと接続を閉じるには：
db.close_db()
SQLite
SQLiteは、軽量のディスクベースのデータベースです。別のデータベースサーバーを必要としないので、しばしばプロトタイピングや、特定の時間に1人のユーザーまたは1人のユーザーが頻繁に使用する小さなアプリケーションに使用されます。
import sqlite3
conn = sqlite3.connect("users.db")
c = conn.cursor()
c.execute("CREATE TABLE user (name text, age integer)")
c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")
conn.commit()
c.execute("SELECT * FROM user")
print(c.fetchall())
conn.close()
上記のコードは、 users.dbという名前のファイルに格納されているデータベースに接続し、ファイルが存在しない場合は最初に作成します。 SQLステートメントを使用してデータベースと対話することができます。
この例の結果は次のようになります。
[(u'User A', 42), (u'User B', 43)]
SQLite構文：詳細な分析
入門
を使用してsqliteモジュールをインポートする
>>> import sqlite3モジュールを使用するには、最初にデータベースを表すConnectionオブジェクトを作成する必要があります。ここでデータはexample.dbファイルに格納されます：
>>> conn = sqlite3.connect('users.db')また、あなたはまた、特別な名前を供給することができます
:memory:以下のように、RAMに一時データベースを作成するために：>>> conn = sqlite3.connect(':memory:')ConnectionCursorたら、Cursorオブジェクトを作成し、そのexecute()メソッドを呼び出してSQLコマンドを実行できます。c = conn.cursor() # Create table c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # Insert a row of data c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)") # Save (commit) the changes conn.commit() # We can also close the connection if we are done with it. # Just be sure any changes have been committed or they will be lost. conn.close()
Connection重要な属性と機能
isolation_level現在の分離レベルを取得または設定するために使用される属性です。自動コミット・モードまたは
DEFERRED、IMMEDIATEまたはEXCLUSIVEいずれかの場合はなし。
cursorカーソルオブジェクトは、SQLコマンドとクエリを実行するために使用されます。
commit()現在のトランザクションをコミットします。
rollback()以前の
commit()呼び出し以降に加えられた変更をロールバックします。
close()データベース接続を閉じます。自動的に
commit()は呼び出されません。最初にcommit()呼び出さずにclose()呼び出すとcommit()自動コミットモードでないと仮定した場合）、すべての変更が失われます。
total_changesデータベースが開かれてから変更、削除、または挿入された行の総数を記録する属性。
execute、executemany、およびexecutescriptこれらの関数は、カーソルオブジェクトの場合と同じ方法で実行されます。これはショートカットです。接続オブジェクトを介してこれらの関数を呼び出すと、中間のカーソルオブジェクトが作成され、対応するカーソルオブジェクトのメソッドが呼び出されるためです
row_factoryこの属性は、カーソルと元の行をタプルとして受け入れ、実際の結果行を返す呼び出し可能なものに変更できます。
def dict_factory(cursor, row): d = {} for i, col in enumerate(cursor.description): d[col[0]] = row[i] return d conn = sqlite3.connect(":memory:") conn.row_factory = dict_factory
Cursor重要な機能
execute(sql[, parameters])単一の SQL文を実行します。 SQL文をパラメータ化することができます（SQLリテラルの代わりにプレースホルダ）。 sqlite3モジュールは2種類のプレースホルダをサポートしています：疑問符
?（ "qmark style"）と名前付きプレースホルダ:name（ "named style"）。import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("create table people (name, age)") who = "Sophia" age = 37 # This is the qmark style: cur.execute("insert into people values (?, ?)", (who, age)) # And this is the named style: cur.execute("select * from people where name=:who and age=:age", {"who": who, "age": age}) # the keys correspond to the placeholders in SQL print(cur.fetchone())
注意：プログラムをSQLインジェクション攻撃（ SQLインジェクション参照）に対して脆弱にする可能性があるため、SQLコマンドに文字列を挿入するために
%sを使用しないでください。
executemany(sql, seq_of_parameters)シーケンスsqlにあるすべてのパラメータシーケンスまたはマッピングに対してSQLコマンドを実行します。 sqlite3モジュールでは、シーケンスの代わりにパラメータを生成するイテレータを使用することもできます。
L = [(1, 'abcd', 'dfj', 300), # A list of tuples to be inserted into the database (2, 'cfgd', 'dyfj', 400), (3, 'sdd', 'dfjh', 300.50)] conn = sqlite3.connect("test1.db") conn.execute("create table if not exists book (id int, name text, author text, price real)") conn.executemany("insert into book values (?, ?, ?, ?)", L) for row in conn.execute("select * from book"): print(row)また、イテレータオブジェクトをパラメータとしてexecutemanyに渡すこともできます。この関数は、イテレータが返す値の各タプルを繰り返し処理します。イテレータは値のタプルを返さなければなりません。
import sqlite3 class IterChars: def __init__(self): self.count = ord('a') def __iter__(self): return self def __next__(self): # (use next(self) for Python 2) if self.count > ord('z'): raise StopIteration self.count += 1 return (chr(self.count - 1),) conn = sqlite3.connect("abc.db") cur = conn.cursor() cur.execute("create table characters(c)") theIter = IterChars() cur.executemany("insert into characters(c) values (?)", theIter) rows = cur.execute("select c from characters") for row in rows: print(row[0]),executescript(sql_script)これは、一度に複数のSQL文を実行するための非標準の便利なメソッドです。最初に
COMMIT文を発行し、パラメータとして取得したSQLスクリプトを実行します。sql_scriptは、strまたはbytesインスタンスにすることができbytes。import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.executescript(""" create table person( firstname, lastname, age ); create table book( title, author, published ); insert into book(title, author, published) values ( 'Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', 1987 ); """)次の関数セットは、SQLの
SELECTステートメントと組み合わせて使用されます。SELECTステートメントの実行後にデータを取得するには、カーソルを反復子として扱い、カーソルのfetchone()メソッドを呼び出して一致する行を1つ検索するか、fetchall()を呼び出して一致する行のリストを取得します。イテレータ・フォームの例：
import sqlite3 stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)] conn = sqlite3.connect(":memory:") conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)") conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks) cur = conn.cursor() for row in cur.execute('SELECT * FROM stocks ORDER BY price'): print(row) # Output: # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14) # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0) # ('2006-04-06', 'SELL', 'IBM', 500, 53.0) # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)fetchone()単一のシーケンスを返すクエリ結果セットの次の行をフェッチします。データがそれ以上利用できない場合は、Noneを返します。
cur.execute('SELECT * FROM stocks ORDER BY price') i = cur.fetchone() while(i): print(i) i = cur.fetchone() # Output: # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14) # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0) # ('2006-04-06', 'SELL', 'IBM', 500, 53.0) # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)fetchmany(size=cursor.arraysize)問合せ結果の次の行セットをフェッチし（サイズで指定）、リストを戻します。 sizeが省略された場合、fetchmanyは単一の行を返します。利用可能な行がなくなったときに空のリストが返されます。
cur.execute('SELECT * FROM stocks ORDER BY price') print(cur.fetchmany(2)) # Output: # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]fetchall()問合せ結果のすべての（残りの）行をフェッチし、リストを戻します。
cur.execute('SELECT * FROM stocks ORDER BY price') print(cur.fetchall()) # Output: # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
SQLiteとPythonのデータ型
SQLiteはネイティブに、NULL、INTEGER、REAL、TEXT、BLOBの各タイプをサポートしています。
これは、SQLからPythonに移動するとき、またはその逆のときにデータ型が変換される方法です。
None <-> NULL
int <-> INTEGER/INT
float <-> REAL/FLOAT
str <-> TEXT/VARCHAR(n)
bytes <-> BLOB
psycopg2を使用したPostgreSQLデータベースアクセス
psycopg2は、軽量で効率的なPostgreSQLデータベースアダプタとして最も普及しています。現在のPostgreSQLアダプタの実装です。
主な機能は、Python DB API 2.0仕様の完全な実装とスレッドの安全性です（いくつかのスレッドは同じ接続を共有できます）
データベースへの接続の確立とテーブルの作成
import psycopg2
# Establish a connection to the database.
# Replace parameter values with database credentials.
conn = psycopg2.connect(database="testpython",
user="postgres",
host="localhost",
password="abc123",
port="5432")
# Create a cursor. The cursor allows you to execute database queries.
cur = conn.cursor()
# Create a table. Initialise the table name, the column names and data type.
cur.execute("""CREATE TABLE FRUITS (
id INT ,
fruit_name TEXT,
color TEXT,
price REAL
)""")
conn.commit()
conn.close()
テーブルにデータを挿入する：
# After creating the table as shown above, insert values into it.
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
VALUES (1, 'Apples', 'green', 1.00)""")
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
VALUES (1, 'Bananas', 'yellow', 0.80)""")
表データの取得：
# Set up a query and execute it
cur.execute("""SELECT id, fruit_name, color, price
FROM fruits""")
# Fetch the data
rows = cur.fetchall()
# Do stuff with the data
for row in rows:
print "ID = {} ".format(row[0])
print "FRUIT NAME = {}".format(row[1])
print("COLOR = {}".format(row[2]))
print("PRICE = {}".format(row[3]))
上記の出力は次のようになります。
ID = 1
NAME = Apples
COLOR = green
PRICE = 1.0
ID = 2
NAME = Bananas
COLOR = yellow
PRICE = 0.8
そして、そこに、あなたは今psycopg2について知る必要があることの半分を知っています ！ :)
Oracleデータベース
前提条件：
- cx_Oracleパッケージ - すべてのバージョンはこちら
- Oracleインスタント・クライアント - Windows x64 、 Linux x64
セットアップ：
cx_Oracleパッケージを次のようにインストールします。
sudo rpm -i <YOUR_PACKAGE_FILENAME>Oracleインスタント・クライアントを抽出し、環境変数を次のように設定します。
ORACLE_HOME=<PATH_TO_INSTANTCLIENT>
PATH=$ORACLE_HOME:$PATH
LD_LIBRARY_PATH=<PATH_TO_INSTANTCLIENT>:$LD_LIBRARY_PATH
接続の作成：
import cx_Oracle
class OraExec(object):
_db_connection = None
_db_cur = None
def __init__(self):
self._db_connection =
cx_Oracle.connect('<USERNAME>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>')
self._db_cur = self._db_connection.cursor()
データベースのバージョンを取得する：
ver = con.version.split(".")
print ver
サンプル出力：['12 '、' 1 '、' 0 '、' 2 '、' 0 ']
クエリを実行：SELECT
_db_cur.execute("select * from employees order by emp_id")
for result in _db_cur:
print result
出力はPythonタプルで出力されます：
（10、 'SYSADMIN'、 'IT-INFRA'、7）
（23、「人事異動」、「人的資源」、6）
クエリを実行：INSERT
_db_cur.execute("insert into employees(emp_id, title, dept, grade)
values (31, 'MTS', 'ENGINEERING', 7)
_db_connection.commit()
Oracle Databaseで挿入/更新/削除操作を実行すると、変更はcommitが発行されるまでセッション内でのみ使用できます。更新されたデータがデータベースにコミットされると、そのデータは他のユーザーやセッションで使用できます。
クエリを実行：バインド変数を使用したINSERT
バインド変数を使用すると、文を再解析するオーバーヘッドなしに、新しい値で文を再実行できます。バインド変数はコードの再利用性を向上させ、SQLインジェクション攻撃のリスクを低減できます。
rows = [ (1, "First" ),
(2, "Second" ),
(3, "Third" ) ]
_db_cur.bindarraysize = 3
_db_cur.setinputsizes(int, 10)
_db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
_db_connection.commit()
接続を閉じる：
_db_connection.close()
close（）メソッドは接続を閉じます。明示的にクローズされていない接続は、スクリプトの終了時に自動的に解放されます。
接続
接続の作成
PEP 249によると、データベースへの接続は、 Connectionオブジェクトを返すconnect()コンストラクタを使用して確立する必要があります。このコンストラクタの引数は、データベースに依存します。関連する引数については、データベース固有のトピックを参照してください。
import MyDBAPI
con = MyDBAPI.connect(*database_dependent_args)
この接続オブジェクトには4つのメソッドがあります。
1：閉じる
con.close()
すぐに接続を閉じます。 Connection.__del___メソッドが呼び出されると、接続は自動的に閉じられることに注意してください。保留中のトランザクションは暗黙的にロールバックされます。
2：コミット
con.commit()
保留中のトランザクションをデータベースにコミットします。
3：ロールバック
con.rollback()
保留中のトランザクションの開始にロールバックします。言い換えれば、データベースに対するコミットされていないトランザクションはすべて取り消されます。
4：カーソル
cur = con.cursor()
Cursorオブジェクトを返します。これは、データベース上でトランザクションを実行するために使用されます。
sqlalchemyの使用
データベースにsqlalchemyを使用するには：
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL
url = URL(drivername='mysql',
username='user',
password='passwd',
host='host',
database='db')
engine = create_engine(url) # sqlalchemy engine
今このエンジンを使うことができます：例えばpandasを使ってmysqlから直接データフレームを取得する
import pandas as pd
con = engine.connect()
dataframe = pd.read_sql(sql=query, con=con)