Skip to content

Instantly share code, notes, and snippets.

@nakagami

nakagami/PyConJP2016.rst

Last active December 13, 2023 04:23
Show Gist options
  • Save nakagami/bfbe98d62377f3f4554121ab161ae8c9 to your computer and use it in GitHub Desktop.
Save nakagami/bfbe98d62377f3f4554121ab161ae8c9 to your computer and use it in GitHub Desktop.
Download ZIP
How to write python database driver (PyCon JP 2016 talk)
Raw
PyConJP2016.rst

How to write python database driver

Preface

This session talk about ...

  • Python database driver sepcificatin overview.
  • How to write python database driver.
  • Network protocol overview on each database.

私は、業務と関係なく趣味で python のデーターベースドライバーを書いています。

本日は、そのドライバーの紹介をしながらデーターベースドライバーの書き方や、そこでやり取りされるネットワークプロトコルの話をします。

紹介するドライバーの github リポジトリにスターを付けてくれると私が喜びます。

Profile

https://secure.gravatar.com/avatar/e69a5f2a5859ce5f897c2e9dc5158ec5?rating=PG&size=100

BeProud Inc.

http://www.beproud.jp/static/img/logo_beproud.png

https://connpass.com/static/img/common/sitelogo_295x100.png

  • Python 研修

http://www.beproud.jp/static/img/training/forte_3.png

https://pyq.jp/static/img/logo_square_small.png

  • 受託開発

PEP-249 -- Python Database API Specification v2.0

  • https://www.python.org/dev/peps/pep-0249/
  • 仕様というより規約、指針といった感じのもの。JDBC ドライバーのようにインターフェースがきっちり決まっているようなものとは異なる
  • 戻り値など規定されていないものを多い。全部実装されていなくても許される
  • 未実装なメソッドは NotImplementedError 例外を投げれば許される
  • 勝手に便利な機能（DictCursor とか）を実装しちゃってるドライバーも多い
  • 順方向の Read only カーソルだけ実装すれば良いので、他のプログラミング言語と比較すると実装はわりと簡単
  • 拡張部分は、実装してもあまり使われない https://www.python.org/dev/peps/pep-0249/#optional-db-api-extensions

Pseudo code

import foo
# make connection
conn = foo.connect(some connection parameters...)
# create database cursor from connection
cur = connect.cursor()
# execute query
cur.execute("select a, b from bar_table")
print(cur.description)      # [('a',...), ('b',...)
for r in cur.fetchall():    # fetch all rows
    print(r[0])             # value of column a
    print(r[1])             # value of column b
  • Connection object represent connection to database server.
  • Loose parameter. ex) connect() function has driver dependent parameters.
  • Loose specification. ex) execute() return value is not defined. https://www.python.org/dev/peps/pep-0249/#id15
  • Cursor object usually one direction, read only cursor.

Module Global parameters

threadsafety

threadsafety Meaning
0 Threads may not share the module.
1 Threads may share the module, but not connections.
2 Threads may share the module and connections.
3 Threads may share the module, connections and cursors.

普通に書くと threadsafety = 1 (Threads may share the module, but not connection.)になるはず。共有リソースをモジュール変数でなく、 connection のプロパティにおけば、threadsafety = 0 (Threads may not share the module.)となることを避けることはできるはず。

threadsafety = 1 が満たせれば実用上十分。

