python操作mysql 

 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']

 

posted @ 2018-05-05 16:25  静心_心静  阅读(229)  评论(0)    收藏  举报