could not connect to the database server

[steppige]

Hello! congratulations for your project, I want to use it to measure the water level present in my tank in my company. I tried to install everything as you wrote, but unfortunately I get this error message .. I think the problem is the no connection between MySql and your Python script. Do you have any idea? I noticed that you entered the password "xyz", but I don't know where to enter it in MyPhpAdmin ..

Thanks for your help!

This is my error:

[lls-hcr]

Hello, I have installed everything, I can see the dashboard. But I don't think the sql DB is receiving data. If I launch the sensor.py script, I get the following output:

pi@raspberry:~ $ python3 sensor.py
sensor.py:17: RuntimeWarning: This channel is already in use, continuing anyway.  Use GPIO.setwarnings(False) to disable warnings.
  GPIO.setup(TRIG,GPIO.OUT)
sensor.py:21: RuntimeWarning: This channel is already in use, continuing anyway.  Use GPIO.setwarnings(False) to disable warnings.
  GPIO.setup(ALARM,GPIO.OUT)
Waiting For Sensor To Settle

But nothing happens and don't see any data. I also have set a cron job.

What did I miss?

Regards,

lls

[jiteshsaini]

In sensor.py replace this:* from urllib.request import urlopen* with this :* from urllib import urlopen* just go in steps first try to send some dummy data to server. See if that in getting stored in database. For that you can replace distance=get_distance() with distance = 32 and see if the value 32 is getting communicated to the server and stored in the database. Ensure that you have created the database named 'level_log' and imported the file 'water_level.sql' in it. The credentials in file 'db_params_wt.php' should be correct other wise you will keep getting that error message Once that is done, try to get the value from the ultrasonic sensor by checking the hardware connections. then, replace the dummy data with the sensor value

…

On Sun, Aug 1, 2021 at 11:49 PM Lorenzo ***@***.***> wrote: Hello, I have installed everything, I can see the dashboard. But I don't think the sql DB is receiving data. If I launch the sensor.py script, I get the following output: ***@***.***:~ $ python3 sensor.py sensor.py:17: RuntimeWarning: This channel is already in use, continuing anyway. Use GPIO.setwarnings(False) to disable warnings. GPIO.setup(TRIG,GPIO.OUT) sensor.py:21: RuntimeWarning: This channel is already in use, continuing anyway. Use GPIO.setwarnings(False) to disable warnings. GPIO.setup(ALARM,GPIO.OUT) Waiting For Sensor To Settle But nothing happens and don't see any data. I also have set a cron job. What did I miss? Regards, lls — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AK7U6FTDTDCCX4JCIAB4XADT2WF2DANCNFSM47XIU2DA> .

[steppige]

Hello everybody! now it works, I followed @jiteshsaini post!
Thanks now it works Congratulations on your idea!

[lls-hcr]

Dear Jitesh,

thank you for the advice. I am still having some trouble.

1. I have replaced from urllib.request import urlopen with from urllib import urlopen
2. I have replaced distance=get_distance() with distance = 32
3. I have adjusted the credentials in db_params_wt.php (user and password)
4. I have created water_level.sql DB with level_log that I can see in phpmyadmin

When I run the script, I get the following error:

sensor.py:17: RuntimeWarning: This channel is already in use, continuing anyway.  Use GPIO.setwarnings(False) to disable warnings.
  GPIO.setup(TRIG,GPIO.OUT)
sensor.py:21: RuntimeWarning: This channel is already in use, continuing anyway.  Use GPIO.setwarnings(False) to disable warnings.
  GPIO.setup(ALARM,GPIO.OUT)
Waiting For Sensor To Settle
('distance: ', 32)
Traceback (most recent call last):
  File "sensor.py", line 97, in <module>
    sendData_to_remoteServer(url_remote,distance)
  File "sensor.py", line 72, in sendData_to_remoteServer
    urlopen(url_full)
  File "/usr/lib/python2.7/urllib.py", line 87, in urlopen
    return opener.open(url)
  File "/usr/lib/python2.7/urllib.py", line 216, in open
    return getattr(self, name)(url)
  File "/usr/lib/python2.7/urllib.py", line 353, in open_http
    h.endheaders(data)
  File "/usr/lib/python2.7/httplib.py", line 1054, in endheaders
    self._send_output(message_body)
  File "/usr/lib/python2.7/httplib.py", line 892, in _send_output
    self.send(msg)
  File "/usr/lib/python2.7/httplib.py", line 854, in send
    self.connect()
  File "/usr/lib/python2.7/httplib.py", line 831, in connect
    self.timeout, self.source_address)
  File "/usr/lib/python2.7/socket.py", line 575, in create_connection
    raise err
