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

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

ただいまの
回答率

90.00%

Pythonでスクレイピング。DB接続しExcelにデータを入れる際エラー頻出。ご指導お願いします!

受付中

回答 1

投稿 編集

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

rare-sugar

score 0

はじめまして。
rare-sugarと申します。Pythonの初心者です。

前提・実現したいこと

i.    環境

  1. OS:        Windows 10 Pro
  2. Python環境    Anaconda (version 1.7.2)
  3. Excel    MS Office 2013 Pro

ii.    使用言語
Python        Version 3.6.7

iii.    現状と問題点

A:    ### 実現したいこと

あるサイトから、1から1,000(10,000)くらいまで連番のついた画像をSQLとfor文で一挙にスクレイピングし、いったんデータベース(SQLite or MySQLなど)に格納します。それらの画像データをデータベースからExcelへ読み込み・書き出しします。最後に、そのExcelシートをMS Accessからインポートして、Accessで画像データベースを作成します。

B    ### 行数列数とデータ型

データベースもExcelも、2列1,000(10,000)行で、A列(フィールド1)のデータ型は整数型、B列(フィールド2)のデータ型はバイナリ型(SQLiteではBLOB型)

発生している問題・エラーメッセージ

  1.    SQLとfor文で複数の画像を一挙にスクレイピングし、いったんデータベース(SQLite)に格納するところまでは、問題なく成功。

  2.    それらの画像データを読み込みExcelへ書き出しする時点で、失敗する。jupyter notebook上に表示させるくらいは可能だが、Excelの場合はやはりExcel独特のセル処理の問題があるので、何列何行目のセルにきちんと入らないとExcelデータにならないようです。

  3.    データ型を「リスト型」にすれば、for文でそのリストの中の画像を【add_image】メソッドで順次複数の画像を複数のセルの中に入れることは可能。現に、それでうまくいっています。

  4.    目的はリストではなくあくまでもデータベースとしてSQL文で上記の処理を実現したいのです。なにしろ、扱う画像ファイルは何千何万とあるのですから、何千何万ものリストを手入力で記述することは現実的ではありません。

  5.    リストやタプルを変数化して短いコードで膨大な画像をExcelに格納できるのであればそれでもいいですから、とにかくこれらの画像をExcelシートのセルに入れるPythonコーディングをお教えください。

  6.      なお、この投稿には多少の矛盾があることに今気づきました。なにぶん初心者ですので、ひらにご容赦くださいませ。

エラーメッセージ

<sqlite3.Cursor at 0x22ecc63ca40>

該当のソースコード

Python
ソースコード

データベースを新規作成(DBに【接続】)

conn = sqlite3.connect('pyDataBase.db') 

「カーソル」を取得

c = conn.cursor()

 SQLを実行

c.execute(DROP_TABLE) 
c.execute(CREATE_TABLE1)


エラーメッセージ

OSError: cannot identify image file '11.png'

該当のソースコード

from openpyxl.drawing.image import Image
from openpyxl.styles import Alignment 

