Mysql хранение изображений python

Inserting images in a MySQL as a Blob using Python.

1. In this article we will see how we can save digital data like images, files, videos, songs etc into a MySQL database in the form of Blobs.

2. We will use the images_demo table in the CPP database for this demonstration.

Blobs(large binary objects) are nothing but a data type in the MySQL system used to store binary data.

There are 4 types of Blobs which can store different amounts of data.

As MySQL cannot store images in its original form we will first convert the images into the binary data and then store it into our database table.

Note — Please make sure that you have a column with a datatype marked as blob before you can perform operations on the database.

In order to store the images we will make a database table named images_demo, with the following attributes.

Here is the snippet of the images we are storing in the database.

We will use the following functions to create the table.

This method is used to establish a connection between to the database.

This method is used to create the cursor object.

This method executes the sql commands given as an argument.

This method closes the connection to the database.

Please make sure that a database is already created in the software named as CPP, without which the connection won’t happen.

Above are the output displayed at the console(1) and a snippet of the database table after insertion operation(2).

Hence we have stored images in the form of a blob, in the next article we will see how we can retrieve the data.

Источник

Insert / Retrieve file and images as a Blob in MySQL using Python

In this lesson, you will learn how to insert or save any digital information such as a file, image, video, or song as blob data into a MySQL table from Python. We will also learn how to fetch the file, image, video, or song stored in MySQL using Python.

Goals of this article

Insert binary data into a MySQL table using Python
Read BLOB data files from the MySQL table in Python

Note: We are using the MySQL Connector Python module to connect MySQL.

Further Reading:

Prerequisites

To Store BLOB data in a MySQL table, we need to create a table containing binary data. Alternatively, if you have a table, then modify it by adding one extra column with BLOB as its data type.

You can use the following query to create a table with a BLOB column.

CREATE TABLE `Python_Employee` ( `id` INT NOT NULL , `name` TEXT NOT NULL , `photo` BLOB NOT NULL , `biodata` BLOB NOT NULL , PRIMARY KEY (`id`))

This table contains the following two BLOB columns.

Photo: To store an employee picture.
Biodata file: To store employee details in file format.

As of now, The python_employee the table is empty. Let’s insert employees’ photos and bio-data files in it. Before executing the following programs, please make sure you have the Username and password to connect MySQL.

What is BLOB

A BLOB (large binary object) is a MySQL data type used to store binary data. We can convert our files and images into binary data in Python and keep them in the MySQL table using BLOB.

Note: To insert a file or image into the MySQL table, we need to create a BLOB column as a type. MySQL has the following four BLOB types. Each holds a variable amount of data.

Above BLOB types differ only in the maximum length of the values they can hold. To read more on BLOB, you can visit this MySQL BLOB document.

Insert Image and File as a BLOB data into MySQL Table

Let’s insert employee photo and bio-data into a python_employee table. To insert BLOB data into MySQL Table from Python, you need to follow these simple steps: –

Install MySQL Connector Python using Pip.
Second, Establish MySQL database connection in Python.
Create a function that can convert images and file into binary data.
Then, Define the Insert query to enter binary data into the database table. All you need to know is the table’s column details.
Execute the INSERT query using a cursor.execute() . It returns the number of rows affected.
After the successful execution of the query, commit your changes to the database.
Close the Cursor and MySQL database connection.
Most important, Catch SQL exceptions, if any.
At last, verify the result by selecting data from the MySQL table.

import mysql.connector def convertToBinaryData(filename): # Convert digital data to binary format with open(filename, 'rb') as file: binaryData = file.read() return binaryData def insertBLOB(emp_id, name, photo, biodataFile): print("Inserting BLOB into python_employee table") try: connection = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') cursor = connection.cursor() sql_insert_blob_query = """ INSERT INTO python_employee (id, name, photo, biodata) VALUES (%s,%s,%s,%s)""" empPicture = convertToBinaryData(photo) file = convertToBinaryData(biodataFile) # Convert data into tuple format insert_blob_tuple = (emp_id, name, empPicture, file) result = cursor.execute(sql_insert_blob_query, insert_blob_tuple) connection.commit() print("Image and file inserted successfully as a BLOB into python_employee table", result) except mysql.connector.Error as error: print("Failed inserting BLOB data into MySQL table <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") insertBLOB(1, "Eric", "D:\Python\Articles\my_SQL\images\eric_photo.png", "D:\Python\Articles\my_SQL\images\eric_bioData.txt") insertBLOB(2, "Scott", "D:\Python\Articles\my_SQL\images\scott_photo.png", "D:\Python\Articles\my_SQL\images\scott_bioData.txt")

Inserting BLOB into python_employee table Image and file inserted successfully as a BLOB into python_employee table None MySQL connection is closed Inserting BLOB into python_employee table

Let’s have a look at python_employee table after inserting the image and file into it.

Note: We inserted employee id, name, photo, and bio-data file. For image and bio-data, we passed the location where it is present.

As you can see, we converted our image and file into a binary format by reading the image and file in the rb mode before inserting it into a BLOB column.

Also, we used a parameterized query to insert dynamic data into a MySQL table.

Retrieve Image and File stored as a BLOB from MySQL Table using Python

