質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Python 3.x

Python 3はPythonプログラミング言語の最新バージョンであり、2008年12月3日にリリースされました。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

Q&A

解決済

1回答

9026閲覧

python postgresqlとのselect文の結果をExcelに出力する方法

---stax---

総合スコア148

Python 3.x

Python 3はPythonプログラミング言語の最新バージョンであり、2008年12月3日にリリースされました。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

0グッド

0クリップ

投稿2018/11/26 10:39

編集2018/11/27 02:20

表題の件で質問お願い致します。
postgresqlのデータベースのテーブルにSELECT文を投げ、その結果をExcel(xlsxが理想)に出力させたいのですが方法が分からず悩んでいます。
使用しているモジュールはpsycopg2です。

以下該当のコードです

python

1import psycopg2 2 3conn = psycopg2.connect('dbname=postgres host=localhost user=postgres password=postgres') 4 5cursor = conn.cursor() 6 7# select文を実行 8sql = ("select * from testdata.prac_data") 9cursor.execute(sql) 10 11# 実行した結果を取得 12results = cursor.fetchall() 13 14cursor.copy_to(r'C:\Users\Desktop\test.xlsx', 'testdata.prac_data')

しかし実行すると以下のエラーが発生します。

python

1--------------------------------------------------------------------------- 2TypeError Traceback (most recent call last) 3<ipython-input-14-5244d5b0d003> in <module>() 4 13 results = cursor.fetchall() 5 14 6---> 15 cursor.copy_to(r'C:\Users\Desktop\test.xlsx', 'testdata.prac_data') 7 8TypeError: argument 1 must have a .write() method 9

自分なりにしらべてみたのですがwrite() methodというのが何を指しているのか分かりませんでした。
ファイルは実行するたび新規に作ってほしかったのでtest.xlsxは存在しない場合で実行しても上記と同じエラーが出ました。
何が原因か分からず悩んでいます。アドバイス頂きたいです。
また、要件がxlsxファイルでの出力なのでひとまずcsv形式で出力してから変換しても良いかなと考えているのでもっとほかのやり方もあれば教えてほしいです。
宜しくお願い致します。

参考リンク
公式サイト

--2018/11/27 追記--

pandasにread_sqlメソッドがあることを知りそちらを使ってみました

python

1import psycopg2 2import sys 3import pandas as pd 4import openpyxl 5 6# 接続情報 7connection_config = { 8 'host': 'localhost ', 9 'port': '5432', 10 'database': 'postgres', 11 'user': 'postgres', 12 'password': 'postgres' 13} 14 15# 接続 16connection = psycopg2.connect(**connection_config) 17 18# DataFrameでロード 19rentals = pd.read_sql(sql="SELECT * FROM testdata.prac_data;", con=connection) 20 21# 表示 22rentals 23 24rentals.to_excel(r'C:\Users\Desktop\test.xlsx')

ただ、これを実行すると以下のエラーが発生します

python

1TypeError: Excel doesn't support timezones in datetimes. Set the tzinfo in the datetime/time object to None or use the 'remove_timezone' Workbook() option

excelに出力する際に時間の情報は保持させたいのですが良い方法はありますでしょうか・・・

--再追記--

display(rentals.head())の結果
display(rentals.head())

display(rentals.info())の結果
display(rentals.info())

試しにcsvで出力しようとto_csvで出力し、欠損値が含まれているところには文字列’None’を入れる部分も追加したいと考え以下のコードを作成しました

python

1import psycopg2 2import sys 3import pandas as pd 4import openpyxl 5 6# 接続情報 7 connection_config = { 8 'host': 'localhost', 9 'port': '5432', 10 'database': 'postgres', 11 'user': 'postgres', 12 'password': 'postgres' 13 } 14 15 # 接続 16 connection = psycopg2.connect(**connection_config) 17 18 # DataFrameでロード 19 data = pd.read_sql(sql="SELECT * FROM testdata.prac_data;", con=connection) 20 21 # 欠損値→Noneに置き換え 22 data = data.fillna('None') 23 24 # 出力 25 data.to_csv(r'C:\Users\Desktop\test.csv' , encoding="SHIFT-JIS") 26 27 28 excel_output()

しかしこちらも
AttributeError: 'Timestamp' object has no attribute 'copy'
というエラーがdata = data.fillna('None')の部分で発生し行き詰ってしまいました・・・
xlsx形式、csv形式のどちらも将来使うことになるので可能であればこちらの対処法もご教授頂けたら嬉しいです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

can110

2018/11/26 17:02

参考にされたページやソースについて追記ください(.copy_toメソッドを選んだ理由が?)
---stax---

2018/11/27 00:15

ご指摘ありがとうございます。参考リンク追加致しました。当方postgresとのやり取りの部分を作った経験が浅く、調べてよく出てくるpsycopg2を使用しました。公式のドキュメントを見ているとcopy_toメソッドを見つけたので試してみようと思いました。内容に再追記したのですが、copy_toは結局使い方が分からず、pandasのread_sqlで結果を取得することが出来たのですがtimezoneは処理できないという旨のエラーが出るのですが、時間情報は維持させたいと思っています。何かいい方法はありますでしょうか・・・
can110

