第1章 Python简介读取操作
SELECT 语句被用来从数据库中读取这些值。我们可以限制该输出的选择通过使用各种查询子句在 SQL 等、限制等。
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')不同列的读取
我们可以通过特定的读出列提及他们的名字 ， 而不是使用星 (*) 。
在以下的例子中 ， 将读出的名称、 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)格式化结果
我们可以通过迭代格式的结果产生的结果或 fetchone FETCHALL () () 方法的结果存在由于光标对象 ， 该元组对象不可读。
考虑以下示例。
实例
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 子句
我们能限制所产生的结果与通过使用 SELECT 语句 WHERE 子句。这将仅仅提取那些列满足 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
Order 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