单条插入

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
import datetime
import mysql.connector
from mysql.connector import errorcode

reload(sys)
sys.setdefaultencoding('utf-8')

conf = {
  'user': 'bestfei',
  'password': 'password',
  'host': '10.101.196.187',
  'database': 'fei',
  'raise_on_warnings': True,
}


try:
    con = mysql.connector.connect(**conf)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print 'mysql connnect'

    cursor = con.cursor()
    insertsql = ("insert into table_name VALUES ('2017-09-13',1,'上海',1,10);")

    try:
        # 执行sql语句
        cursor.execute(insertsql)
        print("sql:"+cursor.statement)
        print(cursor.lastrowid)
        # 提交到数据库执行
        con.commit()
    except:
        # Rollback in case there is any error
        print '插入数据失败!'
        con.rollback()

    cursor.close()
    con.close()

多条插入

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
import datetime
import mysql.connector
from mysql.connector import errorcode

reload(sys)
sys.setdefaultencoding('utf-8')

conf = {
  'user': 'bestfei',
  'password': 'password',
  'host': '10.101.196.187',
  'database': 'fei',
  'raise_on_warnings': True,
}


try:
    con = mysql.connector.connect(**conf)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print 'mysql connnect'

    cursor = con.cursor()
    insertsql1 = ("insert into table_name VALUES ('2017-09-")
    insertsql2 = ("',3,'北京',1,10);")

    for num in range(20,25):
        insertsql = insertsql1 + str(num) + insertsql2
        cursor.execute(insertsql)
        print("sql:"+cursor.statement)
        con.commit()

    cursor.close()
    con.close()