# Weatherstation With MySQL Database and Flask Server

1,912

39

1

For a school project we had to make something that read sensor values, let a Raspberry Pi process the data and store it in a database in order to show it in a browser. I chose to make a weatherstation.

For the documentation on instructables we have to explain it so that even people who know nothing about electronics and programming would be able to recreate it.

Before I start, I want to go over a few things:

• If I mention pin numbers, I'll be talking from top view perspective.
• If I mention picture numbers, the number will be on the picture as a note.
• If you see (number), it refers to the picture with that number from the step you're currently at.
• If, during any installation in the Pi terminal, you'd be asked a "Y" or
"N", always type "y". It's notifying you that additional space will be used for the thing you're doing.

## Step 1: List of Components

Mandatory:

• 1 x Raspberry Pi 3 model B
• 1 x Adafruit assembled Pi+ cobbler & breakout cable
• 1 x LCD display 16x2
• 1 x MCP3008
• 1 x Potentiometer
• 1 x LDR (light sensor)
• 1 x TGS2600 (air quality sensor)
• 1 x HDC1080 (humidity & temperature sensor)
• 1 x 10 kΩ resistor
• 1 x 22 kΩ resistor (or one 20kΩ and one 2kΩ resistor)

Optional:

• 1 x Stripboard
• 1 x Junction box

## Step 2: Electric Circuit

Before I begin, there are a few mistakes on the pictures, but it's right in my explanation:

• On picture 3, the right part of the potentiometer is connected to the 3.3V. It should be connected to the GND.
• On picture 5, the VDD and VREF pins from the MCP3008 should be connected to the 5V, not 3.3V.
• The VCC pins from the TGS2600 should be connected to the 5V.
• The resistor which is connected to the LDR should be connected to the 5V instead of the 3.3V.

For the pin numbers, you can also look at the circuit diagram (0).

First of all, connect the 5V, 3.3V and GND pins to the areas as shown in the picture (1).

The first part to be wired is the LCD display. For this part you'll need the display and the potentiometer. Since I have already used my display before, I soldered it on a piece of stripboard to make setting it up easier (2). I soldered a diode from the 5V pin to the VDD pin, just in case you connect it to the ground and the VSS pin to the 5V, it wouldn't damage the display. I also connected the A(node) and K(athode) to the VDD and VSS pin to make use of the backlight, since my display is pretty dark without it. The backlight is optional, you don't have to use it. The rest of the pins are connected to headers. The same goes for the potentiometer.

Connections display (3):

• VSS = GND
• VDD = 5V
• V0 = Middle pin from potentiometer
• RS = Pi pin 16
• RW = Pi pin 20
• E = Pi pin 21
• D4 = Pi pin 6
• D5 = Pi pin 13
• D6 = Pi pin 19
• D7 = Pi pin 26
• A = 5V
• K = GND

Connecections potentiometer:

• Left pin = 5V
• Middle pin = LCD pin V0
• Right pin = GND

The second part to be wired is the MCP3008. For this part you'll only need the MCP3008. Since I have already used my MCP before, I soldered it on a piece of stripboard to make setting it up easier. I connected the VDD to the VREF pin, since I always use 3.3V or 5V for both pins, and I also connected the AGND pin to the DGND pin, since I also connect them both to the GND. The pins on the MCP are numbered from 1 to 16. The 1 starts at the top left and goes down to 8 at the bottom left. The 9 starts at bottom right and goes to 16 at the top right. Pin 1 to 8, the left part, are channels where you connect your sensors to. Pin 9 to 16, the right part, are pins you need to connect to your Pi, so that they can communicate. See picture (4).

Connections MCP3008 (5):

• CH0 = TGS2600 (air quality sensor)
• CH1 = LDR (light sensor)
• CH2 = Not connected
• CH3 = Not connected
• CH4 = Not connected
• CH5 = Not connected
• CH6 = Not connected
• CH7 = Not connected
• DGND = GND
• CS/SHDN = Pi pin CE0
• DIN = Pi pin MOSI
• DOUT = Pi pin MISO
• CLK = Pi pin SCLK
• AGND = GND
• VREF = 5V
• VDD = 5V

