logging values from arduino to postgres db

Question

I have a temperature sensor ([LM35][1]) interfaced with an Arduino board and my [sketch][2] is able to log values to the serial port, say /dev/ttyACM0 in Ubuntu, and I was able to install pySerial and log the temperature values into a file... I used the command

python -m serial.tools.miniterm /dev/ttyACM0 >> templogger.csv

So it will log values like

27
28
27

into the templogger.csv file.

Instead of logging into the csv file Can I log these values directily to a postresql database say Db1 with username 'abc' and password 'xyz'. Is it possible through python, Can you please help by providing the required script

Answer 1

Start with psycopg2, which implements the Python database API.

You'll probably want to start with simple individual INSERTs, but later on you'll be able to batch work into transactions or use COPY to improve write performance.

In general, it's better to post questions for more specific problems than this. See Stack Overflow Help. If you're stuck on something it's fine to ask for help, but it's preferable that you try to figure it out first. You'll get better results if your questions are more like: "I've tried to use psycopg2 to connect my Python program to PostgreSQL so I can write sensor logging to the database, but when I INSERT a row I get [exact error text here]. Searching for the error message hasn't helped me, so I'm a bit stuck."

In other words, try it, and if you get stuck, ask. I'd normally just close-vote a question like this as "too localized" or "not a real question", which would give you an automatic link to the question writing guide, but you're new here and you've made an effort to write a decent question so I'm trying to explain in a bit more detail.

Answer 2

Instead of logging into the csv file Can I log these values directily to a postresql database say Db1 with username 'abc' and password 'xyz'. Is it possible through python, Can you please help by providing the required script?

Have a good look at a SQLAlchemy tutorial, and try to make a code that can works for you. Basically, you can extrapolate from the example:

from serial import Serial
from sqlalchemy import *

class Temperature(object):
    pass

class TemperatureStore:
    def __init__(self, dburl):
        self.db = create_engine('postgresql://%s' % dburl)
        self.db.echo = True
        metadata = BoundMetaData(db)
        temperatures = Table('temperatures', metadata, autoload=True)
        temperaturemapper = mapper(Temperature, temperatures)
        self.session = create_session()

    def push_temperature(self, temp):
        temp = Temperature()
        temp.value = temp
        session.save(temp)

def main():
    ser = Serial(port='/dev/ttyXXX', baudrate=115200)
    tempstore = TemperatureStore('abc:xyz@localhost:5432/Db1')
    while True:
        tempstore.push_temperature(ser.read())

This is just an example that may or may not work (I did not test it, just wrote it in 5 minutes), so you'd better get some inspiration from that, and try to work out your own!