{"id":44767,"date":"2019-04-23T03:01:45","date_gmt":"2019-04-22T17:01:45","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/ITblog\/?p=44767"},"modified":"2024-09-08T16:56:35","modified_gmt":"2024-09-08T06:56:35","slug":"python-mysql-connector-blob-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/python-mysql-connector-blob-tutorial\/","title":{"rendered":"Python MySql Connector Blob Tutorial"},"content":{"rendered":"
\"Python<\/a>

Python MySql Connector Blob Tutorial<\/p><\/div>\n

\nNo, Nala<\/a>, we can’t see The Blob<\/a> for the 743rd time today. But we can discuss “blob” in terms of MySql database file storage. It’s that, or a bath?! Okay, that’s settled, so make yourself comfortable with your favourite bone while we slither through our “Python MySql Connector Blob Tutorial”\n<\/p><\/blockquote>\n

In the past we’d talked about Python and MySql connections on a MacBook Pro under Mac OS X with Python MySql Connector Primer Tutorial<\/a>. Today, we’re reacquainting and updating the knowledge, and here, at macOS Mojave Version 10.14, on this MacBook Pro we got a great heads up from this great link<\/a>, thanks, about writing an INSERT\/UPDATE\/SELECT style application to download a Python command line application to store and update and retrieve binary files, such as image files, into and out of a MySql database table, in our case a MAMP<\/a> local Apache\/PHP\/MySql web server one that uses port 8889 and a \/Applications\/MAMP\/tmp\/mysql\/mysql.sock socket file (thanks for the connection advice, you guessed it, Stackoverflow<\/a>) via the reinstall<\/a><\/p>\n


\nsudo pip install mysql-connector
\n<\/code><\/p>\n

… that you need should you first get the error …<\/p>\n


\nImportError: No module named mysql.connector
\n<\/code><\/p>\n

… on revving the engines of macOS Mojave Terminal application command line (and we’re assuming you have Python installed here, otherwise “pip” makes no sense, unless<\/a>) via …<\/p>\n


\npython<\/a> readblob.py<\/a>
\n<\/code><\/p>\n

Now this code just shows the use of a MySql table already primed, rather than the Python application priming it. What steps did we take to “prime” the MAMP MySql database “testdb” that we coded for …<\/p>\n

    \n
  1. started up MAMP<\/li>\n
  2. clicked its “Open WebStart Page” button (to a Safari web browser webpage)<\/li>\n
  3. clicked the link to (great, brilliant, marvellous) phpMyAdmin<\/li>\n
  4. create “testdb” database, as required, via (click of) “SQL” button …
    \n
    \ncreate database testdb
    \n<\/code>
    \n … Go button(click) scenario<\/li>\n
  5. be in “testdb” database via click of its link down the left hand side<\/li>\n
  6. create `IMAGES` table via (click of) “SQL” button …
    \n
    \nCREATE TABLE `IMAGES` (
    \n `id` int(11) NOT NULL,
    \n `photo`     longblob<\/a> NOT NULL
    \n) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    \n<\/code>
    \n … Go button (click) scenario<\/li>\n<\/ol>\n

    The Python code (just “def main” detail and MySql connection code tweak changed from this great link<\/a>, thanks) at startup here makes a connection to a MAMP (running) MySql database via …<\/p>\n


    \nfrom mysql.connector import MySQLConnection, Error,connect
    \n
    \ndb_username='root'
    \ndb_password='root'
    \ndatabase_name='testdb'
    \ndb_host='127.0.0.1'
    \n
    \ndef main():
    \n write_blob(1,\"whyno.jpg\")
    \n update_blob(1, \"svg_map.jpg\")
    \n read_blob(1,\"02.jpg\")
    \n
    \nif __name__ == '__main__':
    \n main()
    \n<\/code><\/p>\n

    … respectively …<\/p>\n


    \ndef read_file(filename):
    \n with open(filename, 'rb') as f:
    \n photo = f.read()
    \n return photo
    \n
    \ndef write_blob(author_id, filename):
    \n # read file
    \n data = read_file(filename)
    \n # prepare update query and data
    \n query = \"INSERT INTO `images` (`id`,`photo`) VALUES (%s,%s)\"
    \n args = (author_id,data)
    \n try:
    \n cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, port='8889', \\
    \n unix_socket='\/Applications\/MAMP\/tmp\/mysql\/mysql.sock', database=database_name)
    \n cursor = cnx.cursor()
    \n cursor.execute(query, args)
    \n cnx.commit()
    \n except Exception as e:
    \n print(e)
    \n finally:
    \n cursor.close()
    \n cnx.close()
    \n<\/code><\/p>\n

    … and …<\/p>\n


    \ndef update_blob(author_id, filename):
    \n # read file
    \n data = read_file(filename)
    \n # prepare update query and data
    \n query = \"UPDATE images \" \\
    \n \"SET photo = %s \" \\
    \n \"WHERE id = %s\"
    \n args = (data, author_id)
    \n try:
    \n cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, port='8889', \\
    \n unix_socket='\/Applications\/MAMP\/tmp\/mysql\/mysql.sock', database=database_name)
    \n cursor = cnx.cursor()
    \n cursor.execute(query, args)
    \n cnx.commit()
    \n except Exception as e:
    \n print(e)
    \n finally:
    \n cursor.close()
    \n cnx.close()
    \n<\/code><\/p>\n

    … and …<\/p>\n


    \ndef write_file(data, filename):
    \n with open(filename, 'wb') as f:
    \n f.write(data)
    \n
    \ndef read_blob(author_id, filename):
    \n # select photo column of a specific author
    \n query = \"SELECT photo FROM images WHERE id = %s\"
    \n try:
    \n cnx = MySQLConnection(user=db_username, password=db_password, host=db_host, port='8889', \\
    \n unix_socket='\/Applications\/MAMP\/tmp\/mysql\/mysql.sock', database=database_name)
    \n cursor = cnx.cursor()
    \n cursor.execute(query, (author_id,))
    \n photo=cursor.fetchone()[0]
    \n # write blob data into a file
    \n write_file(photo, filename)
    \n except Exception as e:
    \n print(e)
    \n finally:
    \n cursor.close()
    \n cnx.close()
    \n<\/code><\/p>\n

    … for an initial phpMyAdmin database: testdb `images` link click Browse link that showed …<\/p>\n\n\n
    id<\/th>\nphoto<\/th>\n<\/tr>\n<\/table>\n

    … for us, where the folder of readblob.py had existant whyno.jpg and svg_map.jpg but no 02.jpg until …<\/p>\n


    \n$ ls -l whyno.jpg svg_map.jpg 02.jpg
    \nls: 02.jpg: No such file or directory
    \n-rw-r--r--@ 1 user admin 570207 7 Feb 21:18 svg_map.jpg
    \n-rw-r--r--@ 1 user admin 423196 19 Mar 19:39 whyno.jpg
    \n$ python readblob.py
    \n$ ls -l whyno.jpg svg_map.jpg 02.jpg
    \n-rw-r--r-- 1 user admin 570207 22 Apr 13:36 02.jpg
    \n-rw-r--r--@ 1 user admin 570207 7 Feb 21:18 svg_map.jpg
    \n-rw-r--r--@ 1 user admin 423196 19 Mar 19:39 whyno.jpg
    \n$
    \n<\/code><\/p>\n

    … whereby phpMyAdmin database: testdb `images` link click Browse link then showed …<\/p>\n\n\n\n
    id<\/th>\nphoto<\/th>\n<\/tr>\n
    1<\/td>\n[BLOB – 556.8 KiB]<\/td>\n<\/tr>\n<\/table>\n

    … giving you (in addition to a glint in the eye of a Steve McQueen<\/a> up there somewhere) a blueprint for a slytherin<\/a> good time hangin’ out with MAMP<\/a> … chortle, chortle<\/font> … not forgetting good ol’ 02.jpg<\/font> … good times<\/a>.<\/p>\n

    \n

    Previous relevant Python MySql Connector Primer Tutorial<\/a> is shown below.<\/p>\n

    \"Python<\/a>

    Python MySql Connector Primer Tutorial<\/p><\/div>\n

    In the past we’ve teamed Python (server side language) up with the MySql (database) via a python module called MySQLdb when we presented Python MySql Circle and Point DataTable Primer Tutorial<\/a> below. Then, we shared the Mac OS X MAMP local web server’s MySql instance (teamed with Apache and PHP (server side language)) to perform some SQL (of the MySql ilk) regarding the storage of some Circle and Point database tables and data.<\/p>\n

    Moving on today, we are acting as though MAMP was never installed … what blasphemy! …<\/font> because, in all likelihood, that is likely to be the case if Python has been chosen as your primary serverside language, rather than PHP … what gall?! …<\/font><\/p>\n

    So what’s the go with MySql these days? For us, here at Mac OS X macOS 10.12.3 (on a MacBook Pro) …<\/p>\n


    \nmysql Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)
    \n<\/code><\/p>\n

    What port is listening for a non-MAMP MySql installation?<\/p>\n


    \n3306 is default
    \n<\/code><\/p>\n

    What is the latest Python version as of today?<\/p>\n


    \nPython 3.6.5
    \n<\/code><\/p>\n

    … accessed via “python3” concurrent with an older 2.7.10 version accessed via “python”, on Mac OS X (Terminal bash) command line.<\/p>\n

    What links MySql to Python3?<\/p>\n


    \nmysql-connector-python-8.0.11-macos10.13.dmg installs module mysql.connector (or called \"Connector\/Python\" at MySql webpages) via ...
    \npip 10.0.1 from \/Library\/Python\/2.7\/site-packages\/pip (python 2.7) via ...
    \npip install mysql-connector-python-rf
    \n<\/code><\/p>\n

    Any symlinks needed to make “command line” life easier? Yes …<\/p>\n


    \nsudo ln -s \/usr\/local\/mysql\/bin\/mysql \/usr\/local\/bin\/mysql
    \nsudo ln -s \/Library\/Frameworks\/Python.framework\/Versions\/3.6\/lib\/python3.6\/site-packages\/pip \/usr\/local\/bin\/pip
    \n<\/code><\/p>\n

    … so that …<\/p>\n


    \nmysql -uUserName -pPassWord
    \n<\/code><\/p>\n

    … starts a MySql (port 3306 listener) session ready for SQL DDL and DML commands.<\/p>\n

    In summary, for a MacBook Pro initially just having the MAMP MySql (that listens on port 8889) and Python 2.7.10, we …<\/p>\n

      \n
    1. installed (new) MySql instance version 8.0.11<\/li>\n
    2. installed (new) Python version 3.6.5<\/a> accessed via “python3”<\/li>\n
    3. installed pip 10.0.1 (via downloading get-pip.py then “python get-pip.py”)<\/li>\n
    4. installed (Python module) mysql.connector via “pip install mysql-connector-python-rf”<\/li>\n<\/ol>\n

      … environmentally, then (got great help<\/a>) writing Python mysqlpy.py<\/a> to test MySql database via Python connection code.<\/p>\n

      Then we (got more great help<\/a> and) pieced together Python mysqlemployees.py<\/a> to create the DDL SQL necessary to create the tables needed to support the MySql sample Employees database. To populate those tables we used MySql’s Employees Sample Database<\/a> webpage (and associated downloads and other webpages). At the end of today’s PDF slideshow<\/a> presentation, we show some MySql SQL queries of that Employee Sample Database.<\/p>\n

      We’ll leave you with some pertinent online websites we visited creating today’s blog posting …<\/p>\n