xlImages = [{'id':1',  'Structures':'1.png'},
         {' id':2',  'Structures':'2.png,
         {' id':3',  'Structures':'3.png,
         {' id':4',  'Structures':'4.png,
         {' id':5',  'Structures':'5.png,
         {' id':6',  'Structures':'6.png,
         {' id':7',  'Structures':'7.png,
         {' id':8',  'Structures':'8.png,
         {' id':9',  'Structures':'9.png,
         {' id':10',  'Structures':'10.png ':''}]  
---------------------------------------------------------------------
**d = {}
for x in xlImages
    if x not in d.keys():
        d[x] = 1
    else:
        d[x] += 1
**
wbbk.save('New_myImages.xlsx')

試したこと

import sqlite3
import openpyxl as px
import sqlalchemy as sa
import requests
import cv2
import pandas as pd
import numpy as np

dbName = 'pyDataBase.db'
dbPath = r'C:\Users\shin\python_sqlite3\PubChem\pyDataBase.db'

xlBook = 'xlDataBase.xlsx'
xlPath = r'C:\Users\shin\python_sqlite3\PubChem\xlDataBase.xlsx'

# テーブル作成をやり直したいときに備えます
DROP_TABLE = "DROP TABLE IF EXISTS TABLE1"

# テーブルの定義と作成
CREATE_ TABLE1 = '''CREATE TABLE TABLE1
(id INTEGER PRIMARY KEY,
Structures BLOB)'''

# データベースを新規作成(DBに【接続】)
conn = sqlite3.connect(' pyDataBase.db ') 
# 「カーソル」を取得
c = conn.cursor()
#  SQLを実行
c.execute(DROP_TABLE) 
c.execute(CREATE_TABLE1)

#  【INSERT文】に挿入するナマ・データを直接記述することはセキュリティ面で問題になるので、プレースホルダの?で代替
#   executeメソッドの引数にはSQL文と、かつ挿入する実際値をリストやタプルの形で指定。今回は【文字列のタプルであるrow】を指定

wbpp = px.load_workbook(xlPath, data_only=True)
wspp = wbpp[wbpp.sheetnames[0]]
wspp.delete_rows(1) 

------------------------------------------------------------------------

for row in wspp.values:
    sqlstr = "INSERT INTO TABLE1  ('id, 'Structures') VALUES(?,?)"
    c.execute(sqlstr, row)
conn.commit()  

if field1 == "":
    print("field1(A列名、1番目の列名)は、空っぽです。列名を定義・作成してください")

elif field1 != "" + field2 != "":

    i =1
    for i in range(1, 1000):

        str_id = str(i)

        PubChem_images = str_id + ".png"

        url_PubChem = "https://pubchem.ncbi.nlm.nih.gov/image/imgsrv.fcgi?cid="

        url_PubChem_images = url_PubChem + str_id + "&t=l"

        i = i + 1

        for row in wspp.values:
            sqlstr = "INSERT INTO TABLE1 ('id', 'Structurs') VALUES(?,?)"

            imgUrl = "https://pubchem.ncbi.nlm.nih.gov/image/imgsrv.fcgi?cid=" + str(i) + "&t=l"

            r = requests.get(imgUrl)

        with open(str(i) + '.png', 'wb') as f:

            img = Image(str(i) + '.png')

            wsbk.add_image(img, 'B')

            i = i + 1

            c.execute(sqlstr, row)

            wbbk.save('new_xlDataBase.xlsx) 

    conn.commit()

    conn.close() # 接続解除

補足情報(FW/ツールのバージョンなど)

"1.png"アセチルカルニチン(カルニチン誘導体の一種)の画像データ
NCBI(米国立生物工学情報センター)の化学医学データベースサイトPubChemからidと2D画像データ(分子模型、化学組成)をスクレイピング。ちなみに、その1番目は、Acetylcarnitine。アセチルカルニチン(カルニチン誘導体の一種)で、2D画像データも参考のためUpしておきます。URLは、リンク内容

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • Takumiboo

    2019/12/03 21:44

    ソースコードは<code>ボタンよりマークアップしてください。質問は編集可能です。

    キャンセル

  • Orlofsky

    2019/12/03 21:47

    コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

    キャンセル

回答 1

0

何か元ネタをタイプしたんですか?
タイプミスが多過ぎませんか?
眠いのでちょっとだけ。

CREATE_ TABLE1 = '''CREATE TABLE TABLE1
(id INTEGER PRIMARY KEY,
Structures BLOB)''
    ↓
CREATE_TABLE1 = "CREATE TABLE TABLE1
(id INTEGER PRIMARY KEY,
Structures BLOB)"
c.execute(CREATE_ TABLE1)
    ↓
c.execute(CREATE_TABLE1)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/12/04 00:09

    Orlofskyさん、早速のご回答ありがとうございます。

    ソースコードを丸写しした「元ネタ」は、残念ながらありません。
    たった一人で「涙ぐましい努力」「孤軍奮闘」しましたので、こんな下手なコーディングになったのです。

    ただ、「日経ソフトウエア」誌2019年11月号P006-P022「Python × Excel活用術」(東北大学工学博士清水美樹氏)は参考にさせていただきました。
    しかし、同記事は、あくまでもデータが多数入力済みのExcelブックをPythonでどう扱うかがメインテーマの一つですが、私はその逆で1数字も入力されていない空っぽのExcelブックに、しかもスクレイピングした画像をデータベース化して、Accessやデータベースサーバーに格納するのが目的なので、ベクトルが逆でしょうね。

    キャンセル

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

  • ただいまの回答率 90.00%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる