Python operation MySQL DB instance

For Python to manipulate the MySQL database, the specific steps should be:

1. Connect the MySQL host port number database account password
2. Creating Cursors
3. Execute SQL(Note that Update,insert,delete requires a commit to take effect.) )
4. Get Results
5. Close the connection, close the cursor

First, the result obtained by default is Ganso

1conn = Pymysql.connect (host='localhost', user='Root', passwd='123456', port=3306,db='Sakila', charset='UTF8')2 #CharSet must write UTF8, cannot write utf-8; port to write int type, cannot enclose quotation marks3cur = conn.cursor ()#Create a cursor, the cursor thinks you are the warehouse administrator4Cur.execute ('SELECT * from user limit;')5res = Cur.fetchone ()#get only one result, the result is a one-dimensional ancestor, first gets to the first6 ##print (' Fetchone ', Cur.fetchone ()) # The second time it gets to the second row;7 Print(RES)8 Print('Fetchall', Cur.fetchall ())#get all the data from the beginning of the second, get the result of SQL statement execution, is a two-dimensional ancestor, it puts the result in a meta Zanzuri, each data is also a meta-ancestor9 Print(RES)Ten Print(res[0][1]) One Print('Fetchone', Cur.fetchone ())##被获取完了, so get to the None A #If you determine that only one piece of data is used Fetchone, more than one data is used Fetchall

Second, the obtained results to the dictionary, easy to use

1conn = Pymysql.connect (host='localhost', user='Root', passwd='123456', port=3306,db='Sakila', charset='UTF8')2cur = conn.cursor (cursor=pymysql.cursors.dictcursor)#Convert a meta-ancestor to a dictionary3sql ="SELECT * from user where name= ' AA '"4 cur.execute (SQL)5 Print(Cur.fetchone ())#Get Dictionary6res =Cur.fetchall ()7 Print(RES)#list in-memory dictionary8Cur.close ()#Close Cursors9Conn.close ()#Close Connection

MySQL is often used in Python, so you can write a function for the MySQL operation, and call the following when you want to use it.

1 ImportPymysql2 defOp_mysql (host,user,password,db,sql,port=3306,charset='UTF8'):3conn = Pymysql.connect (host=host,user=User,4password=Password,5db=DB,6port=Port,7charset=CharSet)8cur = conn.cursor (cursor=pymysql.cursors.DictCursor)9 cur.execute (SQL)Ten     #Select, UPDATE, delete, insert length is 6 bits One     #SELECT ASql_start = Sql[:6].upper ()#SQL's first 6-bit string to determine what type of SQL statement it is -     ifSql_start = ='SELECT': -res =Cur.fetchall () the     Else: - Conn.commit () -res ='OK' - cur.close () + conn.close () -     returnRes +sql ='SELECT * from user limit 5;' A Op_mysql ( athost='localhost', -User='Root', -password='123456', -port=3306, -db='Sakila', -charset='UTF8', inSql=sql)

Python operation MySQL DB instance