IOError: [Errno socket error] [Errno 111] Connection refused

Please note that I am using a different sensor, but connected to the same GPIO pin (5 and 6)

Here is the material I am using:

The ultrasonic sensor
The RPi board where I connect the sensor
A Raspberry Pi 4B

The sensor was tested successfully with another python script with connection to UART. But I would like to use it with your script to use the cool dashboard that you have shared.

What should I do?

[lls-hcr]

Oh, I understand my mistake now. I was just not using the wright IP in the URL address.

Great!

[lls-hcr]

So, it worked with distance = 32 However, if I revert back the settings to original state as: from urllib.request import urlopen and distance=get_distance(), than I still have problems.

With the command python3 sensor.py I get: Waiting For Sensor To Settle, and nothing happens.

And the same result with python2 sensor.py and from urllib import urlopen

I think I am almost there, how I can I resolve this?

** EDIT **

OK, now I understand. I changed the TRIG/ECHO numbers in the sensor.py script and now it is working.

Great project and fun to implement.

Thank you.

[lls-hcr]

I apologise for multiple posting.

Is there something I should calibrate in the py or PHP scripts? The values I get does not correspond to the reality.

Also, could you please explain the output of the command python3 sensor.py ? Why all the different values and the last one sent to the DB?

1 distance:  16.073
2 distance:  3279.007
3 distance:  3296.842
4 distance:  1.795
5 distance:  3256.42
6 distance:  3274.26
7 distance:  1.775
8 distance:  3257.618
9 distance:  3275.441
10 distance:  1.783
11 distance:  3256.71
12 distance:  3274.542
13 distance:  1.795
14 distance:  3255.941
15 distance:  3273.757
16 distance:  1.77
17 distance:  3256.031
18 distance:  3273.855
19 distance:  1.783
x:  20
k:  12
distance:  3.347

Thank you.

[jiteshsaini]

I apologise for multiple posting.

Is there something I should calibrate in the py or PHP scripts? The values I get does not correspond to the reality.

Also, could you please explain the output of the command python3 sensor.py ? Why all the different values and the last one sent to the DB?

1 distance:  16.073
2 distance:  3279.007
3 distance:  3296.842
4 distance:  1.795
5 distance:  3256.42
6 distance:  3274.26
7 distance:  1.775
8 distance:  3257.618
9 distance:  3275.441
10 distance:  1.783
11 distance:  3256.71
12 distance:  3274.542
13 distance:  1.795
14 distance:  3255.941
15 distance:  3273.757
16 distance:  1.77
17 distance:  3256.031
18 distance:  3273.855
19 distance:  1.783
x:  20
k:  12
distance:  3.347

Thank you.

Instead of taking 1 reading, the code takes 20 readings at a gap of 100 ms. Then calculates the average of these 20 readings. If any of these readings are above the max height of the tank, which in my case is 125, then it rejects those values as they certainly are erroneous values.
In your case, after rejecting the erroneous values, average of balance values gives the distance
(0+16.073 + 1.795 + 1.775 + 1.783 + 1.795 + 1.77 + 1.783)/8 = 3.347

This clearly indicates that either the sensor is not interfaced correctly or it is faulty. Change the sensor and try. No need to calibrate any py or php script

[lls-hcr]

Mmmh, I am strugling with the python code. I have a sensor which is different than the one you have used. I connect it to a board connected to a RPi to UART. I like it because it is waterproof and it does not require the 1k/2k resistors. That would be perfect for my project. It works well with a simple script I have, but gives totaly strange values with the script that you have provided. What would it take to adjust my script to send the data to the DB? Is this something you could help me with? Does the script below makes sense to you?

mysensor.py

# -*- coding:utf-8 -*-