The third part to be wired is the TGS2600. For this you'll need the TGS2600 and the 22 kΩ resistor. Place the sensor facing the Pi with its small tip. Now take the resistor and place it between the top left pin and the bottom left pin (6). Then connect the bottom left part to the CH0 pin from the MCP3008.

Pinout TGS2600:

• 1 = Top left pin
• 2 = Bottom left pin
• 3 = Bottom right pin
• 4 = Top right pin

Connections TGS2600:

• 1 = GND
• 2 = MCP3008 pin CH0
• 3 = 5V
• 4 = 5V

The fourth part to be wired is the LDR. For this you'll need the LDR and the 10 kΩ resistor. Connect the resistor to the 5V and to the LDR. Connect the LDR to the resistor and the GND. Then connect the part where the LDR and resistor are connected to the CH1 pin from the MCP3008 (7).

The fifth and last part to be wired is the HDC1080. For this you'll only need the HDC1080. It's a sensor that communicates with the Pi trough the I2C protocol.

Connections HDC1080 (8):

• GND = GND
• SCL = Pi pin SCL
• SDA = Pi pin SDA
• VCC = 3.3V

Now that it's all wired up, you can plug the power cable into your Pi and see if the LCD display lights up. There should be black bars on your display (9). You can adjust the brightness of the display by turning the potentiometer either to the left or to the right.

## Step 3: Setup Code and Flask

Now that you have wired the weatherstation, it's time to setup the Pi for the code it will run. My Pi runs on python 3 and uses python interpreter version 3.4.

You can download my code here. Now open PyCharm and open the file with my code as a project.

First you'll need to install flask on your Pi so that it can setup a server.

In PyCharm go to file and then to default settings (1). Then go to deployment (2).

If you haven't already made a deployment in general, press the green + and fill in the fields as shown in the picture (2).

The SFTP host should be the IP address of your Pi, the username should be the user as whom you log in on your Pi. You don't need to do anything at "Mappings" or "Excluded Paths".

Then press ok. By doing this, you'll be able to communicate with your Pi through PyCharm.

After the deployment is finished, press (ctrl + shift + a) and a search bar will pop up (3).

Write "project interpreter" there, press enter and a screen will pop up.

Then press on the settings icon, choose "add remote" (4) and another screen wil pop up.

Finally go to deployment configuration, choose your deployment configuration and then click on ok (5).

## Step 4: Setup Database in Terminal

Now that you have flask and the code, you'll have to make a database in PyCharm.

A terminal should show up at the bottom (2). Type the following code there:

• sudo apt-get update && sudo apt-get upgrade

After it's done updating and upgrading, type the following code there:

• sudo apt-get install mysql-server

After the installation is finished, type the following code:

• sudo apt-get install mysql-client

After that's finished, type the following code:

• sudo apt-get install python3-mysql.connector

When installing that has ended, type the following code:

• mysql -uroot -p

Now, to see the status of your database you type the following code:

• status

You'll see that currently there's no database selected and the current user is the root user. You'll need to make another user and then a database. To make an user, type the following codes:

• create user 'user_local'@'localhost' identified by 'user_local';
• grant all privileges on *.* to 'user_local'@'localhost' with grant option;

Now you've made an user with the name 'user_local' whose password is 'user_local' and has all privileges.

Next, you'll have to create a database. To do so, type the following code:

• create database weatherstation_db;

Now, you've created a database. To make sure it's there, type the following code:

• use weatherstation_db;

If you didn't get an error, it has selected the database. Meaning it was successfully created.

Now, log out from your MySQL by typing the following code:

• quit

• mysql -uuser_local -p

After that, type the following codes:

• use weatherstation_db;
• status

It should show you that the database which is currently selected is weatherstation_db and the user who's logged in is user_local.

Now, log out from your MySQL by typing the following code:

• quit

The terminal part is over and it's time to switch over to PyCharm.

## Step 5: Setup Database in PyCharm

In PyCharm, on the right you'll see database, click on that (1).

It should be empty if you haven't already made a database in PyCharm.

Click on the green +, then go to data source and then to MySQL (2). A screen should pop up (3).

Click on the green + there and then on MySQL to make a data source project.

Now fill in the fields as shown in the picture (4).

After that go to SSH/SSL. Check "use ssh tunnel". Fill in proxy host, which is the IP address of your Pi, the port, the proxy user and its password (5). Press "Test connection", go to general and it should say successful (6). Press on "Test connection" there too and it should say successful aswell.

