Robert Metcalfe Blog

Python MySql Connector Blob Tutorial

No, Nala, we can’t see The Blob 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”

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. 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, 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 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) via the reinstall


sudo pip install mysql-connector


… that you need should you first get the error …


ImportError: No module named mysql.connector


… 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) via …


python readblob.py


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 …

1. started up MAMP
2. clicked its “Open WebStart Page” button (to a Safari web browser webpage)
3. clicked the link to (great, brilliant, marvellous) phpMyAdmin
4. create “testdb” database, as required, via (click of) “SQL” button …
   
create database testdb

   … Go button(click) scenario
5. be in “testdb” database via click of its link down the left hand side
6. create `IMAGES` table via (click of) “SQL” button …
   
CREATE TABLE `IMAGES` (
`id` int(11) NOT NULL,
`photo` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   … Go button (click) scenario

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


from mysql.connector import MySQLConnection, Error,connect

db_username='root'
db_password='root'
database_name='testdb'
db_host='127.0.0.1'

def main():
write_blob(1,"whyno.jpg")
update_blob(1, "svg_map.jpg")
read_blob(1,"02.jpg")

if __name__ == '__main__':
main()


… respectively …


def read_file(filename):
with open(filename, 'rb') as f:
photo = f.read()
return photo

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


… and …


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


… and …


def write_file(data, filename):
with open(filename, 'wb') as f:
f.write(data)

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


… for an initial phpMyAdmin database: testdb `images` link click Browse link that showed …

… for us, where the folder of readblob.py had existant whyno.jpg and svg_map.jpg but no 02.jpg until …


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


… whereby phpMyAdmin database: testdb `images` link click Browse link then showed …

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

You can also see this play out at WordPress 4.1.1′s Python MySql Connector Blob Tutorial.

Previous relevant Python MySql Connector Primer Tutorial is shown below.

Python MySql Connector Primer Tutorial

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 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.

Moving on today, we are acting as though MAMP was never installed … what blasphemy! … 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?! …

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


mysql Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)


What port is listening for a non-MAMP MySql installation?


3306 is default


What is the latest Python version as of today?


Python 3.6.5


… accessed via “python3″ concurrent with an older 2.7.10 version accessed via “python”, on Mac OS X (Terminal bash) command line.

What links MySql to Python3?


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


Any symlinks needed to make “command line” life easier? Yes …


sudo ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
sudo ln -s /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pip /usr/local/bin/pip


… so that …


mysql -uUserName -pPassWord


… starts a MySql (port 3306 listener) session ready for SQL DDL and DML commands.

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

1. installed (new) MySql instance version 8.0.11
2. installed (new) Python version 3.6.5 accessed via “python3″
3. installed pip 10.0.1 (via downloading get-pip.py then “python get-pip.py”)
4. installed (Python module) mysql.connector via “pip install mysql-connector-python-rf”

… environmentally, then (got great help) writing Python mysqlpy.py to test MySql database via Python connection code.

Then we (got more great help and) pieced together Python mysqlemployees.py 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 webpage (and associated downloads and other webpages). At the end of today’s PDF slideshow presentation, we show some MySql SQL queries of that Employee Sample Database.

We’ll leave you with some pertinent online websites we visited creating today’s blog posting …

Previous relevant Python MySql Circle and Point DataTable Primer Tutorial is shown below.

Python MySql Circle and Point DataTable Primer Tutorial

When we’ve talked about the MySql database in the past, apart from going on and on and on and on and on and on about the wonders of the phpMyAdmin MySql management tool frontend, we’ve normally talked about it regarding its “Fred and Ginger” relationship to PHP. Well, Ginger was famous before Fred came along, and who can forget Fred in the Towering Inferno movie, with no Ginger in sight … mind you, can’t remember if there were any scenes with people eating cashew chicken for lunch in that movie?! My point is, and here’s the thing, MySql can be a database for other languages just as other languages can go off looking for other databases, for example, have a peruse of PostgreSQL PHP Tutorial.

Our new teaming today is, if you haven’t guessed it already, Python and MySql. We set this up locally on our Mac OS X MacBook Pro system, and wanted it to live, and not interfere with our beloved MAMP local Apache/PHP/MySql environment, which panned out to be no issue at all, working with those MySql tables featured in Circle and Point jQuery DataTable Primer Tutorial. More, the difficulties came with installing the MySQLdb Python module necessary to make the Python (version 3) code below work …


#!/usr/bin/python
# Python MySql tester
# RJM Programming
# October, 2017
# Thanks to https://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python
import MySQLdb

db = MySQLdb.connect(host="localhost", # your host, usually localhost
user="root", # your username
passwd="root",
unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock",
db="testdb") # name of the data base

# you must create a Cursor object. It will let
# you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("SELECT * FROM CIRCLE,POINT where originid=pointid")

# print some cells of all the rows
print "radius", "x ", "y"
for row in cur.fetchall():
print row[2], row[4], row[5]

db.close()


… also download with mysql_db.py link … via, and resulting in …


$ python < mysql_db.py
radius x y
99.0 221.0 170.0
55.0 222.0 111.0
$


So, that install on Mac OS X, for us, as suggested at this useful link, thanks, involved Mac OS X Terminal application’s command line commands …


sudo brew install mysql-connector-c
sudo pip install MySQL-python


In our stream of consciousness PDF slideshow we show you this installing and the other tricky bit, that being, the Python code’s MySql connection code, and how we crept up on how to approach that. Along the way you can see phpMyAdmin in action, as the tool to show the structure of the MySql database, and to verify that the Python program outputs agree with the MySql database content.

Previous relevant Circle and Point jQuery DataTable Primer Tutorial is shown below.

