在本教程中，我们将学习使用Python将数据插入MySQL数据库。

先决条件

要理解本教程，您必须具有一些SQL，SQL查询和MySQL数据库的知识。

在本教程中，我们将使用"员工"表。

该表已经有一些行，如下所示。

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2016-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2016-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2016-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2016-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2016-01-04 12:00:00 |
+------------+-----------+----------+---------------------+
5 rows in set (0.00 sec)

这是雇员表的创建表查询。

CREATE TABLE `employee` (
  `employeeid` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`employeeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

将数据插入表

要将数据插入表中，我们需要执行以下步骤。

• 使用mysql.connector.connect()方法连接到MySQL服务器。

• 从步骤1中创建的连接中获取MySQL游标。

• 使用游标执行INSERT语句。

• 提交更改。

• 关闭连接。

在表格中插入一行

要将数据插入表中，我们使用execute方法的帮助，该方法带有两个参数-SQL查询和元组形式的值。

在下面的Python程序中，我们将插入一个新的员工数据。

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:
  
    cnx = mysql.connector.connect(
        user='root',
        password='root1234',
        host='127.0.0.1',
        database='mydb'
    )
  
except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')
        
except:
    print("Unknown error occurred!")

# insert operation
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)"
    
    # values
    values = ("e06", "Tony", "Stark", "2016-01-05, 12:13:14")
    
    # insert data into the table
    cnxCursor.execute(query, values)
    
    # commit
    cnx.commit()
    
    # total number of rows inserted
    print("Total rows inserted: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    print("Error: %s" % err.message)
    
except:
    print("Unknown error occurred!")
    
finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

成功的话，我们将得到以下输出。

Total rows inserted: 1

如果现在检查"员工"表，我们将获得新条目。

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2016-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2016-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2016-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2016-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2016-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2016-01-05 12:13:14 |
+------------+-----------+----------+---------------------+
6 rows in set (0.00 sec)

将多个记录插入表

要插入多行，我们创建一个元组形式的值列表，并借助executemany方法。

在下面的Python程序中，我们将两个新的员工记录插入到employee表中。

# import module
import mysql.connector

# import errorcode
from mysql.connector import errorcode

# get db connection
try:
  
    cnx = mysql.connector.connect(
        user='root',
        password='root1234',
        host='127.0.0.1',
        database='mydb'
    )
  
except mysql.connector.Error as err:
    
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Invalid credential. Unable to access database.')
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print('Database does not exists')
    else:
        print('Failed to connect to database')
        
except:
    print("Unknown error occurred!")

# insert operation
try:
    # cursor
    cnxCursor = cnx.cursor()
    
    # sql query
    query = "INSERT INTO employee (employeeid, firstname, lastname, created_at) VALUES (%s, %s, %s, %s)"
    
    # values
    values = [
        ("e07", "Doctor", "Strange", "2016-01-05, 13:14:15"),
        ("e08", "Doctor", "Who", "2016-01-05, 13:14:15")
    ]
    
    # insert data into the table
    cnxCursor.executemany(query, values)
    
    # commit
    cnx.commit()
    
    # total number of rows inserted
    print("Total rows inserted: %d" % cnxCursor.rowcount)
    
except mysql.connector.Error as err:
    print("Error: %s" % err.message)
    
except:
    print("Unknown error occurred!")
    
finally:
    # close cursor
    cnxCursor.close()
    # close connection
    cnx.close()

如果成功，上面的代码将打印以下输出。

Total rows inserted: 2

如果现在检查employee表，我们将看到新条目。

mysql< SELECT * FROM employee;
+------------+-----------+----------+---------------------+
| employeeid | firstname | lastname | created_at          |
+------------+-----------+----------+---------------------+
| e01        | John      | Doe      | 2016-01-01 10:20:30 |
| e02        | Jane      | Doe      | 2016-01-01 12:13:14 |
| e03        | Peter     | Parker   | 2016-01-02 15:16:17 |
| e04        | Bruce     | Banner   | 2016-01-03 10:20:30 |
| e05        | Bruce     | Wayne    | 2016-01-04 12:00:00 |
| e06        | Tony      | Stark    | 2016-01-05 12:13:14 |
| e07        | Doctor    | Strange  | 2016-01-05 13:14:15 |
| e08        | Doctor    | Who      | 2016-01-05 13:14:15 |
+------------+-----------+----------+---------------------+
8 rows in set (0.00 sec)