本例以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