Python DBAPI2

It took me a hour to get rows updated using Python on MySQL, specially PyMySQL driver. The problem is the query is not auto-committed. I have to add a line at the end:

cursor.execute('commit')

Moreover, quoting with HTML data is in trouble, having to do it manually. An important note is, all queries are pre-fetched by default. That mean all data was in RAM no matter we call fetchone or fetch all. In order to save memory, we have to use SSCursor:

cursor = conn.cursor(pymysql.cursors.SSCursor)

Here is a skeleton, querying note field and update it:

connection = pymysql.connect(host='127.0.0.1', user='root', passwd='', db='name', charset='utf8')
cursor = connection.cursor()
cursor.execute('SELECT id, note FROM products')
rows = cursor.fetchall()
for row in rows:
    # new_note = xyz(rows[0])...
    sql = "UPDATE products SET note='%s' WHERE id=%s" % (connection.escape_string(new_note), row[0])
    cursor.execute(sql)
cursor.execute('commit')
cursor.close()
connection.close();