本文概述
- 读取特定的列
- fetchone()方法
- 格式化结果
- 使用where子句
- 订购结果
- 通过DESC订购
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 objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select * from Employee")#fetching the rows from the cursor objectresult = cur.fetchall()#printing the resultfor 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 objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee")#fetching the rows from the cursor objectresult = cur.fetchall()#printing the resultfor 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 objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee")#fetching the first row from the cursor objectresult = cur.fetchone()#printing the resultprint(result)except:myconn.rollback()myconn.close()
输出
('John', 101, 25000.0)
格式化结果我们可以通过迭代游标对象的fetchall()或fetchone()方法产生的结果来格式化结果, 因为结果存在为不可读的元组对象。
【Python如何读取mysql(答案都在这里了)】考虑以下示例。
例子
import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee")#fetching the rows from the cursor objectresult = cur.fetchall()print("NameidSalary");
for row in result:print("%s%d%d"%(row[0], row[1], row[2]))except:myconn.rollback()myconn.close()
输出
NameidSalaryJohn10125000John10225000David10325000Nick10490000Mike10528000
使用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 objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee where name like 'J%'")#fetching the rows from the cursor objectresult = cur.fetchall()print("NameidSalary");
for row in result:print("%s%d%d"%(row[0], row[1], row[2]))except:myconn.rollback()myconn.close()
输出
NameidSalaryJohn10125000John10225000
示例:打印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 objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee where id in (101, 102, 103)")#fetching the rows from the cursor objectresult = cur.fetchall()print("NameidSalary");
for row in result:print("%s%d%d"%(row[0], row[1], row[2]))except:myconn.rollback()myconn.close()
输出
NameidSalaryJohn10125000John10225000David1032500
订购结果ORDER BY子句用于对结果进行排序。考虑以下示例。
例子
import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee order by name")#fetching the rows from the cursor objectresult = cur.fetchall()print("NameidSalary");
for row in result:print("%s%d%d"%(row[0], row[1], row[2]))except:myconn.rollback()myconn.close()
输出
NameidSalaryDavid10325000John10125000John10225000Mike10528000Nick10490000
通过DESC订购这将结果按特定列的降序排列。
例子
import mysql.connector#Create the connection object myconn = mysql.connector.connect(host = "localhost", user = "root", passwd = "google", database = "PythonDB")#creating the cursor objectcur = myconn.cursor()try:#Reading the Employee datacur.execute("select name, id, salary from Employee order by name desc")#fetching the rows from the cursor objectresult = cur.fetchall()#printing the resultprint("NameidSalary");
for row in result:print("%s%d%d"%(row[0], row[1], row[2]))except:myconn.rollback()myconn.close()
输出
NameidSalaryNick10490000Mike10528000John10125000John10225000David10325000
推荐阅读
- 数据库操作(Python如何更新MySQL())
- Python MySQL如何进行事务操作()
- Python MySQL插入操作如何实现()
- Python-MySQL环境设置详解
- Python MySQL如何创建表()
- Python MySQL数据库连接如何操作()
- Python如何创建新数据库()
- Python如何使用继承()
- Python魔术方法介绍