2020-4：MySQL数据查询程序设计

Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取所有数据，使用fetchmany(self, size=None)获取指定条数数据。

程序设计的结构是：

1建立连接

2获取游标

3执行数据查询操作

4关闭游标（使用with语句系统会帮助关闭）

5关闭数据库

代码：

import pymysql.cursors
def db_findtable():
    #创建连接
    connection = pymysql.connect(
        host='localhost',
        port = 3306,
        user = 'root',
        password = 'tang123',
        db = 'my_db',
        charset = 'utf8',
        cursorclass = pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            #执行查询
                     sql = 'SELECT * FROM user WHERE INCOME > %s'%(1000)
            try:
                row_count = cursor.execute(sql)
                print(row_count)
                results = cursor.fetchall()
                print(results)
                for row in results:
                    fname = row['FIRST_NAME']
                    lname = row['LAST_NAME']
                    age = row['AGE']
                    sex = row['SEX']
                    income = row['INCOME']
                    print('fname=%s,lname=%s,age=%s,sex=%s,income=%s'% \
                          (fname,lname,age,sex,income))
            except:
                print('错误：数据查询失败')
    finally:
connection.close()
db_findtable()
------------------------------

复制