Skip to content

TLRKiliann/timetrack

Folders and files

NameName
Last commit message
Last commit date

Latest commit

ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 

Repository files navigation

TIME-TRACK

GitHub followers

GitHub forks

  • python3 ๐Ÿ

  • tkinter8.6

A medical application (time-track - future version) to connect PyMySQL with MySQL (MariaDB) on raspberry. Below, I explain how to use this application with the MariaDB table in localhost, LAN and WAN connection). I chose a LAN infrastructure for security reasons. It is possible to extend the connection to the internet with a forwarding on the server (described below).

Anurag's GitHub stats


Localhost :

One user: (localhost - 127.0.0.1)

I recommend to create the MySQL database table in localhost (on your own machine). To do this you will have to modify the files of "update/" and "nutrition/" folders, as well as the "accessDB.py" file by replacing :

LAN configuration :
pymysql.connect(host='192.168.XX.XX', port=3306, user='usr_namedb', password='user_passwd', database='timetrackconn')

by

Localhost configuration :
pymysql.connect(host='127.0.0.1', user='usr_namedb', password='user_passwd', database='timetrackconn')
(No port needed in this case).


LAN or WAN :

Multiple users: (LAN or WAN)

You should install MySQL on one server. Otherwise each user will have an independent database if it is installed on localhost. This is obviously not the point, if something changes, the other users will not see what has changed in their database. It is also possible to extend the connection to the Internet (WAN) to have access outside the LAN (forwarding). In order to do this, replace the following lines:

LAN configuration :
pymysql.connect(host='192.168.XX.XX', port=3306, user='usr_namedb', password='user_passwd', database='timetrackconn')

by

WAN configuration :
pymysql.connect(host='publicIP', port=3306, user='usr_namedb', password='user_passwd', database='timetrackconn')


Configuration for LOCALHOST :

MySQL Workbench (config on your local machine ! (NOT DISTANT SERVER))

  1. Create a database :
    MYSQL Connections --> click '+'

  2. Enter connection name and password

  3. Click on 'Test connection'
    Password required

  4. Create a table Utf-8

  5. Create columns

  6. Save model

  7. Return to 'home' and click on your new connection


How to install pymysql on client (out of virtualenv)

You could install it on server too for solving problem.

Install python3-pymysql (out of virutalenv !):

sudo apt install python3-pymysql

Install PyMySQL and MySQL (in virtualenv !)

pip3 install pymysql (or PyMySQL)

sudo apt-get build-dep python-mysqldb

pip install mysql-python

pip install mysql-connector-python


Configuration for LAN :

Install ssh public key :

Private key and public key :

Generate rsa key :

ssh-keygen -t rsa -b 4096

Enter file to save key : rsa_file

Enter passphrase : XXXXXXXX


Copy key on server :

ssh-copy-id -i ~/.ssh/rsa_file.pub serv@192.168.x.x passwd


Connection with server :

ssh -i ~/.ssh/rsa_file serv@192.168.x.x

Enter passphrase: XXXXXX

You don't need to enter password.


Save the key on client for remote server

We place ourselves in the right folder (use ls and cd ;) !

ssh-agent bash

ssh -i ~/.ssh/rsa_file serv@192.168.x.x

Enter your passphrase.


To keep passphrase in memory :

ssh-add (-h = help) (-t = time in memory) (-l = list of keys) (-d rsa_file = delete the key)

ssh-add ~/.ssh/rsa_file

Enter passphrase : (once time) Connection established !!!


We only need to enter this sentence when we want to connect to the remote server :

For raspberry pi 3 :

ssh -i ~/.ssh/rsa_file serv@192.168.x.x

For linux distro :

ssh -i ~/.ssh/rsa_file pi@192.168.x.x

logout


Verification :

cat ~/.ssh/config


In the file there should be the following :

Host targetserver.serv@192.168.x.x
IdentityFile ~/.ssh/rsa_file # private key


How to install MySQL on Raspberry pi 3b (server) :

sudo apt update

sudo apt upgrade

sudo apt install mariadb-server

sudo mysql_secure_installation

Make sure you write down the password you set during this process as we will need to use it to access the MySQL server and create databases and users.


To start mysql :

sudo systemctl start mysql

or

sudo /etc/init.d/mysql start

or

service mysql start


