Shulou(Shulou.com)06/01 Report--

Python-Day7

Learning should have a fixed position and learn with a clear goal. I hope I can persevere and gain something-leaves (ps at the beginning of the month something delayed the plan, the schedule is quite behind schedule, forget it. Do not think much, review, learn is the last word, adhere to fighting!)

Python06-the pickle module of python and the initial use of mysql database

1. Pickle module of python

1.1 the role of the pickle module

Pickle is used to serialize / deserialize data, and data can be persisted. Eg: if you have some data that you want to use directly the next time you run the program, or if you want to send the data to other programs on the network, you can package it with pickle, and the python program over there can be deserialized with pickle.

1.2 all functions of the pickle module

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.3Common methods of pickle

1.pickle.dump ("data", file name) converts the dictionary to binary

2.pickle.load ("filename") converts binary to dictionary

# # small exercises for pickle Code

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) # data-> File In [44]: fo.close () # # View the contents of the test.txt file [root@test ketang] # cat test.txt (dp0S'AA'p1S'we231'p2sS'Xman'p3S'123'p4sS'tt'p5g4sS'woniu'p6S'21' p7s [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 realize the addition, deletion, modification and query of users through pickle

# / usr/local/python#coding:utf-8''' # # use pickle to add, delete, change and query user passwords in users.txt files (see github06/pickle code for details)''from pickle import dump, load# defines the global variable filename Used to test filename = 'users.txt'## Note to write users to the users.txt file beforehand (this has been done in the environment of ipython in 1. 3) users = {' AA': 'AA123',' Xman': '123,' tt': '123,' woniu':'21, 'KK':' k123} # get all usernames and passwords, read def getUsers () from the filename file: with open (filename) 'rb') as readf: res = load (readf) return res# add user 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 = "% SV% s-> adding"% (name Password) with open (filename,'wb') as updatef: dump (tmp,updatef) return msg # # change user 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# delete user 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## finds the password corresponding to the username def findUser (name): tmp = getUsers () if name not in tmp: errmsg = "The username is not exists" return errmsg return tmp [name] # # main program entry 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%"% ('*'* 40) print updateUser ('AA1','123') print updateUser (' AA','AA123') print "delete user%"% ('* * 40) print deleteUser ('AA1') print deleteUser (' pc')

2. MySQL stores data

2.1 install mysql and mysql extensions

Yum install-y mysql mysql-server # # after installation, set boot and database password [root@test mysqlData] # chkconfig-- level 1234 mysqld on # set boot [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 startup number According to library # [root@test mysqlData] # [root@test mysqlData] # netstat-ntpl # # check the database running status Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0127.0.0.1 Active Internet connections 0.0.0.0 * LISTEN 3014/hpiod Tcp 0 0127.0.0.1199 0.0.0.0 LISTEN 3032/snmpd tcp 00 0.0.0.0 LISTEN 3416/mysqld tcp 3306 0.0.0.0. 0.0 111 0.0.0.0 LISTEN 2667/portmap tcp 0 0 0.0.0 0 15 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 0127.0.0.1 631 0.0.0.0Vol * LISTEN 3068/cupsd tcp 0 0127.0.0.1VOR 25 0.0.0.0Vol * LISTEN 3472/sendmail: acce tcp 0 0 0.0.0.0 LISTEN 2704/rpc.statd [root@test mysqlData] # set the database password # # mysqladmin-u username-p "password" password new password = = > enter the old password after entering the old password and just install the database for the first time Set password command # # mysqladmin-uroot password "123456" # # mysql extension install 1.yum install python-pip2. Check after pip install MySQL-python## installation (if import MySQLdb as mysql does not report an error message in ipython) In [1]: import MySQLdb as mysqlIn [2]:

2.2 simple operation of the database

# # create library mysql > create database reboot; # create library Query OK, 1 row affected (0.01 sec) mysql > show databases # list all libraries +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | reboot | | reboot10 | | test | +- -- + 6 rows in set (0.00 sec) mysql > use reboot # switch library, use reboot library Database changed# to create tables mysql > create table users (- > id int AUTO_INCREMENT primary key->, name varchar (20) not null comment 'username'->, name_cn varchar (50) not null comment 'Chinese name'->, password varchar (50) not null comment 'user password'->, email varchar (50) comment 'email'-> Mobile varchar (11) not null comment 'Mobile number'->, role varchar (10) not null comment '1:sa 2last_time datetime comment phptans, 3last_time datetime comment iosports4status tinyint->, create_time datetime comment 'creation time'->, last_time datetime comment 'last login time'->, unique key name (name)) engine=innodb comment 'user table'; Query OK, 0 rows affected (0.06 sec) mysql > desc users # View table information Field definition and type in the table +-+-+ | 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 # check which tables are in the library +-+ | Tables_in_reboot | +-+ | users | +-+ 1 row in set (0.00 sec) mysql > # # insert data mysql > insert into users (name,name_cn,password,email,mobile,role,status) in the mysql database Create_time) values ('ss','ss','ss123', 'ss@chinacache.com','ssss','user','0','2016-11-30 15-15-37-48') Query OK, 1 row affected (0.02 sec) mysql > mysql > select * from users # look up all the data in the table +-+- -+ | 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 Operation on mysql in python interactive environment

