Requesting help in reading blob datatype in mariadb via python

66
September 22, 2018, at 8:10 PM

I am trying to print the content inside blob data type and here is my python code and as well the error.

What is the best method to read blob data via python?

Any help here is really appreciated.

Link to the table screenshot.

image of the table

I got to know the blob data type column contains floating point numbers, given sample below.

6.213203675953e-311 1.69759663307e-313 3e-323 1.697596633e-313 2.121995969e-314 5e-324 6.1113478779844e-311 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 4.473620042652186 . . . .

import mysql.connector as mariadb
db_config = {
    'host': '123.0.0.0',
    'port': '1000',
    'database': 'any_db',
    'user': 'user_name',
    'password': 'pass_word'
    }

def read_blob(cell, experiment):
sql = """select Tuning_Curve_AuC from Cell_Table where cell = %s and experiment = %s"""
try:
    # query blob data form the authors table
    conn = mariadb.connect(**db_config)
    cursor = conn.cursor()
    cursor.execute(sql, (cell, experiment))
    blob = cursor.fetchone()[0]
    print(blob)
except(Exception, mariadb.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
       conn.close()
       print('Database connection closed.')
def main():
read_blob(1, 'E40_062716')
if __name__ == '__main__':
main()

Error:

<built-in method fetch_row of _mysql_connector.MySQL object at 0x7ff57f2b5e00> returned a result with an error set

Database connection closed.

Answer 1

I did some digging and got the solution. It turns out that we have to fetch raw data and use array to convert it to decimals(floating). Here's that part of the code snippet.

    conn = mariadb.connect(**db_config)
    cursor = conn.cursor(raw=True)
    cursor.execute(sql, (cell, experiment))
    blob = cursor.fetchone()[0]
    doubles_sequence = array.array('d', blob)
    print(doubles_sequence)

and of course, you have to 'import array'.

READ ALSO
Unexpected MYSQL UNION and extractvalue behaviour

Unexpected MYSQL UNION and extractvalue behaviour

I was attempting an error-based sql injection when I discovered a weird behavior that I can't explainHere is a simple example:

41
Determine/create a geofence/bounding box on the fly

Determine/create a geofence/bounding box on the fly

I have a MySQL table of lat/lon (think of a school campus or shopping mall)

37
Count Case Statement - When One Field Greater Than Another

Count Case Statement - When One Field Greater Than Another

I'm trying to determine how pervasive a particular mistake is in my databaseI'm comparing one field against another, and when that field is greater then the other, I want it to count it

50
Search in a table of three columns where search criteria may be just one column or two column or three column?

Search in a table of three columns where search criteria may be just one column or two column or three column?

I have a database table contains email, code, nameI have a view search page which can take just email as input or just code or just name or email and name or email and code or email, name, code or nothing

34