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