https://pixabay.com/pl/schemat-bazy-danych-tabele-danych-1895779/

Python i MySQL (SQLAlchemy)

www.mysql.com

CC0 Public Domain (pixabay.com)

W ostatnim wpisie opisałem moje pierwsze kroki z częścią webową projektu Sterownika Domowego. Założenie było takie, aby pójść za ciosem i od razu wejść w temat jeszcze głębiej – miałem plan podpięcia bazy danych do projektu. Choć z samą bazą danych udało mi się połączyć, to niestety w kolejnym kroku poległem na integracji Hibernate z Play Framework.

[info] application - Shutting down connection pool.
[info] application - Creating Pool for datasource 'default'
[info] p.a.d.HikariCPConnectionPool - datasource [default] bound to JNDI as DefaultDS
[info] p.a.d.DefaultDBApi - Database [default] connected at jdbc:mysql://127.0.0.1/homecontroller?characterEncoding=UTF-8&useSSL=true
[info] play.api.Play - Application started (Dev)

Trafił mi się problem z konfiguracją, a konkretniej z plikiem persistence.xml. Przy próbie oznaczenia metody adnotacją @Transactional lub próbie wstrzyknięcia obiektu klasy JPAApi dostaję błąd, pomimo, że ten plik wydaje mi się być poprawny i jest w prawidłowym miejscu w strukturze projektu, tzn. w katalogu conf/META-INF. Operacje typu reload classpath niestety nie pomagają.

Error injecting constructor, javax.persistence.PersistenceException: 
No Persistence provider for EntityManager named defaultPersistenceUnit: 
Provider named org.hibernate.jpa.HibernatePersistenceProvider threw unexpected exception at create EntityManagerFactory:
java.lang.NoClassDefFoundError
(...)

Do tematu na pewno wrócę za jakiś czas, być może przetestuje wykorzystanie innego ORM dla Play – EBean zamiast JPA (Hibernate). Tymczasem temat odłożyłem na później i zająłem się również operacjami na bazie danych, lecz tym razem po stronie Raspberry Pi. System ma działać w ten sposób, że RPI zapisuje swój stan (m. in. odczyt z czujników temperatury i wilgotności, informacje o stanie kontaktronu, czujnika PIR czy czujnika zalania wodą) w bazie danych MySQL, która docelowo będzie postawiona na zewnątrz (chmura?). Na tą chwilę moim środowiskiem testowym jest localhost, nawet nie na RPI, tylko na laptopie – tak mi wygodniej pracować. Zakładam, że będę mógł przenieść gotowe rozwiązanie 1:1 na RPI.

Utworzyłem bazę danych homecontroller oraz użytkownika o takiej samej nazwie, któremu nadałem odpowiednie uprawnienia. Z lenistwa całość „wyklikałem” w MySQL Workbench. Nie zmieniałem standardowych ustawień podczas tworzenia – silnik InnoDB, kodowanie utf8_polish_ci.

Może warto wspomnieć o strukturze i o tym, jakie informacje chcę zapisywać. Na pewno wspomniane wcześniej wartości zmierzone, stany sensorów, informacje o samych sensorach, ich typach, lokalizacji, informacje o pomieszczeniach, itp. Aby zobrazować to o czym piszę, zrobię lekki skok w przód do tego, co zrobiłem na końcu. Wykorzystałem MySQL Workbench do wykonania inżynierii wstecznej, przez co rozumiem utworzenie diagramu EER modelu, który mówi prawie wszystko, jak na tą chwilę wygląda struktura tabel i relacje między nimi. Na tą chwilę, bo na pewno nie jest to ostateczny wygląd bazy danych.

Diagram EER bazy danych Sterownika Domowego
Diagram EER bazy danych Sterownika Domowego

Wracając do prawidłowej kolejności realizacji zadania, utworzyłem najprostszą z tabel (room) – przechowującą informacje o pomieszczeniach.

CREATE TABLE `homecontroller`.`room` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(100) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