To stop mysql :

sudo systemctl stop mysql

or

sudo /etc/init.d/mysql stop

or

service mysql stop


To reload MySQL : (after changing conf)

sudo systemctl reload mysql

sudo systemctl reload mariadb.service


To restart MySQL : (after changing conf)

sudo systemctl restart mysql


How to create database with MySQL (on server side)

  1. sudo mysql -u root -p

  2. CREATE USER 'user_name'@localhost IDENTIFIED BY 'password';

  3. CREATE DATABASE database_name;

Create a new user (with remote access) and grant privileges to this user on the new database:

  1. GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

  2. flush privileges;

  3. quit; or exit;

  4. sudo /etc/init.d/mysql restart


Now database is accessible for user_name :

  1. sudo mysql -u user_name -p

  2. SHOW DATABASES;

  3. USE timetrackconn;

  4. SHOW TABLES;

  5. MariaDB [timetrackconn]> CREATE TABLE timetrackconn(
        -> stdid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        -> firstname VARCHAR(45) NULL,
        -> surname VARCHAR(45) NULL,
        -> birth VARCHAR(45) NULL,
        -> allergy VARCHAR(45) NULL,
        -> disease VARCHAR(45) NULL,
        -> maindiagnostic VARCHAR(45) NULL,
        -> PRIMARY KEY (stdid)
        -> );
    
  6. sudo systemctl restart mysql

  7. Configure file to /etc/mysql/mariadb.d.conf/50-server.cnf :

sudo nano /etc/mysql/mariadb.d.conf/50-server.cnf


Active/Change the following lines :

Port Address
3306 0.0.0.0

port 3306
bind-address 0.0.0.0

  1. Restart mariadb.service :

sudo systemctl restart mariadb.service

  1. Configure firewall to open port 3306 for clients on LAN (for example) :

sudo ufw allow from 192.168.XX.100 to any port 3306

sudo ufw allow from 192.168.XX.200 to any port 3306

sudo ufw allow from 192.168.XX.300 to any port 3306

sudo ufw allow from 192.168.XX.400 to any port 3306

sudo ufw reload


To test CONFIGURATION from server side :

sudo netstat -anp | grep 3306


To test CONFIGURATION of server from client side :

mysql -u koala33 -h 192.168.XX.XX -p


Use a virtualenv (on client side)

$ virtualenv myvirtualenv

$ source myvirtualenv/bin/activate


How to install pymysql on client (out of virtualenv)

You could install it on server too for solving problem.

Install python3-pymysql (out of virutalenv !):

sudo apt install python3-pymysql


Install PyMySQL and MySQL (in virtualenv !)

$ pip3 install pymysql (or PyMySQL)

sudo apt-get build-dep python-mysqldb

$ pip install mysql-python

$ pip install mysql-connector-python


Compatibility with pymysql and mariadb (on client side - out of virtualenv !)

sudo apt-get install mariadb-client-10.1


UFW : (Firewall)

You can grant access to the remote system with IP 192.168.XX.XX to connect the port 3306 with the following command (on server side) :

  • LAN

sudo ufw allow from 192.168.XX.XX to any port 22

sudo ufw allow from 192.168.XX.XX to any port 3306

sudo ufw reload


  • INTERNET

sudo ufw allow from 192.168.XX.XX to any port 22

sudo ufw allow 3306/tcp

sudo ufw reload


Configuration of UFW :

  • SERVEUR :
To Action From
22 ALLOW IN 192.168.XX.XX
3306 ALLOW IN 192.168.XX.XX

  • CLIENT :

Status: active Logging: on (low) Default: deny (incoming), allow (outgoing), disabled (routed) New profiles: skip


Configuration for WAN

PORT FOWARDING (on server) to access db from internet (WAN)

# echo 1 > /proc/sys/net/ipv4/ip_forward


Launch app with :

$ python3 heal_track.py

or with :

$ python3 heal_track.py '(Application.__init__(self))


To connect to server from client side :

Use ssh to remote access to server and configure all what you wants.

ssh -i ~/.ssh/rsa_file server@192.168.XX.XX


Still under development ! (It should be finished by june 2022)

Enjoy it ! ๐Ÿ˜‰

ko@l@tr33 ๐Ÿจ

Demo (Youtube)

IMAGE ALT TEXT HERE