Insert data to a mysql server from python ON PC with windows

after installing mysql server and client(python connector) on my beaglebone, I learn to insert data from python in to mysql server.
All these are done on windows PC.
most data are found in http://www.mysqltutorial.org/python-mysql/

Here is a example on how to connect to a data base from python:

import mysql.connector
from mysql.connector import Error


def connect():
    """ Connect to MySQL database """
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='iot',
                                       user='root',
                                       password='')
        if conn.is_connected():
            print('Connected to MySQL database')
                 
    except Error as e:
        print(e)

    finally:
        conn.close()
        print('Disconnected from MySQL database')

connect()

To insert or query from a table, we need to use cursor.execute(), I first do a query all:

'''

2014/1/28

we connect to mysql databse IOT and query out all rows..

there are still some other query ways...fetchone(), fetchmany()

http://www.mysqltutorial.org/python-mysql-query/

'''

import mysql.connector

from mysql.connector import Error

def connect():

    """ Connect to MySQL database """

    try:

        conn = mysql.connector.connect(host='localhost',

                                       database='iot',

                                       user='root',

                                       password='')

        if conn.is_connected():

            print('Connected to MySQL database')

            cursor = conn.cursor()

            cursor.execute("SELECT * FROM greenroom")

            rows = cursor.fetchall()

            print('Total Row(s):', cursor.rowcount)

            for row in rows:

                print(row)

         

    except Error as e:

        print(e)

    finally:

        conn.close()

        print('Disconnected from MySQL database')

#if __name__ == '__main__':

connect()

we use the same method to add a row into greenroom table:
'''
2014/1/28
we connect to mysql databse IOT and query out all rows..
there are still some other query ways...fetchone(), fetchmany()
http://www.mysqltutorial.org/python-mysql-query/

'''
import mysql.connector
from mysql.connector import Error



def connect():
    """ Connect to MySQL database """
    try:
        conn = mysql.connector.connect(host='localhost',
                                       database='iot',
                                       user='root',
                                       password='')
        if conn.is_connected():
            print('Connected to MySQL database')
            cursor = conn.cursor()
            cursor.execute("INSERT INTO\
greenroom(device_nr,record_date,record_time,lux,temp,humid)"\
                                           "values(8,'2014-1-29','21:53:00',256,25.4,80)")

            conn.commit()
            
         
    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()
        print('Disconnected from MySQL database')

#if __name__ == '__main__':
connect()

if we check from command line:

to pass a date or time to cursor, you need to use the %s expression
http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html