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

输出
NameidSalaryNick10490000Mike10528000John10125000John10225000David10325000

    推荐阅读