champ1
Posts: 38
Joined: Tue Jul 17, 2018 12:36 am

Mysql table creation to store sensor data

Mon Sep 24, 2018 2:19 pm

Hello!

I am trying to create table to store sensor data into mysql database

Code: Select all

# Simple example of reading the MCP3008 analog input channels and printing
# them all out.
# Author: Tony DiCola
# License: Public Domain
import time
import datetime

# Import SPI library (for hardware SPI) and MCP3008 library.
import Adafruit_GPIO.SPI as SPI
import Adafruit_MCP3008


# Software SPI configuration:
CLK  = 11
MISO = 9
MOSI = 10
CS   = 8
mcp = Adafruit_MCP3008.MCP3008(clk=CLK, cs=CS, miso=MISO, mosi=MOSI)
print(datetime.datetime.now())
# Hardware SPI configuration:
# SPI_PORT   = 0
# SPI_DEVICE = 0
# mcp = Adafruit_MCP3008.MCP3008(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE))


print('Reading MCP3008 values, press Ctrl-C to quit...')
# Print nice channel column headers.
print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*range(8)))
print('-' * 57)
# Main program loop.
while True:
    # Read all the ADC channel values in a list.
    values = [0]*8
    for i in range(8):
        # The read_adc function will get the value of the specified channel (0-7).
        values[i] = mcp.read_adc(i)
    # Print the ADC values.
    print('| {0:>4} | {1:>4} | {2:>4} | {3:>4} | {4:>4} | {5:>4} | {6:>4} | {7:>4} |'.format(*values))
    # Pause for half a second.
    time.sleep(1)
mysql table.jpg
mysql table.jpg (66.1 KiB) Viewed 572 times
I have created eight column s1 s2 s3 s4 s5 s6 s7 s8

Which is correct option to read the sensor data PK NN UQ B UN ZF AI G ?

oldjake
Posts: 69
Joined: Fri Aug 03, 2018 12:39 pm

Re: Mysql table creation to store sensor data

Mon Sep 24, 2018 2:37 pm

Possibly none of them. Without seeing the rest of your table, it's tricky to tell for sure. I'd try with none and see what happens. You're in a closed environment where you know the scope of the date that's going to be read so you don't have to be quite so disciplined as if there was user generated data in there.

User avatar
DougieLawson
Posts: 34166
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Mysql table creation to store sensor data

Mon Sep 24, 2018 6:11 pm

You need to know what your data source looks like before you can start the design of your SQL schema.

Giving columns banal names like "S1" and "S2" may appear easy but having a column for, for example, a temperature reading called "S3" versus one called "ambient_temp" isn't going to make your SQL programs easier to write or debug. You should always aim for defensive programming, think about trying to debug things at 02:57 on a Sunday morning. That helps you to focus on the KISS principle.

The other thing is it's easy to stuff everything in a collection of VARCHAR(45) columns, but if you have floating point data, store it as a FLOAT or DEC(x,y). If you have a date/time store it as a DATE, TIME or TIMESTAMP. If it really is string data it can go in a VARCHAR. That way SQL can do more for you, it can build better indexes, it can get a better access path. You can use things like date/time arithmetic functions on a TIMESTAMP column that won't work on a VARCHAR(45) with an additional CAST() function.

If you have unique data use that uniqueness for your key columns, wrap an index round a key column and your SQL is less prone to running the evil cartesian join and a massive sort to give you the result you want.

With SQL most of the design is "what question am I trying to answer" from this data set.

So take a step back, analyse your data source then try again.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

W. H. Heydt
Posts: 9216
Joined: Fri Mar 09, 2012 7:36 pm
Location: Vallejo, CA (US)

Re: Mysql table creation to store sensor data

Mon Sep 24, 2018 7:18 pm

DougieLawson wrote:
Mon Sep 24, 2018 6:11 pm
You need to know what your data source looks like before you can start the design of your SQL schema.
.
.
.
With SQL most of the design is "what question am I trying to answer" from this data set..

So take a step back, analyse your data source then try again.
All very good but with one terminology caveat... Schemas aren't written in SQL (Structured Query Language) but in DDL (Data Definition Language).

User avatar
DougieLawson
Posts: 34166
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Mysql table creation to store sensor data

Mon Sep 24, 2018 9:05 pm

W. H. Heydt wrote:
Mon Sep 24, 2018 7:18 pm
All very good but with one terminology caveat... Schemas aren't written in SQL (Structured Query Language) but in DDL (Data Definition Language).
Pedant. I was trying to keep it simple for the OP's benefit. Lots of folks see SQL and DDL as a single somewhat ugly amalgamation.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

champ1
Posts: 38
Joined: Tue Jul 17, 2018 12:36 am

Re: Mysql table creation to store sensor data

Mon Sep 24, 2018 11:38 pm

DougieLawson wrote:
Mon Sep 24, 2018 6:11 pm

So take a step back, analyse your data source then try again.
Big thanks

I think table should be look like in below

Code: Select all

