Code Example to store sensor data to mysql database

[sigdelsanjog]

This code stores the continuous data received from the accelerometer to MySQL database with the help of mysql-python-connector at the interval of 3seconds(which can be changed as per the requirement)

[ladyada]

@sigdelsanjog thanks - please run black to format the code

[sigdelsanjog]

@FoamyGuy Please review the code, and continue with the merge if the code seems okay to you. Thank You

[FoamyGuy]

@sigdelsanjog We will need to get the formatting and pylint issues taken care of before we can merge it. they are shown on this page: https://github.com/adafruit/Adafruit_CircuitPython_MPU6050/pull/9/checks

There is a learn guide here: https://learn.adafruit.com/improve-your-code-with-pylint that covers the process if you are willing to try tackling that. If you run into any trouble I can try to help you work through them.

I can come back and work through the formatting and pylint issues myself if you are unable to, but it will take me a bit of time to get back to it. Probably next week some time.

[sigdelsanjog]

@FoamyGuy I couldn't resolve the issue mentioned in Adafruit_CircuitPython_MPU6050 Please do the needful when you are back in your time convenience.

[evaherrada]

@sigdelsanjog Run black --target-version=py35 .

[FoamyGuy]

I tried out this code today. I was able to get mariadb (mysql) set up on the Raspberry Pi and run this example but it is throwing this error:

1146 (42S02): Table 'sensor_data.collector' doesn't exist

I think we need to add to this example the initialization of the db so that it is ready to receive data. @sigdelsanjog if you can add this it would be great. If not I can try to do that but I'm not very familiar with mysql and it may take me a while to have get to it.

[sigdelsanjog]

I tried out this code today. I was able to get mariadb (mysql) set up on the Raspberry Pi and run this example but it is throwing this error:

1146 (42S02): Table 'sensor_data.collector' doesn't exist

I think we need to add to this example the initialization of the db so that it is ready to receive data. @sigdelsanjog if you can add this it would be great. If not I can try to do that but I'm not very familiar with mysql and it may take me a while to have get to it.

@FoamyGuy Good to hear that you tried it on your PI. Quick comments here:

1. Did you try executing the code after creating a database "sensor_data", creating a table collector within the database?
2. Did you also modify the host, username, and password to your local configuration? My configuration points to a remote host in my home network (192.168.100.7)

Please try the following query to create the collector table:

-- Database: sensor_data

---

-- -- Table structure for table collector

CREATE TABLE collector ( acc_x float DEFAULT NULL, acc_y float DEFAULT NULL, acc_z float DEFAULT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After you create a database and table collector, try executing the code. Please let me know if any error arises after that.

[kattni]

@sigdelsanjog Thank you for the contribution. Have you had a chance to follow up with siddacious' review? Please let me know what your status is. Thanks!

[sigdelsanjog]

@sigdelsanjog Thank you for the contribution. Have you had a chance to follow up with siddacious' review? Please let me know what your status is. Thanks!

@kattni I wrote this code for connecting with MySQL database. If one creates a MySQL database and updates the database settings the code works fine. I was unable to update the code i.e. changing the database from MySQL to sqlite. This code was an outcome of my project so I thought of contributing to the repo example section.

There are some pylint issues which has caused the build fail. If someone from the repo contributors could update/fix this, it would be great. For those who are looking to connect their Sensors and retrieve data via MySQL database, this code will be helpful to begin with.

In future someone might contribute the code to communicate with sqlite and other databases. Why not add them as well in the example section?

[jposada202020]

Hello. @kattni @sigdelsanjog At this moment what would be the future path with this PR?

1. If my understanding is correct, this could not be included as a standalone example as it is, as @FoamyGuy could not make it work
2. Siddacious request changes that has not been integrated in the PR
3. CI has not passed the test and work needs to be done to correct this. Let me know your thoughts

Thanks

[FoamyGuy]

@kattni I think this could be closed for now. If the original contributor or anyone else wants to submit a more self-contained example like Siddacious described in the future they could open a new PR for that.

[kattni]

Closing.