2018/11/27 01:32

追記ありがとうございます。pandasを利用するようにしたのですね。dfのカタチおよびdatetime型がaware/nativeかを確認したいのでprint(df.head()) print(df.info())の結果を追記ください。
---stax---

2018/11/27 02:23

回答ありがとうございます。内容追記致しました。重ねて大変申し訳ないのですがto_csv()で出力する際にもTimestampの関係でエラーとなる部分がありました。日付データの処理は一般的だと思うのですがこちらも対応方法を見つけれませんでした。もし可能でしたらこちらもご教授頂けると幸いです
can110

2018/11/27 03:15

datetime型にstr型を突っ込んでエラーになっています。強引に過ぎます。とりあえずNaTに対応するよう回答を修正しました。回答コードで検証ください。
guest

回答1

0

ベストアンサー

提示エラーTypeError: Excel doesn't support timezones in datetimes~は、DataFrameのデータ中のdatetime型がタイムゾーンや夏時間などの情報を持つawareな型である一方、出力先の.xlsxにはそれらの情報を保持できないために発生していると思われます。
対応策としては、awareな型から、タイムゾーンなどを持たないnativeな型に変換後にto_excelすればよいかと思います。

なおdatetime型のaware/nativeの意味については以下を参考にしてください。
Pythonの日付処理とTimeZone
datetime — 基本的な日付型および時間型

Python

1from datetime import datetime, timezone, timedelta 2 3UTC = timezone(timedelta(hours=+0), 'UTC') 4JST = timezone(timedelta(hours=+9), 'JST') 5 6# 日時はJSTなテストデータ 7import pandas as pd 8import numpy as np 9df = pd.DataFrame({'dt':[datetime(2018,11,27,12,tzinfo=JST),np.NaN],'val':[1,2]}) 10print(df) 11""" 12 dt val 130 2018-11-27 12:00:00+09:00 1 141 NaT 2 15""" 16 17# aware -> native な datetime型に変換 18def toNative(row): 19 if not pd.isnull(row['dt']): # 有効な値 20 #row['dt'] = row['dt'].astimezone(UTC) # JST->UTCに変換して出力するなら 21 row['dt'] = row['dt'].replace(tzinfo=None) 22 else: 23 row['dt'] = datetime(2000,1,1) # とりあえず意味のある値にしたいなら 24 pass 25 return row 26 27df = df.apply(toNative,axis=1) 28print(df) 29""" 30 dt val 310 2018-11-27 12:00:00 1 321 2000-01-01 00:00:00 2 33""" 34 35df.to_excel('ret.xlsx') 36df.to_csv('ret.csv')

投稿2018/11/27 02:00

編集2018/11/27 03:13
can110

総合スコア38233

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

---stax---

2018/11/27 04:46

回答ありがとうございます。 以下のコードでexcel出力、csv出力が出来ました。 ※date列に加えてinput_date列もあったので同様の処理をしています。 from datetime import datetime, timezone, timedelta import numpy as np import psycopg2 import pandas as pd # 接続情報 connection_config = { 'host': 'localhost', 'port': '5432', 'database': 'postgres', 'user': 'postgres', 'password': 'postgres' } # 接続 connection = psycopg2.connect(**connection_config) # DataFrameでロード data = pd.read_sql(sql="SELECT * FROM testdata.prac_data;", con=connection) # aware -> native な datetime型に変換 def toNative(row): if not pd.isnull(row['date']): row['date'] = row['date'].replace(tzinfo=None) if not pd.isnull(row['input_date']): row['input_date'] = row['input_date'].replace(tzinfo=None) return row data = data.apply(toNative,axis=1) display(data) display(data.info()) data.to_excel(r'C:\Users\Desktop\ret.xlsx') data.to_csv(r'C:\Users\Desktop\ret.csv') 追記内容の AttributeError: 'Timestamp' object has no attribute 'copy' に関してもご指摘の通り型が違うものを入れようとしていることに言われてから気づきました・・・ 今回のコードは頂いた回答部分からelse文を取り除いています。 例えばNanという値をtimestamp型のところに入れようとすると型を文字列に変える必要があるかなと思うのですが、後々データ分析に使うとした場合timestamp型を文字列型に変換してしまう事の弊害はありますでしょうか?(時系列としての意味を持たないなど)
can110

2018/11/27 07:04

NaN(NaT)は文字列に変換せずに、どの型の変数にも代入できたかと記憶しています。 またNaNが存在していてもto_csvやto_excelは出来、出力結果は空になります。 また、timestamp型を文字型に変換するメリットは特にないと思います。
---stax---

2018/11/27 07:29

回答ありがとうございます。 どの変数にも入れられるのですね。試してみます。 出力結果に関してもNanと表示が見えた方がよいかと考え入れようかと考えていましたが、空=Nanなので分析の際に下手に文字など入れた方が手間がかかりそうなのでこのままいきます。 型変換に関しても同様に手間がかかるだけで必要ないですよね。 非常に丁寧に答えて頂きありがとうございました。 凄く助かりました。ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問