从零开始学Python-day7
##导入数据库In : import MySQLdb as mysql
##连接数据库
In : data = mysql.connect(user='root',passwd='123456',db='reboot',charset='utf8')
##建立游标,添加这行后数据库就初始化完成
In : cur = data.cursor()
In : data.autocommit(True)##设置自动提交，比较重要
##在ipython端添加数据
#在ipython段查询所有数据
In : cur.execute('select * from users')
Out: 1L
In : import datetime #导入datetime
In : ##datetime模块可以实现在数据库中create_time生成时间
In : sql = "insert into users(name,name_cn,password,email,mobile,role,status,create_time) values ('cc'
...: ,'cc','cC23 ','CC@chinacache.com','CCC','user','0','%s') " %(datetime.datetime.now().strftime("%Y
...: -%m-%d%H:%M:%S"))
In : print sql
insert into users(name,name_cn,password,email,mobile,role,status,create_time) values ('cc','cc','cC23 ','CC@chinacache.com','CCC','user','0','2016-11-3016:04:33')
In : cur.execute(sql)
Out: 1L
#再次查询所有数据发现新增加了一条数据
In : cur.execute("select * from users;")
Out: 2L
##在mysql段查询所有数据确认新增数据
mysql> select * from users;
+----+------+---------+----------+-------------------+--------+------+--------+---------------------+-----------+
| id | name | name_cn | password | email | mobile | role | status | create_time | last_time |
+----+------+---------+----------+-------------------+--------+------+--------+---------------------+-----------+
|1 | ss | ss | ss123 | ss@chinacache.com | ssss | user | 0 | 2016-11-30 15:37:48 | NULL |
|2 | cc | cc | cC23 | CC@chinacache.com | CCC | user | 0 | 2016-11-30 16:04:33 | NULL |
+----+------+---------+----------+-------------------+--------+------+--------+---------------------+-----------+
2 rows in set (0.00 sec)
mysql>
##ipython交互下查找数据
###查询所有数据
In : select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users"
In : cur.execute(select_sql)
Out: 2L
In : res = cur.fetchall() ##查找所有数据使用cur.fetchall(),单条数据则使用cur.fetchone()
In : print res
((u'ss', u'ss', u'ss123 ', u'ss@chinacache.com', u'ssss', u'user', 0, datetime.datetime(2016, 11, 30, 15, 37, 48)), (u'cc', u'cc', u'cC23 ', u'CC@chinacache.com', u'CCC', u'user', 0, datetime.datetime(2016, 11, 30, 16, 4, 33)))
###查询单条数据
In : select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users where
...: name = 'ss'"
In : cur.execute(select_sql)
Out: 1L
In : res = cur.fetchone()
In : print res
(u'ss', u'ss', u'ss123 ', u'ss@chinacache.com', u'ssss', u'user', 0, datetime.datetime(2016, 11, 30, 15, 37, 48))
In :
###ipython交互模式下查询所有数据以及将数据重组成我们想要的形式
In : select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users "
In : cur.execute(select_sql)
Out: 4L
In : res = cur.fetchall()
In : print res
((u'ss', u'ss', u'ss123 ', u'ss@chinacache.com', u'ssss', u'user', 0, datetime.datetime(2016, 11, 30, 15, 37, 48)), (u'cc', u'cc', u'cC23 ', u'CC@chinacache.com', u'CCC', u'user', 0, datetime.datetime(2016, 11, 30, 16, 4, 33)), (u'admin', u'admin', u'admin ', u'admin@cc.com', u'admin', u'admin', 0, datetime.datetime(2016, 11, 30, 16, 25, 17)), (u'TT', u'TT', u'tt123 ', u'tt@cc.com', u'123223', u'sa', 0, datetime.datetime(2016, 11, 30, 16, 26, 17)))
##思考：如何将上述res更改成为我们想要的字典形式
In : fields = ['name','name_cn','password','email','mobile','role','status','create_time']
原生实现方式：
In : select_sql = "select %s from users" %','.join(fields)
In : print select_sql
select name,name_cn,password,email,mobile,role,status,create_time from users
In : cur.execute(select_sql)
Out: 4L
In : res = cur.fetchall()
In : res
Out:
((u'ss',
u'ss',
u'ss123 ',
u'ss@chinacache.com',
u'ssss',
u'user',
0,
datetime.datetime(2016, 11, 30, 15, 37, 48)),
(u'cc',
u'cc',
u'cC23 ',
u'CC@chinacache.com',
u'CCC',
u'user',
0,
datetime.datetime(2016, 11, 30, 16, 4, 33)),
(u'admin',
u'admin',
u'admin ',
u'admin@cc.com',
u'admin',
u'admin',
0,
datetime.datetime(2016, 11, 30, 16, 25, 17)),
(u'TT',
u'TT',
u'tt123 ',
u'tt@cc.com',
u'123223',
u'sa',
0,
datetime.datetime(2016, 11, 30, 16, 26, 17)))
In : users = []
In : for row in res :
...: user = {}
...: for k ,v in enumerate(fields):
...: user = row
...: users.append(user)
...:
In : users
Out:
[{'create_time': datetime.datetime(2016, 11, 30, 15, 37, 48),
'email': u'ss@chinacache.com',
'mobile': u'ssss',
'name': u'ss',
'name_cn': u'ss',
'password': u'ss123 ',
'role': u'user',
'status': 0},
{'create_time': datetime.datetime(2016, 11, 30, 16, 4, 33),
'email': u'CC@chinacache.com',
'mobile': u'CCC',
'name': u'cc',
'name_cn': u'cc',
'password': u'cC23 ',
'role': u'user',
'status': 0},
{'create_time': datetime.datetime(2016, 11, 30, 16, 25, 17),
'email': u'admin@cc.com',
'mobile': u'admin',
'name': u'admin',
'name_cn': u'admin',
'password': u'admin ',
'role': u'admin',
'status': 0},
{'create_time': datetime.datetime(2016, 11, 30, 16, 26, 17),
'email': u'tt@cc.com',
'mobile': u'123223',
'name': u'TT',
'name_cn': u'TT',
'password': u'tt123 ',
'role': u'sa',
'status': 0}]
In :
###高阶的进阶方式代码(字典生成式 + 列表生成式 一行搞定)
In : users = ) for k ,v in enumerate(fields)) for row in res ]
###关闭游标和数据库
In : cur.close()
In : data.close()
页:
[1]