Suppose we want to read the file or images stored in the MySQL table in binary format and write that file back to some arbitrary location on the hard drive. Let see how we can do that.

Read employee image, and file from MySQL table stored as a BLOB.
Write this BLOB binary data on a disk. We can pass the file format we want it to display to write this binary data on a hard disk.

To read BLOB data from MySQL Table using Python, you need to follow these simple steps: –

Install MySQL Connector Python using pip.
Second, Establish MySQL database connection in Python.
Then, Define the SELECT query to fetch BLOB column values from the database table.
Execute the SELECT query using cursor.execute()
Use cursor.fetchall() to retrieve all the rows from the result set and iterate over it.
Create a function to write BLOB or binary data that we retrieved from each row on disk in a correct format.
Close the Cursor and MySQL database connection.

import mysql.connector def write_file(data, filename): # Convert binary data to proper format and write it on Hard Disk with open(filename, 'wb') as file: file.write(data) def readBLOB(emp_id, photo, bioData): print("Reading BLOB data from python_employee table") try: connection = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') cursor = connection.cursor() sql_fetch_blob_query = """SELECT * from python_employee where cursor.execute(sql_fetch_blob_query, (emp_id,)) record = cursor.fetchall() for row in record: print("Id = ", row[0], ) print("Name = ", row[1]) image = row[2] file = row[3] print("Storing employee image and bio-data on disk \n") write_file(image, photo) write_file(file, bioData) except mysql.connector.Error as error: print("Failed to read BLOB data from MySQL table <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") readBLOB(1, "D:\Python\Articles\my_SQL\query_output\eric_photo.png", "D:\Python\Articles\my_SQL\query_output\eric_bioData.txt") readBLOB(2, "D:\Python\Articles\my_SQL\query_output\scott_photo.png", "D:\Python\Articles\my_SQL\query_output\scott_bioData.txt")

Reading BLOB data from python_employee table Id = 1 Name = Eric Storing employee image and bio-data on disk MySQL connection is closed Reading BLOB data from python_employee table Id = 2 Name = Scott Storing employee image and bio-data on disk MySQL connection is closed

Retrieved image and file from MySQL table and stored on disk.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

15+ Topic-specific Exercises and Quizzes
Each Exercise contains 10 questions
Each Quiz contains 12-15 MCQ

Источник

Русские Блоги

pymysql (part4) — mysql хранит информацию об изображении

Использование pymysql, пример хранения информации об изображении в mysql.

тематическое исследование

(Сохраните картинку в базе данных, а затем получите ее из базы данных)

Есть две формы хранения картинок:
1. Путь к хранилищу образа (строка), который занимает мало места, но данные легко потерять;
2. В двоичном файле, в котором хранится изображение, данные потерять непросто, но он занимает много места.

В этом случае мы создаем таблицу Images, в которой хранится как путь к изображению, так и двоичный файл изображения.

нота! Поскольку изображение является двоичным файлом, при использовании py для чтения и записи файлов jpg используйте rb и wb. При использовании mysql для хранения данных изображения используйте поля типа BLOB.

Сначала мы создаем новую таблицу данных:

Мы видим, что есть 3 фотографии маленьких белых кроликов по пути F: \ MyStudio \ PythonStudio \ goatbishop.project01 \ image:

Мы используем pymysql для передачи данных изображения в mysql:

Поскольку в нашем поле данных много данных, его неудобно отображать в cmd (вы можете отобразить его в cmd, cmd может дать сбой), поэтому давайте взглянем на Workbench, официальный инструмент mysql:

Как видите, данные были импортированы успешно!

Как мы получаем картину? Взгляните на следующий код:

Посмотрите еще раз на путь F: \ MyStudio \ PythonStudio \ goatbishop.project01 \ new_image, чтобы увидеть, есть ли какие-нибудь изображения, которые нам нужны:

Да! успех!

Наконец, мы даем код для импорта изображения в mysql и экспорта кода mysql.

#-*-coding：utf-8-*- #coding=utf-8 """ Created on Thu Feb 13 14:59:17 2020 @author: goatbishop """ import pymysql db = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = '19970928', database = 'stu', charset = 'utf8') # Получить объект курсора cur = db.cursor() for i in range(1,4): image_name = 'IMG%d.jpg' % i image_path = 'image/%s' % image_name with open(image_path, "rb") as fd: data = fd.read() try: sql = "insert into Images \ values(%s, %s,%s, %s);" cur.execute(sql, [str(i), image_name, image_path, data]) db.commit() except Exception as e: db.rollback() print("Сообщение об ошибке: ",e) cur.close() db.close()

#-*-coding：utf-8-*- #coding=utf-8 """ Created on Thu Feb 13 14:59:17 2020 @author: goatbishop """ import pymysql db = pymysql.connect(host = '127.0.0.1', port = 3306, user = 'root', passwd = '19970928', database = 'stu', charset = 'utf8') # Получить объект курсора cur = db.cursor() sql = "select * from Images;" cur.execute(sql) for image in cur.fetchall(): with open('new_image/%s' % image[1],'wb') as fd: fd.write(image[3]) cur.close() db.close()

Источник

Читайте также: Bool function in python

Mysql хранение изображений python