+-------+-------------+------+-----+---------+-------+
| Field  | Type     | Not Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Temp1  | float      | YES         |     | NULL    |       |
| Temp2  | float(10,2) | YES  |     | NULL    |       |
| Temp3  | float(10,2) | YES  |     | NULL    |       |
| Temp4  | float(10,2  | YES  |     | NULL    |       |
| Temp5  | float(10,2) | YES  |     | NULL    |       |
| Temp6  | float(10,5) | YES  |     | NULL    |       |
| Temp7  | float(10,2  | YES  |     | NULL    |       |
| Temp8  | float(10,2) | YES  |     | NULL    |       |
| Time     | timestamp  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

W. H. Heydt
Posts: 9216
Joined: Fri Mar 09, 2012 7:36 pm
Location: Vallejo, CA (US)

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 3:16 am

I'm a bit confused by how you've set up that table. You're telling the database engine that the columns are not allowed to have NULL values, but you are also telling it to default the values to NULL. Hadn't you better pick something else for the default (or else allow NULL values)?

champ1
Posts: 38
Joined: Tue Jul 17, 2018 12:36 am

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 4:16 am

W. H. Heydt wrote:
Tue Sep 25, 2018 3:16 am
I'm a bit confused by how you've set up that table. You're telling the database engine that the columns are not allowed to have NULL values, but you are also telling it to default the values to NULL. Hadn't you better pick something else for the default (or else allow NULL values)?
Okay that means I shouldn't select null option

mysql> describe temprature_table;

Code: Select all

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Temp1    | float       |    |     | NULL    |       |
| Temp2    | float       |    |     | NULL    |       |
| Temp3    | float       |    |     | NULL    |       |
| Temp4    | float       |    |     | NULL    |       |
| Temp5    | float       |    |     | NULL    |       |
| Temp6    | float       |    |     | NULL    |       |
| Temp7    | float       |    |     | NULL    |       |
| Temp8    | float       |    |     | NULL    |       |
| TIMEDATE | varchar(45) |    |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

markkuk
Posts: 61
Joined: Thu Mar 22, 2018 1:02 pm

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 6:55 am

The type of your TIMEDATE column should be TIMESTAMP and its default value should be CURRENT_TIMESTAMP. Assuming you are intending to use this as the primary key, it should have the NOT NULL and UNIQUE attributes as well.

User avatar
DougieLawson
Posts: 34166
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 7:31 am

A better design might be

Code: Select all

CREATE sensor_table (sensor_name varchar(45), temperature dec(10,2), recorded_at timestamp);
Sensor_name is unique not null, recorded_at is unique, not null, current_timestamp and temperature is not null. The benefit of that is that it's extensible, you don't have to redesign the table for a new sensor.

You can get all readings from all sensors by

Code: Select all

select sensor_name, temperature, recorded_at from sensor_table where recorded_at between datetime('now','-12 minutes') and datetime('now');
Note: that's SQLite3 syntax.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

champ1
Posts: 38
Joined: Tue Jul 17, 2018 12:36 am

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 11:13 am

markkuk wrote:
Tue Sep 25, 2018 6:55 am
The type of your TIMEDATE column should be TIMESTAMP and its default value should be CURRENT_TIMESTAMP. Assuming you are intending to use this as the primary key, it should have the NOT NULL and UNIQUE attributes as well.
There is no option like CURRENT_TIMESTAMP. It bydefault set to varchar(45)

champ1
Posts: 38
Joined: Tue Jul 17, 2018 12:36 am

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 11:21 am

DougieLawson wrote:
Tue Sep 25, 2018 7:31 am
A better design might be

Code: Select all

CREATE sensor_table (sensor_name varchar(45), temperature dec(10,2), recorded_at timestamp);
Sensor_name is unique not null, recorded_at is unique, not null, current_timestamp and temperature is not null. The benefit of that is that it's extensible, you don't have to redesign the table for a new sensor.
I am confused with second option Should i tick or not in not null option

This table should be show the temprature of eight device over time

Code: Select all

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Device1_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device2_Temp     | float (10,2)      | YES  |     | NULL    |       |
| Device3_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device4_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device5_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device6_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device7_Temp     | float(10,2)       | YES  |     | NULL    |       |
| Device8_Temp     | float(10,2)        | YES  |     | NULL    |       |
| TIMESTAMP      | varchar(45)        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

User avatar
DougieLawson
Posts: 34166
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 4:47 pm

Have one column for a reading and eight rows (one for each sensor). It's easier.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

markkuk
Posts: 61
Joined: Thu Mar 22, 2018 1:02 pm

Re: Mysql table creation to store sensor data

Tue Sep 25, 2018 7:19 pm

champ1 wrote:
Tue Sep 25, 2018 11:13 am
There is no option like CURRENT_TIMESTAMP.
Did you follow the links in my previous post? CURRENT_TIMESTAMP definitely exists in MySQL. Maybe the problem is with whatever GUI tool you are using.
champ1 wrote:
Tue Sep 25, 2018 11:13 am
It bydefault set to varchar(45)
varchar(45) is a data type (which you should replace with TIMESTAMP), CURRENT_TIMESTAMP is what you should set as the default value for the timestamp field.

Return to “Beginners”