Member Avatar for rajeshwari_ib

Hi all,

i am new to python
how to store image into database blob using python
please help me for this its urgent

  • 4 Contributors
  • 5 Replies
  • 2K Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by Gribouillis

Dani AI

Generated

are right to focus on parameterized inserts. For anyone landing here later: storing images as BLOBs is not automatically a bad design. It works well when you want transactions, backups, and access control in one place. If you do it, pick the right column type (TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB differ only by max length) and consider storing filename and MIME type alongside the bytes so you can restore the original extension. When files are very large or served via a CDN, storing them outside MySQL with only metadata in the table is often simpler. See the MySQL reference on BLOB types. BLOB and TEXT types. (dev.mysql.com)

building on , here is a complete fetch-to-file path using Connector/Python. It reads one row by id and writes the bytes to disk.

import mysql.connector as mysql
from pathlib import Path

img_id = 1  # change as needed

cnx = mysql.connect(host="localhost", user="user", password="pass", database="db")
try:
    with cnx.cursor() as cur:
        cur.execute("SELECT blob_field FROM Tab1 WHERE id = %s", (img_id,))
        row = cur.fetchone()
        if not row:
            raise RuntimeError("No image found for id {}".format(img_id))
        data = row[0]
        Path("image_{}.bin".format(img_id)).write_bytes(data)  # use your stored extension
finally:
    cnx.close()

Practical tips:

Recommended Answers

All 5 Replies

Member Avatar for programmersbook

Hi,

first of all storing a BLOB is very bad design.

But, it would be like this:

blob_value = open('image.jpg', 'rb').read()
sql = 'INSERT INTO Tab1(blob_field) VALUES(%s)'
args = (blob_value, )

cursor.execute (sql, args)
connection.commit()
Member Avatar for rajeshwari_ib

Hi,

first of all storing a BLOB is very bad design.

But, it would be like this:

blob_value = open('image.jpg', 'rb').read()
sql = 'INSERT INTO Tab1(blob_field) VALUES(%s)'
args = (blob_value, )

cursor.execute (sql, args)
connection.commit()

Thanks yar your code is working fine.

Member Avatar for rajeshwari_ib

thanks yar your code is working fine

Member Avatar for kilaru.abhinav

can you help me how to retrieve the image back

Member Avatar for Gribouillis

I would try

sql = 'SELECT `blob_field` FROM `Tab1`'
cursor.execute(sql)
for row in cursor:
    blob_value = row[0]
Edited by Gribouillis
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.