Circle and Point jQuery DataTable Primer Tutorial

Perhaps you are a reader who has been interested in the “backend” of web application development, traditionally the database side of Information Technology software development. And maybe along the way you were reading when we presented MySql Stored Procedures Primer Tutorial as shown below? Well, today we take its data “thoughts” and funnel that through the brilliant, the stupendous jQuery (Javascript library) DataTable functionality.

For a lot of programmers, we tend to hone in on Data Tables, in a database, as the most latch-able onto-able thing about databases … or maybe that’s just me … anyway, the Data Table is that “thing” in a database consisting of data presented in …

• columns … to do with the “type” (or types) of data … and …
• rows … to do with the “order” of data

… like you are probably familiar with regarding spreadsheets … ie. the letters up the top are like “columns” and the “numbers” on the left hand side are like “rows” with respect to what we’re talking about above. As you can tell from this, a spreadsheet worksheet is a lot like a database Data Table.

Continuing that spreadsheet analogy, you spreadsheet enthusiasts probably have seen the concept of multiple worksheets within the one spreadsheet? Well, that is like our scenario today where we have two Data Tables called …

• Point
• Circle

… and for both tables we’re going to have these numerical “count” indexes, as is so common in RDBMS work, because computers can sort faster and join things faster with numerical data. The trade off, as for us, if you imagine it, is, that there can be Data Redundancy, or “inefficiency” in that as far as the “Point” Data Table goes, we may have the same (x,y) co-ordinate set existing multiple times, and we’ll define a unique “row” (or record) for both of these, yet, you’d have to agree, that this is, perhaps inefficient and wasteful. The upside, though, is it is not making my head hurt, because there is a one to one relationship between a “Point” (Data Table) row and a “Circle” row, making the Javascript logic we perform, and you can see with circle_point_jquery_datatable.html less involved with what we are doing today. And what is that, pray tell?

We’re going to let the brilliance of jQuery display that aforesaid mentioned data in its inimitable style … such panache! And then we are going to add “row” click event functionality to draw the point or circle of that (Data Table) row be drawn up the top using HTML SVG graphics. You can also have “column” click events, but we choose “row” clicks to be interested in, because on a row click, the whole “.innerHTML” of that row’s (HTML tr) element is accessible in the logic, and so …

• should you click on a Point row we draw (x,y) with some co-ordinate text … and …
• should you click on a Circle row we extract the equivalent Point’s (x,y) to be the circle’s SVG (CX,CY) centre point (easily done because of that one to one correspondence we talked about above), and have the radius value in the last “column” cell of the Circle row we are on, enabling everything we need to draw an SVG circle element

Now in all this, we talked about the “one to one correspondence” numerical indexes, but in practice to make this more robust, in case a superfluous “Point” row is deleted by a do-gooder user later down the track, you may have noticed that all along, the second “column” of the Circle (Data Table) (even though called “originid”) points back (ie. is the same value) as the Point table’s first (and index) column (called “pointid”) and these “join” fields (another name for “column” can be “field”) can still gather apples with other proper apples, in that scenario. But today, that is overkill, because there is no “delete” row (or “record”) functionality in our web application today. That web application you can try for yourself with this live run link.

Previous relevant MySql Stored Procedures Primer Tutorial is shown below.

MySql Stored Procedures Primer Tutorial

Today’s tutorial follows up on phpMyAdmin interface to MySql and PHP Primer Tutorial in that we again use the brilliant phpMyAdmin to oversee the results of some PHP code which uses MySql calls to create tables called POINT and CIRCLE used to store information defining a circle, and then it creates three MySql stored procedures in the database to help add circle data with the single MySql statement that goes CALL AddCircle(x, y, radius); via the use of those stored procedures. Let’s see below what Wikipedia says about Stored Procedures generally.

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.[1]

Here is some downloadable PHP programming source code which shows the results of the MySql SQL requests made and can be renamed to ourmysqlstoredprocedure.php as required.

Here is some downloadable supervisory PHP programming source code which gathers the MySql SQL requests made and can be renamed to ourmysql_storedprocedure.php as required.

If you want to develop your own live usage (have provided a live usage link here which will not work but you can use if you are a beginner, to get used to mysql errors, which will occur after you hit either button of the link (these errors indicate bad MySql connection details which are the same reason the database dropdown is not full of options), or you can use this link to see the raw MySql SQL involved in piecing this tutorial together) of these two PHP source codes then you could fix up the hard codings for MySql host/username/password up the top of ourmysql_storeprocedure.php (where you may notice that the default host in the code is localhost:8889 which is the default host string for MySql (ie. port 8889 is used) when using a MAMP (Mac laptop) local web server (which uses localhost:8888 as its local “domain name” for http usage)) or you can keep the same code and use a URL like:

[your-domain-name-plus-a-bit-maybe]/ourmysql_storeprocedure.php?host=[your MySql host address]&username=[your MySql username]&password=[your MySql password]&database=[your optional MySql default database name within the looked up list presented]

Previous phpMyAdmin interface to MySql and PHP Primer Tutorial is relevant and shown below.

phpMyAdmin interface to MySql and PHP Primer Tutorial

Transcript:

You never hear much about the data when you hear about great PHP products, but we
all know it is the data that differentiates the quality of the end result.

That is probably because database products like MySql, SqlServer, Oracle SQL, Advantage and Access
are pretty good at what they do, and emphasise reliability rather than flashiness.

MySql and PHP have a great open source interface with phpMyAdmin, which is so good,
you forget that it is not the default MySql administrator’s interface product.

Let’s have a look at this WordPress database and a bit of how it looks, looking
through the prism of phpMyAdmin …

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.

If this was interesting you may be interested in this too.