python操作mysql

hostname = "xxx.xxx.xxx.xxx"
username = "root"
password = "123456"
dbname = "test"
str_sql = " SElect * from score"

import pymysql
import re

def r_db(host,username,password,dbname,sql,port=3306,charset='utf8'):
    #连接数据库
    db = pymysql.connect(
        host = host,
        user = username,
        passwd = password,
        db = dbname,
        port = port,
        charset = charset
    )
    #创建一个游标对象
    cursor = db.cursor()
    #通过execute执行sql语句
    cursor.execute(sql)
    if not re.search(r'^select', sql.strip(), flags=re.I):
    #if sql.strip()[:6].upper() == 'SELECT':
        # 如果是updat、delete、insert，需要执行commit
        db.commit()
        res = 'OK'
    else:
        #查询列名信息
        lines = cursor.description
        line_title = []
        for line in lines:
            line_title.append(line[0])

        #查询表的内容
        res = list(cursor.fetchall())
        res.insert(0, line_title)
        print(res)
    cursor.close()
    db.close()
    return res

游标可以指定数据返回的格式
cur = db.cursor(cursor=pymysql.cursors.DictCursor)
cur.execute('select * from cource;')

res = cur.fetchall() #[{'Id': 2, 'C_name': 'lw', 'Sex': '202cb962ac59075b964b07152d234b70'}]
# res = cur.fetchone() #{'Id': 2, 'C_name': 'lw', 'Sex': '202cb962ac59075b964b07152d234b70'}

cur = db.cursor()
cur.execute('select * from cource;')
res = cur.fetchall()
print(res)
((1, 'yn', '女'), (2, 'lw', '202cb962ac59075b964b07152d234b70'))

import pymysql
dir(pymysql.cursors)
['Cursor', 'DictCursor', 'DictCursorMixin', 'PY2', 'RE_INSERT_VALUES', 'SSCursor', 
'SSDictCursor', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__',
 '__name__', '__package__', '__spec__', 'absolute_import', 'err', 'partial',
 'print_function', 'range_type', 're', 'text_type', 'warnings']