'''
  # demo_get_distance.py
  #
  # Connect board with raspberryPi.
  # Run this demo.
  #
  # Connect A02 to UART
  # get the distance value
  #
  # Copyright   [DFRobot](https://www.dfrobot.com), 2016
  # Copyright   GNU Lesser General Public License
  #
  # version  V1.0
  # date  2019-8-31
'''

import time

from DFRobot_RaspberryPi_A02YYUW import DFRobot_A02_Distance as Board

board = Board()

def print_distance(dis):
  if board.last_operate_status == board.STA_OK:
    print("Distance %d mm" %dis)
  elif board.last_operate_status == board.STA_ERR_CHECKSUM:
    print("ERROR")
  elif board.last_operate_status == board.STA_ERR_SERIAL:
    print("Serial open failed!")
  elif board.last_operate_status == board.STA_ERR_CHECK_OUT_LIMIT:
    print("Above the upper limit: %d" %dis)
  elif board.last_operate_status == board.STA_ERR_CHECK_LOW_LIMIT:
    print("Below the lower limit: %d" %dis)
  elif board.last_operate_status == board.STA_ERR_DATA:
    print("No data!")

if __name__ == "__main__":
  dis_min = 0   #Minimum ranging threshold: 0mm
  dis_max = 4500 #Highest ranging threshold: 4500mm
  board.set_dis_range(dis_min, dis_max)
  while True:
    distance = board.getDistance()
    print_distance(distance)
    time.sleep(5) #Delay time < 0.6s

DFRobot_RaspberryPi_A02YYUW.py

# -*- coding:utf-8 -*-

'''
 MIT License
 Copyright (C) <2019> <@DFRobot Frank>
　Permission is hereby granted, free of charge, to any person obtaining a copy of this
　software and associated documentation files (the "Software"), to deal in the Software
　without restriction, including without limitation the rights to use, copy, modify,
　merge, publish, distribute, sublicense, and/or sell copies of the Software, and to
　permit persons to whom the Software is furnished to do so.
　The above copyright notice and this permission notice shall be included in ALL copies or
　substantial portions of the Software.
 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
 INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
 PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE
 FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
 ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
'''

import serial

import time

class DFRobot_A02_Distance:

  ''' Board status '''
  STA_OK = 0x00
  STA_ERR_CHECKSUM = 0x01
  STA_ERR_SERIAL = 0x02
  STA_ERR_CHECK_OUT_LIMIT = 0x03
  STA_ERR_CHECK_LOW_LIMIT = 0x04
  STA_ERR_DATA = 0x05

  ''' last operate status, users can use this variable to determine the result of a function call. '''
  last_operate_status = STA_OK

  ''' variable '''
  distance = 0

  '''Maximum range'''
  distance_max = 4500
  distance_min = 0
  range_max = 4500

  def __init__(self):
    self.ser = serial.Serial("/dev/ttyAMA0", 9600)
    if self.ser.isOpen() != True:
      self.last_operate_status = self.STA_ERR_SERIAL

  def check_sum(self, l):
    return (l[0] + l[1] + l[2])&0x00ff

  def set_dis_range(self, min, max):
    self.distance_max = max
    self.distance_min = min

  def measure(self):
    data = []
    i = 0
    while self.ser.inWaiting() == 0:
      i += 1
      time.sleep(0.05)
      if i > 4:
        break
    i = 0
    while self.ser.inWaiting() > 0:
      data.append(ord(self.ser.read()))
      i += 1
      if data[0] != 0xff:
        i = 0
        data = []
      if i == 4:
        break
    self.ser.read(self.ser.inWaiting())
    if i == 4:
      sum = self.check_sum(data)
      if sum != data[3]:
        self.last_operate_status = self.STA_ERR_CHECKSUM
      else:
        self.distance = data[1]*256 + data[2]
        self.last_operate_status = self.STA_OK
      if self.distance > self.distance_max:
        self.last_operate_status = self.STA_ERR_CHECK_OUT_LIMIT
        self.distance = self.distance_max
      elif self.distance < self.distance_min:
        self.last_operate_status = self.STA_ERR_CHECK_LOW_LIMIT
        self.distance = self.distance_min
    else:
      self.last_operate_status = self.STA_ERR_DATA

  def getDistance(self):
    self.measure()
    return self.distance