# # Import database In [8]: import MySQLdb as mysql## connection database In [9]: data = mysql.connect (user='root',passwd='123456',db='reboot',charset='utf8') # # create cursors, and after adding this line, the database initializes and completes In [10]: cur = data.cursor () In [11]: data.autocommit (True) # # set automatic commit More important # # add data on the ipython side # query all data in the ipython segment In [13]: cur.execute ('select * from users') Out [13]: 1LIn [14]: import datetime # Import datetimeIn [15]: # # datetime module can achieve create_time generation time in the database 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...: -% MMI% d% H:%M:%S ") In [17]: print sqlinsert into users (name,name_cn,password,email,mobile,role,status,create_time) values (' cc','cc','cC23', 'CC@chinacache.com','CCC','user'') In [18]: cur.execute (sql) Out [18]: 1L# queried all the data again and found a new piece of data In [19]: cur.execute ("select * data") ") Out [19]: 2L## queries all data in the mysql segment to confirm the new data 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 > # # find data under ipython interaction # # # query all data 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]: 2LIn [22]: res = cur.fetchall () # # find all data using cur.fetchall (), single data use cur.fetchone () In [23]: print res ((utilisation, utilisation, u'ss123', utilisation chinacache.compose, utilitsful, username, 0, datetime.datetime (2016) 11, 30, 15, 37, 48), (In [24]: select_sql = "select name,name_cn,password,email,mobile,role,status"), (11, 30, 15, 37, 48)), (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]: 1LIn [26]: res = cur.fetchone () In [27]: print res (utilisation, utilitarianism, u'ss123', utilisation chinacache.compose, utilisation, username, 0, datetime.datetime (2016, 11, 30, 15, 37) 48) In [28]: # query all data and reorganize the data into the form we want in ipython interactive mode 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]: 4LIn [38]: res = cur.fetchall () In [39]: print res ((ubiquitous, ubiquitous, u'ss123') Datetime.datetime (2016, 11, 30, 15, 37, 48), (upright CCCC, u'cC23', upright CCCCacache.compose, upright CCC, upright username, 0, datetime.datetime (2016, 11, 30, 16, 4, 33), (upright admins, utilises admins, etc.) 0, datetime.datetime (2016, 11, 30, 16, 25, 17), (upright res, upright TTX, u'tt123', upright res cc.compose, upright 123223, upright sawing, 0, datetime.datetime (2016, 11, 30, 16, 26, 17)) # # thinking: how to change the above res to the dictionary form we want In [40]: fields = ['name','name_cn','password','email','mobile'] Native implementation of 'role','status','create_time']: In [7]: select_sql = "select% s from users"%', '.join (fields) In [8]: print select_sqlselect name,name_cn,password,email,mobile,role,status,create_time from usersIn [9]: cur.execute (select_sql) Out [9]: 4LIn [10]: res = cur.fetchall () In [11]: resOut [11]: U'ss123', ubiquitous ssss, upright username, 0, datetime.datetime (2016, 11, 30, 15, 37, 48), (upright CCC, upright CCC, upright username, 0, datetime.datetime (2016, 11, 30, 16, 4, 33), (upright admins, upright admins, upright admins Datetime.datetime (2016, 11, 30, 16, 25, 17)), (upright TTDs, upright TTDs, u'tt123', upright Tubes cc.compositions, ubiquitous 123223, upright saws, 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]: usersOut [14]: [{'create_time': datetime.datetime (2016, 11, 30, 15, 37, 48) 'email': utilitarian Chinese acache.compositions,' mobile': upright ssages, 'name': utilitarian,' name_cn': uprights, 'password': u'ss123', 'role': upright usernames,' status': 0}, {'create_time': datetime.datetime (2016, 11, 30, 16, 4, 33),' email': upright CCCs chinacache.compositions, 'mobile': upright CCCs' 'name': upright,' name_cn': upright, 'password': u'cC23,' role': upright username, 'status': 0}, {' create_time': datetime.datetime (2016, 11, 30, 16, 25, 17), 'email': upright adminstration,' mobile': upright adminstration, 'name': upright adminstration,' name_cn': upright adminstration, 'password': upright adminstration' 'role': upright admings,' status': 0}, {'create_time': datetime.datetime (2016, 11, 30, 16, 26, 17),' email': utilitarian cc.compositions, 'mobile': upright 123223,' name': upright TTDs, 'name_cn': upright TTDs,' password': u'tt123', 'role': upright sa' 'status': 0}] In [15]: # Advanced mode code (dictionary generation + list generation) In [45]: users = [dict ((vMagrow [k]) for k, v in enumerate (fields)) for row in res] # close cursors and database In [15]: cur.close () In [16]: data.close ()

2.4 self-perception of the database operation

2.4.1 record of high-level usage [dictionary generation + list generation simplified code]

You can use users = [dict ((vMagrow [k]) for k, v in enumerate (fields)) for row in res] as the following code

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 builds a temporary download server

Python-m SimpleHTTPServer (html files in the current directory will be parsed by default)

III. Summary

Python database operation flow

1. Import mysql expansion module, import MySQLdb as mysql

two。 Linked database db = mysql.connect (various parameters)

3. Create a cursor, cur = db.cursor (), and since then the database is initialized and ready to work

4. Write the statement of mysql (add, delete, change and query) sql = "*"

5. Execute the sql statement cur.execute (sql) db.commit () = = > data submission

6. There are two types of queries (query select * from users in mysql)

1. Query a single record, cur.fetchone ()

two。 Query multiple records cur.fetchall ()

7. Note that at this time, viewing the data in mysql is still not updated, and you need to close the cursor.

Db.commit () = = > data submission

Store the data in memory on a table in the database.

Mysql is set to default autosubmit show variables like 'autocommit%'

Or add db.autocommit (True) to set the interactive side to automatically submit data

8. Close cursors and databases

Cur.close ()

Conn.close ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.