MySQL interacts with Python

First, install MySQL

Second, install the third-party module (python2.7)

Third, new database

Iv. increasing, deleting, changing and checking the database

Five, Package

1.1 First install MySQL

sudo apt-get install mysql-server mysql-client

1.2 MySQL start, stop, restart

Service MySQL startservice mysql stopservice mysql restart

1.3 Allow remote connections

1/etc/mysql/mysql.conf.d/mysqld.cnf#  bind-address=127.0.0.12'  root'@'%'mysql' with Grant Option;flush privileges;
3. Restart MySQL

2.1 Installing the MySQL module

sudo pip install Mysql-python

Pip install Pymysql (python3)

2.2 Establishing a connection to the database

1. Create object: Call the Connect () method conn=connect (parameter list)

• 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

2.3 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

Execute SQL statement

Create object: Call the cursor () method of the Connection object Cursor1=conn.cursor ()

• 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

2.4 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

3.1 Creating a new users table in the student database

Using SHA1 encryption

CREATE TABLE users (    ID int primary key auto_increment,    uname varchar),    upwd char (40 ),    isdelete bit default 0);

3.2 Adding test data

' User1 ' ' 40bd001563085fc35165329ea1ff5c5ecbdbbeef ', b'0'); INSERT into users (' id ', ' uname ', ' upwd ', ' Isdelete ') VALUES ( ' User2 ' ' 51eac6b471a284d3341d8c0c63d0f1a286262a18 ', b'0');

4.1 Adding, modifying, deleting

1  #Encoding=utf-82  ImportMySQLdb3  4  Try:5conn = MySQLdb.connect (host='localhost', port=3306, db='Student', user='Root', passwd='Root', charset='UTF8')6Cur =conn.cursor ()7  #Increase8SQL1 ="INSERT into users (Id,uname) VALUES (3, ' Zhang San ')"9  #ModifyTen  #sql = "Update users set uname= ' John Doe ' where id=4" One  #Delete A  #sql = "Delete from users where id=5" -Count =cur.execute (SQL) - Conn.commit () the cs1.close () - conn.close () -  exceptException as E: -     PrintE.message

4.2 Queries

1. Querying a row of data#Encoding=utf8ImportMySQLdbTry: Conn=mysqldb.connect (host='localhost', port=3306,db='Student', user='Root', passwd='MySQL', charset='UTF8') cur=conn.cursor () Cur.execute ('SELECT * from users where id=1') Result=Cur.fetchone ()Printresult Cur.close () conn.close ()exceptexception,e:PrintE.message2. Querying multiple rows of data#Encoding=utf8ImportMySQLdbTry: Conn=mysqldb.connect (host='localhost', port=3306,db='Student', user='Root', passwd='MySQL', charset='UTF8') cur=conn.cursor () Cur.execute ('SELECT * from Users') Result=Cur.fetchall ()Printresult Cur.close () conn.close ()exceptexception,e:PrintE.message

5.1 Package

#Encoding=utf8ImportMySQLdbImportHashlibclassmysqlhelper ():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):#Create object: Call the Connect () methodSelf.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 ()#Fetchone ():    #returns a single tuple, that is, a record (row), if there is no result    #The return    #None    #Fetchall ():    #returns multiple tuples, which returns multiple records (rows), if there is no result    #then return ()    #Note : null in MySQL, and none in Python    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 ()#Create object: Call the cursor () method of the Connection object            #executes the statement, returning the number of rows affected: Execute (operation[, parameters])Count =self.cursor.execute (SQL, params) self.conn.commit () self.close ()exceptException as E:PrintereturnCount

MySQL Package class

#Encoding=utf-8 fromMysqlhelperImportMysqlhelper fromHashlibImportSHA1defMain (): SqlHelper= Mysqlhelper ('127.0.0.1', 3306,'Student','Root','Root')    #User Loginsname = Raw_input ("Please enter user name:") Spwd= Raw_input ("Please enter your password:")    #-Update (ARG): Updates the hash object according to the parameters,    #multiple update calls are equivalent to a single update call that joins all parameters together    #-Digest (): Return hash string    #-Hexdigest (): Return hash string, 16 binary    #-Copy (): Returns a Clone objectS1 =SHA1 () s1.update (spwd) spwdSha1=s1.hexdigest () SQL="Select Upwd from users where uname=%s"params=[sname] userinfo=sqlhelper.get_one (SQL, params)ifUserInfo = =None:Print 'User name Error'    elifUserinfo[0] = =SPWDSHA1:Print 'Login Successful'    Else:        Print 'Password Error'if __name__=='__main__': Main ()

Login

MySQL interacts with Python