How to transfer data on Rpi to mySQL in PC via internet

[yoshii]

Hey everyone, I appreciate any help I can get.

I would like to transfer data (with pictures) from my Rpi to a MySQL database in my PC. I'll be using the internet for the "live mirroring" of the data in Rpi to the database (almost same as real time).

My plan is to directly send the data to the database to avoid storing anything on my Rpi since I want to preserve it for faster processing.

Is this possible? If yes, does anyone have any good advice or resources for direct transfer of data from Rpi to a database via internet?

[DougieLawson]

If the two machines are on the same LAN then it's trivial to make a remote connection from the data collector to a MySQL (or better MariaDB) database on another machine.

This sample python program reads a thermocouple on one Raspberry every minute and stores the data on in MariaDB on another

Code: Select all

#!/usr/bin/python
# -*- coding: utf-8 -*-

from time import sleep, strftime
from datetime import datetime
import Adafruit_GPIO.SPI as SPI
import Adafruit_MAX31855.MAX31855 as MAX31855
import mysql.connector

tickdone = False
db = mysql.connector.Connect(host='remoteDBserver.example.co.uk', database='test_thermo', user='test', password='moresecretthanyoucanimagine')
cursor = db.cursor()

SPI_PORT   = 0
SPI_DEVICE = 0
sensor = MAX31855.MAX31855(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE, max_speed_hz=5000000))

def c_to_f(c):
        return c * 9.0 / 5.0 + 32.0

def sensorRead():
    temp = sensor.readTempC()
    internal = sensor.readInternalC()
    isrt = ("insert into max31855 (event_time, tempval, internval) values(current_timestamp,%s,%s);")
    cursor.execute(isrt,(temp,internal,))

def tick():
    global tickdone, hourdone
    time1 = strftime('%S')
    time2 = strftime('%M')
    time3 = strftime('%H')

    if (time1 <> "00"):
        tickdone = False
    if (time1 == "00"):            # every minute
        if (tickdone == False):
            sensorRead()
            tickdone = True

while True:
    sleep(0.3)
    tick()

[yoshii]

If the two machines are on the same LAN then it's trivial to make a remote connection from the data collector to a MySQL (or better MariaDB) database on another machine.

This sample python program reads a thermocouple on one Raspberry every minute and stores the data on in MariaDB on another

Code: Select all

#!/usr/bin/python
# -*- coding: utf-8 -*-

from time import sleep, strftime
from datetime import datetime
import Adafruit_GPIO.SPI as SPI
import Adafruit_MAX31855.MAX31855 as MAX31855
import mysql.connector

tickdone = False
db = mysql.connector.Connect(host='remoteDBserver.example.co.uk', database='test_thermo', user='test', password='moresecretthanyoucanimagine')
cursor = db.cursor()

SPI_PORT   = 0
SPI_DEVICE = 0
sensor = MAX31855.MAX31855(spi=SPI.SpiDev(SPI_PORT, SPI_DEVICE, max_speed_hz=5000000))

def c_to_f(c):
        return c * 9.0 / 5.0 + 32.0

def sensorRead():
    temp = sensor.readTempC()
    internal = sensor.readInternalC()
    isrt = ("insert into max31855 (event_time, tempval, internval) values(current_timestamp,%s,%s);")
    cursor.execute(isrt,(temp,internal,))

def tick():
    global tickdone, hourdone
    time1 = strftime('%S')
    time2 = strftime('%M')
    time3 = strftime('%H')

    if (time1 <> "00"):
        tickdone = False
    if (time1 == "00"):            # every minute
        if (tickdone == False):
            sensorRead()
            tickdone = True

while True:
    sleep(0.3)
    tick()

Thanks for the fast reply, I'll try to look into the code.

However, my Rpi is located in a car so they're not on the same LAN.

[DougieLawson]

In that case you've got to buffer the data somewhere and transfer it when you can or you've got to open some ports on your home network and use a cell phone connection from your car to "call home" and transfer the data.