從零開始學(xué)Python-day7
python-Day7
郎溪網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián),郎溪網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為郎溪上1000家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站制作要多少錢，請找那個售后服務(wù)好的郎溪做網(wǎng)站的公司定做！
學(xué)習(xí)要有定位,明確目標(biāo)地去學(xué)習(xí)。希望自己能堅持下去,并有所收獲---leaves(ps月初有事耽誤計劃了,進度落后計劃蠻多了,算了。不多想,復(fù)習(xí)，學(xué)會才是硬道理,堅持fighting！)
python06 -- python的pickle模塊以及MySQL數(shù)據(jù)庫初始用
一、python的pickle模塊
1.1 pickle模塊的作用
pickle是為了序列化/反序列化數(shù)據(jù)的,可以把數(shù)據(jù)持久化存儲。eg:你有些數(shù)據(jù)想下次運行程序的時候直接使用,或者想把數(shù)據(jù)傳給網(wǎng)絡(luò)上的其他程序,就可以使用pickle打包,那邊的python程序用pickle反序列化就可以了。
1.2 pickle模塊的所有函數(shù)
In [32]: import pickle In [33]: dir(pickle) Out[33]: 'classmap', 'compatible_formats', 'decode_long', 'dispatch_table', 'dump', 'dumps', 'encode_long', 'format_version', 'load', 'loads', 'marshal', 'mloads', 're', 'struct', 'sys', 'whichmodule']
1.3 pickle的常用方法
1.pickle.dump("數(shù)據(jù)",文件名) 把字典轉(zhuǎn)為二進制
2.pickle.load("文件名") 把二進制轉(zhuǎn)為字典
##pickle代碼小練習(xí)
In [32]: import pickle
In [37]: users = {'AA': 'we231', 'Xman': '123', 'tt': '123', 'woniu': '21'}
In [39]: fo = open("test.txt",'wb')
In [42]: fo = open("test.txt",'wb')
In [43]: pickle.dump(users,fo) #數(shù)據(jù)---> 文件
In [44]: fo.close()
##查看test.txt文件內(nèi)容
[root@test ketang]# cat test.txt
(dp0
S'AA'
p1
S'we231'
p2
sS'Xman'
p3
S'123'
p4
sS'tt'
p5
g4
sS'woniu'
p6
S'21'
p7
s.[root@test ketang]#
In [9]: fi = open('test.txt','rb+')
In [10]: print pickle.load(fi)
{'AA': 'we231', 'Xman': '123', 'tt': '123', 'woniu': '21'}1.4 通過pickle實現(xiàn)用戶的增刪改查
#/usr/local/python
#coding:utf-8
'''
##使用pickle實現(xiàn)對users.txt文件中用戶密碼的增、刪、改、查功能(具體見github06/pickle代碼)
'''
from pickle import dump ,load
#定義全局變量filename,用于測試
filename = 'users.txt'
##注意事先要將users寫入到users.txt文件中(此處在1.3中ipython的環(huán)境中操作過了)
users = {'AA': 'AA123', 'Xman': '123', 'tt': '123', 'woniu': '21', 'KK': 'k123'}
#獲取所有用戶名密碼,從filename文件中讀取
def getUsers():
with open(filename,'rb') as readf:
res = load(readf)
return res
#添加用戶
def addUser(name,password):
tmp = getUsers()
if (not name) or (not password):
errmsg = "Wrong name or password"
return errmsg
if (name in tmp):
errmsg = "name is exists"
return errmsg
tmp[name] = password
msg = "%s:%s ---->adding" %(name,password)
with open(filename,'wb') as updatef:
dump(tmp,updatef)
return msg
##更改用戶
def updateUser(name,password):
tmp = getUsers()
if name not in tmp:
errmsg = 'The update username is not exist'
return errmsg
msg = "Update %s:%s ---->%s" %(tmp[name],password,name)
tmp[name] = password
with open(filename,'wb') as updatef:
dump(tmp,updatef)
return msg
#刪除用戶
def deleteUser(name):
tmp = getUsers()
if name not in tmp:
errmsg = 'The delete username is not exist'
return errmsg
msg = "Delete %s ---->%s" %('users.txt',name)
tmp.pop(name)
with open(filename,'wb') as updatef:
dump(tmp,updatef)
return msg
##查找用戶名對應(yīng)的密碼
def findUser(name):
tmp = getUsers()
if name not in tmp :
errmsg = "The username is not exists"
return errmsg
return tmp[name]
##主程序入口
if __name__ == "__main__":
print getUsers()
print findUser('')
print findUser('AA')
print "add user %s" % ('*' * 40)
print addUser('pc','pc123')
print addUser('TT','')
print addUser('','pc123')
print addUser('AA','pc123')
print "update user %s" % ('*' * 40)
print updateUser('AA1','123')
print updateUser('AA','AA123')
print "delete user %s" % ('*' * 40)
print deleteUser('AA1')
print deleteUser('pc')二、MySQL存儲數(shù)據(jù)
2.1 安裝mysql以及mysql擴展
yum install -y mysql mysql-server ##安裝完成后設(shè)置開機啟動以及數(shù)據(jù)庫密碼 [root@test mysqlData]# chkconfig --level 1234 mysqld on #設(shè)置開機啟動 [root@test mysqlData]# chkconfig --list mysqld mysqld 0:off 1:on 2:on 3:on 4:on 5:on 6:off [root@test mysqlData]# # /etc/init.d/mysqld start 啟動數(shù)據(jù)庫 # [root@test mysqlData]# [root@test mysqlData]# netstat -ntpl ##查看數(shù)據(jù)庫運行狀態(tài) Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 127.0.0.1:2208 0.0.0.0:* LISTEN 3014/hpiod tcp 0 0 127.0.0.1:199 0.0.0.0:* LISTEN 3032/snmpd tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3416/mysqld tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 2667/portmap tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 3102/httpd tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3052/sshd tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 3068/cupsd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 3472/sendmail: acce tcp 0 0 0.0.0.0:766 0.0.0.0:* LISTEN 2704/rpc.statd [root@test mysqlData]# ##設(shè)置數(shù)據(jù)庫密碼 ##mysqladmin -u 用戶名 -p"密碼" password 新密碼 ===>回車后輸入舊密碼 剛安裝數(shù)據(jù)庫的話第一次設(shè)置密碼命令 ## mysqladmin -uroot password "123456" ##mysql擴展安裝 1.yum install python-pip 2. pip install MySQL-python ##安裝完成后的檢查(在ipython中import MySQLdb as mysql 無報錯信息則成功) In [1]: import MySQLdb as mysql In [2]:
2.2 數(shù)據(jù)庫的簡單操作
##創(chuàng)建庫
mysql> create database reboot; #創(chuàng)建庫
Query OK, 1 row affected (0.01 sec)
mysql> show databases; #列出所有庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| reboot |
| reboot10 |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use reboot; #切換庫,使用reboot庫
Database changed
#創(chuàng)建表
mysql> create table users(
-> id int AUTO_INCREMENT primary key
-> ,name varchar(20) not null comment '用戶名'
-> ,name_cn varchar(50) not null comment '中文名'
-> ,password varchar(50) not null comment '用戶密碼'
-> ,email varchar(50) comment '電子郵件'
-> ,mobile varchar(11) not null comment '手機號碼'
-> ,role varchar(10) not null comment '1:sa;2:php;3:ios;4:test'
-> ,status tinyint
-> ,create_time datetime comment '創(chuàng)建時間'
-> ,last_time datetime comment '最后登錄時間'
-> ,unique key name (name) ) engine=innodb comment '用戶表';
Query OK, 0 rows affected (0.06 sec)
mysql> desc users; #查看表信息,表中字段定義及類型
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| name_cn | varchar(50) | NO | | NULL | |
| password | varchar(50) | NO | | NULL | |
| email | varchar(50) | YES | | NULL | |
| mobile | varchar(11) | NO | | NULL | |
| role | varchar(10) | NO | | NULL | |
| status | tinyint(4) | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
| last_time | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
mysql> show tables; #查看庫中有哪些表
+------------------+
| Tables_in_reboot |
+------------------+
| users |
+------------------+
1 row in set (0.00 sec)
mysql>
##mysql數(shù)據(jù)庫中插入數(shù)據(jù)
mysql> insert into users (name,name_cn,password,email,mobile,role,status,create_time) values ('ss','ss','ss123 ','ss@chinacache.com','ssss','user','0','2016-11-30 15:37:48');
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from users; #查找表中所有數(shù)據(jù)
+----+------+---------+----------+-------------------+--------+------+--------+---------------------+-----------+
| 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 |
+----+------+---------+----------+-------------------+--------+------+--------+---------------------+-----------+
1 row in set (0.00 sec)2.3 python交互環(huán)境下對mysql的操作
##導(dǎo)入數(shù)據(jù)庫
In [8]: import MySQLdb as mysql
##連接數(shù)據(jù)庫
In [9]: data = mysql.connect(user='root',passwd='123456',db='reboot',charset='utf8')
##建立游標(biāo),添加這行后數(shù)據(jù)庫就初始化完成
In [10]: cur = data.cursor()
In [11]: data.autocommit(True) ##設(shè)置自動提交，比較重要
##在ipython端添加數(shù)據(jù)
#在ipython段查詢所有數(shù)據(jù)
In [13]: cur.execute('select * from users')
Out[13]: 1L
In [14]: import datetime #導(dǎo)入datetime
In [15]: ##datetime模塊可以實現(xiàn)在數(shù)據(jù)庫中create_time生成時間
In [16]: 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 [17]: 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-30 16:04:33')
In [18]: cur.execute(sql)
Out[18]: 1L
#再次查詢所有數(shù)據(jù)發(fā)現(xiàn)新增加了一條數(shù)據(jù)
In [19]: cur.execute("select * from users;")
Out[19]: 2L
##在mysql段查詢所有數(shù)據(jù)確認新增數(shù)據(jù)
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交互下查找數(shù)據(jù)
###查詢所有數(shù)據(jù)
In [20]: select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users"
In [21]: cur.execute(select_sql)
Out[21]: 2L
In [22]: res = cur.fetchall() ##查找所有數(shù)據(jù)使用cur.fetchall(),單條數(shù)據(jù)則使用cur.fetchone()
In [23]: 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)))
###查詢單條數(shù)據(jù)
In [24]: select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users where
...: name = 'ss'"
In [25]: cur.execute(select_sql)
Out[25]: 1L
In [26]: res = cur.fetchone()
In [27]: 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 [28]:
###ipython交互模式下查詢所有數(shù)據(jù)以及將數(shù)據(jù)重組成我們想要的形式
In [36]: select_sql = "select name,name_cn,password,email,mobile,role,status,create_time from users "
In [37]: cur.execute(select_sql)
Out[37]: 4L
In [38]: res = cur.fetchall()
In [39]: 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 [40]: fields = ['name','name_cn','password','email','mobile','role','status','create_time']
原生實現(xiàn)方式：
In [7]: select_sql = "select %s from users" %','.join(fields)
In [8]: print select_sql
select name,name_cn,password,email,mobile,role,status,create_time from users
In [9]: cur.execute(select_sql)
Out[9]: 4L
In [10]: res = cur.fetchall()
In [11]: res
Out[11]:
((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 [12]: users = []
In [13]: for row in res :
...: user = {}
...: for k ,v in enumerate(fields):
...: user[v] = row[k]
...: users.append(user)
...:
In [14]: users
Out[14]:
[{'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 [15]:
###高階的進階方式代碼(字典生成式 + 列表生成式 一行搞定)
In [45]: users = [dict((v,row[k]) for k ,v in enumerate(fields)) for row in res ]
###關(guān)閉游標(biāo)和數(shù)據(jù)庫
In [15]: cur.close()
In [16]: data.close()2.4 自己感悟的數(shù)據(jù)庫操作
2.4.1 高階用法記錄 【字典生成式 + 列表生成式 簡化代碼】
如下面代碼可用 users = [ dict((v,row[k]) for k ,v in enumerate(fields))for row in res ]
In [12]: users = []
In [13]: for row in res :
...: user = {}
...: for k ,v in enumerate(fields):
...: user[v] = row[k]
...: users.append(user)
...:2.4.2 python搭建一個臨時的下載服務(wù)器;
python -m SimpleHTTPServer(默認會解析當(dāng)前目錄下的html文件)
三、總結(jié)
python數(shù)據(jù)庫操作流程
1.導(dǎo)入mysql擴展模塊, import MySQLdb as mysql
2.鏈接數(shù)據(jù)庫 db = mysql.connect(各種參數(shù))
3.建立游標(biāo), cur = db.cursor() ,自此數(shù)據(jù)庫初始化完成,可以干活了
4.編寫mysql的語句(增刪改查) sql = "*****"
5.執(zhí)行sql語句 cur.execute(sql) db.commit() ===>數(shù)據(jù)提交
6.查詢 分為兩種 (在mysql中查詢 select * from users)
1.查詢單條記錄, cur.fetchone()
2.查詢多條記錄 cur.fetchall()
7.注意此時,在mysql中查看數(shù)據(jù)還是未更新的，需要關(guān)閉游標(biāo)
db.commit() ===>數(shù)據(jù)提交
將內(nèi)存中的數(shù)據(jù)存儲到數(shù)據(jù)庫的表上。
mysql設(shè)置為默認自動提交 show variables like 'autocommit%';
或者添加db.autocommit(True) 設(shè)置交互端自動提交數(shù)據(jù)
8.關(guān)閉游標(biāo)和數(shù)據(jù)庫
cur.close()
conn.close()
名稱欄目：從零開始學(xué)Python-day7
網(wǎng)站URL：http://muchs.cn/article16/jpdigg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、域名注冊、網(wǎng)頁設(shè)計公司、企業(yè)建站、全網(wǎng)營銷推廣、品牌網(wǎng)站建設(shè)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 為什么你的網(wǎng)站不被搜索引擎收錄？ 2016-09-14
- 網(wǎng)站制作完成之后如何讓搜索引擎收錄你的站點？ 2016-10-20
- 新網(wǎng)站做了一個月SEO百度不收錄的原因在這里 2014-09-11
- 影響網(wǎng)站收錄排名的因素有哪些 2023-04-16
- 新站域名促進SEO快速收錄的方法是什么？ 2015-10-14
- 網(wǎng)站一直不收錄，外鏈是影響網(wǎng)站收錄的因素嗎？ 2022-04-28
- 怎么能使搜索引擎收錄我們的網(wǎng)站 2015-09-22
- 面對新的頁面鏈接如何使網(wǎng)站收錄和爬行？ 2014-03-31
- 那些導(dǎo)致百度收錄停滯不前的原因 2016-11-01
- 最新導(dǎo)致新網(wǎng)站不收錄的七大原因 2016-11-04
- 百度快照與收錄是否有關(guān)，我們又為什么要發(fā)外鏈？ 2016-11-10
- SEO新站優(yōu)化如何才能加快收錄？ 2015-04-15