1. Install the mysqldb. Download the MySQL for Python package from the website note that there are 32-bit and 64-bit points. 2. Import MySQLdb >>>import mysqldb 3 from Python IDLE after installation is complete. Create a new database connection: >>>conn =mysqldb.connect (host = ' 127.0.0.1 ', user= ' root ', passwd= ' 123456 ', db= ' test ', port=3306, charset= ' UTF8 ') >>> Note: The value of the Hsot parameter can also be written as host = "localhost",passwd do not write a password,The port parameter requires the int type, and the above 3306 does not enclose the quotation marks. The charset to be the same as the database's character set. 4. Create a cursor: >>>cur = conn.cursor () >>> 5. Execute Insert Single data command >>>cur.execute ("INSERT into MSG (title,name,content) VALUES (' Python ', ' zz ', ' Test MySQL insert ')")1L #返回受影响的记录条数. >>>conn.commit () #需要提交事务插入才会生效. >>> 6. Inserting multiple data commands with parameters >>>sql = "INSERT into MSG (title,name,content) VALUES (%s,%s,%s)" #定义一个sql语句 >>>cur.exec Utemany (sql,[(' title01 ', ' name01 ', ' content01 '), (' Title02 ', ' name02 ', ' content02 ')])2L>>>conn.commit () >>> 7. Insert multiple pieces of data with the FOR Loop stitching SQL command. >>>sql = "INSERT into MSG (title,name,content) values" >>>for I in range: >>> SQL + = "(' id" + str (i) + "'," + "' name" + str (i) + "'," + "' content" + str (i) + "')," >>>sql = Sql[:-1] #利用切片将最后的 "," Delete. >>>cur.execute (SQL)103L>>>conn.commit () >>>8. Execute Delete command >>>cur.execute ("delete from msg where title = ' Title02 '")1L>>>conn.commit () >>> 9. Execute change command >>>cur.execute ("Update msg set title= ' Changedtitle ' where title= ' title01 '")1L>>>conn.commit () >>> 10. Execute the query command >>>cur.execute ("select * from msg")3L #返回记录条数>>>python query statements do not return the actual values accessed in the database, only the number of records obtained, so what do you want to do to get the values in the database? We need to fetch the cursor. 11. Use Fetchone () to obtain a record. The record is the next row of data for the current cursor. >>>cur.fetchone ()(1L, ' title01 ', ' name01 ', ' content01 ') 12. Use Fetchmany (size=num) to get multiple records.>>>cur.fetchmany (size=5)( (2L, ' python ', ' chenyl ', ' insert content '), (3L, ' py03 ', ' name03 ', ' content03 '), (4L, ' py04 ', ' name04 ', ' content04 ') ), (5L, ' id0 ', ' NAME0 ', ' content0 '), (7L, ' id2 ', ' name2 ', ' Content2 '))>>>note: If the query data is larger than the size parameter, gets the same number of records as the size parameter, and if the query data record is less than the value of the size parameter, only the number of data record bars queried is displayed. 13. Use Fetchall () to get all records from the next row in the cursor position until the end of the query record. >>>cur.fetchall ()105L>>>14. Use scroll () to move the cursor >>>cur.scroll (1,mode= "Absolute") >>>note: When mode is absolute, the cursor walks to the first parameter row in the table (if the first argument is 1, To the first row in the table), the number of times with Fetchone () is taken to the second row. When mode is relative, the cursor will go down one line, and if the cursor is on the third row, scroll will go to line fourth. 15. Remember to close the cursor after the database is used. Close the database connection. >>>cur.close () >>>conn.close
Note: The number of record bars and results returned by the above instance execution results are related to the records in the database.
Python MySQL operation.
