mysql blob odbc_mysql.connector操作mysql的blob值

This tutorial shows you how to work with MySQL BLOB data in Python, with examples of updating and reading BLOB data.

The  authors table has a column named  photo whose data type is BLOB. We will read data from a picture file and update to the photo column.

Updating BLOB data in Python

First, we develop a function named  read_file() that reads a file and returns the file’s content:

1

2

3

4

defread_file(filename):

withopen(filename,'rb')asf:

photo=f.read()

returnphoto

Second, we create a new function named  update_blob() that updates photo for an author specified by author_id .

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

frommysql.connector importMySQLConnection,Error

frompython_mysql_dbconfig importread_db_config

defupdate_blob(author_id,filename):

# read file

data=read_file(filename)

# prepare update query and data

query="UPDATE authors "

"SET photo = %s "

"WHERE id  = %s"

args=(data,author_id)

db_config=read_db_config()

try:

conn=MySQLConnection(**db_config)

cursor=conn.cursor()

cursor.execute(query,args)

conn.commit()

exceptError ase:

print(e)

finally:

cursor.close()

conn.close()

Let’s examine the code in detail:

First, we call the  read_file() function to read data from a file and return it.

Second, we compose an UPDATE statement that updates photo column for an author specified by author_id . The  args variable is a tuple that contains file data andauthor_id . We will pass this variable to the  execute() method together with the query .

Third, inside the  try except block, we connect to the database, instantiate a cursor, and execute the query with args . To make the change effective, we call commit() method of the MySQLConnection object.

Fourth, we close the cursor and database connection in the  finally block.

Notice that we imported MySQLConnection and Error objects from the MySQL Connector/Python package and  read_db_config() function from the  python_mysql_dbconfig module that we developed in the previous tutorial.

Let’s test the  update_blob() function.

1

2

3

4

5

defmain():

update_blob(144,"picturesgarth_stein.jpg")

if__name__=='__main__':

main()

Notice that you can use the following photo and put it into the pictures folder for testing.

Inside the main function, we call the  update_blob() function to update the photo column for the author with id 144. To verify the result, we select data from the  authors table.

1

2

SELECT*FROMauthors

WHERE id=144;

It works as expected.

Reading BLOB data in Python

In this example, we select BLOB data from the  authors table and write it into a file.

First, we develop a  write_file() function that write a binary data into a file as follows:

1

2

3

defwrite_file(data,filename):

withopen(filename,'wb')asf:

f.write(data)

Second, we create a new function named  read_blob() as below:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

defread_blob(author_id,filename):

# select photo column of a specific author

query="SELECT photo FROM authors WHERE id = %s"

# read database configuration

db_config=read_db_config()

try:

# query blob data form the authors table

conn=MySQLConnection(**db_config)

cursor=conn.cursor()

cursor.execute(query,(author_id,))

photo=cursor.fetchone()[0]

# write blob data into a file

write_file(photo,filename)

exceptError ase:

print(e)

finally:

cursor.close()

conn.close()

The  read_blob() function reads BLOB data from the  authors table and write it into a file specified by the  filename parameter.

The code is straightforward:

First, we compose a SELECT statement that retrieves photo of a specific author.

Second, we get the database configuration by calling the  read_db_config() function.

Third, inside the  try except block, we connect to the database, instantiate cursor, and execute the query. Once we got the BLOB data, we use the  write_file() function to write it into a file specified by the filename .

Fourth, in the finally block, we close the cursor and database connection.

Now, let’s test the  read_blob() function.

1

2

3

4

5

defmain():

read_blob(144,"outputgarth_stein.jpg")

if__name__=='__main__':

main()

If you open the output folder in the project and see a picture there, it means that you have successfully read the blob from the database.