Retrieve a blob file from SQL database and save on local storage in python environment

Question

I am trying to retrive a image from a SQl database and save in local storage. The image uploaded into database is a tif file(using a medium blob field). How can i retrieve the image as a tif file in to the local storage. I have tried to do perform this query using This tutorial.

Here is my code

import pymysql
from arcpy import env
from mysql.connector import MySQLConnection, Error
env.workspace = "D:/year 4 semester 1/Python/Data/TestData"

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)


def read_blob(author_id, filename):
    # select photo column of a specific author
    query = "SELECT picture FROM sourcedata WHERE Number = %s"
    try:
        # query blob data form the authors table
        conn = pymysql.connect(host='localhost', user='root', password='', db='research')
        cursor = conn.cursor()
        cursor.execute(query, (author_id,))
        photo = cursor.fetchone()[0]
        # write blob data into a file
        write_file(photo, filename)

    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()

def main():
    read_blob(1, "D:\\year 4 semester 1\\Python\\Data\\TestData\\abc.tif")


if __name__ == '__main__':
    main()

Instead of following code in tutorial

from python_mysql_dbconfig import read_db_config
db_config = read_db_config()

try:
    conn = MySQLConnection(**db_config)

I have tried following code to connect to database

conn = pymysql.connect(host='localhost', user='root', password='', db='research')

Python script runs without generating any error. But the image is not downloaded into file location.

score 0 · Answer 1 · answered Nov 06 '18 at 17:55

I have tried this code, and it works well in my computer.

import mysql.connector
import sys

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)

sampleNum=0;
# select photo column of a specific author
# read database configuration
db_config = mysql.connector.connect(user='root', password='root',
                      host='localhost',
                      database='db_name')
# query blob data form the authors table
cursor = db_config.cursor()

try:
    sampleNum=sampleNum+1;
    query = "SELECT img FROM img WHERE id=%s"
    cursor.execute(query,(sampleNum,))
    photo = cursor.fetchone()[0]
    write_file(photo, 'User'+str(sampleNum)+'.jpg')

except Error as e:
    print(e)
finally:
    cursor.close()

Retrieve a blob file from SQL database and save on local storage in python environment

1 Answers1