Send data SOC to database

Projects / Brainstorming
#1

Hello every body and @Malte @Peter

Day after day I understand a little more how it work, but I am still a printer, not an programer, lol :sweat_smile:
i understand we can make custom code and execut from AutoPi.
Custom code work with telegram (@plord make this very useful system. thanks to him)
This I want to do is send SoC to my database. if I can write it correctly, then I can understand and can experience other thing by myself…
This is the code, off course it miss a lot’s of thing
anyone can help me ?
11

#2

orginal post updated

#3

I’m no expert here, but guessing a custom job my_write_soc -

import mysql.connector

# write soc
#
def write_soc()

  soc = get_soc()

  mydb = mysql.connector.connect(
    host="localhost",
    user=" *yourusername* ",
    passwd=" *yourpassword* ",
    database="mydatabase"
  )

  mycursor = mydb.cursor()

  mycursor.execute("INSERT INTO bms (soc) VALUES (%s)", (soc))

  mydb.commit()

# get display state of charge
#
def get_soc():
    args = ['soc']
    kwargs = {
        'mode': '220',
        'pid': '105',
        'header': '7E4',
        'baudrate': 500000,
        'formula': 'bytes_to_int(message.data[34:35])/2.0',
        'protocol': '6',
        'verify': False,
        'force': True,
    }
    return __salt__['obd.query'](*args, **kwargs)['value']

Then in the job configuration, use my_write_soc.write_soc

2 Likes
#4

@plord Thank’s you so much :grinning:
I just have some problem to solve about timestamp is required in my data base, but I think have a solution for that directly in the data base. so I will use the code you send me and test it soon a possible.
I already did test from php code and all it work fine. My iPhone webapp update the data when it pushed, so, only problem was python code from autopi.
So thanks you for python code :wink:

#5

ok, I did test.:sweat_smile:

From php request all ok. I only send soc, and it’s work :nerd_face:

From autopi python code, it’s not working. :joy:
I did jobs and custom code same as you said…

I should search what is wrong, request of soc ? or sending wrong ? :grimacing:

#6

Hard to say … what I wrote above was a guess ( I don’t have mysql running ).

Do you see any error messages when run from the console ? Anything in minion logs ?

#7

@plord
the error is:

Traceback (most recent call last):

File "/usr/lib/python2.7/dist-packages/salt/utils/schedule

.py", line 842, in handle_func

  message=self.functions.missing_fun_string(func))

File "/usr/lib/python2.7/dist-packages/salt/utils/error.py

", line 36, in raise_error

  raise ex(message)

Exception: ‘my_write_soc’ virtual returned False: No mod

ule named

mysql.connector

:grimacing: :sweat_smile:

So I think, it is python 2,7 light, and there is no module installed, right ?
if so, I want install module… but I am not sure

#8

Hi Remy

It sounds like you are just missing the python module that allows you to connect to the mysql database from python.

See this stackoverflow post

You may just need to install the python module

pip install mysql-connector-python

You should be able to do so when ssh-ing to the device, with the above command, or from the web terminal with this command.

cmd.run "pip install mysql-connector-python"

But be aware that the error you see is from the very first line in your script, so other errors may show up now.

Best regards
/Malte

1 Like
#9

thanks you. I will try it tomorrow :smiley:

#10

@Malte
it is good?

47

#11

Hi Remy

It looks like it was already installed, did you run the command twice?
Now try calling your module again, and then seeing the output - i would expect the error you saw last to not show up now, but we will see :slight_smile:

Best regards
/Malte

#12

@Malte
My minionutil_last_logs show me he try to connect to my database, but still in the wrong port. he had automatiqueley 3306 port at my ip. but still Synology in dbmaria 10 is 3307…:grimacing::joy::grimacing::joy::grimacing::cold_face::cold_face:
28 IMG_3031
so this is the screenshot:

1 Like
#13

Ah yeah okay, so it seems that the error with the missing package has been resolved.

So did it work when you changed the port?

#14

I can’t. I don’t know how to do that… I only study by myself lol …:grimacing::joy:
I should search on google again …
I think I have 3 solutions:
1-is change the fault port on autopi of mysqlconnect. (I don’t know how to do :mask:)
2-change the fault port on Synology Maria db10 (I think we can’t :no_mouth:)
3-redirect the 3306 to 3307 on my routeur (I will try but :roll_eyes:)

#15

@Malte
i redirect the port 3306 to 3307 to my routeur
i delete the port 3307 on the custom code
@plord thank’s you so much, you custom code will working I hope !!! I test later :smiley::smiley::smiley:
I just make test like that to force data 15 to send to mysql because I test it in my home, autopi is still now at home for test. but like that he work without a car lol :joy:

12

53

#16

Personally I wouldn’t expose a database directly to the internet. Maybe consider -

[ autopi ] —REST interface—> [ web server php script --SQL–> database ]

2 Likes
#17

yes, I thinking about it too, mysql web access is not good, but still I don’t know how to do by php script yet :sweat_smile:

anyway, i can send the good data with my test script: 12

But not working with this:
58

so I should search why …

maybe like this ?:

59

#18

how did you get the level of charge from the battery, sounds interesting