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