Log to MySQL

[RichPyke]

Hi,

I'm very new to the world of EventGhost and Python and trying to learn (with some success but not as much as I'd like).

I have a few sensors which are picked up by EG, for now I'll just specify the one in the hopes I can add the rest later, the same way.

The sensor logs the current drawn from my incoming supply, this is transmitted to the PC wirelessly with the RXFCOM RXFtrx and the message read. Currently I use FileOperations Write to log this value to a simple text file on the PC.

I also have a MySQL database set up on a separate machine. This holds the XBMC database, database for my wifi heating, and a few other things too.

What I want to do is log the value and a datestamp in the mysql db. So, sensor gets reading, sends to PC, EG picks it up, python script reads the message and takes out the bit it wants (all this currently happens), then takes the bit it wants and the current time/date and adds it to the MySQL table.

Is there a simple way to add the value to the database every time it is received, much like the fileoperations write? If not, is anyone going to be kind enough to offer a guide on how to get a python script to log it to the db?

I'm not asking for someone else to do this for me, i'm just asking for a nudge in the right direction. I've been searching for the past couple of hours but have yet to come across anything that makes sense.
Any help would be greatly appreciated.

[krambriw]

I think you will find something interesting here:
viewtopic.php?f=2&t=505&hilit=mysql

The search function will give you even more results related to MySQL

[RichPyke]

Thanks, although that didn't help neither did searching on here...

But I found it elsewhere so will share here for future reference.

Download Python 2.6 (http://www.python.org/download/releases/2.6/)
Download MySQL-Python for yor version of MySQL (http://www.codegood.com/)

Install Python 2.6
Install MySQL-Python
Copy or move all files from python26/Lib/site-packages to the EventGhost/lib26/site-packages.

MySQL-Python wont install without python installed, so we have to install it, even if only temporary. Once done you can uninstall python26 if you wish.

That's it, MySQL for eg installed. Now write your python scripts with MySQL support.

Test it works with;

Code: Select all

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()

print "Database version : %s " % data

# disconnect from server
db.close()

[krambriw]

Fantastic, very good!

BestR Walter

[eatmeimadanish]

You don't have to install python 2.6. You can just put the MySQL plugin in the EventGhost\lib26\site-packages folder. This way you can use EG python scripting to access a MySQL database and query data to and from it.

I have attached all the files you need to make this work in EG.
Example, to connect, run a stored proc, and get a value or values:

Code: Select all

import MySQLdb
db = MySQLdb.connect("localhost","root","password","database" )
cursor = db.cursor()
cursor.execute('CALL database.storedproc ("%s", "%s", "%s", "%s")'%(egvariable1, egvariable2, egvariable3, egvariable4))
cursor.execute('SELECT column FROM `database`.`table` where column = 1')
variablefetch = cursor.fetchone ()
variable=variablefetch[0]
cursor.close()
db.commit()
db.close()

[RichPyke]

You need Python 2.6 installed for the codegood installer to work, without it it'll stop when it tries to find python.

The archive you supplied, I assume, includes the MySQL files to extract straight in to the eg site-packages directory? However, it depends on the version of MySQL you run as I had issues when I installed the wrong ones for my MySQL db version...

Either way, there's two ways to do it, easy to find on here now

[eatmeimadanish]

This is true, but I seldom use the "installers" instead just decompress the EXE file via 7Zip and just put the python files in my sitepackages. That's all the installer is doing really. I have sitepackages now for wmi, pyodbc and psycopg2 (postgresql) if you want to use those. PYODBC can either define a new DSN or use an existing DSN, so its pretty versatile if you already have ODBC sources setup in windows.

I attached the WMI, PYSCOPG2, and PYODBC python libraries that I confirm work with EG. If you need help using these just PM me.

[artefact]

Hi,

I make a connection to database but I have a problem for write data in DB :

I have a Oregon temperature sensor and a RFXcom, I read temperature without problem but I want to write the temperature in my database logtemp with temp table.

My python script for connect to my DB is OK (base logtemp and table temp)

Code: Select all

#!/usr/bin/python

import MySQLdb, sys

# Open database connection
db = MySQLdb.connect("localhost","root","","logtemp" )
...

But I don't know how to write in data base in python script (I named the sensor OREGON in my sql table) !

My eventghost


Thank for your help

[artefact]

It work now (for all user who want make this) :

Code: Select all

import time
import MySQLdb

temperature = eg.event.payload

#Met en forme la temperature
my_data = temperature.split(' ')
td = my_data[2]

#Envois l'info dans la BDD
connexion = MySQLdb.connect("localhost","root","","oregon" )
cursor = connexion.cursor()

sql = "INSERT INTO temp(THR138) VALUES(%s)" %(td)
cursor.execute(sql)

connexion.autocommit(True)
cursor.close()
connexion.close()

[Pliind]

Can anybody explain this like im a 12yr old? i suckety suck at Python and as soon as Python's in the picture im lost

What i want to acheive is posting temperature/humidity from sensors to MySQL. From there im hoping to post these values to a Wordpress page.

[Pliind]

Ok, Iv'e studied a bit and i just feel more stupid

I've seam to gotten the worst part done (installed python-mysql-plugin sucessfully)

So i've got these variables eg.globals.outtemp135, eg.globals.outhum135, eg.globals.intemp151, eg.globals.inhum151, eg.globals.outtemp167

and i want to post them to a MySQL database. My database name is eventghost. and iv'e made a table called temp. From here im lost

Any help at all would be really helpfull!