Właściwości takie jak np. kodowanie utworzonej tabeli możemy sprawdzić poleceniem:

SHOW TABLE STATUS WHERE name LIKE 'room';

Sprawdzenie poprawności połączenia z bazą danych sprawdzę wykonując po prostu zapytanie typu SELECT (SELECT * FROM room;), w związku z czym wypełniłem wcześniej tabelę przykładowymi danymi.

INSERT INTO `homecontroller`.`rooms`(`name`,`description`)VALUES("room1","Pokój dzienny");
INSERT INTO `homecontroller`.`rooms`(`name`,`description`)VALUES("kitchen","Kuchnia");
INSERT INTO `homecontroller`.`rooms`(`name`,`description`)VALUES("bathroom","Łazienka");
INSERT INTO `homecontroller`.`rooms`(`name`,`description`)VALUES("hall","Przedpokój");

Najczęściej występującym wynikiem w przeglądzie dostępnych modułów Pythona obsługujących MySQL był MySQLdb. Niestety jest on kompatybilny z Pythonem w wersji 2, z której nie miałem zamiaru korzystać. Oczywiście dla Pytona w wersji 3 również są dostępne moduły, a z pomocą przyszedł stackoverflow.com. Zdecydowałem się użyć pymsql, który może być szybszy od oficjalnego sterownika mysql-connector. Oczywiście, przed użyciem pakiet wymaga zainstalowania w systemie.

pip install pymysql

Prosty test połączenia z bazą danych oraz wykonania zapytania typu SELECT wykonałem wykorzystując poniższy skrypt, który wypisuje w terminalu listę pomieszczeń. Wszystko byłoby dobrze, gdyby nie polskie znaki w wynikach. W bazie danych są zapisane poprawnie, jednak na wyjściu pojawiają się krzaki. Wystarczyło dodać do funkcji connect() dodatkowe parametry: charset=’utf8′ oraz use_unicode=True.

#!/usr/bin/python3
import pymysql
import sys
from MySQLConnectionData import MySQLConnectionData
 
 
def read_rooms():
	connection_data = MySQLConnectionData()
	try:
		db = pymysql.connect(host=connection_data.host,
							 user=connection_data.user,
							 password=connection_data.password,
							 database=connection_data.database,
							 charset=connection_data.charset,
							 use_unicode=connection_data.use_unicode)
	except:
		print("Problem z połączeniem z bazą danych")
		sys.exit()
 
	cursor = db.cursor()
 
	try:
		cursor.execute("SELECT * FROM room")
 
		results = cursor.fetchall()
		for row in results:
			print(row)
 
	except:
		print("Nie można przechwycić danych")
 
	db.close()
 
 
if __name__ == "__main__":
	read_rooms()

W zasadzie komentarz jest tu zbędny. Dane połączeniowe przetrzymuję w oddzielnym obiekcie klasy MySQLConnectionData, w której kodzie tu umieszczonym dla uproszczenia wyciąłem część metod z dekoratorem @property. Jeśli już mowa o tym dekoratorze, jest to takie cudo, które daje mi możliwość zachowania pewnej enkapsulacji (nie chcę aby atrybuty klas były używane bezpośrednio), jednocześnie umożliwiając taki sam sposób dostępu jak do publicznego atrybutu.

#!/usr/bin/python3
class MySQLConnectionData(object):
	__host = "localhost"
	__user = "homecontroller"
	__password = "hidden_password"
	__database = "homecontroller"
	__charset = 'utf8'
	__use_unicode = '0'
	__prefix = "mysql+pymysql://"
 
	def get_connection_string(self):
		connection_string = self.__prefix + self.__user + ':' 
                + self.__password + '@' + self.__host + '/' + self.__database + '?charset=' 
                + self.__charset + '&use_unicode=' + self.__use_unicode
		return connection_string
 
	@property
	def host(self):
		return self.__host
        # (...)
	@property
	def use_unicode(self):
		if self.__use_unicode:
			return True
		else:
			return False

