使用fetchone()
方法获取单条记录使用fetchall()
方法从数据库表中获取多个值。
fetchone() - 它获取查询结果集的下一行。 结果集是当使用游标对象来查询表时返回的对象。 fetchall() - 它获取结果集中的所有行。 如果已经从结果集中提取了一些行,则从结果集中检索剩余的行。 rowcount - 这是一个只读属性,并返回受execute()方法影响的行数。
import pymysql # 打开数据库链接 db = pymysql.connect("localhost","root","123456","test" ) # 获取操作游标:使用cursor()方法获取 cursor = db.cursor()
# 按字典格式返回数据 # cursor = db.cursor(pymysql.cursors.DictCursor)
# 查询语句 sql = "SELECT * FROM USER \ WHERE INCOME > %d" % (1000) #print (sql)
try: # 执行sql cursor.execute(sql) # 获取所有结果列表 results = cursor.fetchall() for row in results: #print (row) fname = row[1] lname = row[2] age = row[3] sex = row[4] income = row[5] # 打印查看一下结果 print ("name = %s %s,age = %s,sex = %s,income = %s" % (fname, lname, age, sex, income )) except: import traceback #打印出报错具体信息 traceback.print_exc() print ("Error: unable to fetch data") # 关闭数据库链接 db.close()
注意:
什么是 Traceback
Traceback 是 Python 错误信息的报告。在其他编程语言中有着不同的叫法包括 stack trace, stack traceback, backtrac 等名称, 在 Python 中,术语就是 Traceback
当代码中出现错误,会在输出的时候打印 Traceback 错误信息
-
import pymysql
-
-
# Open database connection
-
db = pymysql.connect("localhost","root","123456","test" )
-
-
# prepare a cursor object using cursor() method
-
cursor = db.cursor()
-
# 按字典返回
-
# cursor = db.cursor(pymysql.cursors.DictCursor)
-
-
# Prepare SQL query to select a record from the table.
-
sql = "SELECT * FROM EMPLOYEE \
-
WHERE INCOME > %d" % (1000)
-
#print (sql)
-
try:
-
# Execute the SQL command
-
cursor.execute(sql)
-
# Fetch all the rows in a list of lists.
-
results = cursor.fetchall()
-
for row in results:
-
#print (row)
-
fname = row[1]
-
lname = row[2]
-
age = row[3]
-
sex = row[4]
-
income = row[5]
-
# Now print fetched result
-
print ("name = %s %s,age = %s,sex = %s,income = %s" % \
-
(fname, lname, age, sex, income ))
-
except:
-
import traceback
-
traceback.print_exc()
-
-
print ("Error: unable to fetch data")
-
-
# disconnect from server
-
db.close()