threadsafety が 2 や 3 のドライバーも存在するが(https://github.com/mfenniak/pg8000)、クリティカルな領域で、 lock/release している。必要であれば呼び出し側（ドライバーを使う側）で制御すればよい話なので、ドライバーがそこまでする必要はないと思う。

paramstyle

paramstyle Meaning
qmark SELECT * FROM foo WHERE name=?
numeric SELECT * FROM foo WHERE name=:1
named SELECT * FROM foo WHERE name=:name
format SELECT * FROM foo WHERE name=%s
pyformat SELECT * FROM foo WHERE name=%(name)s

qmark, numeric, named は、データーベースの機能としてパラメータ展開できることを、format, pyformat は、ドライバーが python コードによる文字列変換をすることを想定していると思われる。

Cursor attributes

description

execute() を実行した時のQueryの結果セットのフィールドの情報を下記のタプルのリストとしてアクセスできる

(name, type_code, display_size, internal_size, precision, scale, null_ok)

それぞれの値は、厳密に定義されていない。display_size とは文字数なのかバイト数なのか？precision, scale が意味を持つかどうかは type_code 次第だが、type_code の値はドライバー依存なので、複数のドライバーで使える値は name くらい。

rowcount

結果セットを全部配列に読み込んで長さを数えれば rowcount を計算するのは簡単だが、あえて全件をメモリにおくことをしないで -1 を返しているドライバーもある

CSV

文字列で問い合わせて、結果としてフィールド名がついた表（テーブル）形式の結果データを返すものはデーターベースドライバーが書ける

https://github.com/nakagami/csvdb

  • DBAPI like driver to access a csv file.
  • CSV データを DBAPI っぽいインターフェースで参照するパッケージ
  • ほとんど csv モジュールのラッパー
  • この発表のために DBAPI の実装の一例として書いた
  • データーベースドライバーの機能は突き詰めると、サーバーに問い合わせ文字列を渡して表形式の結果セットをもらう機能で、問い合わせ文字列は SQL 文である必要はない（通常、ドライバーは問い合わせ文字列がSQL文かどうかを気にしない）

https://github.com/nakagami/csvsqlite3

  • CSV でも SQL文が使いたい
  • q コマンド https://github.com/harelba/q から着想を得た
  • 内部で csv から sqlite3 への読み込みをして、その connection を返しているので、sqlite3 の SQL文が使える

How to read and understand network protocols

RDBMS のデーターベースサーバーと TCP/IP で接続するデータベースドライバを 書く場合、以下のような点に注意して、プロトコル仕様を読むと良い。

  • Negotiation ネゴシエーション（通信路の暗号化するか？暗号化方法、認証方法など。サーバーのバージョン、機能の表明）
  • Authentication （認証のないものもある）
  • Request （SQL文など）のデータ形式。
  • Response 検索結果のデータ形式。処理件数など。
  • Error

Request と Response, Response と Error が同じ形式になっているのか別の形式になっているのか、それらが同じ形式の envelope に包まれているか。

Response のストリームから、結果レコードの値（文字列、数値、時刻など）に変換するのは、プロトコルドキュメントがあれば頑張れる。 難しいのは Negotication と Authentication の処理を書き（理解し）接続状態を Connection オブジェクトに表現するところ。 connect() ができれば、exec(), fetchone(), fetchall() は時間をかければできるのではないかと思う。

Negotiation から Authentication までのプロトコルのドキュメントがない、もしくは不明瞭の場合はドライバーを書くのは難しそう。

仕様がきちんと書かれているような RDBMS であっても、勘違いや、自分が途中まで書いたドライバーの不具合があるので、開発の初期段階から

RDBMS サーバー <----> 中継スクリプト <----> クライアント（データーベースドライバー）

のようにサーバーとクライアントの間に入って中継しつつやりとりするデータをダンプするスクリプトを書いて、ドライバーと一緒に育てると良い

Apatch Derby

https://github.com/nakagami/pydrda

現在は、ネットワークプロトコル（やりとりされるデータのフォーマット）の調査中。

データーベースドライバーが書けるかどうかの瀬戸際で最もドキドキする（楽しい）時期。

最低限の動作をするものが完成しないとここまで費やした時間が無駄になってしまう。仕事ではできない（したくない）経験

Apache Derby は、 Java 製の RDBMS で通常は組み込み型で使用されるがネットワークサーバーとして、クライアントから接続することも可能。ネットワークプロトコルは Open Group により定義されている DRDA という仕様に従っている。

Protocol Format

protocol documents

DSS header

Length Meaning
2bytes Length of DSS data
1byte 0xD0
1byte DSS type, flags
2bytes request correlation id

DSS type, flags

|---|---------|----------|
| 0 |  flags  |  type    |
|---|---------|----------|
| 0 | 1  2  3 | 4 5 6 7  |
|---|---------|----------|
bit 0 - '0'
bit 1 - '0' - unchained, '1' - chained
bit 2 - '0' - do not continue on error, '1' - continue on error
bit 3 - '0' - next DSS has different correlator, '1' - next DSS has same correlator
type - 1 - Request DSS
     - 2 - Reply DSS
     - 3 - Object DSS
     - 4 - Communications DSS
     - 5 - Request DSS where no reply is expected

DSS body

Length Meaning
2bytes Length of DSS body
2bytes Code point
(Length of DSS body - 4) Object data 
(Length of DSS body) == (Length of DSS header) - 6

Issues

  • Big recordset (many columns, many records)
  • Transaction treatment
  • DB2 support

PyConJP 2016 Sprint days

I will join PyCon JP sprint to write pydrda.

9/23 (Fri.) 13:00-18:00, 9/24 (Sat.) 10:30-18:00

http://pyconjp.connpass.com/event/36909/

PostgreSQL

PostgreSQL のプロトコルはわりと単純なので、基本的な機能のドライバー minipg は1000行弱（実質的に1ファイル）で書けた。minipg.py だけあれば動作するので、なにか活用場所があると嬉しい（・・・が今のところ使いどころがない）。

Protocol Format

protocol document

非定形のログインパケット（C -> S)のあとは単純で定型のメッセージフォーマットのやりとりが続く

