database connection tested successfully - tables readed on pgadmin

5 files changed, 31 insertions, 28 deletions

diff --git a/compose.yaml b/compose.yaml
index c5102e4..549379a 100644
--- a/compose.yaml
+++ b/compose.yaml
@@ -23,6 +23,7 @@ services:
     environment:
       MQTT_CLIENT_USER: ${MQTT_CLIENT_USER}
       MQTT_CLIENT_PASSWORD: ${MQTT_CLIENT_PASSWORD}
+      DATABASE_URL: postgresql://root:public@postgres:5432/control_station
     build:
       context: ./mqtt_client/
     networks:
@@ -33,14 +34,18 @@ services:
     image: postgres:13
     container_name: postgres
     networks:
-      - database
+      - mosquitto
+    ports:
+    - "5432:5432"
     environment:
       POSTGRES_PASSWORD: public
       POSTGRES_USER: root
       POSTGRES_DB: control_station
     volumes:
-      - ${PWD}/database/init_scripts:/docker-entrypoint-initdb.d
+      #- ${PWD}/database/init_scripts:/docker-entrypoint-initdb.d
       - db_data:/var/lib/postgresql/data
+
+  
   pgadmin:
     image: dpage/pgadmin4
     container_name: pgadmin
@@ -50,7 +55,7 @@ services:
     ports:
       - "5050:80"
     networks:
-      - database
+      - mosquitto
     depends_on:
       - postgres
 networks:
diff --git a/database/init_scripts/create_table.sql b/database/init_scripts/create_table.sql
deleted file mode 100644
index ae5b3d1..0000000
--- a/database/init_scripts/create_table.sql
+++ /dev/null
@@ -1,21 +0,0 @@
-create table if not exists station (
-   id                integer,
-   name              varchar(100),
-   locationid        int null,
-   installation_date date
-);
-
-create table if not exists climameasures (
-   stationname  varchar(255),
-   measurevalue int null,
-   unit         float null,
-   currenttime  timestamp default current_timestamp
-);
-
-
-create table if not exists climameasures (
-   stationname  varchar(255),
-   measurevalue int null,
-   unit         float null,
-   currenttime  timestamp default current_timestamp
-);
\ No newline at end of file
diff --git a/mqtt_client/database.py b/mqtt_client/database.py
index e69de29..c0691a1 100644
--- a/mqtt_client/database.py
+++ b/mqtt_client/database.py
@@ -0,0 +1,10 @@
+from sqlalchemy import create_engine
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker
+import os
+
+DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://root:public@postgres:5432/control_station")
+
+engine = create_engine(DATABASE_URL)
+SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
+Base = declarative_base()
diff --git a/mqtt_client/main.py b/mqtt_client/main.py
index dad09b5..109ca49 100644
--- a/mqtt_client/main.py
+++ b/mqtt_client/main.py
@@ -2,8 +2,15 @@ import paho.mqtt.client as mqtt
 from paho.mqtt.enums import MQTTProtocolVersion
 import json
 from time import sleep
+from database import engine
+from models import Base
 import os
 
+print("Creating ORM SQL Tables..")
+Base.metadata.create_all(bind=engine)
+print("Tables created successfully.")
+
+
 def on_connect(client, userdata, flags, reason_code, properties):
     print(f"Conectado: {reason_code}")
     # Me inscrevo em todos os tópicos sobre clima
@@ -13,9 +20,9 @@ def on_connect(client, userdata, flags, reason_code, properties):
 def on_message(client, userdata, msg):
     payload = json.loads(msg.payload)
     print(msg.topic)
-    print(f"Value: {payload["value"]}")
-    print(f"Unit: {payload["unit"]}")
-    print(f"Timestamp: {payload["timestamp"]}")
+    print(f"Value: {payload['value']}")
+    print(f"Unit: {payload['unit']}")
+    print(f"Timestamp: {payload['timestamp']}")
 
 try:
     user_name = os.environ["MQTT_CLIENT_USER"]
diff --git a/mqtt_client/requirements.txt b/mqtt_client/requirements.txt
index 96a2716..1521b2d 100644
--- a/mqtt_client/requirements.txt
+++ b/mqtt_client/requirements.txt
@@ -1 +1,3 @@
-paho-mqtt==2.1.0
+sqlalchemy
+psycopg2-binary
+paho-mqtt