Python operation MySQL

Operating MySQL data in Python3 requires installing a third-party module, Pymysql, using the PIP install Pymysql installation.

In the Python2 is the MySQLdb module, there is no MYSQLDB module in the Python3, so use Pymysql.

import pymysql # Create a connection, specify the IP address of the database, account, password, port number, database to manipulate, character set Conn=Pymysql.Connect (host= ' 127.0.0.1 ' , port=3306, user= ' root ' , passwd= ' 123456 ' , db= ' data ' ,charset= ' UTF8 ' # Create cursors cursor = conn. Cursor() # Executes SQL and returns the number of rows affected effect_row = cursor. Execute("Update students set name = ' Niuhy ' where id = 1;" ) # Executes SQL and returns the number of rows affected #effect_row = cursor.execute ("Update students set name = ' Niuhy ' where id =%s;", (1,)) # Executes SQL and returns the number of rows affected    effect_row < Span class= "Crayon-o" >= cursor.executemany ( "INSERT into students (Name,age) Values (%s,%s); ", [ ( "Andashu" ,18) , ( "12345" ,20] #执行select语句 cursor. Execute("select * from students;" ) #获取查询结果的第一条数据, a tuple is returned row_1 = cursor. Fetchone() # Get top N rows of data row_2 = cursor. Fetchmany(3) # Get all the data row_3 = cursor. Fetchall() # Commit, or you cannot save new or modified data Conn. Commit() # Get the latest self-increment ID new_id = cursor. Lastrowid Print(new_id) # Close Cursors cursor. Close() # Close Connection Conn. Close() The above operation, the returned results obtained are tuples, if you want to get the result is a dictionary type, you can use the following actions Import pymysql # Create a connection, specify the IP address of the database, account, password, port number, database to manipulate, character set Conn=Pymysql.Connect (host= ' 127.0.0.1 ' , port=3306, user= ' root ' , passwd= ' 123456 ' , db= ' data ' ,charset= ' UTF8 ' # Create cursors cursor = conn. Cursor()      cursor = coon. Cursor(cursor=pymysql. Cursors. Dictcursor)#需要指定游标的类型, dictionary type # Execute SQL cursor. Execute("select * from User;" ) #获取返回结果, this time the return result is a dictionary res = cursor. Fetchone()#返回一条数据 If the result is more than one. Print(res) res2 = cursor. Fetchall()#所有的数据一起返回

Python operation MySQL