SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Mac の SQL Server on Linux を Python の pymssql で操作してごにょごにょしたけどよくわからなかったの巻

leave a comment

SQL Server on Linux の勉強がてら、Mac の Python で pymssql をインストールして、ごにょごにょしていたのですが、varchar のような非 Unicode 文字列の操作がよくわからんかったの巻きです。

Pythonで色々なデータベースを操作する を参考にさせていただいています。

環境としては、デフォルトでインストールされていた、Python 2.7.10 を使用しています。

SQL Server + Python ? What’s new
Build an app using SQL Server
も見ておくとよさそうですね。

最近は、MSDN でも Python についての情報が公開されていて便利な時代ですね。
Python SQL DriverSQL

SQL Server に関しては、Mac 上にインストールした SQL Server on Linux のデフォルトの状態で使用しているため、照合順序は「SQL_Latin1_General_CP1_CI_AS」で動作させています。

コードに関しては、サンプル を参考に以下のようなものを作成しました。

#coding:UTF-8
# easy_install pipeasy_install pip
# easy_install pylint or pip install pylint
# easy_install pymssql or pip install pymssql
# http://stackoverflow.com/questions/37771434/mac-pip-install-pymssql-error
# http://pymssql.org/en/stable/pymssql_examples.html
import pymssql
conn = pymssql.connect(server='127.0.0.1',user='sa',password='M@sterEr0s')
cursor = conn.cursor()
cursor.execute("""
DROP TABLE IF EXISTS T1
CREATE TABLE T1(C1 varchar(100) COLLATE Japanese_XJIS_100_CI_AS, C2 varchar(100), C3 nvarchar(100))
""")
conn.commit()
cursor.executemany("INSERT INTO T1 VALUES(%s, %s, %s)",
[
    ("かずあきさん", "zaibatsu", u"えろす師匠"), # この方法だと varchar に想定している文字列が格納されない
    (u"かずあきさん", "zaibatsu", u"えろす師匠"),
    (u"かずあきさん".encode("cp932"), "zaibatsu", u"えろす師匠")
])
conn.commit()
cursor.execute("SELECT * FROM T1 WHERE C1 = %s", "かずあきさん")
row = cursor.fetchone()
while row:
    print("%s  %s %s" % (row[0], row[1], row[2]))
    row = cursor.fetchone()
print "----"
cursor.execute("SELECT * FROM T1 WHERE C1 = %s", u"かずあきさん".encode("cp932"))
row = cursor.fetchone()
while row:
    print("%s  %s %s" % (row[0], row[1], row[2]))
    row = cursor.fetchone()

ブランクの DB を作るのが面倒だったので「SQL_Latin1_General_CP1_CI_AS」の master データベースでごにょごにょしています。

SSMS で取得した情報がこちら。

image

 

("かずあきさん", "zaibatsu", u"えろす師匠")

で INSERT した「かずあきさん」の文字が特定行のみ文字化けしていますね。

(u"かずあきさん", "zaibatsu", u"えろす師匠"),

(u"かずあきさん".encode("cp932"), "zaibatsu", u"えろす師匠")


で INSERT したものは正常に格納されています。

財閥とえろす師匠は 1 バイト文字だったり、nvarchar を使用しているので、こちらは想定通り情報が格納されています。

python の SELECT 文の部分で取得した情報がこちら。

image

こちらも格納されている情報通りで、検索についても INSERT 同様の動作となっていますね。

varchar に unicode 文字列を INSERT したり、cp932 にエンコードすれば、想定した操作ができるのですが、シンプルな方法で pymssql で varchar のマルチバイト文字を操作する方法がいまいちわからずでした。。。。

Unicode 文字をベースとしたクエリで入っているからよいかというとそういうことでもなく、

("かずあきさん", "zaibatsu", u"えろす師匠"), # この方法だと varchar に想定している文字列が格納されない

(u"かずあきさん", "zaibatsu", u"えろす師匠"),

(u"かずあきさん".encode("cp932"), "zaibatsu", u"えろす師匠")

では、順番に

INSERT INTO T1 VALUES(0xe3818be3819ae38182e3818de38195e38293, 'zaibatsu', N'えろす師匠')
INSERT INTO T1 VALUES(N'かずあきさん', 'zaibatsu', N'えろす師匠')
INSERT INTO T1 VALUES(0x82a982b882a082ab82b382f1, 'zaibatsu', N'えろす師匠')

というようなクエリが実行されています。

今回の環境ではパラメーター化されたクエリとして実行されているため、バイナリとして実行されているクエリは最初のもの、nvarchar として 実行されたクエリは 2 番目のパターンで実行が行われています。

(@1 varbinary(8000),@2 varchar(8000),@3 nvarchar(4000))INSERT INTO [T1] values(@1,@2,@3)
(@1 nvarchar(4000),@2 varchar(8000),@3 nvarchar(4000))INSERT INTO [T1] values(@1,@2,@3)

実際とは異なる型で実行されていますので、暗黙の型変換が各パラメーターに対して実行されます。

検索についても同様で、

SELECT * FROM T1 WHERE C1 = 0xe3818be3819ae38182e3818de38195e38293
SELECT * FROM T1 WHERE C1 = 0x82a982b882a082ab82b382f1

というクエリで実行され、実際にはパラメーター化された以下のクエリでキャッシュされています。

(@1 varbinary(8000))SELECT * FROM [T1] WHERE [C1]=@1

今回のケースであればパラメーターとして渡された値が暗黙の型変換が行われているため、実データに対しては変換が行われていないので、オーバーヘッドは抑えられているかと思いますが、できれば通常の文字で検索したいですね…

# データ型の優先順位 に記載されていますが、varbinary は優先順位が低いので、実テーブルの方が変換されることはなさそうですが、変換なしで検索するのがよいので。

Unicode 文字列を使う場合はクセはなさそうなのですが、非 Unicode 文字列に対してマルチバイトの操作をする際の、ベストプラクティスがよくわかりませんでした…。

pyodbc も非 Unicode 文字列もなかなかに難しそうで同じ状態でした。。。

#coding:UTF-8
# brew unlink freetds; brew install homebrew/versions/freetds091
# easy_install pymssql or pip install pymssql
# easy_install pyodbc or pip install pyodbc
# brew install freetds --with-unixodbc
# brew link --overwrite freetds
# brew install unixodbc
# /usr/local/etc/freetds.conf
# /usr/local/Cellar/unixodbc/2.3.4/etc/odbcinst.ini
# https://msdn.microsoft.com/ja-jp/library/mt652092(v=sql.1).aspx
# http://stackoverflow.com/questions/11678696/sql-server-python-and-os-x
# https://gist.github.com/Bouke/10454272
import pyodbc
conn = pyodbc.connect("DRIVER=freeTDS;port=1433;server=127.0.0.1;UID=sa;PWD=M@sterEr0s;")
cursor = conn.cursor()
cursor.executemany("INSERT INTO T1 VALUES(?, ?, ?)",
[
    (u"かずあきさん", "zaibatsu", u"えろす師匠")
])
conn.commit()
Share

Written by Masayuki.Ozawa

12月 10th, 2016 at 1:50 pm

Posted in SQL Server

Tagged with ,

Leave a Reply