Message format (Request & Response)

Length Meaning
1byte Message ID
4bytes Length of message
(Length of message - 4) Message Data

https://www.postgresql.org/docs/current/static/protocol-message-formats.html

Server -> Client (Backend)

MessageID Meaning
R AuthenticationOk
S ParameterStatus
C CommandComplete
K BackendKeyData
Z ReadyForQuery
T RowDescription
D DataRow

Client -> Server (Front)

MessageID Meaning
Q Query
X Terminate

RowDescription

First 2 bytes are column count, and continue columns description sequence like this table.

Length Meaning
不定(0x00 が現れるまで） name
4bytes oid
2bytes position
4bytes type_code
2bytes size
4bytes modifier(precision, scale or string length)
2bytes format

This driver use only name, type_code, size, modifier as description() values.

DataRow

First 2 bytes are data count, and continue data sequence.

  • 0xFFFFFFFF → NULL
  • length (4bytes) + string value

数値データ等の文字列以外のデータも文字列で受け取るので、本来の型にする。変換する型は、RowDescription で受け取った type_code を参考にしている

MySQL

https://github.com/nakagami/CyMySQL

This is a PyMySQL fork, pure python (and cython powered) database driver.

PyMySQL のメンテナンスが停滞していた時期に fork して魔改造したが、現在は PyMySQL もメンテナンスされている。現在のような状況であれば、fork しなかったと思う。魔改造しすぎて PyMySQL と挙動が異なる部分がでてきてしまっている。 libmysql に依存せずに PyMySQL よりも速いという存在価値も多少はあると思うので引き続きメンテナンスはしていきたいと思っている。

Protocol Format

protocol documents

最初にサーバーからクライアントへパケットを送信するところからネゴシエーションが始まるところが、他の RDBMS と比較して変わっている。

MySQL packet

Length Meaning
3bytes Length (little endian)
1bytes Sequence number
(Length) Payload

Command(Request)

Client → Server の MySQL packet の Payload の 1byte 目がコマンド、2byte 目以降は、そのコマンドに付随するデータ

COM_QUERY(0x03) + b'SQL Command'
COM_QUIT(0x01)

https://github.com/nakagami/CyMySQL/blob/master/cymysql/constants/COMMAND.py

  • 基本的には COM_QUERY (0x03), COM_QUIT(0x01) だけを使っている
  • CyMySQL(PyMySQL) には COM_PROCESS_KILL, COM_PING を使っているメソッドもある
  • Prepared Statement や Stored Procedure の機能はあるが CyMySQL では使っていない。

binary encoded binary

Server → Client の MySQL packet の Payload は「長さ + 値」の連続データが入っており「長さ」の部分のデータ長は、最初の１バイト目で変わる

VLI + value

VLI format

MysqlPacket::read_length_coded_binary() https://github.com/nakagami/CyMySQL/blob/master/cymysql/packet.pyx#L122

Binary Length
0x00 - 0xFA (0 - 250) as is
0xFB NULL
0xFC XX XX unsigned 2 bytes int
0xFD XX XX XX unsigned 3 bytes int
0xFE XX XX XX XX XX XX XX XX unsigned 8 bytes int

Command Response

レスポンスの MysqlPacket の最初の1byte は、binary encoded binary の長さ以外に以下のような意味を持つ

Result Sets

結果セットが得られる場合、 binary encoded binary 形式の連続で

  • description
  • row data

の順番でサーバーから返される。description や row data の種別を表すタグは持ってない

Cythonize

データーベースドライバーの書き方とは関係ないが CyMySQL のチューニングで感じた Cython を使った高速化のコツについて

python のコードを cython で C のコードにするだけでは 20% 程度しか速くならないので、何もしないで速くなることを期待するなら、SQL文を工夫したほうが良い。

データーベースドライバーは、 Network バウンドで、最終的に Python のオブジェクトに変換しないといけないので（=CPUバウンドな計算が少ないので）Cython を使って高速化する効果も中程度。

SQLServer

https://github.com/nakagami/minitds

SQLServer は Sybase から派生したので、Sybase のネットワークプロトコル TDS(Tabular Data Stream) Protocol をベースにした MS-TDS を使っている。

  • プロトコルバージョン毎に Payload 内のバイナリフォーマットが少しづつ違う
  • SQLServer 2012以降は TDS74 というバージョンで minitds は TDS74 にのみ対応している
  • Azure SQL にも接続できると思うので誰か試してみてほしい
  • NVarchar や内部的な国際化文字列が UTF16LE でやり取りされているあたりが Windows っぽい

Protocol Format

protocol document

ドキュメントは、一番しっかり書かれていて最近リリースされた SQLServer 2016 の情報に従ってアップデートされている

Packet Header and Data

Length Meaning
1bytes type of TDS packet
1byte Status https://msdn.microsoft.com/en-us/library/dd358342.aspx
2byte Length
2bytes spid (ProcessID)
1byte PacketID (sequence number)
1byte Window (0)
(Length-8) Payload

TDS type

https://msdn.microsoft.com/en-us/library/dd304214.aspx

Value Name
1 TDS_SQL_BATCH
3 TDS_RPC
4 TDS_TABULAR_RESULT
6 TDS_ATTENTION_SIGNAL
7 TDS_BULK_LOAD_DATA
14 TDS_TRANSACTION_MANAGER_REQUEST
16 TDS_LOGIN
18 TDS_PRELOGIN

Firebird

https://github.com/nakagami/pyfirebirdsql

Firebird http://firebirdsql.org/ は Interbase 6.0 が OSS ライセンスで公開されたときに fork されて開発が始まり、現在は、南米、EU、ロシアでよく使われている。開発の中心はロシアの模様。

Firebird のドライバーを書くことは（難しい割には報われないので）オススメしない。

Protocol Format

protocol documents

ドキュメントはなく、ネット上の断片情報や Interbase6.0 の頃の（古い）資料や Firebird のソースコードや、 Firebird の開発メーリングリストやらを参考にし、試行錯誤で実装した。Firebird のバージョンが上がるにつれ、 wire protocol も機能追加されていって、ドライバーの機能は、その都度追随していった。 何もないところから、もう一度実装しろと言われても無理。

Protocol Version Number History

Number Firebird Version Feature
10 Firebird 1.0 Interbase 6.0 compatible
11 Firebird 2.1 Lazy Response
12 Firebird 2.5 utf-8 filename
13 Firebird 3.0 Plugin Authentication, WireEncryption, Null Bitmap, WireCompression

Other programming language bindings for Firebird

Firebird の python ドライバーを書いたおかげで、他のプログラミング言語用のドライバーをスクラッチから書いたり、既存のプロジェクトに Contribute したりできるようになった。プロトタイプとして、また参照実装として python のドライバーを最初に書いたのが良かった

Why python driver at first

データーベースドライバーを最初に書くプログラミング言語として Python が良いと思う理由

Why am I writing database drivers

It is difficult task for me, but not impossible.

De-facto standard drivers

MySQL

PostgreSQL

SQLServer

Firebird

Oracle

Which is the best network protocol ?

Everyone's different, all are wonderful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment