3. Mariadb
3.1. Intro
All my data is currently stored in a Maria DB
and therefore it is necessary to use the Mariadb. The format
is simple: a single table holds all the data.
Name
|
Type
|
Description
|
number
|
INTEGER
|
Unique key for the image
|
type
|
VARCHAR(255)
|
The directory under /links/diaadm where the image is placed
|
file
|
VARCHAR(255)
|
The file name for the image
|
year
|
INTEGER
|
The year that the picture is made
|
month
|
INTEGER
|
The month that the picture is made
|
descr
|
VARCHAR(4096)
|
A short description, using many keywords
|
GPS_Latitude
|
VARCHAR(255)
|
Location where the picture is taken, if available
|
GPS_Longitude
|
VARCHAR(255)
|
Location where the picture is taken, if available
|
GPS_Altitude
|
VARCHAR(255)
|
Location where the picture is taken, if available
|
ISO_equiv
|
VARCHAR(255)
|
Copy of the JPEG header, if the data is available
|
Aperture
|
VARCHAR(255)
|
Copy of the JPEG header, if the data is available
|
Exposure_time
|
VARCHAR(255)
|
Copy of the JPEG header, if the data is available
|
Focal_length
|
VARCHAR(255)
|
Copy of the JPEG header, if the data is available
|
All sorts of improvements can be made (a perceptual hash can be created, keywords
in the description can get their own table), but that will be a later concern.
3.2. Sample program
From the Internet, I retrieved the following python script.
#!/usr/bin/python
import mysql.connector as Mariadb
mariadb_connection = mariadb.connect(user='diaadm', password='diaadm', database='diaadm', host='127.0.0.1')
cursor = mariadb_connection.cursor()
#retrieving information
some_name = 'l-j'
cursor.execute("SELECT type,file,descr FROM diaadm WHERE descr RLIKE %s", (some_name,))
for tpe, f, descr in cursor:
print("Type: {}, File: {}, Description: {}").format(tpe,f,descr)
Before I go into the details, I encountered a number of problems. First problem:
Traceback (most recent call last): File "maria.py", line 2, in <module> import mysql.connector as mariadb ImportError: No module named mysql.connector
This says that the module is not installed. So that means that you should install
the modules, using
gslapt
(or whatever your distributions flavor is)
or using
pip install.
Second problem:
Traceback (most recent call last): File "maria.py", line 4, in <module> mariadb_connection = mariadb.connect(user='diaadm', password='diaadm', database='diaadm', host='127.0.0.1') File "/usr/lib64/python2.7/site-packages/mysql/connector/__init__.py", line 179, in connect return MySQLConnection(*args, **kwargs) File "/usr/lib64/python2.7/site-packages/mysql/connector/connection.py", line 95, in __init__ self.connect(**kwargs) File "/usr/lib64/python2.7/site-packages/mysql/connector/abstracts.py", line 719, in connect self._open_connection() File "/usr/lib64/python2.7/site-packages/mysql/connector/connection.py", line 206, in _open_connection self._socket.open_connection() File "/usr/lib64/python2.7/site-packages/mysql/connector/network.py", line 475, in open_connection errno=2003, values=(self.get_address(), _strioerror(err))) mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)
A MySQL client on Unix can connect to the mysqld server in two different ways:
-
By using a Unix socket file,
-
by using TCP/IP, which connects through a port number.
My TCL/Tk script connected to a socket, which is more secure, because you do not have to expose
the TCP-port. Apparently, as a default, Python's module uses a network connection.
Searching through the Internet did not turn up a quick solution to make Python behave
more securely, therefore, I restarted mariadb with
# SKIP="--skip-networking"
commented out in
/etc/rc.d/rc.mysqld.
3.3. Commitment
Contrary to the other languages I use (Perl, TCL) I use frequently, Python
turns autocommit off by default. Also, the mysql CLI starts with autocommit
on. But Python's PEP0249 states:
Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on.
The problem is that if a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
So, you have three choices:
-
Turn on autocommit
-
Explicitly commit your changes
-
Loose your data
Turning on autocommit can be done directly when you connect to a database:
import mysql.connector as mariadb connection = mariadb.connect(user='testdb', password='testdb', database='testdb', host='127.0.0.1',autocommit=True)
or separately:
connection.autocommit=True
Explicitly committing the changes is done with
connection.commit()
Note that the commit is done via the connection to the database, not via the
cursor.