本例以Windows, Python 3.4 为基础,来演示使用python 来查询My SQL数据库的数据。

相关

使用python插入数据到My SQL 数据库中:
Python(3.x) Insert Record 插入数据

或者使用脚本创建表和插入数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL DEFAULT '',
`AGE` tinyint(4) DEFAULT NULL,
`BRD` date DEFAULT NULL,
`CREATEDT` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Jack', '27', '1990-03-27', '2017-06-25 00:25:32');
INSERT INTO `student` VALUES ('2', 'Zhao San', '21', '1995-10-21', '2017-06-25 00:25:32');

查询数据

my_sql_select.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
48
49
50
51
52
53
54
55
56
57
58
59
60
import mysql.connector

#Method 1 : query all student
def queryAllStu(config):
output = []

conn = mysql.connector.Connect(**config)
cur= conn.cursor()

tbl = "STUDENT"
stmt_query = "select ID, NAME, AGE, BRD, CREATEDT from {0}".format(tbl)

cur.execute(stmt_query)

for row in cur.fetchall():
output.append("%2d | %20s | %d | %10s | %18s" % (row[0], row[1], row[2], row[3], row[4]))

cur.close()
conn.close()

return output

#Method 2 : query by student id
def queryById(config, id):
output = []

conn = mysql.connector.Connect(**config)
cur = conn.cursor()

tbl = "STUDENT"
stmt_query = "select ID, NAME, AGE, BRD, CREATEDT from {0} where ID = {1}".format(tbl, id)
#print(stmt_query)

cur.execute(stmt_query)

for row in cur.fetchall():
output.append("%2d | %20s | %d | %10s | %18s" % (row[0], row[1], row[2], row[3], row[4]))

cur.close()
conn.close()

return output

if __name__ == '__main__':

config = {
'host': 'localhost',
'port': 3306,
'database': 'test',
'user': 'root',
'password': '!qaz2wsx',
'charset': 'utf8',
'use_unicode': True,
'get_warnings': True,
}

output = queryAllStu(config)
#output = queryById(config, 1)
print('\n'.join(output))
#(output)

运行结果:

1
2
1 |                 Jack | 27 | 1990-03-27 | 2017-06-25 00:25:32
2 | Zhao San | 21 | 1995-10-21 | 2017-06-25 00:25:32