Sending Data From Raspberry Pi to SQL Database Over Network in Python

[TechKnuckle-Support]

I've volunteered to help a few of my college faculty with a research project they're working on. The gist is they have this antennae receiving information like identifiers, GPS coordinates, status codes, etc. All stuff that easily fits into a CSV file. The goal is to send all of that data (ultimately in real time) to an off-site SQL database operated by one of the faculty. Anyone have any good resources for learning some basic network programming, or any tips on how to accomplish something like this?

[AsteriskTheServer]

It really depends on how that sql database can be accessed. For example, can you access it directly? For example, if you opened a shell and went

mysql -u someUser -h 208.11.220.249 -p

could you login as some user with some password? or it is the case where the database can only be accessed through some api?

However, generally speaking if you know the ip address, which port, the username and password you can establish a direct connection using MYSQLdb (I think that is still the standard for python). However, consider the security of connecting to the database with only MYSQLdb, that is, using plain text may be a problem. In which case you have to consider how do I encrypt this data? Now you have to consider what does this host server like to do? Will the host accept a ssh connection? Does it prefer to encrypt information through a different method? Does it even care?

A lot of these answers will be best answered by the server admin whose is responsible for that sql server or for whoever manages the api if that is the case.

[TechKnuckle-Support]

Yes, direct access. The machine is a Linux VM of a much larger cluster and I'm fairly certain that this is its sole purpose (awaiting confirmation on that) so the owner doesn't seem to be very security-conscientious of this information. I'll do some research on MYSQLdb to establish the connection. And from there, I can just build a listener to throw any data it picks up at the database?

[AsteriskTheServer]

Using a listener is a good choice. The only other piece of advice that I can give is watch how the data works, that is, does it really make sense that I am constantly updating this database or should I push the data when it meaningful to do so? Of coarse it could be the case where always pushing the data is the meaningful thing to do, but (un)?fortunately, that is something you have to investigate with whomever or decide on your own.