0

I have a program that collects some data from the web-site. Text data is appended into "info" DataFrame and photo urls are appended to "photos" DataFrame.

I have already inserted "info" table to my SQL database and works really fine!

data.to_sql('Flat', con=conn, if_exists='replace',index=False)

Now i need to understand how can convert image links to Blob data and insert it into DataBase.

J. Murray
  • 1,460
  • 11
  • 19
Andrew 76868
  • 394
  • 1
  • 4
  • 14

1 Answers1

2

BLOBs are Binary Large OBjects. First you need to convert the image to a binary object.

def convertToBinaryData(imageLocation):
    #Convert digital data to binary format
    with open(imageLocation, 'rb') as file:
        blobData = file.read()
    return blobData

The rest is a simple insert, make sure you are connected. Create an insert statement, inject your binaries into this statement.

 insert = """ INSERT INTO 'images' ('id', 'image') VALUES (?, ?) """
 id = 1
 image = convertToBinary(imageLocation)
 cursor.execute(insert, (id, image))
 connection.commit()

These functions are omitting how to create a connection and get a cursor, however full example can be found at: https://pynative.com/python-sqlite-blob-insert-and-retrieve-digital-data/

J. Murray
  • 1,460
  • 11
  • 19
Devin
  • 134
  • 9
  • That's great, but i have image URLs) – Andrew 76868 Oct 13 '19 at 17:23
  • Depending on the size of the images, you can request them and then turn them to blobs. Or even request them as blobs. https://stackoverflow.com/questions/34077942/python-use-requests-to-read-an-image-url-then-save-as-blob-data-to-mysql – Devin Oct 13 '19 at 17:25
  • and how can i get them as Blobs ? – Andrew 76868 Oct 13 '19 at 17:27
  • 1
    Depends on the provider or tools I left a link to another question, where the question contains accurate code. – Devin Oct 13 '19 at 17:29