python更新mysql

python使用pymysql更新数据库:

def connect_mysql() :
    # 打开数据库连接
    conn = mysql.connector.connect(host = 'ip', user = 'user', password = 'pwd',
                                   database = 'database', charset = 'utf8')
    # 使用cursor()方法获取操作游标
    cursor = conn.cursor()
    # sql_write  = 'ALTER TABLE region ADD COLUMN cell_nums INT DEFAULT null'
    sql_select = 'SELECT id FROM region LIMIT 0,5'

    # cursor.execute(sql_write)
    try :
        cursor.execute(sql_select)
        res = cursor.fetchall()
        print('lower_right_lon  lower_right_lat upper_left_lon  upper_left_lat')
        for row in res :
            id              = row[0]
            sql_insert = "update region set cell_nums = '%s' where id = '%d';" %(cell_num, id)
            cursor.execute(sql_insert)
            conn.commit()   # 提交更新
    except :
        print('EROOR: unable to fecth data')
    conn.close()  # 关闭数据库连接

update之后要commit进行更新才成功

int类型数据也用%s表示

    try :
        cursor.execute('DROP TABLE IF EXISTS region_in_out;')
        cursor.execute("CREATE TABLE region_in_out (id INT AUTO_INCREMENT PRIMARY KEY, in_time INT(10), out_time INT(10), in_lon INT(11), in_lat INT(11), out_lon INT(11), out_lat INT(11))")
        cursor.execute(
            "INSERT INTO region_in_out (in_time,out_time,in_lon,in_lat,out_lon,out_lat) VALUES(%s,%s,%s,%s,%s,%s)",
            (int(data[0]), int(data[1]), int(data[2]), int(data[3]), int(data[4]), int(data[5])))
        conn.commit()
    except :
        print('EROOR: unable to fecth data')