How to insert / retrieve a file stored as a BLOB in a MySQL db using python

by 
thedata = open('thefile', 'rb').read()
sql = "INSERT INTO sometable (theblobcolumn) VALUES (%s)"
cursor.execute(sql, (thedata,))

That code of course works as written only if your table has just the BLOB column and what
you want to do is INSERT, but of course you could easily tweak it to add more columns,
use UPDATE instead of INSERT, or whatever it is that you exactly need to do.

I’m also assuming your file is binary rather than text, etc; again, if my guesses are
incorrect it’s easy for you to tweak the above code accordingly.

Some kind of SELECT on cursor.execute, then some kind of fetching from the cursor, is how you
retrieve BLOB data, exactly like you retrieve any other kind of data.

More Related Contents:

  1. Lazy Method for Reading Big File in Python?
  2. Reading binary file and looping over each byte
  3. open() gives FileNotFoundError/IOError: Errno 2 No such file or directory
  4. How to open a file using the open with statement
  5. How to write a multidimensional array to a text file?
  6. Read file from line 2 or skip header row
  7. Python 32-bit memory limits on 64bit windows
  8. How can I connect to MySQL in Python 3 on Windows?
  9. Python & MySql: Unicode and Encoding
  10. Authentication plugin ‘caching_sha2_password’ is not supported
  11. Writing a list to a file with Python, with newlines
  12. File read using “open()” vs “with open()” [duplicate]
  13. How to disable SQLAlchemy caching?
  14. Setting Django up to use MySQL
  15. memory-efficient built-in SqlAlchemy iterator/generator?
  16. How can I insert data into a MySQL database?
  17. How to use MySQLdb with Python and Django in OSX 10.6?
  18. When to close cursors using MySQLdb
  19. How to enable MySQL client auto re-connect with MySQLdb?
  20. Python Pandas: How to read only first n rows of CSV files in?
  21. How to safely open/close files in python 2.4
  22. Python MySql Insert not working
  23. Python: MySQLdb and “Library not loaded: libmysqlclient.16.dylib”
  24. How to filter (or replace) unicode characters that would take more than 3 bytes in UTF-8?
  25. How to read a single character at a time from a file in Python?
  26. Is it possible to get writing access to raw devices using python with windows?
  27. pickle.load() raising EOFError in Windows
  28. Print results in MySQL format with Python
  29. Python’s MySqlDB not getting updated row
  30. flask-login: can’t understand how it works

Leave a Comment