python如何读取mysql_Python如何读取mysql？答案都在这里了

本文概述

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

读取特定的列

我们可以通过提及特定的列名而不是使用星号(*)来阅读它们。

在下面的示例中, 我们将从Employee表中读取姓名, 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)

格式化结果

我们可以通过迭代游标对象的fetchall()或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 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子句

我们可以使用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 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

通过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