sqlite3のexecutemanyの動作確認
1回で10万件を挿入するのに、0.35秒。
別実行した1件ソートの繰り返しでは0.42秒。大した差ではないが、ループを組まなくていいメリットあり。
値を埋めたいところは、"INSERT INTO users VALUES(?,?,?)" のようにハテナにしておく。
リストのリストや、タプルのリストにしておけば、全件Insertしてくれるようす。
プログラム
import sqlite3 from faker import Faker import random from datetime import datetime con = sqlite3.connect(":memory:") con.isolation_level = None # None で自動コミットモード cur = con.cursor() cur.execute('PRAGMA temp_store=MEMORY;') cur.execute('PRAGMA journal_mode=MEMORY;') listsize = 100000 sql_create = "CREATE TABLE users(number INTEGER, name TEXT, score REAL)" sql_instert = "INSERT INTO users VALUES(?,?,?)" sql_select = "SELECT number,name,score FROM users ORDER BY number LIMIT 3" sql_select_last = "SELECT number,name,score FROM users ORDER BY number LIMIT 3 OFFSET {0}".format(listsize-3) list1 = [None] * listsize fake = Faker() print("データ準備 開始") for i in range(listsize): list1[i] = [i, fake.name(), random.random()] print("データ準備 完了") cur.execute(sql_create) print("テーブルの定義を表示") tmp = cur.execute("SELECT * FROM sqlite_master WHERE type='table'").fetchall() print(tmp) # ここからexecutemany時間計測 tic = datetime.now() print("データの挿入") cur.executemany(sql_instert, list1) # executemany時間計測終わり toc = datetime.now() cur.execute(sql_select) print("テーブルの中身を表示") print(cur.fetchone()) print(cur.fetchone()) print(cur.fetchone()) print() cur.execute(sql_select_last) print(cur.fetchone()) print(cur.fetchone()) print(cur.fetchone()) print() print("executemany {0}件 処理時間 {1}".format(listsize, toc-tic)) con.close()
実行結果
(base) C:\dev\SampleCodes\hello_sqlite>python execmeny.py
データ準備 開始
データ準備 完了
テーブルの定義を表示
[('table', 'users', 'users', 2, 'CREATE TABLE users(number INTEGER, name TEXT, score REAL)')]
データの挿入
テーブルの中身を表示
(0, 'Dawn West', 0.5244752623689407)
(1, 'Marvin Chambers', 0.8354440852719125)
(2, 'Clinton Lowe', 0.4116042084368037)
(99997, 'Aaron Conley', 0.795391969430207)
(99998, 'Alyssa Brooks', 0.17316654138026266)
(99999, 'Marisa Peterson', 0.018786705346994004)
executemany 100000件 処理時間 0:00:00.350097
sqlite3とpandasの連動確認
やっていること
0. sqliteのDB（インメモリ）の用意
1. データ準備
2. sqliteのDB（インメモリ）に書き込み
3. sqliteのDB（インメモリ）のテーブル定義を確認
4. sqliteのDB（インメモリ）のテーブルの内容を確認 ORDER BY numberでソート。
5. fillnaでNaN値を補完
6. ソートしなおして、NaNを補完した値で、sqliteに書き込み＆内容確認
ソースコード
import sqlite3 import pandas as pd con = sqlite3.connect(":memory:") con.isolation_level = None # None で自動コミットモード cur = con.cursor() cur.execute('PRAGMA temp_store=MEMORY;') cur.execute('PRAGMA journal_mode=MEMORY;') list1 = [[1, 'alpha', 10.0], [2, 'Bravo', None], [3, 'Charlie', 8.5], [5, 'Echo', 9.8], [4, 'Delta', 3.6]] #list1 = [[1,'alpha', 10],[2,'Bravo', None],[3,'Charlie', 8], [5,'Foxtrot', 9],[None,'Echo', 3]] #list1 = [[1,'alpha', 10],[2,'Bravo', 7],[3,'Charlie', 8], [5,'Foxtrot', 9],[4,'Echo', 3]] df1 = pd.DataFrame(list1, columns=['number', 'name', 'score']) print(df1) print(df1.dtypes) # sqliteに書き込み df1.to_sql(name='users', con=con, index=False, schema='number INTEGER, name TEXT, score REAL') tmp = cur.execute("SELECT * FROM users").fetchall() print(tmp) # schemaに'number TEXT, name TEXT, score REAL'を指定しても、 # 'number TEXT, name TEXT, score REAL'になるので、dataframeの設定が優先される様子。 # 値にNoneがあると、INTEGERではなくREALになる。 tmp = cur.execute("SELECT * FROM sqlite_master WHERE type='table'").fetchall() print(tmp) # read_sql_table only supported for SQLAlchemy connectable. df2 = pd.read_sql(sql='SELECT * FROM users ORDER BY number', con=con) # df3.to_sql(・・・,if_exists="replace")を実行したとき、 # pandas.io.sql.DatabaseError: Execution failed on sql 'DROP TABLE "users"': database table is locked # になることがある。再実行するか、curを閉じるとよい。 print(df2) print(df2.dtypes) df3 = df2.fillna(method="ffill") print(df3) df3.to_sql(name='users', con=con, index=False, schema='number INTEGER, name TEXT, score REAL', if_exists="replace") tmp = cur.execute("SELECT * FROM users").fetchall() cur.close() print(tmp) con.close()
実行結果
number name score
0 1 alpha 10.0
1 2 Bravo NaN
2 3 Charlie 8.5
3 5 Echo 9.8
4 4 Delta 3.6
number int64
name object
score float64
dtype: object
[(1, 'alpha', 10.0), (2, 'Bravo', None), (3, 'Charlie', 8.5), (5, 'Echo', 9.8), (4, 'Delta', 3.6)]
[('table', 'users', 'users', 2, 'CREATE TABLE "users" (\n"number" INTEGER,\n "name" TEXT,\n "score" REAL\n)')]
number name score
0 1 alpha 10.0
1 2 Bravo NaN
2 3 Charlie 8.5
3 4 Delta 3.6
4 5 Echo 9.8
number int64
name object
score float64
dtype: object
number name score
0 1 alpha 10.0
1 2 Bravo 10.0
2 3 Charlie 8.5
3 4 Delta 3.6
4 5 Echo 9.8
[(1, 'alpha', 10.0), (2, 'Bravo', 10.0), (3, 'Charlie', 8.5), (4, 'Delta', 3.6), (5, 'Echo', 9.8)]
可変引数のテスト**kwargs
可変引数のテスト
プログラム
def func1(**kwargs):
print(kwargs)
# キーに一致する値の取得。キーがない場合はNoneになる。
print(kwargs.get("a"))
print(kwargs.get("b"))
print(kwargs.get("c"))
print(kwargs.get("key1"))
print(kwargs.get("key2"))
print(kwargs.get("key3"))
print("func1(key1=1, key2=2)の場合")
func1(key1=1, key2=2)
# 辞書を引数として渡せる
print("func1(**dict1)の場合")
dict1 = {"a": 1, "b": "bb"}
func1(**dict1)
実行結果
func1(key1=1, key2=2)の場合
{'key1': 1, 'key2': 2}
None
None
None
1
2
None
func1(**dict1)の場合
{'a': 1, 'b': 'bb'}
1
bb
None
None
None
None
pythonでyamlファイルの読み込み（コンフィグファイルとして）
yamlに書いた内容が、文字列や数値がその型で取得できる。
辞書型と、リストにはいった辞書のyamlの書き方を追記。
import yaml
import os
import pprint
# yamlファイルは、このhello_yaml.pyと同じディレクトリにある想定
default_yaml_filename = "config.yaml"
class config():
def __init__(self):
self.foo = None
self.bar = None
self.hoge = None
self.piyo = None
self.dics = None
self.dics_list=None
self.default_yaml_fullpath = os.path.dirname(
os.path.abspath(__file__)) + os.sep + default_yaml_filename
self.load()
def load(self):
try:
# 念のため絶対パスでアクセス
with open(self.default_yaml_fullpath,"r",encoding="utf-8") as f:
y = yaml.load(stream=f, Loader=yaml.SafeLoader)
self.foo = y.get("foo")
self.bar = y.get("bar")
self.hoge = y.get("hoge")
self.piyo = y.get("piyo")
self.dics = y.get("dics")
self.dics_list = y.get("dics_list")
except:
print("error", self.default_yaml_fullpath)
raise
config = config()
print("config.foo: {0} {1}".format(config.foo, type(config.foo)))
print("config.bar: {0} {1}".format(config.bar, type(config.bar)))
print("config.hoge: {0} {1}".format(config.hoge, type(config.hoge)))
print("config.piyo: {0} {1}".format(config.piyo, type(config.piyo)))
print("config.dics: {0} {1}".format(config.dics, type(config.dics)))
pprint.pprint(config.dics)
print("config.dics_list: {0} {1}".format(config.dics_list, type(config.dics_list)))
pprint.pprint(config.dics_list)config.yamlファイル（ソースコードと同じディレクトリ）
foo: foo1
bar: 100
hoge: 3.14
piyo:
- piyo1
- piyo2
- 100
- 3.14
dics:
x: 100
y: 200
dics_list:
- a: 1111
b: 2222
- c: 3333
d: 4444実行結果
> >python hello_yaml.py
config.foo: foo1 <class 'str'>
config.bar: 100 <class 'int'>
config.hoge: 3.14 <class 'float'>
config.piyo: ['piyo1', 'piyo2', 100, 3.14] <class 'list'>
config.dics: {'x': 100, 'y': 200} <class 'dict'>
{'x': 100, 'y': 200}
config.dics_list: [{'a': 1111, 'b': 2222}, {'c': 3333, 'd': 4444}] <class 'list'>
[{'a': 1111, 'b': 2222}, {'c': 3333, 'd': 4444}]補足
Loader指定のないyaml.load(f)は推奨されない。y = yaml.load(stream=f, Loader=yaml.SafeLoader)に変更。
C:\dev\SampleCodes\hello_yaml\hello_yaml.py:20: YAMLLoadWarning: calling yaml.load() without Loader=... is deprecated, as the default Loader is unsafe. Please read https://msg.pyyaml.org/load for full details. y = yaml.load(f)
Visual Studio Code ＆ Anaconda環境構築
最近、VSCodeでPython環境を作るとデバッグ実行がうまくいかないことがあるので、試行錯誤のメモ。
path（ユーザーの環境変数）に以下を追加
C:\Users\<ユーザー名>\Anaconda3\Scripts
C:\Users\<ユーザー名>\Anaconda3\
anacondaの初期設定
「CommandNotFoundError: Your shell has not been properly configured to use 'conda activate'.」と言われたときは、「conda init」を実行してみるとよい。
(base) C:\Users\<ユーザー名>>conda init
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda.exe
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-env.exe
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-script.py
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\conda-env-script.py
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda.bat
no change C:\Users\<ユーザー名>\Anaconda3\Library\bin\conda.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\_conda_activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\rename_tmp.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda_auto_activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\conda_hook.bat
no change C:\Users\<ユーザー名>\Anaconda3\Scripts\activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\activate.bat
no change C:\Users\<ユーザー名>\Anaconda3\condabin\deactivate.bat
modified C:\Users\<ユーザー名>\Anaconda3\Scripts\activate
modified C:\Users\<ユーザー名>\Anaconda3\Scripts\deactivate
modified C:\Users\<ユーザー名>\Anaconda3\etc\profile.d\conda.sh
modified C:\Users\<ユーザー名>\Anaconda3\etc\fish\conf.d\conda.fish
no change C:\Users\<ユーザー名>\Anaconda3\shell\condabin\Conda.psm1
modified C:\Users\<ユーザー名>\Anaconda3\shell\condabin\conda-hook.ps1
modified C:\Users\<ユーザー名>\Anaconda3\Lib\site-packages\xonsh\conda.xsh
modified C:\Users\<ユーザー名>\Anaconda3\etc\profile.d\conda.csh
modified C:\Users\<ユーザー名>\Documents\WindowsPowerShell\profile.ps1
modified HKEY_CURRENT_USER\Software\Microsoft\Command Processor\AutoRun
「Missing required dependencies ['numpy']」と言われたら以下。
ファイルのありか Python Extension Packages for Windows - Christoph Gohlke
参考サイトscipyが突然読み込まれなくなった話 - Qiita
pip install C:\Users\<ユーザー名>\Downloads\numpy-1.16.2+mkl-cp37-cp37m-win_amd64.whl
PowerShellを管理者として実行
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
pandasでcsv読みこみ datetime型の型指定で。
pandasでファイルの読み書きのテスト。datetimeの処理が必要なので、型指定の手順を確認。
読んでるファイル 'Book2.csv'
,time,x,y,z 0,2019-03-23 08:53:16,0.384126267,0.791150474,1 1,2019-03-23 08:53:16,0.121509436,0.161273729,3 2,2019-03-23 08:53:16,0.97859278,0.926904462,5 3,2019-03-23 08:53:16,0.824561636,0.455903221,7 4,2019-03-23 08:53:16,0.543611046,0.7457197440000001,9 5,2019-03-23 08:53:16,0.056624959,0.39308888200000003,0 6,2019-03-23 08:53:16,0.912447124,0.7451860359999999,-1 7,2019-03-23 08:53:16,0.354390345,0.881826662,-3 8,2019-03-23 08:53:16,0.7894431120000001,0.256685437,-5 9,2019-03-23 08:53:16,0.758507423,0.067165236,-7 10,2019-03-23 08:53:16,0.400961991,0.547244365,-9
プログラム
import pandas as pd
book2_dtypes = {'time':'str', 'x':'float', 'y':'str','z':'int'}
datetime_format= '%Y-%m-%d %H:%M:%S'
datetime_parser = lambda date: pd.datetime.strptime(date, datetime_format)
df1 = pd.read_csv('Book2.csv', index_col=0, dtype=book2_dtypes, parse_dates=[1], date_parser = datetime_parser)
print(df1["time"].dtype)
print(df1["x"].dtype)
print(df1["y"].dtype)
print(df1["z"].dtype)
print(df1)
df1.to_csv("Book3.csv",sep=",",encoding="utf_8")
実行結果
datetime64[ns]
float64
object
int32
time x y z
0 2019-03-23 08:53:16 0.384126 0.791150474 1
1 2019-03-23 08:53:16 0.121509 0.161273729 3
2 2019-03-23 08:53:16 0.978593 0.926904462 5
3 2019-03-23 08:53:16 0.824562 0.455903221 7
4 2019-03-23 08:53:16 0.543611 0.7457197440000001 9
5 2019-03-23 08:53:16 0.056625 0.39308888200000003 0
6 2019-03-23 08:53:16 0.912447 0.7451860359999999 -1
7 2019-03-23 08:53:16 0.354390 0.881826662 -3
8 2019-03-23 08:53:16 0.789443 0.256685437 -5
9 2019-03-23 08:53:16 0.758507 0.067165236 -7
10 2019-03-23 08:53:16 0.400962 0.547244365 -9
移動標準偏差
移動平均同様、numpyを使って、2次元のマトリックスに対する移動標準偏差の計算。
移動平均も内部で利用。
プログラム
import numpy as np
def moving_sum(data_2d,axis=1,windowsize=3):
answer = np.zeros((data_2d.shape))
answer[:,:] = np.nan
v = np.ones(windowsize,)
for i in range(data.shape[axis]):
if axis==0:
answer[i,windowsize-1:]=np.convolve(data_2d[i,:], v, mode = "valid")
if axis==1:
answer[windowsize-1:,i]=np.convolve(data_2d[:,i], v, mode = "valid")
answer=answer
return answer
def moving_average(data_2d,axis=1,windowsize=3):
return moving_sum(data_2d,axis,windowsize)/windowsize
def moving_std(data_2d,axis=1,windowsize=3):
answer = np.zeros((data_2d.shape))
answer[:,:] = np.nan
answer = moving_sum(np.square(data_2d),axis,windowsize) -\
np.square(moving_average(data_2d,axis,windowsize))*windowsize
answer = answer/(windowsize)
answer = np.sqrt(answer)
return answer
#4列のデータが8点
#data=np.arange(32)
#data=np.random.rand(32)
#data=data.reshape(8,4)
#検算のため即値で
data=np.array([[0.86619006, 0.9130783, 0.51988756, 0.35008161],
[0.12355818, 0.3230697, 0.70366867, 0.74275339],
[0.58942652, 0.74948935, 0.30359438, 0.55652164],
[0.40820522, 0.85400935, 0.29218585, 0.21874757],
[0.06330341, 0.91181499, 0.73940466, 0.88877802],
[0.7945424, 0.67662696, 0.44624821, 0.65392414],
[0.26358476, 0.43238069, 0.00853011, 0.05989708],
[0.89179866, 0.52684014, 0.14116962, 0.6934826 ]])
print("元データ")
print(data)
print("横方向で平均")
answer = moving_average(data,axis=0,windowsize=3)
print(answer)
print("縦方向で平均")
answer = moving_average(data,axis=1,windowsize=3)
print(answer)
print("横方向で標準偏差")
answer=moving_std(data,axis=0,windowsize=3)
print(answer)
print("縦向で標準偏差")
answer=moving_std(data,axis=1,windowsize=3)
print(answer)
print("検算 縦向 右下の値 標準偏差")
print(data[5:8,3])
print(np.std(data[5:8,3]))
実行結果
元データ [[0.86619006 0.9130783 0.51988756 0.35008161] [0.12355818 0.3230697 0.70366867 0.74275339] [0.58942652 0.74948935 0.30359438 0.55652164] [0.40820522 0.85400935 0.29218585 0.21874757] [0.06330341 0.91181499 0.73940466 0.88877802] [0.7945424 0.67662696 0.44624821 0.65392414] [0.26358476 0.43238069 0.00853011 0.05989708] [0.89179866 0.52684014 0.14116962 0.6934826 ]] 横方向で平均 [[ nan nan 0.76638531 0.59434916] [ nan nan 0.38343218 0.58983059] [ nan nan 0.54750342 0.53653512] [ nan nan 0.51813347 0.45498092] [ nan nan 0.57150769 0.84666589] [ nan nan 0.63913919 0.59226644] [ nan nan 0.23483185 0.16693596] [ nan nan 0.51993614 0.45383079]] 縦方向で平均 [[ nan nan nan nan] [ nan nan nan nan] [0.52639159 0.66187912 0.5090502 0.54978555] [0.37372997 0.64218947 0.43314963 0.50600753] [0.35364505 0.8384379 0.44506163 0.55468241] [0.42201701 0.81415043 0.49261291 0.58714991] [0.37381019 0.67360755 0.39806099 0.53419975] [0.64997527 0.5452826 0.19864931 0.46910127]] 横方向で標準偏差 [[ nan nan 0.17534819 0.23579612] [ nan nan 0.24064464 0.18930211] [ nan nan 0.1844338 0.18258364] [ nan nan 0.24217704 0.28374409] [ nan nan 0.36618303 0.07642602] [ nan nan 0.14464027 0.10366564] [ nan nan 0.17422663 0.1888656 ] [ nan nan 0.30648191 0.23131534]] 縦向で標準偏差 [[ nan nan nan nan] [ nan nan nan nan] [0.30643714 0.24870894 0.16350932 0.16037833] [0.1917459 0.22965072 0.19134254 0.21688596] [0.21822617 0.06717765 0.20818406 0.27354188] [0.29868678 0.10006632 0.1854965 0.27758398] [0.30853401 0.19573988 0.30031751 0.34881834] [0.27608926 0.10056226 0.1832616 0.28980139]] 検算 縦向 右下の値 標準偏差 [0.65392414 0.05989708 0.6934826 ] 0.28980139385514914
