MySQL (eight)

Interactive installation with Python 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-8ImportMySQLdbTry: 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 (' Zhang Liang ')")    PrintCount Conn.commit () cs1.close () conn.close ()exceptexception,e:PrintE.message

Modify

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

  #Encoding=utf-8ImportMySQLdbTry: 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= ' bang ' where id=6")    PrintCount Conn.commit () cs1.close () conn.close ()exceptexception,e:PrintE.message

Delete

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

#Encoding=utf-8ImportMySQLdbTry: 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")    PrintCount Conn.commit () cs1.close () conn.close ()exceptexception,e:PrintE.message

Parameterization of SQL statements

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

#Encoding=utf-8ImportMySQLdbTry: Conn=mysqldb.connect (host='localhost', port=3306,db='test1', user='Root', passwd='MySQL', charset='UTF8') CS1=conn.cursor () sname=raw_input ("Please enter student's name:") Params=[sname] Count=cs1.execute ('INSERT into students (SNAME) values (%s)', params)PrintCount Conn.commit () cs1.close () conn.close ()exceptexception,e:PrintE.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=utf8ImportMySQLdbTry: 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 ()Printresult Cur.close () conn.close ()exceptexception,e:PrintE.message

Querying multiple rows of data

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

#Encoding=utf8ImportMySQLdbTry: 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 ()Printresult Cur.close () conn.close ()exceptexception,e:PrintE.message

MySQL (eight)