• Introducing modules into files

import Mysqldb

Connection object

• Used to establish connection with database
• Create object: call connect() method

Conn = connect (parameter list)

• Parameter host: connected mysql host, if the local host is'localhost'
• Parametric port: The port of the mysql host connected by default is 3306
• Parameter db: the name of the database
• Parameter user: the user name of the connection
• Parameter password: password for connection
• charset: The default encoding method for communication is'gb2312', which requires the same encoding as specified at the time of database creation. Otherwise, Chinese will be scrambled.

Object Method

• close() closes the connection
• commit() transaction, so commit is required to take effect
• rollback() transaction, abandoning previous operations
• cursor() returns the Cursor object for executing sql statements and obtaining results

Cursor object

• Execute sql statements
• Create an object: Call the cursor() method of the Connection object

cursor1=conn.cursor()

Object Method

• close() closed
• execute(operation [, parameters]) executes the statement, returning the number of rows affected
• When fetchone() executes a query statement, it retrieves the first row data of the query result set and returns a tuple.
• When next() executes a query statement, get the next row of the current row
• When fetchall() executes a query, it gets all the rows of the result set, one row constitutes a tuple, and then assembles these elements into a tuple to return.
• scroll(value[,mode]) moves the row pointer somewhere
  • mode represents the way of movement
  • The default value of mode l is relative, which means that based on the current row moving to value, value moves downward regularly and value moves upward negatively.
  • The value of mode is absolute, indicating the location based on the first data, and the location of the first data is 0.

Object attributes

• rowcount read-only property representing the number of rows affected after the last execute() execution
• Connection gets the current connection object

increase

• Create the testAdd.py file and insert a data into the student table

#encoding=utf-8
import MySQLdb
try:
    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('chief counsellor of Liu Bang')")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

modify

• Create the testUpdate.py file to modify a piece of data in the student table

#encoding=utf-8
import MySQLdb
try:
    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='Liu Bang' where id=6")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

delete

• Create the testDelete.py file and delete a piece of data from the student table

#encoding=utf-8
import MySQLdb
try:
    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

sql statement parameterization

• Create the testInsertParam.py file and insert a data into the student table

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    sname=raw_input("Please enter the name of the student:")
    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

Query a row of data

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

#encoding=utf8
import MySQLdb
try:
    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

Query for multiline data

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

#encoding=utf8
import MySQLdb
try:
    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

Example: User login

Create user table userinfos

• The table is structured as follows
  • id
  • uname
  • upwd
  • isdelete
• Note: Passwords need to be encrypted
• If md5 encryption is used, the password contains 32 characters
• If sha1 encryption is used, the password contains 40 characters, which is recommended.

create table userinfos(
id int primary key auto_increment,
uname varchar(20),
upwd char(40),
isdelete bit default 0
);

Add test data

• Insert the following data, username 123, password 123, which is the value of sha1 encryption

insert into userinfos values(0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);

Receive input and verify

• Create testLogin.py file and introduce hashlib module and MysqlHelper module
• Receive input
• According to the user name query, if not found, prompt the user name does not exist
• If it is found, the matching password is equal, and if it is equal, the login is prompted to succeed.
• If not equal, a password error is prompted.

#encoding=utf-8
from MysqlHelper import MysqlHelper
from hashlib import sha1

sname=raw_input("Please enter a user name:")
spwd=raw_input("Please input a 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)
if userinfo==None:
    print 'User name error'
elif userinfo[0]==spwdSha1:
    print 'Login successfully'
else:
    print 'Password error'

demo address: https://download.csdn.net/download/hiphopxiao/10649250