Q&A
実現したいこと
python-oracledbを使って、DataFrameの内容をDataFrame.to_sql()でOracle Databaseのテーブルに値を書き込みたい。
発生している問題・分からないこと
python-oracledbを使って、Oracle Databaseのテーブルに値を書き込みたいのですが、実行してもデータベースに値が反映されません。
エラーメッセージはでないので、create_engineで echo=True にしています。
select（下記）はできていますので、データベースの接続は問題ありません。
また、cursor.execute（下記）もできました。
ソースでなく、DB側の設定の問題なのでしょうか？
そのためソースのほかに、以下に実行結果、テーブル定義を記しました。
調べる手掛かりさえもわからなくなってしまったので、質問させていただきます。
手がかりだけでも教えていただければ幸いです。
（実際のテーブルはもっとカラムが多く、型も DATE、NUMBER、VARCHAR2 の3種を使っています）
該当のソースコード
Python
1import oracledb 2import pandas as pd 3 4from sqlalchemy import create_engine 5 6engine = create_engine("oracle+oracledb://R_TEST:***@DB001:1521/?service_name=TST19C", echo=True) 7 8df = pd.DataFrame( [ {'A': 'A001', 'B':'B000001'}, {'A': 'A002', 'B':'B000002'} ] ) 9print(df) 10print(df.dtypes) 11 12table_name="R_TEST.DUMMY_F3" 13 14with engine.connect() as con: 15 df.to_sql( 16 name=table_name, 17 con=con, 18 if_exists='replace', 19# if_exists='append', 20 index=False 21 )
SQL
1CREATE TABLE R_TEST.DUMMY_F3 2( 3 A VARCHAR2(10) NOT NULL, 4 B VARCHAR2(100), 5 CONSTRAINT PK_DUMMY_F3 PRIMARY KEY (A) USING INDEX 6 PCTFREE 10 7 INITRANS 2 8 MAXTRANS 255 9 TABLESPACE USERS 10 STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) 11 LOGGING 12 ENABLE 13) 14PCTFREE 10 15MAXTRANS 255 16TABLESPACE USERS 17STORAGE(INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT) 18NOCACHE 19LOGGING 20/
試したこと・調べたこと
- teratailやGoogle等で検索した
- ソースコードを自分なりに変更した
- 知人に聞いた
- その他
上記の詳細・結果
- if_exists='replace'と'append'の両方とも同じ結果でした。
- selectはできました。
- また、cursor.executeで、insert intoもできました。
python
1query = """SELECT * FROM DUMMY_F3""" 2df = pd.read_sql(query, con=engine) 3 4cursor.execute("INSERT INTO R_TEST.DUMMY_F3 (A,B) VALUES ('A01', 'B000001')")
実行結果
. A B
0 A001 B000001
1 A002 B000002
A object
B object
dtype: object
2024-09-09 16:29:31,688 INFO sqlalchemy.engine.Engine select sys_context( 'userenv', 'current_schema' ) from dual
2024-09-09 16:29:31,700 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-09-09 16:29:31,733 INFO sqlalchemy.engine.Engine SELECT value FROM v$parameter WHERE name = 'compatible'
2024-09-09 16:29:31,756 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-09-09 16:29:31,820 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-09-09 16:29:31,828 INFO sqlalchemy.engine.Engine SELECT tables_and_views.table_name
FROM (SELECT a_tables.table_name AS table_name, a_tables.owner AS owner
FROM all_tables a_tables UNION ALL SELECT a_views.view_name AS table_name, a_views.owner AS owner
FROM all_views a_views) tables_and_views
WHERE tables_and_views.table_name = :table_name AND tables_and_views.owner = :owner
2024-09-09 16:29:31,842 INFO sqlalchemy.engine.Engine [generated in 0.01477s] {'table_name': 'R_TEST.DUMMY_F3', 'owner': 'R_TEST'}
2024-09-09 16:29:31,845 INFO sqlalchemy.engine.Engine SELECT tables_and_views.table_name
FROM (SELECT a_tables.table_name AS table_name, a_tables.owner AS owner
FROM all_tables a_tables UNION ALL SELECT a_views.view_name AS table_name, a_views.owner AS owner
FROM all_views a_views) tables_and_views
WHERE tables_and_views.table_name = :table_name AND tables_and_views.owner = :owner
2024-09-09 16:29:31,845 INFO sqlalchemy.engine.Engine [cached since 0.01812s ago] {'table_name': 'R_TEST.DUMMY_F3', 'owner': 'R_TEST'}
2024-09-09 16:29:31,862 INFO sqlalchemy.engine.Engine SELECT a_tables.table_name
FROM all_tables a_tables
WHERE (coalesce(a_tables.tablespace_name, :coalesce_1) NOT IN (:coalesce_2_1, :coalesce_2_2)) AND a_tables.owner = :owner_1 AND a_tables.iot_name IS NULL AND a_tables.duration IS NULL MINUS SELECT a_mviews.mview_name AS table_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_2
2024-09-09 16:29:31,875 INFO sqlalchemy.engine.Engine [generated in 0.01290s] {'coalesce_1': 'no tablespace', 'owner_1': 'R_TEST', 'owner_2': 'R_TEST', 'coalesce_2_1': 'SYSTEM', 'coalesce_2_2': 'SYSAUX'}
2024-09-09 16:29:31,896 INFO sqlalchemy.engine.Engine SELECT a_views.view_name
FROM all_views a_views
WHERE a_views.owner = :owner_1
2024-09-09 16:29:31,930 INFO sqlalchemy.engine.Engine [generated in 0.03389s] {'owner_1': 'R_TEST'}
2024-09-09 16:29:31,960 INFO sqlalchemy.engine.Engine SELECT a_mviews.mview_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_1
2024-09-09 16:29:31,984 INFO sqlalchemy.engine.Engine [generated in 0.02366s] {'owner_1': 'R_TEST'}
2024-09-09 16:29:32,005 INFO sqlalchemy.engine.Engine SELECT a_tab_cols.table_name, a_tab_cols.column_name, a_tab_cols.data_type, a_tab_cols.char_length, a_tab_cols.data_precision, a_tab_cols.data_scale, a_tab_cols.nullable, a_tab_cols.data_default, a_col_comments.comments, a_tab_cols.virtual_column, a_tab_cols.default_on_null, CASE WHEN (a_tab_identity_cols.table_name IS NULL) THEN NULL ELSE a_tab_identity_cols.generation_type || :generation_type_1 || a_tab_identity_cols.identity_options END AS identity_options
FROM all_tab_cols a_tab_cols LEFT OUTER JOIN all_col_comments a_col_comments ON a_tab_cols.table_name = a_col_comments.table_name AND a_tab_cols.column_name = a_col_comments.column_name AND a_tab_cols.owner = a_col_comments.owner LEFT OUTER JOIN all_tab_identity_cols a_tab_identity_cols ON a_tab_cols.table_name = a_tab_identity_cols.table_name AND a_tab_cols.column_name = a_tab_identity_cols.column_name AND a_tab_cols.owner = a_tab_identity_cols.owner
WHERE a_tab_cols.table_name IN (:all_objects_1) AND a_tab_cols.hidden_column = :hidden_column_1 AND a_tab_cols.owner = :owner_1 ORDER BY a_tab_cols.table_name, a_tab_cols.column_id
2024-09-09 16:29:32,067 INFO sqlalchemy.engine.Engine [generated in 0.06137s] {'generation_type_1': ',', 'hidden_column_1': 'NO', 'owner_1': 'R_TEST', 'all_objects_1': 'R_TEST.DUMMY_F3'}
2024-09-09 16:29:32,075 INFO sqlalchemy.engine.Engine SELECT a_objects.object_name
FROM all_objects a_objects
WHERE a_objects.owner = :owner_1 AND a_objects.object_type IN (:object_type_1_1, :object_type_1_2) AND a_objects.object_name IN (:filter_names_1)
2024-09-09 16:29:32,083 INFO sqlalchemy.engine.Engine [generated in 0.00856s] {'owner_1': 'R_TEST', 'object_type_1_1': 'TABLE', 'object_type_1_2': 'VIEW', 'filter_names_1': 'R_TEST.DUMMY_F3'}... 略 ...
2024-09-09 16:29:32,374 INFO sqlalchemy.engine.Engine [generated in 0.06879s] {'owner_1': 'R_TEST', 'table_name_1': 'BIN$%', 'owner_2': 'R_TEST', 'mview_name_1': 'BIN$%', 'filter_names_1': 'R_TEST.DUMMY_F3'}
2024-09-09 16:29:32,379 INFO sqlalchemy.engine.Engine SELECT a_tables.table_name, a_tables.compression, a_tables.compress_for
FROM all_tables a_tables
WHERE a_tables.owner = :owner_1 AND a_tables.table_name IN (:filter_names_1)
2024-09-09 16:29:32,406 INFO sqlalchemy.engine.Engine [generated in 0.02691s] {'owner_1': 'R_TEST', 'filter_names_1': 'R_TEST.DUMMY_F3'}
2024-09-09 16:29:32,545 INFO sqlalchemy.engine.Engine
DROP TABLE "R_TEST.DUMMY_F3"
2024-09-09 16:29:32,559 INFO sqlalchemy.engine.Engine [no key 0.01441s] {}
2024-09-09 16:29:32,591 INFO sqlalchemy.engine.Engine
CREATE TABLE "R_TEST.DUMMY_F3" (
"A" CLOB,
"B" CLOB
)2024-09-09 16:29:32,593 INFO sqlalchemy.engine.Engine [no key 0.00473s] {}
2024-09-09 16:29:32,639 INFO sqlalchemy.engine.Engine INSERT INTO "R_TEST.DUMMY_F3" ("A", "B") VALUES (:A, :B)
2024-09-09 16:29:32,706 INFO sqlalchemy.engine.Engine [generated in 0.06744s] [{'A': 'A001', 'B': 'B000001'}, {'A': 'A002', 'B': 'B000002'}]
2024-09-09 16:29:32,716 INFO sqlalchemy.engine.Engine SELECT a_tables.table_name
FROM all_tables a_tables
WHERE (coalesce(a_tables.tablespace_name, :coalesce_1) NOT IN (:coalesce_2_1, :coalesce_2_2)) AND a_tables.owner = :owner_1 AND a_tables.iot_name IS NULL AND a_tables.duration IS NULL MINUS SELECT a_mviews.mview_name AS table_name
FROM all_mviews a_mviews
WHERE a_mviews.owner = :owner_2
2024-09-09 16:29:32,740 INFO sqlalchemy.engine.Engine [cached since 0.8774s ago] {'coalesce_1': 'no tablespace', 'owner_1': 'R_TEST', 'owner_2': 'R_TEST', 'coalesce_2_1': 'SYSTEM', 'coalesce_2_2': 'SYSAUX'}
2024-09-09 16:29:32,764 INFO sqlalchemy.engine.Engine COMMIT
補足
実行環境
Python 3.12.4
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production 19.0.0.0.0
venvを使用
(venv) >pip list
cffi 1.17.0
cryptography 43.0.0
greenlet 3.0.3
numpy 2.1.0
oracledb 2.4.1
pandas 2.2.2
pip 24.2
pycparser 2.22
python-dateutil 2.9.0.post0
pytz 2024.1
six 1.16.0
SQLAlchemy 2.0.34
typing_extensions 4.12.2
tzdata 2024.1
回答1件
0
with engine.connect() では明示的にコミットをしないと、データベースに反映されないです。(ブロックを抜ける時にロールバックされる)
https://docs.sqlalchemy.org/en/20/core/connections.html#commit-as-you-go
python
1with engine.connect() as con: 2 df.to_sql(name=table_name, con=con, if_exists='replace', index=False) 3 con.commit()
with engine.begin() で書けば、ブロックを抜ける時にコミットされるようになります。
https://docs.sqlalchemy.org/en/20/core/connections.html#connect-and-begin-once-from-the-engine
python
1with engine.begin() as con: 2 df.to_sql(name=table_name, con=con, if_exists='replace', index=False)
トランザクションが不要なら、シンプルに df.to_sql() に engine を渡すのでもいいと思いました。
python
1df.to_sql(name=table_name, con=engine, if_exists='replace', index=False)
追記
pandas の to_sql() でテーブル名の指定をするときは、スキーマ名は含めないです。
python
1table_name = 'DUMMY_F3' 2df.to_sql(name=table_name, con=engine, if_exists='replace', index=False)
スキーマを指定したい(デフォルトから変更したい)ときは、引数 schema= で指定します。
(今回のケースではschemaの指定はなくても大丈夫ではないでしょうか)
python
1table_name = 'DUMMY_F3' 2schema_name = 'R_TEST' 3df.to_sql(name=table_name, con=engine, schema=schema_name, if_exists='replace', index=False)
投稿2024/09/24 00:15
編集2024/09/25 00:10
総合スコア4774
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2024/09/24 19:48 編集
2024/09/25 00:11