Python如何读取mysql（答案都在这里了）

本文概述

读取特定的列
fetchone()方法
格式化结果
使用where子句
订购结果
通过DESC订购

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 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()

输出