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

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

ただいまの
回答率

90.48%

  • Python

    12201questions

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

  • Python 3.x

    10213questions

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

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 628

---stax---

score 119

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

以下該当のコードです

import psycopg2

conn = psycopg2.connect('dbname=postgres host=localhost user=postgres password=postgres')

cursor = conn.cursor()

# select文を実行
sql = ("select * from testdata.prac_data")
cursor.execute(sql)

# 実行した結果を取得
results = cursor.fetchall()

cursor.copy_to(r'C:\Users\Desktop\test.xlsx', 'testdata.prac_data')


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

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-14-5244d5b0d003> in <module>()
     13 results = cursor.fetchall()
     14 
---> 15 cursor.copy_to(r'C:\Users\Desktop\test.xlsx', 'testdata.prac_data')

TypeError: argument 1 must have a .write() method

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

参考リンク
公式サイト

--2018/11/27 追記--

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

import psycopg2
import sys
import pandas as pd
import openpyxl

# 接続情報
connection_config = {
    'host': 'localhost ',
    'port': '5432',
    'database': 'postgres',
    'user': 'postgres',
    'password': 'postgres'
}

# 接続
connection = psycopg2.connect(**connection_config)

# DataFrameでロード
rentals = pd.read_sql(sql="SELECT * FROM testdata.prac_data;", con=connection)

# 表示
rentals

rentals.to_excel(r'C:\Users\Desktop\test.xlsx')

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

TypeError: 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’を入れる部分も追加したいと考え以下のコードを作成しました

import psycopg2
import sys
import pandas as pd
import openpyxl

# 接続情報
    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)

    # 欠損値→Noneに置き換え
    data = data.fillna('None')

    # 出力
    data.to_csv(r'C:\Users\Desktop\test.csv' , encoding="SHIFT-JIS")


    excel_output()


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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • can110

    2018/11/27 10:32

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

    キャンセル

  • ---stax---

    2018/11/27 11:23

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

    キャンセル

  • can110

    2018/11/27 12:15

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

    キャンセル

回答 1

checkベストアンサー

+1

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

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

from datetime import datetime, timezone, timedelta

UTC = timezone(timedelta(hours=+0), 'UTC')
JST = timezone(timedelta(hours=+9), 'JST')

# 日時はJSTなテストデータ
import pandas as pd
import numpy as np
df = pd.DataFrame({'dt':[datetime(2018,11,27,12,tzinfo=JST),np.NaN],'val':[1,2]})
print(df)
"""
                         dt  val
0 2018-11-27 12:00:00+09:00    1
1                       NaT    2
"""

# aware -> native な datetime型に変換
def toNative(row):
    if not pd.isnull(row['dt']): # 有効な値
        #row['dt'] = row['dt'].astimezone(UTC) # JST->UTCに変換して出力するなら
        row['dt'] = row['dt'].replace(tzinfo=None)
    else:
        row['dt'] = datetime(2000,1,1) # とりあえず意味のある値にしたいなら
        pass
    return row

df = df.apply(toNative,axis=1)
print(df)
"""
                   dt  val
0 2018-11-27 12:00:00    1
1 2000-01-01 00:00:00    2
"""

df.to_excel('ret.xlsx')
df.to_csv('ret.csv')

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/27 13: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型を文字列型に変換してしまう事の弊害はありますでしょうか?(時系列としての意味を持たないなど)

    キャンセル

  • 2018/11/27 16:04

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

    キャンセル

  • 2018/11/27 16:29

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

    キャンセル

同じタグがついた質問を見る

  • Python

    12201questions

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

  • Python 3.x

    10213questions

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