Python MySQL读取操作|极客笔记

Python MySQL读取操作

SELECT语句用于从数据库中读取值。我们可以使用SQL中的各种子句（如where、limit等）来限制select查询的输出。

Python提供了fetchall()方法，以行的形式返回存储在表中的数据。我们可以遍历结果以获取各个行。

在本教程的本节中，我们将使用Python脚本从数据库中提取数据。我们还将格式化输出以在控制台上打印出来。

示例

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select * from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()
    #printing the result

    for x in result:
        print(x);
except:
    myconn.rollback()

myconn.close()

输出：

('John', 101, 25000.0, 201, 'Newyork')
('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')

读取特定列

我们可以通过提及列名而不是使用星号(*)来读取特定的列。

在下面的示例中，我们将从Employee表中读取姓名、ID和工资，并将其打印到控制台上。

示例

import mysql.connector
#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()
    #printing the result
    for x in result:
        print(x);
except:
    myconn.rollback()
myconn.close()

输出：

('John', 101, 25000.0)
('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)

fetchone()方法

fetchone()方法用于从表中仅获取一行数据。fetchone()方法返回结果集中的下一行数据。

考虑以下示例。

示例

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the first row from the cursor object
    result = cur.fetchone()

    #printing the result
    print(result)

except:
    myconn.rollback()

myconn.close()

输出：

('John', 101, 25000.0)

格式化结果

我们可以通过迭代游标对象的fetchall()或fetchone()方法产生的结果来格式化结果，因为结果存在作为不可读的元组对象。

考虑以下示例。

示例

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:

    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");
    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出：

Name    id    Salary
John    101    25000
John    102    25000
David    103    25000
Nick    104    90000
Mike    105    28000

使用where子句

通过使用where子句，我们可以限制select语句产生的结果。这将提取满足where条件的列。

考虑以下示例。

示例：打印以j开头的名称

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee where name like 'J%'")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出：

Name    id    Salary
John    101    25000
John    102    25000

示例：打印id为101、102和103的名字

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee where id in (101,102,103)")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出：

Name    id    Salary
John    101    25000
John    102    25000
David    103    2500

对结果进行排序

ORDER BY子句用于对结果进行排序。考虑以下示例。

示例

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee order by name")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    print("Name    id    Salary");

    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))
except:
    myconn.rollback()

myconn.close()

输出：

Name    id    Salary
David   103    25000
John    101    25000
John    102    25000
Mike    105    28000
Nick    104    90000

按降序排列

这将按照特定列的递减顺序对结果进行排序。

示例

import mysql.connector

#Create the connection object 
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")

#creating the cursor object
cur = myconn.cursor()

try:
    #Reading the Employee data    
    cur.execute("select name, id, salary from Employee order by name desc")

    #fetching the rows from the cursor object
    result = cur.fetchall()

    #printing the result
    print("Name    id    Salary");
    for row in result:
        print("%s    %d    %d"%(row[0],row[1],row[2]))

except:
    myconn.rollback()

myconn.close()

输出:

Name    id    Salary
Nick    104    90000
Mike    105    28000
John    101    25000
John    102    25000
David    103    25000

Python MySQL读取操作