SELECT语句用于从数据库中读取值。无涯教程可以通过使用SQL中的各种子句(例如where,limit等)来限制select查询的输出。
Python提供了fetchall()方法以行的形式返回存储在表中的数据。可以迭代结果以获取各个行。
在本教程的这一部分中,将使用python脚本从数据库中提取数据。还将格式化输出以将其打印在控制台上。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select * from Employee") #从游标对象中获取行 result = cur.fetchall() #打印结果 for x in result: print(x); except: myconn.rollback() myconn.close()
输出:
('John', 101, 25000.0, 201, 'Newyork') ('John', 102, 25000.0, 201, 'Newyork') ('Learnfk', 103, 25000.0, 202, 'Port of spain') ('Nick', 104, 90000.0, 201, 'Newyork') ('Mike', 105, 28000.0, 202, 'Guyana')
可以通过提及特定的列名而不是使用星号(*)来读取它们。
在下面的示例中,将从Employee表中读取name,ID和salary,并将其打印在控制台上。
链接:https://www.learnfk.comhttps://www.learnfk.com/python3/python-mysql-read-operation.html
来源:LearnFk无涯教程网
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee") #从游标对象中获取行 result = cur.fetchall() #打印结果 for x in result: print(x); except: myconn.rollback() myconn.close()
输出:
('John', 101, 25000.0) ('John', 102, 25000.0) ('Learnfk', 103, 25000.0) ('Nick', 104, 90000.0) ('Mike', 105, 28000.0)
fetchone()方法用于从表中仅获取一行。 fetchone()方法返回结果集的下一行。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee数据 cur.execute("select name, id, salary from Employee") #从游标对象中获取第一行 result = cur.fetchone() #打印结果 print(result) except: myconn.rollback() myconn.close()
输出:
('John', 101, 25000.0)
可以通过迭代游标对象的fetchall()或fetchone()方法产生的结果来格式化结果,因为结果存在为不可读的元组对象。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee") #从游标对象中获取行 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 Learnfk 103 25000 Nick 104 90000 Mike 105 28000
可以使用where子句来限制select语句产生的结果。这将仅提取那些满足where条件的列。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee where name like 'J%'") #从游标对象中获取行 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
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee where id in (101,102,103)") #从游标对象中获取行 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 Learnfk 103 2500
ORDER BY子句用于对结果进行排序,考虑以下示例。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee order by name") #从游标对象中获取行 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 Learnfk 103 25000 John 101 25000 John 102 25000 Mike 105 28000 Nick 104 90000
这将结果按特定列的降序排列。
import mysql.connector #创建连接对象 myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "Learnfk",database = "PythonDB") #创建光标对象 cur = myconn.cursor() try: #读取Employee表数据 cur.execute("select name, id, salary from Employee order by name desc") #从游标对象中获取行 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 Nick 104 90000 Mike 105 28000 John 101 25000 John 102 25000 Learnfk 103 25000
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)