Python e Mysql

[Callievi]

Hi guys,
I should upgrade the tables of a database through a python script.
Script and database stay on raspberry.
I found a library,Mysqldb,I try to make a script,it run,but the tables doesn't upgrande.

Thank you for your help.

Silvio

[DougieLawson]

Post your python code and SQL / DDL on here.

[Callievi]

It is the Python script

Code: Select all

#! /usr/bin/env python

import _mysql
db=_mysql.connect("localhost","root","pinkrabbits","domotica")
db.query("UPDATE casa SET luci=1 WHERE id_stanza=1")
conn.close()

and it is the mysql table

Code: Select all

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| ID_stanza      | int(11)     | NO   | PRI | NULL    | auto_increment |
| nome           | varchar(20) | NO   |     | NULL    |                |
| luci           | tinyint(1)  | NO   |     | NULL    |                |
| irrigazione    | tinyint(1)  | YES  |     | NULL    |                |
| finestra       | tinyint(1)  | YES  |     | NULL    |                |
| sensoreD       | tinyint(1)  | YES  |     | NULL    |                |
| sensoreA       | int(11)     | YES  |     | NULL    |                |
| condizionatore | tinyint(1)  | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

[DougieLawson]

You're running an updating query but not checking whether it completed OK.

I wrote this to easily display the rows in the table

Code: Select all

#!/usr/bin/python
import _mysql
try:
        db=_mysql.connect('xxx', 'uuu', 'ppp', 'ddd')
        db.query('SELECT id_stanza, luci from casa;')
        res=db.use_result()
        for row in res.fetch_row():
                print row
except _mysql.Error, e:
        print "SQL SELECT error %d: %s" % (e.args[0], e.args[1])
finally:
        if db:
                db.close()

And updated your program like this

Code: Select all

#!/usr/bin/python
import _mysql
try:
        db=_mysql.connect('xxx', 'uuu', 'ppp', 'ddd')
        db.query('UPDATE casa set luci=99 WHERE ID_stanza=11;')
        if db.affected_rows() == 0:
                raise Exception('zeroRows')
except _mysql.Error, e:
        print "SQL UPDATE error %d: %s" % (e.args[0], e.args[1])
finally:
        if db:
                db.close()

if row#11 doesn't exist then it gets a "zeroRows" exception raised. Any syntax or environmental errors (like MySQL not available) trigger the except clause. You could add your own exception handler for

Code: Select all

except zeroRows, e:

to handle that.

Code: Select all

import MySQLdb as mdb

has better error handling for python and it's easier to use.

[pavelbor]

In order to save to database you have to commit your changes in the database
db.commit()