Podczas planowania tego projektu nie przewidywałem pisania wielu natywnych zapytań do bay danych, nie tylko po stronie webowej, ale również z poziomu Pythona. Wstępny research wykazał, że mogę użyć czegoś takiego co się nazywa SQLAlchemy Object Relational Mapper. Nie miałem wcześniej wiele praktyki z Pythonem, więc mój wybór akurat tego ORM jest bardziej na zasadzie „pierwszy z brzegu”, a później zobaczymy czy się sprawdzi. Po kolei, jak to wszystko działa? W pierwszej kolejności należy zainstalować odpowiedni pakiet.

pip install sqlalchemy

Utworzyłem skrypt startowy, w którym określiłem jak chcę, aby wyglądała moja baza danych. Sprowadza się to do utworzeni klas z odpowiednimi atrybutami, które będą odpowiadać kolumnom tabel z odpowiednimi właściwościami. Poniżej zamieszczam fragment całego skryptu, całość znajdziecie na GitHub.

#!/usr/bin/python3
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from MySQLConnectionData import MySQLConnectionData
 
Base = declarative_base() #1
 
class Direction(enum.Enum):
	IN = "Wejście"
	OUT = "Wyjście"
 
class Room(Base): #2
    __tablename__ = 'room'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False, unique=True)
    description = Column(String(250))
 
 
class Sensor_type(Base):
    __tablename__ = 'sensor_type'
 
    id = Column(Integer, primary_key=True)
    type_name = Column(String(150), nullable=False, unique=True)
    description = Column(String(250))
    direction = Column(Enum(Direction), nullable=False)
 
 
class Sensor(Base):
    __tablename__ = 'sensor'
 
    id = Column(Integer, primary_key=True)
    description = Column(String(250))
 
    position_x = Column(Integer)
    position_y = Column(Integer)
 
    type_id = Column(Integer, ForeignKey('sensor_type.id', ondelete='CASCADE'), nullable=False)
    sensor_type = relationship(Sensor_type)
 
    room_id = Column(Integer, ForeignKey('room.id', ondelete='CASCADE'), nullable=False)
    room = relationship(Room)
 
 
connection_data = MySQLConnectionData() #3
db = create_engine(connection_data.get_connection_string(), echo=True) #4
 
Base.metadata.create_all(db) #5

W kroku #1 tworzymy deklaratywną klasę bazową, która będzie stanowiła podstawę dla klas modelu danych. Opis tego mechanizmu nadawałby się z pewnością na oddzielny wpis, tutaj dokonam pewnego uproszczenia. Wszystkie klasy modelu, które będą odzwierciedlać strukturę bazy danych, muszą dziedziczyć po klasie Base, która narzuci pewną konwencję ich struktury.

Krok #2 to tworzenie właściwych klas, które posłużą do odwzorowania obiektów na tabele bazy danych. Minimalnym wymaganiem jest, aby każda z nich posiadała atrybut __tablename__ i przynajmniej jedną kolumnę będącą kluczem głównym (Primary Key). W moim projekcie kluczami głównymi będą wartości typu Integer w kolumnie o nazwie id. Pozostałym kolumnom można nadać różne właściwości, np. ograniczenie typu nullable=False (wartość nie może być pusta) czy unique=True (wartość musi być unikalna). Jeśli chodzi o typy danych przeze mnie używane to głównie Integer, String z podaną długością, co odpowiada typowi VARCHAR z MySQL, DateTime. Stosuję również typ Enum dla kolumn, którym chcę nadać wartość z ograniczonego zakresu.

W kroku #3 tworzę instancję klasy MySQLConnectionData przechowującej dane potrzebne do połączenia z bazą danych, które w #4 wykorzystuję do otwarcia połączenia. Na koniec w kroku #5, na podstawie wszystkich klas tworzę właściwe tabele. U mnie wszystko poszło tak jak oczekiwałem, co zweryfikowałem w MySQL Workbench generując diagram EER. Tabele są, czas na wypełnienie ich danymi.

#!/usr/bin/python3
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from db_declarative import Base, Room, Sensor_type, Direction, Sensor #1
from MySQLConnectionData import MySQLConnectionData 
 
