python 实现mysql 查询

女爷i 2022-07-17 01:48 203阅读 0赞

1.  要想使Python可以操作MySQL数据库，首先需要安装MySQL-python包，在CentOS上可以使用一下命令来安装

$ sudo yum install MySQL-python

1.  啥都不说了，走两步吧，下面的程序创建了一个到mysql数据库的连接，然后执行了一个简单的查询，并打印查询结果

import MySQLdb  
    
    conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")  
    cursor = conn.cursor ()  
    cursor.execute ("SELECT VERSION()")  
    row = cursor.fetchone ()  
    print "MySQL server version:", row[0]  
    cursor.close ()  
    conn.close ()

1.  下面看一个数据库建表和插入操作

import MySQLdb  
    
    conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")  
    cursor = conn.cursor ()  
    
    cursor.execute (""" CREATE TABLE TEST ( ID INT, COL1 VARCHAR(40), COL2 VARCHAR(40), COL3 VARCHAR(40) ) """)  
    
    cursor.execute (""" INSERT INTO TEST (ID, COL1, COL2, COL3) VALUES (1, 'a', 'b', 'c'), (2, 'aa', 'bb', 'cc'), (3, 'aaa', 'bbb', 'ccc') """)  
    
    conn.commit()  
    cursor.close ()  
    conn.close ()

1.  下面再来看看查询，查询通常有两种方式：一种是使用cursor.fetchall（）获取所有查询结果，然后再一行一行的迭代；另一种每次通过cursor.fetchone()获取一条记录，直到获取的结果为空为止。看一下下面的例子：

import MySQLdb  
    
    conn = MySQLdb.connect (host = "172.17.23.121", user = "fkong", passwd = "fkong", db = "fkong")  
    cursor = conn.cursor ()  
    
    cursor.execute ("SELECT * FROM TEST")  
    rows = cursor.fetchall()  
    for row in rows:  
        print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])  
    
    print "Number of rows returned: %d" % cursor.rowcount  
    
    cursor.execute ("SELECT * FROM TEST")  
    while (True):  
        row = cursor.fetchone()  
        if row == None:  
            break  
        print "%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])  
    
    print "Number of rows returned: %d" % cursor.rowcount  
    
    cursor.close ()  
    conn.close ()