本例以Windows, Python 3.4 为基础，来演示使用Python采用预处理操作完成插入操作。

相关

创建表：
Python(3.x) Create Table 创建表

或者使用SQL Script:

1
2
3
4
5
6
7
8
CREATE TABLE STUDENT (
    ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME VARCHAR (50) DEFAULT '' NOT NULL,
    AGE TINYINT DEFAULT NULL,
    BRD date DEFAULT NULL,
    CREATEDT datetime DEFAULT NULL,
    PRIMARY KEY (ID)
)

使用Python查询数据：
Python(3.x) Query Record 查询数据

Prepared Statements 预处理操作

my_sql_prepared_statements.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import mysql.connector
from mysql.connector.cursor import MySQLCursorPrepared
from datetime import datetime
from datetime import date
import my_sql_select

def main(config):
    conn = mysql.connector.Connect(**config)
    
    cour = conn.cursor(cursor_class=MySQLCursorPrepared)
    
    prepstmt = "insert into student (name, age, brd, CREATEDT) values (%s, %s, %s, %s)"
    
    #prepared statements
    cour.execute(prepstmt)
    
    created = datetime.now()
    #set the value
    cour.execute(prepstmt, ("test", 29, date(1988, 3, 27), created))
    
    conn.commit()
    

if __name__ == '__main__':
    
    config = {
            'host': 'localhost',
            'port': 3306,
            'database': 'test',
            'user': 'root',
            'password': '!qaz2wsx',
            'charset': 'utf8',
            'use_unicode': True,
            'get_warnings': True,
        } 
    
    print("----------before insert------------")
    output = my_sql_select.queryAllStu(config)
    print('\n'.join(output))
    
    main(config)
    
    print("----------after insert------------")
    
    output = my_sql_select.queryAllStu(config)
    print('\n'.join(output))

运行结果：

1
2
3
4
5
----------before insert------------
 2 |             Zhao San | 21 | 1995-10-21 | 2017-06-25 00:25:32
----------after insert------------
 2 |             Zhao San | 21 | 1995-10-21 | 2017-06-25 00:25:32
 3 |                 test | 29 | 1988-03-27 | 2017-07-02 16:30:38