# get connection data
connection_data = MySQLConnectionData()
 
# configure sqlalchemy and start session
engine = create_engine(connection_data.get_connection_string(), echo=True) #2
DBSession = sessionmaker(bind=engine) #3
session = DBSession() #4
 
# prepare objects
# SENSOR TYPES
ds18b20 = Sensor_type() #5
ds18b20.type_name = 'DS18B20'
ds18b20.description = 'Czujnik temperatury 1-wire'
ds18b20.direction = Direction.IN
 
triac = Sensor_type()
triac.type_name = 'TRIAC'
triac.description = 'Moduł triaka'
triac.direction = Direction.OUT
 
# add sensor types to session
session.add(ds18b20) #6
session.add(triac)
 
tempSensor = Sensor(description='Czujnik w sypialni przy oknie', sensor_type=ds18b20, 
position_x=20, position_y=30, room=bedroom) #7
session.add(tempSensor)
 
triac_module = Sensor(description='Moduł optotriaka sterujący lampą sufitową', sensor_type=triac, 
position_x=100, position_y=180, room=living_room)
session.add(triac_module)
 
# commit all
session.commit() #8

Cały skrypt uzupełniający bazę danych znajduje się na GitHub, powyżej jest tylko najistotniejszy fragment. Nie jest on zbyt skomplikowany, więc opiszę go w skrócie. W kroku #1 importuję potrzebne moduły, w tym klasy modelu danych. Następnie (#2) tworzę silnik za pomocą create_engine(), któremu przekazuje dane potrzebne do połączenia z bazą. W kroku #3 silnik jest wiązany z sesją (sessionmaker(bind=engine)), a następnie tworzona jest jej instancja (#4).

Po takiej konfiguracji można już tworzyć obiekty danych (#5), które będą zapisane w MySQL. Są to obiekty klas, utworzonych w skrypcie db_declarative.py. Metoda add() (#6) zapisuje do sesji obiekt modelu. Obiekty klasy Sensor_type nie zawierają klucza obcego (Foreign Key), jak ma to miejsce np. w przypadku klasy Sensor, gdzie takim kluczem są kolumny type_id oraz room_id. Sposób dodania do sesji obiektu, który odwołuje się do innej tabeli poprzez klucz obcy ma miejsce w #7. Dla przypomnienia, w deklaracji klasy Sensor znalazła się kolumna wiążąca sensor z typem sensora.

class Sensor(Base):
     # (...)
    type_id = Column(Integer, ForeignKey('sensor_type.id', ondelete='CASCADE'), nullable=False)
    sensor_type = relationship(Sensor_type)

Podczas tworzenia obiektu będącego realizacją klasy Sensor, za pomocą sensor_type=ds18b20łączymy go relacją z obiektem klasy Sensor_type. Analogiczna sytuacja jest w przypadku zależności z obiektem klasy Room (room=bedroom) reprezentującym pomieszczenie, w którym zainstalowany jest sensor.

Finalnie w kroku #8 za pomocą commit() wypycham wszystkie dane zgromadzone w sesji do bazy danych. W tym miejscu tworzone jest połączenie przydzielone z puli połączeń zarządzanej przez silnik, po czym obiekty są zapisywane do tabel.

W logicznym ciągu należałoby opisać jak za pomocą SQLAlchemy pobrać dane z bazy, prawdopodobnie znajdzie się to w jednym z kolejnych postów, gdy w moim projekcie zajdzie konieczność takiego odczytu. Na razie poprzestanę na wykonaniu ręcznego zapytania SELECT w MySQL Workbench w celu weryfikacji działania skryptu.

SELECT * FROM homecontroller.sensor;
SELECT * FROM homecontroller.sensor;

Literatura obowiązkowa:

PyMySQL Documentation Release 0.7.2
Python 3 – MySQL Database Access
Jakie są różnice między MyISAM a InnoDB?
Python – gettery i settery – @property
Object Relational Tutorial

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *