pandas+sqlalchemy読み書きoracleデータベース
pandasのDataFrameはよく使われるデータ分析データフォーマットであり、Oracleデータベースはよく使われる構造化データストレージ方式であり、通常データ分析に大量のデータが絡む場合、excelだけではなく一定のツールを借りて行う必要があるため、pandasによってデータ分析を行う.Oracleをデータの格納媒体として使用することは、最も便利で柔軟な選択となります.また、DataFrameのデータフォーマットスタイルはoracleの構造化テーブルと非常に似ています.DataFrame形式のデータをoracleに直接書き込むことや、oracleのデータテーブルを直接DataFrameに読み込むことで、データフォーマット変換の作業を大幅に簡素化し、非常に便利です.
今回はsqlalchemyライブラリでoracleにログインし、pandasの関連関数と方法でデータの削除変更を実現します.
ログインoracle
まずsqlalchemyライブラリのcreate_をインポートします.engine,
engine=create_でengine("dialect+driver://username:password@host:port/database")接続を初期化
パラメータの説明:
dialectは、データベースのタイプです.sqlite、mysql、postgresql、oracle、mssqlなどのdriver、データベースに接続するAPIを指定します.例えば、`psycopg 2`、`pyodbc`、`cx_oracle``などは、オプションのキーワードです.username、ユーザー名password、パスワードhost、ネットワークアドレス、ip、ドメイン名、コンピュータ名、もちろんアクセスできます.port、データベースポート.Database、データベース名.
たとえばmysqlの接続を確立する方法は、(echo=Trueで、ロード・データベースで実行されるSQL文が表示されますが、このパラメータは選択せず、デフォルトはFalseです)
engine = create_engine("mysql://scott:tiger@hostname/dbname",encoding='utf-8', echo=True)
Oracleの接続方法は、次のとおりです.
engine = create_engine("oracle://scott:tiger@hostname/dbname",encoding='utf-8', echo=True)
これにより、データベースへの接続が初期化されます.つまり、データベースにログインしました.
添削して調べる.
pandasのread_を使ってsqlメソッドはデータベースのクエリー、削除、更新操作を実現し、to_sqlメソッドはデータの書き込みを実現する.
read_sql
まずはread_を見てsql公式紹介:pd.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
パラメータの説明:
sql、実行されるsql、クエリー、削除、作成、更新などのsql、ここで直接テーブル名を指定できます.デフォルトはselect*from tablenameです.
con,指定したデータベース接続,すなわちcon=engine,すなわちさっき初期化したデータベース接続engine
index_col,クエリの場合,その列がDataFrameのindexであることを指定し,複数の列['a','b']であってもよいが,このときMultiindexが生成される.
coerce_float,boolean,デフォルトはTrueで、sqlクエリの結果を設定するためにfloatの値を変換しようとします
params,list,tuple or dict,オプションキーワード,デフォルト:None,実行メソッドに渡すパラメータリスト.このキーワードがよく分からないので、普通は使えません.
parse_Dates,list or dict,デフォルトはNone,日付時間として解析するフィールド
columns、クエリー時に選択する列、すなわちselect*fromの*を指定し、デフォルトのすべての列
chunksize,int,デフォルトはNoneで、数値を指定すると反復器が返され、反復器内のデータの行数が指定されます.
注意:read_を使用sql方式でdrop/create/truncateなどの操作を実行すると、結果データが返されないためエラーが報告されます(ただし、実行に成功しました).この場合、プログラムを継続して実行する必要がある場合は、tryスナップエラーに参加してプログラムを継続して実行してみてください.
to_sql
to_sqlはデータベースデータを書き込むためのDataFrameのメソッドであり、create/insertと同等である
公式紹介:DataFrame.to_sql(self, name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
キーパラメータの説明:
name、データベース・テーブル名
con,指定したデータベース接続,すなわちcon=engine,すなわちさっき初期化したデータベース接続engine
schema、スタイルを指定して、何の役に立つか分かりません
if_exists,{'fail','place','append'},デフォルトは'fail',すなわちデータベース・テーブルが存在する場合の処理方法を指定し,デフォルトはfail,保留エラー
*fail:エラーを保留*replace:drop元のテーブルを削除し、*appendを再作成します.元のテーブルにデータを挿入します.
index,boolean,デフォルトはTrueで、DataFrameのindexが一緒にデータベースに書き込まれるかどうかを指定します.
index_Label、indexキーワードがTrueの場合、書き込まれたindexのフィールド名を指定し、デフォルトがNoneの場合、フィールド名がindex
chunksize,int,デフォルトはNoneで、数値を指定すると反復器が返され、反復器内のデータの行数が指定されます.書き込みデータ量が多い場合は、このキーワードの数値を指定したほうがいいです.
dtype,dict,オプションキーワード,デフォルトNone,すなわち書き込みフィールド文字タイプを指定し,指定文字タイプに注意する.デフォルト書き込みデータタイプ数はcolbであるため,データタイプを指定しないとエラーが報告される.
to_sql用法実戦
DataFrame.to_sqlメソッドでは、DataFrameデータを直接書き込むことができるデータベース内のテーブル、テーブルはデータベース内に存在しても存在しなくてもよい、異なるタイプのデータベースでは文字のタイプが異なるため、データを書き込む文字のタイプを指定しないと、思わぬエラーが発生することが多いため、エラーを回避するために、書き込むデータのタイプを指定することは非常に必要であり、特にテーブルが存在しない場合、直接to_を使いますsqlメソッドは、テーブルを作成することに相当します.
書き込みデータの文字型はdtypeキーで指定できますが、便宜上、DataFrameの各列のデータ型を自動的に取得して辞書を生成する関数を定義しています.
これにより、関数mapping_が作成されます.df_types、DataFrameの各列の文字タイプを自動的に取得し、dtypeキーワードに使用する
OK、これでデータの書き込みが完了し、「stock_class'テーブルが存在する場合、元のデータに基づいてデータを挿入し、存在しない場合は自動的にstock_を生成するclass’テーブルは、テーブル内の各フィールドの文字タイプも同時に指定されています.
今回はsqlalchemyライブラリでoracleにログインし、pandasの関連関数と方法でデータの削除変更を実現します.
ログインoracle
まずsqlalchemyライブラリのcreate_をインポートします.engine,
engine=create_でengine("dialect+driver://username:password@host:port/database")接続を初期化
パラメータの説明:
dialectは、データベースのタイプです.sqlite、mysql、postgresql、oracle、mssqlなどのdriver、データベースに接続するAPIを指定します.例えば、`psycopg 2`、`pyodbc`、`cx_oracle``などは、オプションのキーワードです.username、ユーザー名password、パスワードhost、ネットワークアドレス、ip、ドメイン名、コンピュータ名、もちろんアクセスできます.port、データベースポート.Database、データベース名.
たとえばmysqlの接続を確立する方法は、(echo=Trueで、ロード・データベースで実行されるSQL文が表示されますが、このパラメータは選択せず、デフォルトはFalseです)
engine = create_engine("mysql://scott:tiger@hostname/dbname",encoding='utf-8', echo=True)
Oracleの接続方法は、次のとおりです.
engine = create_engine("oracle://scott:tiger@hostname/dbname",encoding='utf-8', echo=True)
from sqlalchemy import create_engine
engine = create_engine("oracle://scott:tiger@hostname/dbname",encoding='utf-8', echo=True)これにより、データベースへの接続が初期化されます.つまり、データベースにログインしました.
添削して調べる.
pandasのread_を使ってsqlメソッドはデータベースのクエリー、削除、更新操作を実現し、to_sqlメソッドはデータの書き込みを実現する.
read_sql
まずはread_を見てsql公式紹介:pd.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
パラメータの説明:
sql、実行されるsql、クエリー、削除、作成、更新などのsql、ここで直接テーブル名を指定できます.デフォルトはselect*from tablenameです.
con,指定したデータベース接続,すなわちcon=engine,すなわちさっき初期化したデータベース接続engine
index_col,クエリの場合,その列がDataFrameのindexであることを指定し,複数の列['a','b']であってもよいが,このときMultiindexが生成される.
coerce_float,boolean,デフォルトはTrueで、sqlクエリの結果を設定するためにfloatの値を変換しようとします
params,list,tuple or dict,オプションキーワード,デフォルト:None,実行メソッドに渡すパラメータリスト.このキーワードがよく分からないので、普通は使えません.
parse_Dates,list or dict,デフォルトはNone,日付時間として解析するフィールド
columns、クエリー時に選択する列、すなわちselect*fromの*を指定し、デフォルトのすべての列
chunksize,int,デフォルトはNoneで、数値を指定すると反復器が返され、反復器内のデータの行数が指定されます.
data=pd.read_sql('stock_basic',engine)
data.head()
Out[41]:
ts_code symbol name area industry market list_date
0 000702.SZ 000702 19970318
1 000703.SZ 000703 19970328
2 000705.SZ 000705 19970410
3 000707.SZ 000707 *ST 19970415
4 000708.SZ 000708 19970326
data=pd.read_sql('stock_basic',engine,index_col='ts_code',columns=['symbol','industry'])
data.head()
Out[43]:
symbol industry
ts_code
000702.SZ 000702
000703.SZ 000703
000705.SZ 000705
000707.SZ 000707
000708.SZ 000708 注意:read_を使用sql方式でdrop/create/truncateなどの操作を実行すると、結果データが返されないためエラーが報告されます(ただし、実行に成功しました).この場合、プログラムを継続して実行する必要がある場合は、tryスナップエラーに参加してプログラムを継続して実行してみてください.
to_sql
to_sqlはデータベースデータを書き込むためのDataFrameのメソッドであり、create/insertと同等である
公式紹介:DataFrame.to_sql(self, name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
キーパラメータの説明:
name、データベース・テーブル名
con,指定したデータベース接続,すなわちcon=engine,すなわちさっき初期化したデータベース接続engine
schema、スタイルを指定して、何の役に立つか分かりません
if_exists,{'fail','place','append'},デフォルトは'fail',すなわちデータベース・テーブルが存在する場合の処理方法を指定し,デフォルトはfail,保留エラー
*fail:エラーを保留*replace:drop元のテーブルを削除し、*appendを再作成します.元のテーブルにデータを挿入します.
index,boolean,デフォルトはTrueで、DataFrameのindexが一緒にデータベースに書き込まれるかどうかを指定します.
index_Label、indexキーワードがTrueの場合、書き込まれたindexのフィールド名を指定し、デフォルトがNoneの場合、フィールド名がindex
chunksize,int,デフォルトはNoneで、数値を指定すると反復器が返され、反復器内のデータの行数が指定されます.書き込みデータ量が多い場合は、このキーワードの数値を指定したほうがいいです.
dtype,dict,オプションキーワード,デフォルトNone,すなわち書き込みフィールド文字タイプを指定し,指定文字タイプに注意する.デフォルト書き込みデータタイプ数はcolbであるため,データタイプを指定しないとエラーが報告される.
to_sql用法実戦
DataFrame.to_sqlメソッドでは、DataFrameデータを直接書き込むことができるデータベース内のテーブル、テーブルはデータベース内に存在しても存在しなくてもよい、異なるタイプのデータベースでは文字のタイプが異なるため、データを書き込む文字のタイプを指定しないと、思わぬエラーが発生することが多いため、エラーを回避するために、書き込むデータのタイプを指定することは非常に必要であり、特にテーブルが存在しない場合、直接to_を使いますsqlメソッドは、テーブルを作成することに相当します.
書き込みデータの文字型はdtypeキーで指定できますが、便宜上、DataFrameの各列のデータ型を自動的に取得して辞書を生成する関数を定義しています.
# oracle
from sqlalchemy.dialects.oracle import \
BFILE, BLOB, CHAR, CLOB, DATE, \
DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, \
NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \
VARCHAR2
# ， DataFrme oracle , to_sql ( ， SQLAlchemy type )
def mapping_df_types(df):
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: VARCHAR(256)})
if "float" in str(j):
dtypedict.update({i: NUMBER(19,8)})
if "int" in str(j):
dtypedict.update({i: VARCHAR(19)})
return dtypedictこれにより、関数mapping_が作成されます.df_types、DataFrameの各列の文字タイプを自動的に取得し、dtypeキーワードに使用する
data_base.to_sql('stock_class',engine,index=False,if_exists='append',dtype=dtypedict,chunksize=100)OK、これでデータの書き込みが完了し、「stock_class'テーブルが存在する場合、元のデータに基づいてデータを挿入し、存在しない場合は自動的にstock_を生成するclass’テーブルは、テーブル内の各フィールドの文字タイプも同時に指定されています.