Python MySQL读取操作

Python MySQL读取操作

1、读取操作

1. SELECT 语句被用来从数据库中读取这些值。我们可以限制该输出的选择通过使用各种查询子句在 SQL 等、限制等

2. Python 提供 fetchall() 方法返回的数据存储在表的行。我们可以重复的结果以得到单个行

3. 实例

import mysql.connector  
   
myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB") 
 
mycur = myconn.cursor()  
     
mycur.execute("select * from Employee")  

result = mycur.fetchall()
      
for x in result:  
    print(x)

• 输出

('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')
('John', 110, 25000.0, 201, 'Newyork')

2、不同列的读取

1. 我们可以通过特定的读出列提及他们的名字 ， 而不是使用星 (*)
2. 实例

import mysql.connector  

myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB")  

cur = myconn.cursor()  
    
cur.execute("select name, id, salary from Employee")  

result = cur.fetchall()  

for x in result:  
    print(x)

• 输出

('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)
('John', 110, 25000.0)

3、fetchone() 方法

1. 在 fetchone() 方法用于只获取一个列的表。fetchone() 方法返回的第一个行的结果集合

2. 实例

import mysql.connector  
    
myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB")  

cur = myconn.cursor()  
  
cur.execute("select name, id, salary from Employee")  
result = cur.fetchone()  
print(result)  

• 输出

('John', 102, 25000.0)

4、格式化结果

1. 我们可以通过迭代格式的结果产生的结果或 fetchone()、 fetchall() 方法的结果存在由于光标对象 ， 该元组对象不可读

2. 实例

import mysql.connector  

myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB") 

cur = myconn.cursor()  
   
cur.execute("select name, id, salary from Employee")  
  
result = cur.fetchall()  
  
print("Name    id    Salary")
 
for row in result:  
    print("%s    %d    %d"%(row[0],row[1],row[2])) 

• 输出

Name    id    Salary
John    102    25000
David    103    25000
Nick    104    90000
Mike    105    28000
John    110    25000

5、使用 WHERE 子句

1. 我们能限制所产生的结果与通过使用 SELECT 语句 WHERE 子句。这将仅仅提取那些列满足 WHERE 条件

2. 实例：姓名从J开始

import mysql.connector  
  
myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB") 

cur = myconn.cursor()  
    
cur.execute("select name, id, salary from Employee where name like 'J%'")  

result = cur.fetchall()  
  
print("Name    id    Salary")
  
for row in result:  
    print("%s    %d    %d"%(row[0],row[1],row[2]))  

• 输出

Name    id    Salary
John    102    25000
John    110    25000

3. 实例：姓名和ID = 101、102和103

import mysql.connector

myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB")

cur = myconn.cursor()

cur.execute("select name, id, salary from Employee where id in (101,102,103)")

result = cur.fetchall()

print("Name    id    Salary")

for row in result:
    print("%s    %d    %d" % (row[0], row[1], row[2]))

• 输出

Name    id    Salary
John    102    25000
David    103    25000

6、结果排序

1. ORDER BY 子句中使用命令的结果，默认是升序排列

2. 实例

import mysql.connector

myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB")

cur = myconn.cursor()

cur.execute("select name, id, salary from Employee order by name")

result = cur.fetchall()

print("Name    id    Salary")

for row in result:
    print("%s    %d    %d" % (row[0], row[1], row[2]))

• 输出

Name    id    Salary
David    103    25000
John    102    25000
John    110    25000
Mike    105    28000
Nick    104    90000

7、Order By <字段名> DESC

1. 此命令的结果与order by 相反，返回降序排列结果

2. 实例

import mysql.connector

myconn = mysql.connector.connect(host="192.168.126.20", user="root", passwd="mysql", database="PythonDB")

cur = myconn.cursor()

cur.execute("select name, id, salary from Employee order by id desc")

result = cur.fetchall()
print("Name    id    Salary")
for row in result:
    print("%s    %d    %d" % (row[0], row[1], row[2]))
 

• 输出

Name    id    Salary
John    110    25000
Mike    105    28000
Nick    104    90000
David    103    25000
John    102    25000