1 hostname = "xxx.xxx.xxx.xxx"
2 username = "root"
3 password = "123456"
4 dbname = "test"
5 str_sql = " SElect * from score"
6
7 import pymysql
8 import re
9
10 def r_db(host,username,password,dbname,sql,port=3306,charset='utf8'):
11 #连接数据库
12 db = pymysql.connect(
13 host = host,
14 user = username,
15 passwd = password,
16 db = dbname,
17 port = port,
18 charset = charset
19 )
20 #创建一个游标对象
21 cursor = db.cursor()
22 #通过execute执行sql语句
23 cursor.execute(sql)
24 if not re.search(r'^select', sql.strip(), flags=re.I):
25 #if sql.strip()[:6].upper() == 'SELECT':
26 # 如果是updat、delete、insert，需要执行commit
27 db.commit()
28 res = 'OK'
29 else:
30 #查询列名信息
31 lines = cursor.description
32 line_title = []
33 for line in lines:
34 line_title.append(line[0])
35
36 #查询表的内容
37 res = list(cursor.fetchall())
38 res.insert(0, line_title)
39 print(res)
40 cursor.close()
41 db.close()
42 return res
1 游标可以指定数据返回的格式
2 cur = db.cursor(cursor=pymysql.cursors.DictCursor)
3 cur.execute('select * from cource;')
4
5 res = cur.fetchall() #[{'Id': 2, 'C_name': 'lw', 'Sex': '202cb962ac59075b964b07152d234b70'}]
6 # res = cur.fetchone() #{'Id': 2, 'C_name': 'lw', 'Sex': '202cb962ac59075b964b07152d234b70'}
1 cur = db.cursor()
2 cur.execute('select * from cource;')
3 res = cur.fetchall()
4 print(res)
5 ((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']
