Python教程-读取操作
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 表中读取 name、id 和 salary，并在控制台上打印它。
示例
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 90000Order by DESC
这将按某一列的降序排列结果。
示例
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
