MySQL (ix)

Packaging

• Observe the previous file discovery, except that the SQL statements and parameters are different, the other statements are the same
• Create a mysqlhelper.py file, define a class

#Encoding=utf8ImportMySQLdbclassmysqlhelper ():def __init__(self,host,port,db,user,passwd,charset='UTF8'): Self.host=host Self.port=Port Self.db=DB Self.user=User self.passwd=passwd Self.charset=CharSetdefConnect (self): Self.conn=mysqldb.connect (host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset) Self.cursor=self.conn.cursor ()defClose (self): Self.cursor.close () self.conn.close ()defGet_one (self,sql,params=()): Result=NoneTry: Self.connect () self.cursor.execute (SQL, params) result=Self.cursor.fetchone () self.close ( )exceptException, E:PrintE.messagereturnresultdefGet_all (self,sql,params=()): List=()        Try: Self.connect () self.cursor.execute (sql,params) List=Self.cursor.fetchall () self.close ( )exceptexception,e:PrintE.messagereturnListdefInsert (self,sql,params=()):        returnSelf.__edit(Sql,params)defUpdate (self, SQL, params=()):        returnSelf.__edit(SQL, params)defDelete (self, SQL, params=()):        returnSelf.__edit(SQL, params)def __edit(self,sql,params): Count=0Try: Self.connect () Count=Self.cursor.execute (Sql,params) self.conn.commit () self.close ()exceptexception,e:PrintE.messagereturnCount

Add to

• Create a testinsertwrap.py file and use the encapsulated helper class to complete the insert operation

#Encoding=utf8 fromMysqlhelperImport*SQL='INSERT into students (Sname,gender) VALUES (%s,%s)'sname=raw_input ("Please enter user name:") Gender=raw_input ("please input gender, 1 for male, 0 for female") Params=[Sname,bool (gender)]mysqlhelper=mysqlhelper ('localhost', 3306,'test1','Root','MySQL') Count=Mysqlhelper.insert (sql,params)ifCount==1:    Print 'OK'Else:    Print 'Error'

Query A

• Create a testgetonewrap.py file and use the encapsulated Help class to complete querying the latest row of data operations

#Encoding=utf8 fromMysqlhelperImport*SQL='Select Sname,gender from students ORDER by id DESC'Helper=mysqlhelper ('localhost', 3306,'test1','Root','MySQL') One=helper.get_one (SQL)PrintOne

Example: User login create user table Userinfos

• Table structure is as follows
  • Id
  • Uname
  • Upwd
  • Isdelete
• Note: Passwords need to be encrypted
• If you use MD5 encryption, the password contains 32 characters
• If you use SHA1 encryption, the password contains 40 characters and is recommended in this way

CREATE TABLE Userinfos (ID int primary KEY auto_increment,uname varchar), Upwd char (+), Isdelete bit default 0);

Adding test data

• Insert the following data, the user name is 123, the password is 123, this is the value after SHA1 encryption

Insert  into Values (0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef' ,0);

Receive input and verify

• Create testlogin.py file, introduce Hashlib module, Mysqlhelper module
• Receive input
• Query based on user name, if not found prompt user name does not exist
• If the matching password is found to be equal, then the login is successful if equal
• Prompt for password error if not equal

#Encoding=utf-8 fromMysqlhelperImportMysqlhelper fromHashlibImportSha1sname=raw_input ("Please enter user name:") Spwd=raw_input ("Please enter your password:") S1=SHA1 () s1.update (spwd) spwdSha1=s1.hexdigest () SQL="Select Upwd from Userinfos where uname=%s"params=[Sname]sqlhelper=mysqlhelper ('localhost', 3306,'test1','Root','MySQL') UserInfo=Sqlhelper.get_one (sql,params)ifuserinfo==None:Print 'User name Error'elifuserinfo[0]==SPWDSHA1:Print 'Login Successful'Else:    Print 'Password Error'

MySQL (ix)