MySQL python interaction

Installing the Introduction module

• Installing the MySQL module

sudo apt-get install python-mysql

• Introducing modules into a file

import Mysqldb

Connection object

• Used to establish a connection to the database
• Create object: Call the Connect () method

conn=connect(参数列表)

• Parameter host: The connected MySQL host, if the machine is ' localhost '
• Parameter: Port of the connected MySQL host, default is 3306
• Parameter db: Name of the database
• Parameter User: The username of the connection
• Parameter password: the password for the connection
• Parameter charset: The encoding method used by the communication, the default is ' gb2312 ', the requirement is consistent with the encoding specified at database creation, otherwise Chinese will be garbled

Methods of the Object

• Close () Closes the connection
• Commit () transaction, so a commit is required before it takes effect
• Rollback () transaction, discarding the previous operation
• Cursor () Returns the cursor object for executing the SQL statement and obtaining the result

Cursor Object

• Execute SQL statement
• Create object: Call the cursor () method of the Connection object

cursor1=conn.cursor()

Methods of the Object

• Close () off
• Execute (Operation [, parameters]) executes the statement, returning the number of rows affected
• Fetchone () Executes the query statement, gets the first row data of the query result set, returns a tuple
• Next () Gets the next row of the current row when executing a query statement
• Fetchall () When executing a query, gets all the rows of the result set, one row to form a tuple, and then assembles the elements into a tuple to return
• Scroll (Value[,mode]) moves the row pointer to a location
  • Mode means the way it moves.
  • The default value of mode is relative, which means moving to value,value based on the current line and moving downward, and value being negative.
  • The value of mode is absolute, which represents the position based on the first data, where the first data is 0

Properties of the Object

• ROWCOUNT read-only property that represents the number of rows affected after the most recent execute () execution
• Connection getting the current Connection object

Increase

• Create a testinsert.py file to insert a piece of data into the student table

#encoding=utf-8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cs1=conn.cursor()    count=cs1.execute("insert into students(sname) values(‘张良‘)")    print count    conn.commit()    cs1.close()    conn.close()except Exception,e:    print e.message

Modify

• Create a testupdate.py file to modify a data for a student table

#encoding=utf-8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cs1=conn.cursor()    count=cs1.execute("update students set sname=‘刘邦‘ where id=6")    print count    conn.commit()    cs1.close()    conn.close()except Exception,e:    print e.message

Delete

• Create a testdelete.py file to delete a data from the student table

#encoding=utf-8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cs1=conn.cursor()    count=cs1.execute("delete from students where id=6")    print count    conn.commit()    cs1.close()    conn.close()except Exception,e:    print e.message

Parameterization of SQL statements

• Create a testinsertparam.py file to insert a piece of data into the student table

#encoding=utf-8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cs1=conn.cursor()    sname=raw_input("请输入学生姓名：")    params=[sname]    count=cs1.execute(‘insert into students(sname) values(%s)‘,params)    print count    conn.commit()    cs1.close()    conn.close()except Exception,e:    print e.message

Other statements

• The Execute () method of the cursor object, which can also be used to execute statements such as CREATE table
• It is recommended that you create a database table structure at the beginning of development and do not execute it here

Querying a row of data

• Create a testselectone.py file to query a student's information

#encoding=utf8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cur=conn.cursor()    cur.execute(‘select * from students where id=7‘)    result=cur.fetchone()    print result    cur.close()    conn.close()except Exception,e:    print e.message

Querying multiple rows of data

• Create a testselectmany.py file to query a student's information

#encoding=utf8import MySQLdbtry:    conn=MySQLdb.connect(host=‘localhost‘,port=3306,db=‘test1‘,user=‘root‘,passwd=‘mysql‘,charset=‘utf8‘)    cur=conn.cursor()    cur.execute(‘select * from students‘)    result=cur.fetchall()    print result    cur.close()    conn.close()except Exception,e:    print e.message

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 =utf8import Mysqldbclass Mysqlhelper (): def __init__ (self,host,port,db,user,passwd,charset= ' UTF8 '): sel F.host=host self.port=port self.db=db self.user=user self.passwd=passwd self.charset=ch Arset def Connect (self): Self.conn=mysqldb.connect (HOST=SELF.HOST,PORT=SELF.PORT,DB=SELF.DB,USER=SELF.USER,PASSW        D=self.passwd,charset=self.charset) Self.cursor=self.conn.cursor () def close (self): Self.cursor.close ()            Self.conn.close () def get_one (self,sql,params= ()): Result=none Try:self.connect () Self.cursor.execute (SQL, params) result = Self.cursor.fetchone () self.close () except Ex             Ception, E:print e.message return result def get_all (self,sql,params= ()): list= () Try: Self.connect () Self.cursor.execute (sql,params) list=self.cursor.fetchall () se Lf.close () except Exception,e:print e.message return list def insert (self,sql,params= ()): Return Self.__edit (S QL,PARAMS) def update (self, SQL, params= ()): Return Self.__edit (SQL, params) def delete (self, SQL, params= ()) : Return Self.__edit (SQL, params) def __edit (self,sql,params): count=0 Try:self.connec T () Count=self.cursor.execute (sql,params) self.conn.commit () self.close () except E Xception,e:print E.message return Count

Add to

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

#encoding=utf8from MysqlHelper import *sql=‘insert into students(sname,gender) values(%s,%s)‘sname=raw_input("请输入用户名：")gender=raw_input("请输入性别，1为男，0为女")params=[sname,bool(gender)]mysqlHelper=MysqlHelper(‘localhost‘,3306,‘test1‘,‘root‘,‘mysql‘)count=mysqlHelper.insert(sql,params)if count==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=utf8from MysqlHelper import *sql=‘select sname,gender from students order by id desc‘helper=MysqlHelper(‘localhost‘,3306,‘test1‘,‘root‘,‘mysql‘)one=helper.get_one(sql)print one

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(20),upwd char(40),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 userinfos 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-8from MysqlHelper import MysqlHelperfrom hashlib import sha1sname=raw_input("请输入用户名：")spwd=raw_input("请输入密码:")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)if userinfo==None:    print ‘用户名错误‘elif userinfo[0]==spwdSha1:    print ‘登录成功‘else:    print ‘密码错误‘

MySQL python interaction