Then go to "Schemas" and select "weatherstation_db" (7).

Now you'll have to make tables. Make 3 tables: measurement, type and weatherstation.

To do so, right click on weatherstation_db and then on "new table".

On the new screen, down below, you'll see "SQL Script". Delete what's already in there and paste the following in that (8):

• CREATE TABLE weatherstation_db.measurement ( ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Value DECIMAL(10,2), ID_type INT ); ALTER TABLE weatherstation_db.measurement COMMENT = 'table for measurements.';

Then press "Execute" and if all goes well, a new table will be made with the fields ID, Value and ID_type.

Now repeat the process for type, but this time with this code:

• CREATE TABLE weatherstation_db.type ( ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Description VARCHAR(45) ); ALTER TABLE weatherstation_db.type COMMENT = 'table for types.';

Then press "Execute" and if all goes well, a new table will be made with the fields ID and Description.

Now for weatherstation, repeat the process again, but use this as code:

• CREATE TABLE weatherstation_db.weatherstation ( ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Time TIME, ID_measurement INT ); ALTER TABLE weatherstation_db.weatherstation COMMENT = 'table for weatherstation.';

Then press "Execute" and if all goes well, a new table will be made with the fields ID, Time and ID_measurement.

For the last part, open the mysql console and paste the following code (9):

• TRUNCATE TABLE type; #delete all data from that table INSERT INTO type (Description) VALUES ('Air quality'); #add data to table INSERT INTO type (Description) VALUES ('Humidity'); #add data to table INSERT INTO type (Description) VALUES ('Light'); #add data to table INSERT INTO type (Description) VALUES ('Temperature'); #add data to table

Finally, press on the green triangle at the left to run the sql code.

To check your tables and its content, you can double click one the table in the list with tables (10).

Now you've setup the database in PyCharm and can start running the weatherstation scripts.

## Step 6: Run Code

Now that everything is set up, it's time to run the code.

First run the weatherstation script by pressing the green triangle at the top right.

You pi should start measuring values and show them on your LCD display (1).

Now, you run the flask_weatherstation script. To run it you'll have to do it in the terminal of your Pi.

On your Pi, navigate to the path where the project is located. Once you're in the file, type this:

This will run the file as a root user. The sudo needs to be in front, otherwise you'll get an error.

Now, open your browser. In the URL, type the IP address of your Pi followed by ":86".

Navigate trough the pages to look at a graph displaying the measurements from the last hour uptill now from the specified sensor.

If your time is wrong on the graph, in PyCharm go to "time_local_time.py" in the "Classes" directory.

Change the numbers in the __init__(self, time_zone=1, season_hour=1) to make the time right (3).

Now, you need to set up the Pi so it runs the scripts automatically after it has finished booting up.

For this go to your Pi terminal again and type the following code:

• sudo nano /etc/rc.local

This will open the rc.local file. Go down untill you see "exit 0".

Just above that part, it's important it's above that, paste this code:

• #start script after initialisation
• sudo python3 /home/pi/Python_Oefeningen/project_weatherstation/weatherstation.py &

You'll have to change the path a bit. The path written there is my path to the project and the two scripts it should run. The '&' at the end has to be there aswell so that the Pi will run both scripts in the background and you'll still be able to do other things on your Pi.

After that press (ctr + x), then "y" to save the changes in that file and exit.

Now just plug out the power cable from your Pi, wait for 5 seconds and plug the power cable back in.

It should start running both scripts. If it doesn't, what sometimes happens to me, just connect the Pi with an ethernet cable to your PC and it'll start the scripts.

## Step 7: Solder Prototype

For the final part, you can solder the the components on a stripboard. This is optional, so you don't have to do this.

After soldering, check if all connections and solderings are correct. One small mistake and you'll be spending hours looking where you made a short circuit. Your Pi will probably be damaged aswell.

If all connections and solderings are good, plug in the power cable. The script should start running after booting up the Pi and you'll see the fruits of your labour on your display!

Finally you can put your project in a case, so that you can put it outside, and you're finished making the weatherstation!

## Recommendations

• ### Beginner 3D Printing Class

43,260 Enrolled

## Discussions

Good tutorial, thanks for sharing :)